Exercises 1. Expenditure Analysis in Current Dollars a. You are

Question Description:

25

Exercises 1. Expenditure Analysis in Current Dollars a. You are a fiscal analyst in the city’s budget office. Complete the current-dollar portion of Table 4.4 analyzing the Health Department’s expenditures; use the available data in Table 4.1. and Write a memo from you (as a fiscal analyst) to the budget director explaining the trends. Exercises 2. Expenditure Analysis in Constant Dollars a. In this exercise, you will complete the rest of the spreadsheet in Table 4.4, conducting the same analysis you did above, only you will convert all the dollars to constant dollars. This exercise allows you to adjust a spreadsheet for inflation. The technique is to convert current dollar expenditures using cost of living indexes to constant (real) dollars by removing inflation as a factor. b. After you complete the spreadsheet calculating constant dollars, write a paragraph from you, as a fiscal analyst, to OMB’s budget director. In it you will explain what differences you found when you controlled for inflation compared with the first exercise when you did not control for inflation. Attached is a copy of the spreadsheet. . copy of Budget tools chapter 4 exercis 1 and 2.xls Table 4.1 Expenditures for Health in New York City, Fiscal Years 1996-2007, in Thousands of Dollars Fiscal years FY 1996 FY 1997 FY 1998 FY 1999 FY 2000 FY 2001 FY 2002 FY 2003 FY 2004 FY 2005 FY 2006 FY 2007 Health Mental Health Department Department \$419,308 \$319,275 420,275 345,284 472,030 396,095 491,603 437,292 790,726 251,446 906,947 295,114 1,049,135 256,064 1,414,923 0 1,441,247 0 1,432,047 0 1,467,786 0 1,513,879 0 Hospitals Department \$1,090,173 682,924 684,601 722,094 735,127 757,023 826,307 826,572 976,875 992,136 1,290,016 758,603 New York City’s total health New York City’s total expenditures expenditures \$1,828,756 \$32,066,586 1,448,483 33,736,152 1,552,726 34,923,250 1,650,989 35,858,612 1,777,299 37,879,886 1,959,084 40,226,977 2,131,506 40,860,000 2,241,495 44,340,229 2,418,122 47,292,395 2,424,183 52,789,712 2,757,802 53,999,075 2,272,482 58,705,982 CPI 166.9 170.8 173.6 177 182.5 187.1 191.9 197.8 204.8 212.7 220.7 229.5 Notes: The Mental Health Department was merged with the Health Department in FY 2003. CPI = consumer price index. CPI Adjusted Index: CUURA101SA0, New York-Northern New Jersey-Long Island, NY-NJ-CT-PA; base period 1982-1984 = 100. CPI figure for FY 2007 is November 2007. Figure 4.1: Holt Exponential Smoothing Direction of time 9,400.00 8,400.00 7,400.00 6,400.00 V al ue axis 5,400.00 4,400.00 Forecast 3,400.00 2,400.00 1,400.00 Time index Xt Column D 2013 2012 2011 2010 2009 2008 2007 2005 2006 2004 2003 2002 2001 2000 1999 1998 1997 1996 1995 1994 1993 1992 1991 1990 1989 1988 1986 1987 1985 1984 1983 1982 1981 1980 400.00 Table 4.2 Holt Exponential Smoothing Period Period 0 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 Data Xt 2,210.0 2,320.6 2,488.3 2,767.7 3,082.9 3,348.8 3,430.8 3,943.7 3,656.3 4,083.9 4,021.0 4,254.9 4,772.5 4,979.3 4,968.9 4,894.3 5,173.8 5,648.2 6,547.3 6,871.8 6,749.2 7,209.0 5,762.6 5,661.4 6,653.4 7,635.3 8,245.0 8,647.8 Ft 2,097.36 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! ME #VALUE! et 112.68 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! α β #VALUE! #VALUE! St Bt 1,855.08 242.28 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!