Carl Lipke is the marketing VP for a propane gas distributor. He would like to have a forecast of sales on a quarterly basis, and he has asked you to prepare a time-series decomposition model. The data for 2005 through 2016 follow:
|
Propane Gas Sales in Millions of Pounds (Total at End-Month of Each Quarter) |
||||
|
Year |
March |
June |
September |
December |
|
2005 |
6.44 |
4.85 |
4.67 |
5.77 |
|
2006 |
6.22 |
4.25 |
4.14 |
5.34 |
|
2007 |
6.07 |
4.36 |
4.07 |
5.84 |
|
2008 |
6.06 |
4.24 |
4.20 |
5.43 |
|
2009 |
6.56 |
4.25 |
3.92 |
5.26 |
|
2010 |
6.65 |
4.42 |
4.09 |
5.51 |
|
2011 |
6.61 |
4.25 |
3.98 |
5.55 |
|
2012 |
6.24 |
4.34 |
4.00 |
5.36 |
|
2013 |
6.40 |
3.84 |
3.53 |
4.74 |
|
2014 |
5.37 |
3.57 |
3.32 |
5.09 |
|
2015 |
6.03 |
3.98 |
3.57 |
4.92 |
|
2016 |
6.16 |
3.79 |
3.39 |
4.51 |
- To help Carl Lipke see how propane gas sales have varied over the 12-year period, prepare a time-series plot of the raw data and the deseasonalized data (i.e., the centered moving averages).
- Use Forecast X™ to find seasonal indices for quarters 1 through 4. Write a short paragraph in which you explain to Carl Lipke exactly what these indices mean.
- Plot the values of actual sales, the centered moving averages, and the trend. All of these can be found in your Forecast X™ results.
- From Forecast X™, get a forecast for 2017 Q1 through 2017 Q4 based on the time-series decomposition model. Enter your forecast values into an Excel sheet that you set up like in the table shown below. Given the actual values shown in the table, calculate the mean absolute percentage error (MAPE) for 2017.
