Free Demo menu close

Inventory Crash Course

May 17, 2017


Lesson 2: Forecasting

Forecast Management – a critical component to a healthy inventory

The Pain

We all know that forecasting is one of the key drivers of inventory replenishment. If we over-forecast, then we end up with excess; if we under-forecast, then we could run out of stock.

The things that make forecasting difficult are:

  • Erratic demand
  • Seasonality
  • New product introduction
  • One-off project sales
  • Tens of thousands of products
  • Product replacement
  • Market cannibalization
  • Channel forecasts

The Theory

The simple fact is that forecasts will always be wrong. What’s more important is how wrong the forecast is, and how to identify the items where the forecasts need to be adjusted.

In order to produce consistent and measurable forecasts, we need a tool that creates a baseline forecast that can then be adjusted to incorporate market knowledge and exceptions to the norm.

The Approach

The best way to produce the highest quality forecasts in a repeatable manner is as follows:

  1. Establish a baseline forecast per item
  2. Adjust the forecast to take account of:
  • Seasonality
  • Product growth
  • Product replacement
  • Marketing campaigns

Once you’ve established these basics, you’ll need to review and monitor the calculations you’ve done:

  1. Review forecasts at a macro or summarized level to ensure that they are reasonable, and adjust at a top level where required, and then blow macro forecast adjustments down to a line item level using a prorating mechanism
  2. Monitor forecast performance using a mechanism that distills the amount of over- and under-forecasting. The direction is important as a bias towards:
  • over-forecasting will lead to excess stock and a bias towards
  • under-forecasting will lead to stock-outs  

Now that you’ve assessed the quality of your forecasts, you should establish a process to continually manage and adjust them:

  1. Have a mechanism for highlighting, both on a weekly and monthly basis, the forecast that are tracking sales poorly. Always rank the items in these exception reports by value of what will have the biggest impact on generating excess or leading to stockouts.
  2. Have a structured weekly and monthly review to ensure ongoing improvement


Let’s examine each of these steps in a bit more detail.


Establishing a baseline forecast

This is the most difficult part and is normally performed by a computer algorithm. In the absence of computer assistance, we need to use a spreadsheet. (Note that later versions of excel provide some forecasting functionality.)

The example below is for an item with at least 24 months demand history, which includes seasonality and product growth. If we have less than 24 months of history, simple formulas such as a weighted average or exponential smoothing are usually the most robust and simple to compute (see standard forecast models for formulae).

Extract the sales history per item, per month by forecast group (by warehouse or channel), then perform the following steps:

  • De-seasonalize the data
  • Establish a trend forecast
  • Seasonalize the data

Step 1 – De-seasonalize the data

seasonality and forecasting

  1. Calculate the two-year total for each month
  2. Compute the average for the two-year total (550)
  3. Divide the average (550) into the monthly totals to get the monthly seasonal index
  4. De-seasonalize the demand data by dividing by the monthly seasonal index

Step 2 – Establish a trend forecast

The simplest way to do this is by using linear regression and creating a straight line trend using the formula y = bx + a. In this formula:

  • b is the slope of the line and
  • a is the intercept on the y-axis
  • y is the de-seasonalized demand data
  • x is the demand periods

Now we need to compute the elements so that we can calculate:

  • The slope b
  • The intercept a

Here’s the formula to use:

forecasting & inventory

   a = 217.269

   b = 4.608

   y = x*4.608 +217.226

Using the formula above we enter x=25,26,27. That corresponds with each period up to 12 months into future to get the linear trend forecast, shown in the diagram below:

forecasting trend

Step 3 – Seasonalize the data with the trend forecast

Take the liner forecast and multiply by the seasonal trends for each period and you get the forecast as shown below:

seasonality with trend forecast

So now we have a forecast that has picked up the trend growth as well as the seasonality. The only things that remain are to add in market knowledge or promotional information.

  • Review and enter market info

Hold a meeting to review the key forecasts and enter market info, new product forecasts and any promotional impact on individual forecasts and cannibalization of other forecasts.  

  • Macro review and adjustment

Once the baseline forecasts have been agreed upon, then we can review them at a macro level by adding up all the forecasts multiplied by cost price, selling price or margin.

Once we have the summary, we can then adjust the summary and see a proration mechanism to raise or lower the forecast in a specific period as shown below:

Original forecast summary at cost

original inventory forecast

Adjusted for 10% growth in March

In the above example, the graphic shows that March has been increased by 10%. If we are happy with the macro view, we can use a simple prorating method to explode the changes down to a lower level, if required.

  • Monitor forecast performance

There are many methods that can be used to monitor forecasting performance, and one of them is to distill the bias between over- and under-forecasting.

This measure compares the forecast vs the actual sales and computes the difference between them in units. The units are then multiplied by the item cost and summed up for all over over-forecast and under-forecast items separately. These totals are then divided by the total cost of sales to produce an over- and under-forecast percentage. Here’s an example:

This measure is a very harsh measure, as it is a zero-based error measure, but it’s useful because it highlights key impacts on inventory health. A heavy bias towards under-forecasting will lead to stock-outs, and a bias towards over-forecasting will lead to excess stock.

The goal is to reduce the overall error as much as possible while keeping a reasonable balance between over- and under-forecasting.

  • Exception reporting

If we keep forecast history, then we can always compare the forecast to the actual sales demand, and compute the variance between them for any period, or periods. Once we have the variance, then we can rank forecasts in descending order of dollar variance.

This approach means that we are always focussing on items that are are going to create the largest dollar value of excess or cause the biggest dollar stock-outs

  • Forecast process

By adopting a structured monthly review following the steps listed above you will be able to get the best of both worlds in terms of:

  • Statistically created baseline forecasts with
  • Market and sales input

See the next lesson: Safety Stock

Streamline Your Forecasting

NETSTOCK can help you streamline your forecasting, benchmark your performance, and improve your inventory performance.

Get a FREE Demo