Homework SourseHowever your consulting manager at ECG wants to go the next
However, your consulting manager at ECG wants to go the next step and investigate another forecasting method. It is important to do a thorough job for the client, and you have the expertise to analyze different forecasting methods. You have decided to look at the sales data for client’s lottery app as a single data set and use a time series analysis, namely SES, single exponential smoothing.
Using Excel, use the forecasted sales from Case 3 to compute the MAPE, by doing the following:
Case 3 Data
Following are the data for website hits and app sales (number of the Lottery apps.)
Month
Hits
Sales
Jan
1200
420
Feb
820
545
Mar
1151
301
Apr
1050
510
May
1180
485
Jun
1047
525
Jul
1102
460
Aug
1054
500
Sep
1254
402
Oct
1071
584
Nov
1120
422
Dec
1287
514
Jan
1164
441
Feb
1159
421
Mar
1298
355
April 1298 512
Calculate the MAPE for the first 12 months (assume the forecast for Month 1 – or January – is equal to January’s actual sales). Use 0.15 and 0.90 alphas.
Using the forecasted sales for Feb - April (taken from the Case 3 Linear Regression exercise), compute the MAPE by comparing actual sales for each month, or Y(t) to forecasted sales, or F(t). Compare this 3-month MAPE to the two MAPE values you calculated in your SES analysis above. Use the following table:
Month
Sales, Y(t)
Sales F(t)
Y(t) - F(t)
PE
APE
February
?
?
?
?
?
March
?
?
?
?
?
April
?
?
?
?
?
?
?
?
ME
MPE
MAPE
Then write a report to your boss that briefly describes the results that you obtained. Using MAPE values, make a recommendation on which method appears to be more accurate -- SES or Linear Regression.
Data: Use the data that you previously have generated from your analyses in Case 3
| Month | Hits | Sales |
| Jan | 1200 | 420 |
| Feb | 820 | 545 |
| Mar | 1151 | 301 |
| Apr | 1050 | 510 |
| May | 1180 | 485 |
| Jun | 1047 | 525 |
| Jul | 1102 | 460 |
| Aug | 1054 | 500 |
| Sep | 1254 | 402 |
| Oct | 1071 | 584 |
| Nov | 1120 | 422 |
| Dec | 1287 | 514 |
| Jan | 1164 | 441 |
| Feb | 1159 | 421 |
| Mar | 1298 | 355 |
Solution
Using 0.15 alpha
using 0.9 alpha
| Sales | Forecast | Error | Absolute error | Square of absolute error | Absolute error/Sales |
| 420 | 420 | 0 | 0 | 0 | 0 |
| 545 | 420 | 125 | 125 | 15625 | 0.229358 |
| 301 | 526.25 | -225.25 | 225.25 | 50737.56 | 0.748339 |
| 510 | 334.7875 | 175.2125 | 175.2125 | 30699.42 | 0.343554 |
| 485 | 483.7181 | 1.281875 | 1.281875 | 1.643204 | 0.002643 |
| 525 | 484.8077 | 40.19228 | 40.19228 | 1615.419 | 0.076557 |
| 460 | 518.9712 | -58.9712 | 58.97116 | 3477.597 | 0.128198 |
| 500 | 468.8457 | 31.15433 | 31.15433 | 970.592 | 0.062309 |
| 402 | 495.3269 | -93.3269 | 93.32685 | 8709.901 | 0.232156 |
| 584 | 415.999 | 168.001 | 168.001 | 28224.33 | 0.287673 |
| 422 | 558.7999 | -136.8 | 136.7999 | 18714.2 | 0.32417 |
| 514 | 442.52 | 71.48002 | 71.48002 | 5109.394 | 0.139066 |
| 441 | 503.278 | -62.278 | 62.278 | 3878.549 | 0.14122 |
| 421 | 450.3417 | -29.3417 | 29.3417 | 860.9353 | 0.069695 |
| 355 | 425.4013 | -70.4013 | 70.40125 | 4956.337 | 0.198313 |
| 512 | 365.5602 | 146.4398 | 146.4398 | 21444.62 | 0.286015 |
| ME | MAD | MSE | MAPE | ||
| 5.14954406 | 89.6956666 | 12189.09 | 0.213305 |
