Problem:
The excel file closing stock prices provides data for four stocks over a 6 month period.
A. Develop a spreadsheet module for forecasting each of the stock prices using simple moving average and simple exponential smoothing.
B. Using MAD, MSE, and MAPE as guidance, find the best number of moving average periods and best smoothing constant for exponential smoothing. (You might consider using data tables to facilitate your search).
| Closing Stock Prices |
|
|
|
|
|
|
|
|
| Date |
GOOG |
YHOO |
CSCO |
AAPL |
| 02-01-08 |
$ 657.00 |
$ 23.16 |
$ 26.12 |
$ 180.05 |
| 07-01-08 |
$ 638.25 |
$ 23.36 |
$ 25.87 |
$ 172.69 |
| 14-01-08 |
$ 600.25 |
$ 20.78 |
$ 24.30 |
$ 161.36 |
| 22-01-08 |
$ 566.40 |
$ 21.94 |
$ 24.20 |
$ 130.01 |
| 28-01-08 |
$ 515.90 |
$ 28.38 |
$ 24.94 |
$ 133.75 |
| 04-02-08 |
$ 516.69 |
$ 29.20 |
$ 23.54 |
$ 125.48 |
| 11-02-08 |
$ 529.64 |
$ 29.66 |
$ 23.30 |
$ 124.63 |
| 19-02-08 |
$ 507.80 |
$ 28.42 |
$ 23.60 |
$ 119.46 |
| 25-02-08 |
$ 471.18 |
$ 27.78 |
$ 24.39 |
$ 125.02 |
| 03-03-08 |
$ 433.35 |
$ 29.03 |
$ 24.11 |
$ 122.25 |
| 10-03-08 |
$ 437.92 |
$ 26.71 |
$ 24.32 |
$ 126.61 |
| 17-03-08 |
$ 433.55 |
$ 27.66 |
$ 24.77 |
$ 133.27 |
| 24-03-08 |
$ 438.08 |
$ 28.99 |
$ 24.08 |
$ 143.01 |
| 31-03-08 |
$ 471.09 |
$ 28.36 |
$ 24.39 |
$ 153.08 |
| 07-04-08 |
$ 457.45 |
$ 28.34 |
$ 23.38 |
$ 147.14 |
| 14-04-08 |
$ 539.41 |
$ 28.43 |
$ 24.51 |
$ 161.04 |
| 21-04-08 |
$ 544.06 |
$ 26.80 |
$ 25.60 |
$ 169.73 |
| 28-04-08 |
$ 581.29 |
$ 28.67 |
$ 26.75 |
$ 180.94 |
| 05-05-08 |
$ 573.20 |
$ 25.93 |
$ 25.49 |
$ 183.45 |
| 12-05-08 |
$ 580.07 |
$ 27.66 |
$ 26.51 |
$ 187.62 |
| 19-05-08 |
$ 544.62 |
$ 27.72 |
$ 25.10 |
$ 181.17 |
| 27-05-08 |
$ 585.80 |
$ 26.76 |
$ 26.72 |
$ 188.75 |
| 02-06-08 |
$ 567.00 |
$ 26.44 |
$ 26.54 |
$ 185.64 |
| 09-06-08 |
$ 571.51 |
$ 23.47 |
$ 26.37 |
$ 172.37 |
| 16-06-08 |
$ 546.43 |
$ 21.99 |
$ 24.64 |
$ 175.27 |
| 23-06-08 |
$ 528.07 |
$ 21.33 |
$ 23.61 |
$ 170.09 |
| 30-06-08 |
$ 526.42 |
$ 20.66 |
$ 23.26 |
$ 167.44 |
|
|
|
|
|