Forecasting Assignment Calculations in Excel. Report in Word 1. The table below
ID: 365634 • Letter: F
Question
Forecasting Assignment Calculations in Excel. Report in Word 1.
The table below shows monthly sales of a motorcycle store during the last two years. The commission for each motorcycle sale is $1,000. The manager wants to estimate how much money he will need for month 25.
Submit a nice report in word answering each question clearly. Explain your answer. Include an excel table with all calculations as an appendix.
1
180
a. Forecast motorcycle sales for month 25 using:
a. Plot your data
b. Naïve method.
c. MA3
d. MA4
e. ES with best alpha
f. AES with best beta
g. Which method is better and why?
Note: Be sure to use all 24 observations in your analysis
Month1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Sales 195 210 190 170180
156 134 155 145 165 128 135 155 120 135 110 122 97 85 110 85 78 66 45Explanation / Answer
b.Naive method states that tomorrow will be like today. Forecasting for any period is previous period's actual value.
Therefore forecast for 25th month is actual 45 for the 24th period.
c. MA3 stands for Simple Moving Average for immediate last three periods, therefore no forecasts for the first three periods.
d. MA4 stands for moving averages of immediate last four periods.
FORECASTS Month Sales Naïve MA3 MA4 1 195 2 210 195 Naïve F(t) = A(t-1) 3 190 210 4 170 190 198 MA3 F(t) = (A(t-1)+A(t-2)+A(t-3))/3 5 180 170 190 191 6 156 180 180 188 MA4 F(t) = (A(t-1)+A(t-2)+A(t-3)+A(t-4))/4 7 134 156 169 174 8 155 134 157 160 9 145 155 148 156 10 165 145 145 148 11 128 165 155 150 12 135 128 146 148 13 155 135 143 143 14 120 155 139 146 15 135 120 137 135 16 110 135 137 136 17 122 110 122 130 18 97 122 122 122 19 85 97 110 116 20 110 85 101 104 21 85 110 97 104 22 78 85 93 94 23 66 78 91 90 24 45 66 76 85 25 45 63 69