Making better use of the data in your inventory system

The technique described here is called “exponential smoothing”. It is a simple method of updating an average as new data becomes available. It is the basis of most techniques used for forecasting customer demands. It was discussed briefly in the article entitled “Demand Rate Estimation”. In this article, it will be illustrated in relation to demand rate estimation. However, its use is not restricted to that.

## Weightings Given to Historical Data

Click on the appropriate link below depending on what spreadsheet software you are using:

Open document format (for use with modern spreadsheet software)

Excel 97 format (for use with pre-2013 versions of Microsoft Excel)

You will then see a spreadsheet showing the weightings given to the demand in each period. You can download it and open it with your own spreadsheet software. Alternatively, you can view it online by clicking on “Open”. If the periods are months then the spreadsheet will show the weightings given to the demand last month, the second to last month, the third to last month, etc. The weighting given to the demand in the most recent period is called the “smoothing constant” or “smoothing factor” or “smoothing coefficient” (α). The periods can be any length of time, e.g, months or weeks or days. Try changing the smoothing constant in the yellow cell (Cell B3). Fig.1 below shows what you will see for a smoothing constant of 0.1: Fig.1 – Exponential smoothing weightings with a smoothing constant of 0.1

## How it works

Suppose that it is to be used to average the monthly demands for an item and that average is to be updated each month. That average can be treated as a forecast if the demand rate is reasonably stable.

The technique is as follows: At the end of each month, the average demand per month is adjusted taking into account the most recent month’s demand. Suppose, for example, the smoothing constant is 0.2 (i.e. 20%). Then 20% of the weighting is given to the demand in the most recent month and 80% of the weighting is given to the old average, i.e.

New average = α(latest month’s demand) + (1 – α)(old average)

This formula can be applied using this calculator.

If the old average is 30, the latest month’s demand is 40 and the smoothing coefficient is 0.2 then

New average = 0.2 x 40 + (1 – 0.2) * 30

= 0.2 x 40 + 0.8 x 30

= 8 + 24

= 32

Here is another way to look at it: For α=0.2 (20%), the average is adjusted by 20% of the error in using the old average as a forecast of the latest month’s demand (i.e. by 20% of the difference between the new demand and the old average).

New average = old average + α(error in the forecast of the latest month’s demand)

i.e. New average = old average + α(latest month’s demand – old average)

which is equivalent to the first formula.

Using the above data,

New average = 30 + 0.2 x (40 – 30)

= 30 + 0.2 x 10

= 30 + 2

= 32

In this case, if the old average had been used as a forecast of the latest month’s demand then that forecast would have had an error of 40-30, i.e. 10. The average is then increased by 20% of that forecasting error to give a new average of 32.

The exponential smoothing formula can be applied using this calculator.

## Selection of an Appropriate Smoothing Constant

If the smoothing constant is small then the estimated demand rate will take a long time to catch up with changes in the demand rate. If it is high then the demand rate will tend to be sensitive to random fluctuations in demand. Consequently, choice of a smoothing constant involves a compromise. Ideally, the smoothing constant should be set by means of simulation of the effects on your overall service level and overall investment in inventory.

The following formula will usually give a reasonably appropriate smoothing constant (α):

α = 1/(4L + 1)

where L is the expected lead time in the event of the inventory position falling below the reorder point just after a reorder review. The above formula can be applied using this calculator. The formula is compromise between keeping up with a changing demand rate and being overly sensitive to random fluctuations in demand. The lead time and reorder review period (time between reorder recommendations reports) should both be in months if the exponential smoothing is applied to monthly demands, weeks if weekly demands are used, etc.

Suppose that the lead time, ignoring the reorder review period, is two months and that reordering recommendations are produced monthly. Suppose that the total of the other components of the lead time is one month. Then an appropriate smoothing constant is given by

α = 1/(4(2 + 1 + 1) + 1)

= 1/17

= 0.058

The (2 + 1 + 1) in the above example is the supplier lead time (2 months) plus the reorder review period (1 month) plus the other components of the lead time (1 month).

Try using the calculator with the above worked example. If a month is treated as being 30 days then the answer will be 0.0667.

Don’t be tempted to use a higher smoothing constant to keep up with trends. There are better methods of dealing with that and one of them will be discussed in a later article.

## Investigate the Effects With Your Own Data

The simulator described in the article entitled “An Educational Inventory Management Simulator” can be used to investigate the effects when used with your own data. Try entering your own demand history into that simulator for at least one of your fast moving items and at least one of your infrequently moving items. In each case, try more than one smoothing constant including the one suggested by the above-mentioned calculator and one which is considerably higher than that. Note the large orders which tend to result from relatively high demands and note how that problem is exacerbated by use of a high smoothing constant.

## Effects of Using a Smoothing Constant Which is Too High

Fig. 2 below illustrates the effect of using a smoothing constant of 0.5 if the lead time is three months.

Notice how erratic the estimated mean demand is in spite of the fact that the simulation was carried out with a constant demand rate (constant mean demand per month).

The “inventory level” is the stock on hand minus customer back orders so yellow points above the axis indicate stock on hand and those below the axis represent customer back orders.

Notice the excessively high order quantities in months 7, 26 and 30 resulting from the relatively high demands in months 5, 24 and 28. Not all peaks in demand cause orders to be placed on the supplier. This is because ordering only takes place when the inventory position falls below the reorder point. For example the relatively high demands in periods 21 and 37 do not result in ordering because at those times the inventory levels are already high as a result of earlier over-ordering. With regard to the shortages early on, see the section entitled “Initialisation” below. These shortages could be prevented by the means of safety stock but that would make the problems of over-ordering and of excessive stocks even greater.

Fig.3 below shows how much more stable the demand rate estimate is with a smoothing constant of 0.1. Notice however that there is still the problem of over-ordering after relatively high demands. The reasons are given in the article entitled “Evaluating Forecasting Algorithms“. Fig.2. – Effects of a high smoothing constant (0.5) Fig.3 – Effects of reducing the smoothing constant to 0.1

In Figures 2 and 3,note the prolonged shortages when the item is new. This problem can be dealt with by manual entry of the initial average demand per month.

## Benefits of Exponential Smoothing

• It uses all of the demand history, even that which is no longer stored in the system.
• It will not result in zero demand rate estimates if there has ever been any demand.
• The greatest weightings are given to the most recent demands.
• It facilitates manual intervention when the demand rate is expected to change.

## Shortcomings of Exponential Smoothing

• As with most demand rate estimation techniques and forecasting techniques, relatively high demands tend to result in over-ordering.
• Selection of a smoothing constant involves a compromise between responsiveness and stability.
• The optimal smoothing constant is not the same for all items.
• The algorithm requires modification if there are trends or seasonal effects.

## Initialisation

The initial old average demand per period needs to be set. If the exponential smoothing is implemented when there is already some demand history available then all of that history should be used in order to ensure that the initial average is not zero if there has ever been any demand.

Use whatever relevant information you can obtain from the supplier and also any other relevant data. In a multiple store operation, if an item is introduced into one store when there is already some history of the item in other stores, then appropriate use should be made of that history.

If no useful information is available then the smoothing “constant” (better called the “smoothing factor”) should initially be fairly high but only for a short time.