Fluent Essays

I have attached 3 excel worksheets I need to be done by Saturday afternoon. Assignment 3 and 4 have the instructions as a picture on the excel worksheet and if you need more info for 5 let me know.Questions Type your name here, Last Name First Forecasting Concepts Exercises 28.1. The State of Arizona has asked you to examine tax burdens for residents in preparation for the next forecasting effort. Use the data in Table 28.3. (a) Prepare a graph showing the total state and local per capita taxes paid per year. Identify any outliers. (b) Calculate the level, trend and growth, and their averages. Table 28.5 State of Arizona: State and Local Per Capital Taxes Year Total State and Local Per Capita Taxes Paid ($) 1977 729.10 1978 792.06 1979 871.42 1980 928.18 1981 960.51 1982 1014.01 1983 1058.44 1984 1226.99 1985 1372.09 1986 1470.50 1987 1559.85 1988 1631.95 1989 1718.13 1990 1804.99 1991 1870.55 1992 1878.01 1993 1951.56 1994 2017.41 1995 2105.29 1996 2133.51 1997 2147.02 1998 2230.04 1999 2366.24 2000 2498.91 2001 2553.06 2002 2592.34 2003 2679.79 2004 2787.50 2005 3128.02 2006 3368.10 2007 3750.00 2008 3582.34 2009 3202.74 2010 3006.35 28.2. In the Arizona tax information in assignment 1, a typographical error in the data has been discovered. The tax per capita in 2006 was actually $6,368.10 rather than $3,368.10. (a) Prepare a graph showing the total state and local per capita taxes paid per year. Identify any outliers. (b) Using the Windsorizing technique, adjust the data and prepare a graph showing the original information and the adjusted information. 28.3. Northland is developing a forecast for its special revenue fund that does not conform to a trend. Use the data in Table 28.4 to calculate a 5-year moving average and then use that result to project revenues for this fund for the next 3 years. Year 1996 53421417.00 1997 64600858.00 1998 67053747.00 1999 24316124.10 2000 66110642.25 2001 93389137.00 2002 49686022.50 2003 20706410.20 2004 81238032.40 2005 49055130.40 2006 124202746.80 2007 86126169.60 2008 27862519.00 2009 85704726.16 2010 81483729.60 2011 45459604.80 2012 22672731.80 2013 58226034.00 28.4. Prepare a memo to the budget director of Northland providing a brief explanation of the revenue forecasts developed for FY 2014 to FY 2016. Based on the results of assignment 3, provide a recommendation. 28.5. River County receives revenue through property taxes. The budget director has asked you to build a forecast for the next 3 years. Use the data in Table 28.5. (a) Prepare a forecast using a 7-, 5-, and 3-year offset moving average of level and trend. (b) Utilize an analysis of errors to determine which forecast provides the most accurate information. Fiscal Year Table 28.5 River County: Property Tax Revenue (in thousands) 1992 34,762.30 1993 32,354.05 1994 30,971.67 1995 31,010.82 1996 32,382.92 1997 33,211.58 1998 36,407.76 1999 38,930.54 2000 43,246.20 2001 48,342.35 2002 45,042.55 2003 45,322.70 2004 44,165.63 2005 50,550.37 2006 62,73Questions Type your name here, Last Name First For SES solutions, use α = 0.1. For Holt solutions, use α = 0.95 and β = 0.01. Forecasting Intermediate Methods Exercises 1. Recall the forecast produced for assignment 3 in Module 28 for the Northland special revenue fund. The original data are provided in Table 29.1. a. Using the same initial data and assumptions, refine the forecast using the exponential smoothing method appropriate for nontrending data. b. Produce a graph showing the original data, the 5-year moving average forecast from Module 28, and the forecast produced using the exponential method. c. Which forecast would you recommend using, and why? Table 29.1 Northland: Special Revenue Fund 1996 53,421,417 1997 64,600,858 1998 67,053,747 1999 24,316,124 2000 66,110,642 2001 93,389,137 2002 49,686,023 2003 20,706,410 2004 81,238,032 2005 49,055,130 2006 124,202,747 2007 86,126,170 2008 27,862,519 2009 85,704,726 2010 81,483,730 2011 45,459,605 2012 22,672,732 2013 58,226,034 2. Recall the forecast produced in assignment 5 in Module 28 for River County’s property tax revenue. The original data are provided in Table 29.2. a. Using the same initial data and assumptions, refine the forecast using the exponential smoothing method appropriate for trending data. b. Create a matrix showing the ME and RMSE for the three scenarios of moving-average forecasts from Module 28 and the ME and RMSE produced using the exponential method. c. Which forecast would you recommend using, and why? Table 29.2 River County: Property Tax Revenue 1992 34,762.30 1993 32,354.10 1994 30,971.70 1995 31,010.80 1996 32,382.90 1997 33,211.60 1998 36,407.80 1999 38,930.50 2000 43,246.20 2001 48,342.40 2002 45,042.60 2003 45,322.70 2004 44,165.60 2005 50,550.40 2006 62,733.40 2007 67,096.60 2008 70,422.90 2009 74,674.30 2010 74,996.80 2011 68,019.60 2012 62,401.20 2013 54,584.60 2014 39,843.00 x Type your name here, Last Name First Please enter your name on the first page Put graphs here This page for graphs only. Other pages are self-checking Put solved data for Exercise 1 Here Year Total State and Local Per Capita Taxes Paid ESForecast 5 yr MA 1996 53,421,417 Graph 1 for Exercise 1 1997 64,600,858 1998 67,053,747 1999 24,316,124 2000 66,110,642 2001 93,389,137 2002 49,686,023 2003 20,706,410 2004 81,238,032 Put Graph 1 Exercise 1 Here 2005 49,055,130 2006 124,202,747 2007 86,126,170 2008 27,862,519 2009 85,704,726 2010 81,483,730 2011 45,459,605 2012 22,672,732 2013 58,226,034 Graph 2 for Exercise 1 2014 2015 2016 Put Graph 2 Exercise 1 Here Exercise 29.1 To show the cell checking, put a check here: x Grade Graph 1 Percent Correct 0\% Grade Graph 2 Special Revenue α Error Error SQ Error Error SQ Mean Year Revenue ESForecast Questions Type your name here, Last Name First Forecasting Advanced Intermediate Exercises 30.1. Businesses in the city of Techville are required to remit their sales tax collections every month to the city tax department. To ensure that business owners are in compliance, the city assesses a penalty on businesses that are out of compliance with this regulation at the beginning of each quarter. The exercise spreadsheet includes the revenue data for the collection of sales tax receipts from businesses. a. Prepare a graph of the revenue collection from July 2006 to June 2013. Does there appear to be seasonality in the data? b. Prepare an overlay graph of the revenue collection to validate your assumption. c. Go through the appropriate steps to deseasonalize the data in preparation for the next budget cycle forecasting. Upon reaching the step of having deseasonalized data for July 2006 to June 2013, prepare a chart similar to Figure 30.20 showing the original data with the deseasonalized data. 30.2. Valley County is developing a forecast for its sales tax revenue. The data they believe will increase the accuracy of the forecast include the number of single-family home permits, the average monthly rent for an apartment in a multifamily facility, and the employment levels in the county. This problem requires using a non-restricted spreadsheet. Data are provided separately a. Using the data provided and the regression functionality in Excel, develop a model (formula) for predicting sales tax revenues. b. Calculate the Durbin Watson statistic. Are the residuals correlated with each other, and why? c. Reverse the log of the predicted value (sales tax). d. Provide the predictive formula derived from these calculations. e. Calculate the predicted change in sales tax revenue next year using differences. x Type your name here, Last Name First Please enter your name on the first page Put Graphs Here Put Results Exercise 29.1c below 1a and 1c below, 1b to the right Exercise 1 Put data for graph 1b here Fiscal Year Month MonthYear Revenue Deseasonlized FY 2007 FY 2008 FY 2009 FY 2010 FY 2011 FY 2012 FY 2013 FY 2007 July Jul-06 486 Graph for Exercise 1a July FY 2007 Aug Aug-06 118,228 Aug FY 2007 Sept Sep-06 738,995 Sept FY 2007 Oct Oct-06 40,469 Oct FY 2007 Nov Nov-06 194,269 Nov FY 2007 Dec Dec-06 522,234 Dec FY 2007 Jan Jan-07 7,241 Jan FY 2007 Feb Feb-07 163,667 Feb FY 2007 Mar Mar-07 543,571 Mar FY 2007 Apr Apr-07 7,700 Apr FY 2007 May May-07 72,777 May FY 2007 Jun Jun-07 654,310 Jun FY 2008 July Jul-07 600 FY 2008 Aug Aug-07 132,025 Put Graph for Exercise 1a Here FY 2008 Sept Sep-07 694,925 Gra