Oct. 24, 2007 LAB #4 ECON 140A/240A-1 L. PhillipsScatterplots and Regression I. Creating Charts from the UC Budget FileOpen the UC Budget FileA. Trends in the UC Budget, Nominal $1. Select Fiscal Year and UC Budget, Millions of nominal $ (columns A6:A42-C6:C42)2. Select Insert in the menu bar, select Chart, and select line and click the next button3. check that the data are in columns and click the next button4. Select the legend tab and deactivate the legend button5. Select the titles taba. for the title type: UC Budget, General Fund Component, Millions of Nominal $b. for the category axis title type: Fiscal Yearc. for the value axis title type: Millions $6. Hit the next button7. Insert the chart as a new sheet8. Select the chart by clicking on it9. If you want to add a data value, for example for the last yeara. click on the line to select it and then click again on the peak data pointb. go to the menu bar and select format selected data point, select the Data Labels tab, and select the Show Value buttonc. You can select the value and position it and format its font size10. Selecting the Chart Title allows you to edit it, repositioning text etc.11. To delete something from the chart, for example the title for the value axis,a. select it, choose edit from the menu bar, choose clear and select allb. to type a new title, select insert, titles, value axis from the menu bar13. To delete the chart, go to edit in the menu bar and choose delete sheet 14. To add a trend line, select the data points, go to the chart menu and select addtrendlinea.select linear for typeOct. 24, 2007 LAB #4 ECON 140A/240A-2 L. PhillipsScatterplots and Regression b. go to the format menu and choose selected trendline, and the options tabi. check the display equation on chartii. check the display the R-squared value on chartSource: Governor’s Budget Summary 2007-08, Appendix, Schedule 9, URL: http://www.dof.ca.gov/B. Trends in UC’s Share of General Fund Expenditures1. If you have not yet done so, divide the UC budget by total General Fund expenditures to obtain UC Budget Share2. Select fiscal year and holding down the control key, select UC Budget Share(this selects nonadjacent columns)3. Select Insert in the menu bar, select Chart, and select line for type of chart4. check that the data are in columns and click the next button5. Select the legend tab and deactivate the legend button6. Select the titles taba. for the title type: UC Budget as Percent of Total General Fundb. for the category axis title type: Fiscal Yearc. for the value axis title type: Percent7. Click the next button8. Insert the chart as a new sheet9. If you do not want the plot area shadeda. click on plot areb. go to Format in the menu bar and choose selected plot areac.under area choose the none button10. To add a trend line, select the data points, go to the chart menu and select addtrendlined. select linear for typee.go to the format menu and choose selected trendline, and the options tabiii.check the display equation on chartiv. check the display the R-squared value on chartOct. 24, 2007 LAB #4 ECON 140A/240A-3 L. PhillipsScatterplots and Regression Source: Governor’s Budget Summary 2007-08, Appendix, Schedule 6, URL: http://www.dof.ca.gov/C. Scatterplot of Total General Fund Expenditures Versus California Personal Income 1. If you have not done so yet in your data set, convert total General Fund Expenditures from millions into billions by dividing by 1000 so both variables arein comparable units2. Select General Fund Expenditures (in Billions) first3. Hold down the control key and select State Personal Income (in Billions) 4. Select Insert in the menu bar, select Chart, and select scatter chart 5. click the next button6. Select the no legend buttona. for the title type: Total General Fund Expenditures Vs. CAPersonal Income, Billions Nominal $b. for the category axis type CA Personal Income, Billionsc.for the value axis type General Fund, Billions7. Select the finish button8. To add a trend line, select the data points, go to the chart menu and select add trendlinea.select linear for typeb. go to the format menu and choose selected trendline, and the options tabi. check the display equation on chartii. check the display the R-squared value on chartEstimated slope: for every nominal dollar increase in CA Personal Income, State General Fund Expenditures increases by 6.6 cents.9. Regression results in tabular form in Excel a. go to the tools menu, select Data Analysis, select regressioni. put in the input range for y (General Fund)ii. put in input range foe x (Personal Income)Oct. 24, 2007 LAB #4 ECON 140A/240A-4 L. PhillipsScatterplots and Regression iii.Select new worksheet ply, residuals, residual plots, line fit plotsII. Exercises1. Has the UC budget returned to its trend line up to 1990-91?2. Plot the relative size of the state budget, i.e. state General Fund expenditures as a percent of CA Personal Income, against fiscal year. Comment on the observed trends and the political significance of this
View Full Document
Unlocking...