Inventory Data Index and Forecasting

Only available on StudyMode
  • Download(s) : 2927
  • Published : January 29, 2012
Open Document
Text Preview
A common practice among businesses is to gather relevant data to evaluate how well their business is doing, what areas may need improvement, and what changes or needs they may expect in the near future. However, attempting to make decisions and conclusions based only on raw data, for example inventory numbers by month for the past few years, may prove to be difficult. This type of data may have many fluctuations, creating difficulties when trying to deduce any valuable information. Indexes are a tool to assemble this data in a more useable way. Quantitative indexes show general trends in the data that allows one to more easily make comparisons and decisions (Sevilla & Sommers, 2007). Evaluating Historical Data

Table 1 below contains four years of data from University of Phoenix Winter Historical Inventory Data.
Actual Demands (in units)

MonthYear 1Year 2Year 3Year 4
155,20039,80032,18062,300
257,35064,10038,60066,500
315,40047,60025,02031,400
427,70043,05051,30036,500
521,40039,30031,79016,800
617,10010,30031,10018,900
718,00045,10059,80035,500
819,80046,53030,74051,250
915,70022,10047,80034,400
1053,60041,35073,89068,000
1183,20046,00060,20068,100
1272,90041,80055,20061,100
Table 1. University of Phoenix (UOPXX) Material - Winter Historical Inventory Data

Seasonal Index
The inventory data shows a trend across all for years of seasonal highs during the winter months, and severe dips mid-year in the summer months. Therefore, creating a seasonal index would be an appropriate way to show general trends in the data. A seasonal index attempts to “deseasonalize” the time series to see if the seasonality spikes masked other real trends (Arsham, 1994). The following displays the seasonal index calculations for the Winter Historical Data. To calculate the index, first find the moving average across spans of 12 months and then use the corresponding season index ratio to calculate how far the original month’s data differs from the 12-month average. Because the seasonal index ratio numbers did not all add up to exactly 12, it is necessary to scale the averages to make them correct, and the results are the Seasonal Index numbers below. Month-YearOriginal DataSeasIndex RatioSeasonal Index

Jan-0155200 52193.307
Feb-0157350 43685.977
Mar-0115400 19385.273
Apr-0127700 27442.836
May-0121400 31744.024
Jun-0117100 35448.642
Jul-01180000.48018321.364
Aug-01198000.53424710.359
Sep-01157000.40523605.760
Oct-01536001.31640066.262
Nov-01832001.97654686.395
Dec-01729001.71353552.739
Jan-02398000.91737632.131
Feb-02641001.40448827.744
Mar-02476001.01259918.116
Apr-02430500.92042650.329
May-02393000.87958296.269
Jun-02103000.24621352.106
Jul-02451001.12045905.195
Aug-02465301.19758069.343
Sep-02221000.59933228.491
Oct-02413501.13930909.327
Nov-02460001.26730235.267
Dec-02418001.13430706.509
Jan-03321800.83930427.185
Feb-03386001.00829403.290
Mar-03250200.64631494.775
Apr-03513001.24750823.736
May-03317900.73847156.193
Jun-03311000.70364470.922
Jul-03598001.29860867.643
Aug-03307400.63438363.456
Sep-03478000.95871869.767
Oct-03738901.49155233.137
Nov-03602001.24639568.762
Dec-03552001.17040550.222
Jan-04623001.36558906.576
Feb-04665001.46250655.928
Mar-04314000.68639525.816
Apr-04365000.81136161.138
May-04168000.37324920.542
Jun-0418900 39180.078
Jul-0435500 36133.801
Aug-0451250 63959.893
Sep-0434400 51722.176
Oct-0468000 50830.333
Nov-0468100 44761.340
Dec-0461100 44884.395
Table 2. Seasonal Index calculations for UOPX Winter Historical Inventory Data
To see the differences between the index and the original inventory numbers, Graph 1 below shows the two sets of data graphed together.
Graph 1. Seasonal Index of UOPX Winter Historical Inventory Data with Original...
tracking img