Learning Goal: I’m working on a excel multi-part question and need the explanation and answer to help me learn.
1. In sheet “Figure 7-4”, the “Deseasonalized Demand” column has been hard-coded with the values of the Trend and Level from “Regression-1”. Please add another column and recalculate the “Deseasonalized Demand” by pulling up values from the cells corresponding to Trend and Level from the “Regression-1” sheet. Hence, this column should no longer be hard coded.
You must change the hardcoded value to a ‘reference’ value in excel. In Excel, you can refer to the cell of a sheet in another sheet. Refer to this link –
Please note that the values in the column mentioned in the question are hardcoded, and you need to change them as per the reference formula.
2. In sheet “Figure 7-4,” please calculate the “Absolute Error,” “Mean Squared Error (MSE),” “Mean absolute deviation (MAD),” “% Error,” “Mean absolute percentage error (MAPE),” and “Tracking signal (TS).
You need to use the formulas provided in excel on Sheet 7-7 (starting from Column F) and calculate the same metrics for Sheet 7-4. Please see the formulas inside the excel cell and try to replicate them.
3. For the winter’s method covered in class, Please refer to sheet “Figure 7-10” of Tahoe salt case while answering the question. Please explain in detail how the forecast for periods 13, 14, 15 and 16 are calculated.
Please explain the different components of the forecast (level, trend, etc.).
