Reference no: EM132154828
1. Create a spreadsheet template to encapsulate the SMA (n=2), WMA (n=2) (0.4, 0.6 weights – higher weight to the most recent data), and exponential model (alpha = 0.3 and forecast for period 1 given at 100). Use the data shown below in data set 1.
You will be graded on the following basis:
Automation (5) – make sure your model allows for varying actual data, WMA weights, and alpha and given forecast value for period 1 in the case of the exponential model).
Input of variable values percolates automatically through the spreadsheet
Limited requirement for cutting and pasting.
Ability to vary number for frames by month
Use of “dashboard” with equations/calculations hidden in the background (page 2 of spreadsheet)
Display (5)
If possible (but not required) create a macro which automatically tell me which model is the best and worst based on ME and MSE). Hint: think about nested if functions in excel. If unfamiliar you can search you tube for the use of such functions.
Easy of understanding/use of spreadsheets
Correct calculations (10) Forecast for August, Bias and Accuracy.
2. Conduct sensitivity analyses and write up (10 POINTS) – on page 3 of spreadsheet. Use graphs to support your argument(s). Locate/identify the best forecasting model (change weights for WMA and exponential) for the two new data sets (sets 1 and 2) using the spreadsheet you created. (7 points) Provide explanations (3 points) as to the link between the data and type of model (think about responsiveness and sensitivity).
Data 1
Jan-100
Feb-90
mar-80
April-100
May-75
June-90
July-100
Data Set 2
JAN-80
FEB-90
MAR-85
APRIL-140
MAY-100
JUNE-90
JULY-85