Spring91a MPA Computer Proficiency Exam Exam #3 On your disk you should find an EXCEL file with the filename “Grants.” This spreadsheet contains data on Federal grants-in-aid to state and local...

MPA Computer Proficiency Exam Exam #3

Spring91a MPA Computer Proficiency Exam Exam #3 On your disk you should find an EXCEL file with the filename “Grants.” This spreadsheet contains data on Federal grants-in-aid to state and local governments for selected fiscal years, by type of grant. Perform the following operations on this data. 1. Calculate the total amount of federal grants for each year. 2. Convert the data in these into constant dollars. The formula for converting current dollars into constant dollars is: constant dollars = current dollars/(CPI-U * 0.01) Modify the cell format for the constant dollar columns so that only one decimal place is displayed. (You should create four new columns (general purpose, block, categorical, total) with the data in constant dollar values.) 3. For each year, calculate the percent of total funds that are disbursed by each grant type. Modify the cell format for the percentage data by selecting the percent format and display only one decimal place. (You should create three new columns (general purpose, block, categorical). You can use either the current or constant dollar values for this operation.) 4. Construct a pie chart using the percent data for the year 1995. Your pie chart should indicate the percent of Federal grants-in-aid that were disbursed by each type of grant. (Be sure to provide a legend or labels for the slices of your pie chart.) 5. Construct a memo in Word that reports to Mr. I. M. Rich the trend in federal grants over the period for which you have data. Answer the following questions in your memo: a. Has the Federal government significantly decreased the amount of grants-in-aid to state and local governments since 1975? (Look at your Total column for the constant dollar data.) b. Which category of grant was is used to distribute Federal grants to state and local governments in Fiscal Year 1995? Import your pie chart into your Word document to provide support for your answer to the second question. Save both your EXCEL and Word documents with the filename: GRANTxxx (where xxx are your initials). Federal Outlays (Current $, billion) Year CPI-U General Purpose Blocks Categorical 1975 53.8 7.0 4.6 38.2 1978 65.2 9.6 11.5 56.8 1981 82.4 6.8 10.0 77.9 1984 103.9 6.8 13.0 77.8 1987 113.6 2.1 13.1 93.2 1989 124.0 2.3 12.7 106.9 1991 136.2 2.2 16.4 133.4 1993 144.5 2.1 20.5 171.1 1995 (est.) 152.4 2.3 22.8 202.9