How to Estimate Lost Sales Costs Better Calculator

Executive Summary

  • Our lost sales calculator estimates lost sales so that it can be used to set service levels.
  • We offer two versions of the cost of inaccuracy calculator.

Video Introduction: Quantification of Higher Forecast Error

Text Introduction (Skip if You Watched the Video)

The background as to the overall topic of forecast error cost estimation is explained in this article. This article contains the calculator for one of the fees associated with forecast error, which is a lost sale cost. In obtaining these costs, two calculation methods are presented below.

Having two calculations is helpful because one may have some data in some circumstances and not others. Secondly, using both calculations (if the data is available) can average the two. Unless quantification is performed for every product location combination, there is no way to accurately estimate the improvement in forecast error related to lost sales. And even in cases where the new forecast error is determined through accurate testing, there will still be a variance when the latest forecast is rolled out. However, the objective should not be to think that the estimate will be perfect but to get the expected close. 

Our References for This Article

If you want to see our references for this article and related Brightwork articles, visit this link.

The Quantification of Forecast Error

This article contains the calculator for one of the expenses associated with forecast error, which is the cost of reduced premium procurement.

Forecasting Accuracy, Lost Sales, and Inventory Costs

The costs of lost sales and excess inventory costs are prominent places to look for the expenses of forecast error. What is shown below are two calculations for the estimation of each?

Cost #1: Costs of Over Forecasting

The Number of Units Over Forecasted X The Cost Per Unit X A Yearly Inventory Carrying Cost (should be applied for both finished goods and raw materials, components, and even packaging materials)

Cost #2: Costs of Under Forecasting

The Number of Units Under Forecasted X The Margin Per Unit X a Client Determined Multiple

If we take a typical example of the following assumptions:

  • Product Cost = $5
  • Product Margin = $5
  • Yearly Inventory Carrying Cost = 30% (translates to a 2.5% carrying cost per month)
  • Multiple = 2

…we end up with a ratio between the under forecasting and the cost of over forecasting 80. This essentially would mean that the cost of over forecasting was inconsequential.

What Following an Unbalanced Model of the Costs of Over Forecasting Versus Under Forecasting Would Mean?

If this ratio were followed, the company could justify carrying a too high safety stock and over forecasting every month. I don’t know any company that behaves this way. Most companies refuse to carry the calculated value of safety stock, as is explained in this article.

Apparently, a different model for lost sales must be developed if we are to come to an excellent relative cost of under and over forecasting.

The Common Problems of the Costs of Lost Sales Set Too High

This metric’s primary purpose is to determine investment levels in forecasting to improve the forecast. However, it’s problematic to have a metric that can be easily gamed only by doubling forecasting. One would like to have a metric where the costs of doing one of the other are a bit closer so that the costs can be traded off. At an 80 to 1 ratio, these values cannot be traded off.

This problem faces other areas of supply chain planning, most notably when a cost optimizer is used for supply planning. This is because a supply planning cost optimizer requires estimating penalty costs that can be traded off against specific costs like inventory and production costs.

As explained in this article, many cost optimization projects have such a high value set for the expense of lost sales that it overwhelms the other expenses placed in the optimizer.

Note: The fields below start with default values. However, all but the final field — which is the output field, can be changed. The output field continually updates based upon the input fields – and there is no button to hit to get the output field to update.

  1. Version two of this calculator is easier to find data because it begins with the company’s revenues. If you are only estimating a sub-segment of the business, then only the income from that sub-segment should be used.
  2. These formulas have different default settings, so there is no reason for the output to be close. However, in a real scenario, there should be some correspondence between the two output values.

Why Do the Standard Forecast Error Calculations Make Forecast Improvement So Complicated and Difficult?

It is important to understand forecasting error, but the problem is that the standard forecast error calculation methods do not provide this good understanding. In part, they don't let tell companies that forecast how to make improvements. If the standard forecast measurement calculations did, it would be far more straightforward and companies would have a far easier time performing forecast error measurement calculation.

What the Forecast Error Calculation and System Should Be Able to Do

One would be able to for example:

  1. Measure forecast error
  2. Compare forecast error (For all the forecasts at the company)
  3. To sort the product location combinations based on which product locations lost or gained forecast accuracy from other forecasts. 
  4. To be able to measure any forecast against the baseline statistical forecast.
  5. To weigh the forecast error (so progress for the overall product database can be tracked)

Getting to a Better Forecast Error Measurement Capability

A primary reason these things can not be accomplished with the standard forecast error measurements is that they are unnecessarily complicated, and forecasting applications that companies buy are focused on generating forecasts, not on measuring forecast error outside of one product location combination at a time. After observing ineffective and non-comparative forecast error measurements at so many companies, we developed, in part, a purpose-built forecast error application called the Brightwork Explorer to meet these requirements.

Few companies will ever use our Brightwork Explorer or have us use it for them. However, the lessons from the approach followed in requirements development for forecast error measurement are important for anyone who wants to improve forecast accuracy.