Oct 24 2007 LAB 4 ECON 140A 240A 1 Scatterplots and Regression L Phillips I Creating Charts from the UC Budget File Open the UC Budget File A Trends in the UC Budget Nominal 1 Select Fiscal Year and UC Budget Millions of nominal columns A6 A42C6 C42 2 Select Insert in the menu bar select Chart and select line and click the next button 3 check that the data are in columns and click the next button 4 Select the legend tab and deactivate the legend button 5 Select the titles tab a for the title type UC Budget General Fund Component Millions of Nominal b for the category axis title type Fiscal Year c for the value axis title type Millions 6 Hit the next button 7 Insert the chart as a new sheet 8 Select the chart by clicking on it 9 If you want to add a data value for example for the last year a click on the line to select it and then click again on the peak data point b go to the menu bar and select format selected data point select the Data Labels tab and select the Show Value button c You can select the value and position it and format its font size 10 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 all b to type a new title select insert titles value axis from the menu bar 13 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 add trendline a select linear for type Oct 24 2007 LAB 4 ECON 140A 240A 2 Scatterplots and Regression L Phillips b go to the format menu and choose selected trendline and the options tab i check the display equation on chart ii check the display the R squared value on chart Source Governor s Budget Summary 2007 08 Appendix Schedule 9 URL http www dof ca gov B Trends in UC s Share of General Fund Expenditures 1 If you have not yet done so divide the UC budget by total General Fund expenditures to obtain UC Budget Share 2 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 chart 4 check that the data are in columns and click the next button 5 Select the legend tab and deactivate the legend button 6 Select the titles tab a for the title type UC Budget as Percent of Total General Fund b for the category axis title type Fiscal Year c for the value axis title type Percent 7 Click the next button 8 Insert the chart as a new sheet 9 If you do not want the plot area shaded a click on plot are b go to Format in the menu bar and choose selected plot area c under area choose the none button 10 To add a trend line select the data points go to the chart menu and select add trendline d select linear for type e go to the format menu and choose selected trendline and the options tab iii check the display equation on chart iv check the display the R squared value on chart Oct 24 2007 LAB 4 ECON 140A 240A 3 Scatterplots and Regression L Phillips 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 are in comparable units 2 Select General Fund Expenditures in Billions first 3 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 button 6 Select the no legend button a for the title type Total General Fund Expenditures Vs CA Personal Income Billions Nominal b for the category axis type CA Personal Income Billions c for the value axis type General Fund Billions 7 Select the finish button 8 To add a trend line select the data points go to the chart menu and select add trendline a select linear for type b go to the format menu and choose selected trendline and the options tab i check the display equation on chart ii check the display the R squared value on chart Estimated 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 regression i 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 Scatterplots and Regression L Phillips iii Select new worksheet ply residuals residual plots line fit plots II Exercises 1 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 plot
View Full Document
Unlocking...