Show All

Hide All

Supply and demand is the basis of inventory management, and making sure you have enough inventory on hand to meet order requests can be challenging, especially if you have variable peaks in your industry. By using the Moving Average analysis tool in Microsoft Office Excel 2003, you can use historical inventory levels to predict future demand, enabling you to better plan financial and physical resources.

Avoid shortages and overstock

The following illustration shows a summary of Contoso Pharmaceutical's inventory for their 10 best-selling products from last year. This report shows a large variance in the ending inventory quantities from month to month, indicating both shortages and stagnant product — neither of which is good for business. With a few simple steps, you can use this information to manage your inventory levels more precisely this year.

 Note   To use this feature, you must have the Excel Analysis Toolpak installed on your computer. If the Data Analysis command doesn’t appear on the Tools menu, you must install the Analysis ToolPak.

The Moving Average analysis tool

The Moving Average analysis tool projects values in the forecast period, based on the average value of the variable over a specific number of preceding periods. A moving average provides trend information that a simple average of all historical data would mask. This example uses the data for Contoso product 1176 to predict a target inventory level for the new fiscal year.

Chart a target inventory level

1. On the Tools menu, click Data Analysis.
2. In the Data Analysis dialog box, click Moving Average, and then click OK.
3. The Moving Average dialog box opens.

   

4. In the Input Range box, enter a single row or column of data. This example uses the row of data from product 1176 on the Contoso top-10 products report.
5. In the Interval box, enter the number of values that you want to include in the moving average. In this example, enter 3, the default interval.

    Note   The interval is the number of data points used to calculate the moving average. The larger the interval, the smoother the moving average line; the smaller the interval, the more the moving average is affected by individual data point fluctuations.

6. In the Output Range box, enter the cell address where you want the results to start.
7. Select the Chart Output check box to see a graph comparing the actual and forecasted inventory levels.
8. Click OK.

The resulting chart gives a better picture of ideal inventory levels, and with a little fine-tuning, can show exactly what the optimal monthly ending inventory should be.

Add data labels to a chart

1. Double-click the first data point in the Forecast series to open the Format Data Series dialog box.
2. On the Data Labels tab, select the Value check box, and then click OK.

   

3. Double-click the first data label to open the Format Data Label dialog box.
4. On the Font tab, set the font size to 8.
5. On the Number tab, select number from the list on the left.
6. Set the decimal places to 0.
7. Click OK.

Your chart now clearly shows your forecasted inventory levels and identifies each month’s optimal ending quantity.