[Image unavailable: graded paper]Assignment 6: Forecasting
Introduction
This assignment assesses your knowledge of applying time series decomposition techniques to a time series data using Excel.
Instructions
Submit your answers to all of the questions in a Word document. Download the data sets and use Excel to solve the problems.
Question 1. A national supplier of jet fuel is interested in forecasting its sales. Download the sales data Download Download the sales datafor the periods 2001, Q1 to 2016, Q4.
- Plot these sales against time. What, if any, seasonal pattern do you see in the plot? Explain.
- Deseasonalize the data by calculating the centered moving average. Plot the deseasonalized data on the graph created in (a). Calculate the seasonal index for each quarter. Write a short explanation of why the results make sense.
- Develop a trend for the data USING deseasonalized SALES (NOT ACTUAL SALES) and plot that trend line on the graph developed in (a). Compare the deseasonalized sales data and the trend line. Does there appear to be a cyclical pattern in the data? Explain.
- For the historical period, plot the values estimated by the time series decomposition model with the original data.
- Make a forecast of sales for the four quarters of 2017. Using the actual data for that year given in the table, calculate the RMSE:
The quarterly sales of the Jet Fuel for the previous four quarters are presented in the table:
JET FUEL SALES QuarterForecastActualSquared Error2017Q1 66.81 2017Q2 77.52 2017Q3 82.76 2017Q4 74.51 Sum of Squared Errors= Mean-squared Errors= Root Mean-Squared Errors=
Submission
- Submit your answers in a Word document.
- Submit the Excel files to show your calculations.
- Include the relevant graphs of actual and forecast values of the series. Please do not forget to title your graph.