Question 3 The Ministry of Finance in St. Vincent is preparing for the annual bu
ID: 448198 • Letter: Q
Question
Question 3
The Ministry of Finance in St. Vincent is preparing for the annual budgetary exercise and wants to project the collections from Value added tax (VAT) for the next year. Using very efficient collection strategies they have been able to increase their annual revenue by approximately $10 million over the past 10 years. The recent international recession has slowed the growth rate of collections in the past few years. The annual revenue, in millions of dollars, for the previous 10 years in shown below. Year Revenue (mn)
Year
Revenue (mn)
1
8.53
2
10.54
3
12.98
4
14.11
5
16.31
6
17.21
7
18.37
8
18.45
9
18.4
10
18.43
(a) Using a weighted moving average with three periods, determine the revenue collections for period II. Use 3,2, and 1 for the weights of the most recent, second most recent, and third most recent periods, respectively. Compute the MAD, MSE AND MPE for this method
(b) Use exponential smoothing with a smoothing constant of 0.30 to forecast the revenue – Assume that last period’s forecast for year is equal to actual to begin the procedure. Is this an improvement over the weighted average – use MAD only.
(c) Use regression analysis to estimate the line of best fit-use the manual method with the formulas shown below. And fine the forecast revenue for period II.
Year
Revenue (mn)
1
8.53
2
10.54
3
12.98
4
14.11
5
16.31
6
17.21
7
18.37
8
18.45
9
18.4
10
18.43
Explanation / Answer
a Error Error^2 Year Revenue (mn) 3 Month MA Absolute Error Square Error |Error| A B C = A-B D = C^2 E 1 8.53 2 10.54 3 12.98 4 14.11 11.43 2.69 7.2092 2.69 5 16.31 13.14 3.17 10.0595 3.17 6 17.21 15.02 2.19 4.7888 2.19 7 18.37 16.39 1.98 3.9072 1.98 8 18.45 17.64 0.81 0.6561 0.81 9 18.40 18.22 0.18 0.0336 0.18 10 18.43 18.41 0.02 0.0003 0.02 11.03 26.65 11.03 Mean Absolute Deviation (MAD) = |Actual - Forecast| / n MAD = 11.03 / 7 = 1.5762 Mean Square Error (MSE) = (Actual - Forecast)^2 / (n -1) MSE = 26.65 / 6 = 4.4425 Mean Absolute Percentage Error (MAPE) = [|Actual - Forecast| / Actual * 100]/n MPE = 11.03 / 121.28 * 100% / 7 = 1.2996% 3 Month Moving Average Calculation Month 4 = (8.53 * 1 + 10.54 *2 + 12.98 * 3) / (1+2+3) = 11.43 Month 5 = (10.54 *1 + 12.98 * 2 + 14.11 *3) / (1+2+3) = 13.14 b Forecast Using Exponential Smoothing has following formula Next forecast = Previous forecast + alpha * (Actual -Previous forecast) alpha is the smoothing factor = 8.43 (given) Forecast Calculation Year 2 = 8.53 + 0.30 (10.54 - 8.53) = 9.13 Year 3 = 9.13 + 0.30 (12.98 - 10.54) = 10.29 Error Error^2 Year Revenue (mn) Exponential Forecast Absolute Error Square Error |Error| A B C = A-B D = C^2 E 1 8.53 8.53 0.00 0.0000 0.00 2 10.54 9.13 1.41 1.9796 1.41 3 12.98 10.29 2.69 7.2517 2.69 4 14.11 11.43 2.68 7.1611 2.68 5 16.31 12.90 3.41 11.6501 3.41 6 17.21 14.19 3.02 9.1159 3.02 7 18.37 15.44 2.93 8.5584 2.93 8 18.45 16.35 2.10 4.4261 2.10 9 18.40 16.96 1.44 2.0669 1.44 10 18.43 17.40 1.03 1.0555 1.03 20.70 53.27 20.70 Mean Absolute Deviation (MAD) = |Actual - Forecast| / n MAD = 20.70/10 = 2.07 No weighted average was better as MAD is higher under this method c Year (X) Revenue (mn) (Y) X^2 Y^2 XY A B C = A^2 D = B^2 E = A*B 1 8.53 1 72.76 8.53 2 10.54 4 111.09 21.08 3 12.98 9 168.48 38.94 4 14.11 16 199.09 56.44 5 16.31 25 266.02 81.55 6 17.21 36 296.18 103.26 7 18.37 49 337.46 128.59 8 18.45 64 340.40 147.6 9 18.40 81 338.56 165.6 10 18.43 100 339.66 184.3 55 153.33 385 2469.71 935.89 Regression Equation = a + Bx a = [(y - bx) / n b = [nxy - (x * y)] / [(n * x^2) - (x)^2)] n = 10 b = (10 * 935.89 - 55*153.33) / (10 *55^2 - 385) = 1.1221 a = (153.33 - 1.221 * 55) / 10 = 9.1613 The equation is given as Y = 9.1613 + 1.1221x Forecast Year a bx a+bx A B C = A * 1.1221 B+C 1 9.1613 1.1221 10.28 2 9.1613 2.2442 11.41 3 9.1613 3.3664 12.53 4 9.1613 4.4885 13.65 5 9.1613 5.6106 14.77 6 9.1613 6.7327 15.89 7 9.1613 7.8548 17.02 8 9.1613 8.9770 18.14 9 9.1613 10.0991 19.26 10 9.1613 11.2212 20.38
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.