1. (6 points) Moving Average Forecasting

– In Excel, create three different moving average forecasts with periods of 2, 3, and 5 (k = 2, 3, 5). Compute the mean squared error (MSE) from each of these forecasts.

– Create a simple table that reports the MSEs and the forecasted stock price for May 2021 for each of the variations (k = 2, 3, 5). Discuss what the results tell us about the accuracy of these forecasts, and which forecast you have the most confidence in.

2. (6 points) Exponential Smoothing

– In Excel, create two different exponential smoothing forecasts, one with α = 0.25 and one with α = 0.75. Compute the MSE from each of these forecasts.

– Create a simple table that reports the MSEs and the forecasted stock price for May 2021 for each of the variations (α = 0.25, 0.75). Discuss what the results tell us about the accuracy of these forecasts, and which forecast you have more confidence in.

– Compare the moving average and exponential smoothing results.

3. (6 points) Forecasting with a Linear Trend

– Create a trend variable (t) that has a value of 1 in January 2016, 2 in February 2016, and so on.

– Run a regression with t as your independent variable and Netflix’s stock price as the dependent variable.

– Interpret the coefficient on the trend variable.

– Compare the accuracy of this linear trend model with the models from questions 1 and 2.

– What does the trend model forecast Netflix’s stock price will be at the end of 2021 (December 2021)?

4. (5 points) Graphing your Trend Forecast

– Using the results from your linear trend model in question 3, create a forecast through the end of 2022.

– Create a line graph that shows the actual data through April 2021, and then the forecast through 2022. Make sure the forecast portion is differentiated on the graph (by color and/or dashed line).

– Discuss how you feel about the accuracy of the linear trend model based on the graph.

5. (7 points) Seasonality in Netflix Stock Price?

– Create dummy variables for Quarters 2, 3, and 4 in the year. Quarter 2 is April to June, 3 is July to September, and 4 is October to December.

– Run a regression with the dummies for quarters 2, 3, and 4 and the linear trend (t) variable that we used in questions 3 and 4.

– Interpret the coefficients on the quarter dummy variables. Is there evidence that there is seasonality in Netflix’s stock price?

– What does this trend with the seasonality model forecast Netflix’s stock price will be at the end of 2021 (December 2021)?

– Overall, was it a good idea to include the seasonal component in our forecasting model (as compared to the model with just the trend in Question 3)?

6. (6 points) Autoregression

– Run a regression where you use a 1-period lag of the Netflix stock price as the independent variable.

– What does this model predict the price will be at the end of May 2021?

– Discuss the results of this regression model. How does the forecast accuracy of this model compare to the previous models?

7. (4 points) Summarize your work to this point

– Discuss the various methods used to try and forecast the price of Netflix stock going forward. Which do you place more trust in, which do you disregard?

– Based on your analysis so far, and anything else you want to incorporate, if you are forced to forecast one number for the price of Netflix stock at the end of May 2021, what would that number be? Explain briefly.

Note: for the rest of the assignment, you should cut the sample to the period of January 2019 through April 2021 (28 observations)

8. (8 points) Using the data from January 2019 to April 2021 (28 observations), examine the fit of different trend models.

– First, run a simple linear trend model in which t = 1 in January 2019, 2 in February 2019, and so on.

– Using the prediction equation from the linear trend model, create a graph that shows the actual and predicted values (trend line) for the period of January 2019 to April 2021.

– Next, run a cubic trend model. This model should include the linear trend variable (t), as well as the squared and cubed versions of t.

– Using the prediction equation from the cubic trend model, create a graph that shows the actual and predicted values (trend line) for the period of January 2019 to April 2021.

– Based on the statistical results and the graphs you created discuss the fit of these two models. Which does a better job of matching the actual trends in the data?

9. (7 points) Forecasting with our different trend models

– Use the prediction equations for the linear and cubic trend models to forecast each through the end of 2022. On one chart shows the actual Netflix stock price from January 2019 through April 2021, then the forecast paths for each of the trend models. (NOTE: this graph should not have the predicted trend lines from January 2019 to April 2021 like the graphs in question 8 had. This should be a “forecast” graph as we did in question 4).

– What does each model predict the price will be at the end of 2021 (December 2021)?

– Between the accuracy measures and your graphs in questions 8 and 9, which of the two models do you feel more comfortable using to forecast the price of Netflix stock going forward? Explain.

## Sheet1

Date | Netflix |

Jan-16 | 91.84 |

Feb-16 | 93.41 |

Mar-16 | 102.23 |

Apr-16 | 90.03 |

May-16 | 102.57 |

Jun-16 | 91.48 |

Jul-16 | 91.25 |

Aug-16 | 97.45 |

Sep-16 | 98.55 |

Oct-16 | 124.87 |

Nov-16 | 117.00 |

Dec-16 | 123.80 |

Jan-17 | 140.71 |

Feb-17 | 142.13 |

Mar-17 | 147.81 |

Apr-17 | 152.20 |

May-17 | 163.07 |

Jun-17 | 149.41 |

Jul-17 | 181.66 |

Aug-17 | 174.71 |

Sep-17 | 181.35 |

Oct-17 | 196.43 |

Nov-17 | 187.58 |

Dec-17 | 191.96 |

Jan-18 | 270.30 |

Feb-18 | 291.38 |

Mar-18 | 295.35 |

Apr-18 | 312.46 |

May-18 | 351.60 |

Jun-18 | 391.43 |

Jul-18 | 337.45 |

Aug-18 | 367.68 |

Sep-18 | 374.13 |

Oct-18 | 301.78 |

Nov-18 | 286.13 |

Dec-18 | 267.66 |

Jan-19 | 339.50 |

Feb-19 | 358.10 |

Mar-19 | 356.56 |

Apr-19 | 370.54 |

May-19 | 343.28 |

Jun-19 | 367.32 |

Jul-19 | 322.99 |

Aug-19 | 293.75 |

Sep-19 | 267.62 |

Oct-19 | 287.41 |

Nov-19 | 314.66 |

Dec-19 | 323.57 |

Jan-20 | 345.09 |

Feb-20 | 369.03 |

Mar-20 | 375.50 |

Apr-20 | 419.85 |

May-20 | 419.73 |

Jun-20 | 455.04 |

Jul-20 | 488.88 |

Aug-20 | 529.56 |

Sep-20 | 500.03 |

Oct-20 | 475.74 |

Nov-20 | 490.70 |

Dec-20 | 540.73 |

Jan-21 | 532.39 |

Feb-21 | 538.85 |

Mar-21 | 521.66 |

Apr-21 | 509.00 |