Plese, give me actual calculations not just graphs I\'m trying to learn how this
ID: 3059856 • Letter: P
Question
Plese, give me actual calculations not just graphs I'm trying to learn how this is done. Also please answer all of the questions.
Question 1 contains the actual values for 12 periods (listed in order, 1-12). In Excel, create forecasts for periods 6-13 using each of the following methods: 5 period simple moving average; 4 period weighted moving average (0.63, 0.26, 0.08, 0.03); exponential smoothing (alpha = 0.23 and the forecast for period 5 = 53); linear regression with the equation based on all 12 periods; and quadratic regression with the equation based on all 12 periods. Round all numerical answers to two decimal places.
The actual values for 12 periods (shown in order) are:
(1)45,(2)52,(3)48,(4)59,(5)55,(6)57,(7)64,(8)63,(9)72,(10)66,(11)73,(12)73
Q1.Using a 5 period simple moving average, the forecast for period 13 will be:
Q2. Using the 4 period weighted moving average, the forecast for period 13 will be:
Q3. With exponential smoothing, the forecast for period 13 will be:
Q4. With linear regression, the forecast for period 13 will be:
Q5. With quadratic regression, the forecast for period 13 will be:
Q6. Considering only the forecasts for period 6-12, what is the lowest MAD value for any of the methods?
Question 1 contains the actual values for 12 periods (listed in order, 1-12). In Excel, create forecasts for periods 6-13 using each of the following methods: 5 period simple moving average; 4 period weighted moving average (0.63, 0.26, 0.08, 0.03); exponential smoothing (alpha = 0.23 and the forecast for period 5 = 53); linear regression with the equation based on all 12 periods; and quadratic regression with the equation based on all 12 periods. Round all numerical answers to two decimal places.
Explanation / Answer
Please find below the answers to the questions (spreadsheet): -
Period Data 5-SMA 4-WMA ES Trend Abs_Error_SMA Abs_Error_WMA Abs_Error_ES Abs_Error_Trend 1 45 46.75641026 2 52 49.27039627 3 48 51.78438228 4 59 54.2983683 5 55 55.16 53 56.81235431 6 57 51.8 55.39 53.46 59.32634033 5.2 1.61 3.54 2.326340326 7 64 54.2 56.37 54.2742 61.84032634 9.8 7.63 9.7258 2.15967366 8 63 56.6 61.31 56.511134 64.35431235 6.4 1.69 6.488866 1.354312354 9 72 59.6 62.54 58.00357318 66.86829837 12.4 9.46 13.99642682 5.131701632 10 66 62.2 68.57 61.22275135 69.38228438 3.8 2.57 4.777248651 3.382284382 11 73 64.4 67.26 62.32151854 71.8962704 8.6 5.74 10.67848146 1.103729604 12 73 67.6 70.8 64.77756927 74.41025641 5.4 2.2 8.222430725 1.41025641 13 69.4 72.41 66.66872834 MAD MAD MAD MAD 7.371428571 4.414285714 8.204179094 2.40975691 Trend SUMMARY OUTPUT Regression Statistics Multiple R 0.947401594 R Square 0.897569781 Adjusted R Square 0.887326759 Standard Error 3.211521363 Observations 12 ANOVA df SS MS F Significance F Regression 1 903.777972 903.777972 87.62743946 2.90172E-06 Residual 10 103.1386946 10.31386946 Total 11 1006.916667 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 44.24242424 1.976554764 22.38360659 7.11964E-10 39.83838578 48.64646271 39.83838578 48.64646271 Period 2.513986014 0.268560908 9.360952914 2.90172E-06 1.915595021 3.112377007 1.915595021 3.112377007