1 What was the average quarterly revenue during these 12 years?
2 What was the standard deviation of the quarterly revenue?
3 In which quarter did Nike achieve the highest quarterly revenue?
4 What is the slope of the line that fits the quarterly revenues? (Hint: you can use an XY Chart and the Add Trendline option)
5 Use the SES sheet for 5-7. Use the first 4 quarters to initialize SES. What is the initial estimate of level?
6 Use LS =0.3 to forecast the quarterly revenue using SES for the next two quarters.
a Quarter ending Feb-20
b Quarter ending May-20
7 Use the solver (or a data table) to find the value of LS that minimizes MSE for SES. What is the optimal LS? (At least a three-decimal accuracy is required–for example 0.357.)
8 Use the DES sheet for 8-10. What is the initial estimate of trend in DES?
9 Use LS =0.3 and TS = 0.4 to forecast the quarterly revenue using DES for the next two quarters.
a Quarter ending Feb-20
b Quarter ending May-20
10 Use the solver (or a data table) to find the values of LS and TS that minimize MSE for DES. What is the optimal LS? (At least a three-decimal accuracy is required–for example 0.357.)
11 Use the TES sheet for 11-13. What is the initial estimate of the seasonality index for the first quarter in TES?
12 Use LS =0.3, TS = 0.4, and SS = 0.2 to forecast the quarterly revenue using TES for the next two quarters.
a Quarter ending Feb-20
b Construct a %95 confidence interval around the Feb-20 forecast and report the lower limit. (Hint: Check Lecture 5 ppt file)
c Quarter ending May-20
13 Use the solver (or a data table) to find the values of LS, TS, and SS that minimizes MSE for TES. What is the optimal LS? (At least a three-decimal accuracy is required–for example 0.357.)
14 Suppose the TES forecast for the Quarter ending Feb-20 is 10.45B and the RMSE of the model is 0.35. The actual revenue turned out to be 10.1B which is well within the confidence interval of the point forecast. However, for May-20 the revenue turned out to be is 6.31B, while the forecast from the model was 10.85B. Explain why the model seems to have failed miserably for May-20 in 140 characters or less (the length of an old tweet :-). Cell H29 shows the number of characters you used.
15 Now consider the sheet TES2. Implement TES with a holdout. Hold out the last two years. Use LS = 0.3, TS = 0.4 and SS = 0.2. Let the model “learn” until the end of 2017. Then “forecast” for the 8 quarters of 2018 and 2019.
a Forecast for quarter ending May-18
b Forecast for quarter ending May-19
c What is the MAPE for the two holdout years (i.e. the last 8 quarters in the data)?
16 Use the sheet TES3 for this problem. The version of the TES we covered so far assumes additive trend and multiplicative seasonality. Devise another version with additive trend and additive seasonality.
a What is the initial value of the seasonality (difference) for the first quarter?
b Use LS =0.3, TS = 0.4, and SS = 0.2 to forecast the quarterly revenue using the new version (the one with additive seasonality) of TES for the quarter ending Feb-20.