Forecasting with Indices
Jerry Dennis
QRB 501
April 29, 2013
Susan Swanson

Forecasting with Indices
There are no absolute certainties in volatile economic environments. Forecasting is a method for managers to predict future outcomes based on past performance in an attempt to prepare for presented complexities and constraints. For the purposes of the week three individual assignment the Winter Historical Inventory Data was utilized to develop a fifth year forecast. This data was comprised of four years of typical seasonal demand of winter highs and separated by individual months; see Table 1. Table 1 | | | | | | Winter Historical Inventory Data | | | | | | | Year 1 | Year 2 | Year 3 | Year 4 | 1 | 55200 | 39800 | 32180 | 62300 | 2 | 57350 | 64100 | 38600 | 66500 | 3 | 15400 | 47600 | 25020 | 31400 | 4 | 27700 | 43050 | 51300 | 36500 | 5 | 21400 | 39300 | 31790 | 16800 | 6 | 17100 | 10300 | 31100 | 18900 | 7 | 1800 | 45100 | 59800 | 35500 | 8 | 19800 | 46530 | 30740 | 51250 | 9 | 15700 | 22100 | 47800 | 34400 | 10 | 53600 | 41350 | 73890 | 68000 | 11 | 83200 | 46000 | 60200 | 68100 | 12 | 72900 | 41800 | 55200 | 61100 |

The original data is useful in understanding and depicting an exact representation of inventory but has little substantive value for extrapolating meaningful information for planning and forecasting. An index was created to figure out which values were above the monthly average and which were below; see Table 2.

Table 2 | | | | | | | Index | | | | | | | | | | | | Year 1 | Year 2 | Year 3 | Year 4 | Average | 1 | 1.165294 | 0.840194 | 0.679333 | 1.315178 | 1.240236 | 2 | 1.01258 | 1.131759 | 0.681527 | 1.174134 | 1.093357 | 3 | 0.515826 | 1.594373 | 0.838051 | 1.05175 | 0.783788 | 4 | 0.698833 | 1.086093 | 1.294229 | 0.920845 | 0.809839 | 5 | 0.783237 | 1.438375 | 1.16351 | 0.614878 | 0.699058 | 6 | 0.883721 | 0.5323 | 1.607235 | 0.976744 | 0.930233 | 7 | 0.050633 | 1.268636 | 1.682138 | 0.998594 | 0.524613 | 8 | 0.533981 | 1.254854 | 0.829018 | 1.382147 | 0.958064 | 9 | 0.523333 | 0.736667 | 1.593333 | 1.146667 | 0.835 | 10 | 0.905252 | 0.698362 | 1.247931 | 1.148455 | 1.026854 | 11 | 1.292427 | 0.714563 | 0.935146 | 1.057864 | 1.175146 | 12 | 1.262338 | 0.72381 | 0.955844 | 1.058009 | 1.160173 |

To determine the values indicated in the index chart above the monthly averages were calculated for all four years of historical data. For instance; the January average for years one through four was determined to be 47370. The actual January inventory data for year one was then divided by the average to yield an index of 1.165294. If the index is greater than one, the inventory for that month was greater than the average. If equal to one, the inventory was exactly the same as the monthly average. If the index was less than one then the inventory was less than the monthly average. A better understanding of the historical data can be obtained when presented in an index. The index chart is more effective at interrupting the historical data. When evaluating the index chart above it is instantly apparent the highest seasonal demand for winter highs is in the months of January, February, October, November and December. When organizing and planning for the future business needs an index chart would be instrumental in determine the meaning of raw data.

Forecasting
The first forecasting method which was used was a mathematical technique to further extrapolate the data. The assumptions of mathematical forecasting are the responsible entities for creating the past will continue to influence in the future. Using the mathematical tools in Microsoft Excel a trend forecast was created. After the data was imputed Excel created a Linear Forecast Trendline; see Figure 1.
Figure 1 Index Graph with Linear Forecast Trendline

The slop and the intercept of the line were determined. The slope was