*******NOTE: I must be able to attach an **EXCEL** document with cell data to the assignment submission. More important than the answer is the step by step process on how to get these functions to work properly in **EXCEL**. Thank you!******
Using the given data set, please forecast future sales data using each method. Then, determine which method you prefer and why.
GAP Data set:
| $M | |||
| Year | Quarter | Period | GAP |
| 2012 | Q1 | 1 | $ 3,487 |
| 2012 | Q2 | 2 | $ 3,575 |
| 2012 | Q3 | 3 | $ 3,864 |
| 2012 | Q4 | 4 | $ 4,725 |
| 2013 | Q1 | 5 | $ 3,729 |
| 2013 | Q2 | 6 | $ 3,868 |
| 2013 | Q3 | 7 | $ 3,976 |
| 2013 | Q4 | 8 | $ 4,575 |
| 2014 | Q1 | 9 | $ 3,774 |
| 2014 | Q2 | 10 | $ 3,981 |
| 2014 | Q3 | 11 | $ 3,972 |
| 2014 | Q4 | 12 | $4,708 |
| Forecasting Method | Q1 | Q2 | Q3 | Q4 |
| 4 point moving average | - | - | - | |
| Simple Exponential Smoothing (0.1) | - | - | - | |
| Simple Linear Regression (Trend) | ||||
| Trend and Seasonal | ||||
| Forecasting Errors | MAD | RSQ | ||
| 4 pt MA | - | |||
| Exp Sm | - | |||
| Trend (Linear Regression) | ||||
| Trend and Seasonal | ||||
| Q1 | Q2 | Q3 | Q4 | |
| Scaled Seasonal Index | ||||
| Optimal alpha (exp smoothing) | ||||
a: Fill in the blank Units: $Millions
Where appropriate provide answers as accurate as: 3 d.p.
B: Explain, based on your analysis, which method of forecasting should be used by the company and justify this using as many arguments as possible. You will earn points for a convincing answer.
c: Why do you think this company experiences its biggest sales push in the season you chose? What should the company do to make sales more evenly distributed throughout the year? Discuss the implication of the slope and intercept in your analysis.
| Year | quarter | period | $M Gap | 4 Figures Moving Total | 4 Figures Moving Average | 2 Figures Moving Total | 2 Figures Moving Average(T) |
| 2012 | 1 | 1 | 3487 | ||||
| 2 | 2 | 3575 | |||||
| 15651 | 3912.75 | ||||||
| 3 | 3 | 3864 | 7886 | 3943 | |||
| 15893 | 3973.25 | ||||||
| 4 | 4 | 4725 | 8019.75 | 4009.875 | |||
| 16186 | 4046.5 | ||||||
| 2013 | 1 | 5 | 3729 | 8121 | 4060.5 | ||
| 16298 | 4074.5 | ||||||
| 2 | 6 | 3868 | 8111.5 | 4055.75 | |||
| 16148 | 4037 | ||||||
| 3 | 7 | 3976 | 8085.25 | 4042.625 | |||
| 16193 | 4048.25 | ||||||
| 4 | 8 | 4575 | 8124.75 | 4062.375 | |||
| 16306 | 4076.5 | ||||||
| 2014 | 1 | 9 | 3774 | 8152 | 4076 | ||
| 16302 | 4075.5 | ||||||
| 2 | 10 | 3981 | 8184.25 | 4092.125 | |||
| 16435 | 4108.75 | ||||||
| 3 | 11 | 3972 | |||||
| 4 | 12 | 4708 | |||||
We use 2 figure moving average then
| Years/Quarters | 1 | 2 | 3 | 4 |
| 2012 | 3943 | 4009.88 | ||
| 2013 | 4060.5 | 4055.75 | 4042.63 | 4062.38 |
| 2014 | 4076 | 4092.13 | ||
| Total | 8136.5 | 8147.88 | 7985.63 | 8072.26 |
| Seasonal Average | 4068.25 | 4073.94 | 3992.815 | 4036.13 |
Get Answers For Free
Most questions answered within 1 hours.