Demand Planning.Net: Are you Planning By Exception?

# Frequently Asked Forecasting and Modeling Questions

Post your questions here for a customized response from Mark Chockalingam and the rest of the DPNet expert team.

1. What is MAPE?

Mape is Mean Absolute Percent Error. This is defined as the Average Absolute Error divided by the Average of the Actual Quantity. A more elegant way to compute this would be to divide the Sum of the Absolute Error by the Sum of the Actual Quantity.

MAPE = (Sum of Abs. Error) / (Sum of Actuals)

2. Should we divide the Error by Actual or Forecast? Why one over the other?

Most commonly Error is divided by the Actual rather than forecast. This avoids certain self-induced biases by the forecaster. If we divide by forecast, then the forecaster has the incentive to overforecast when in doubt. A higher forecast drives MAPE lower and accuracy higher.

3. If we divide by Actuals, and when actuals are zero, MAPE is undefined (excel gives #DIV/zero error). What to do?

When actuals are zero, MAPE is infinite. By definition, forecast error can be greater than 100%. However, accuracy cannot be below zero.

Forecast Accuracy = max (1 - forecast error, 0)

If Actuals are 25 and forecast is 100, then error is 75 implying a 300% error. But accuracy is always zero for cases where error is higher than 100%. You can use the ISERROR function in excel to overcome the DivisionByZero error. Although this is an excel workaround, this actually preserves the impact of the actual being zero in calculating the total forecast error. See the excel accuracy template available in the downloads section!

4. If Forecast error is greater than 100%, is accuracy negative?

By definition, Accuracy can never be negative. As a rule, forecast accuracy is always between 0 and 100% with zero implying a very bad forecast and 100% implying a perfect forecast.

5. What is RMSE?

RMSE stands for Root Mean Squared Error. This is an alternative to measuring absolute errors. Here you compute the square of the error and take a square root of the total.

6. What is weighted forecast error?

Weighted forecast error just turns the MAPE into a weighted MAPE calculation. Each absolute error is weighted either by price or some other factor of importance. The weighted MAPE is the sum of all such weighted errors divided by the sum of the Actual volume similarly weighted. We can calculate a price weighted MAPE or a discrete-weighted MAPE that ranks items based on importance.

7. Why is Mean Percent Error not useful?

Mean percent error uses a simple average of computed forecast errors. The problem with this measure is that it may weight low volume items disproportionately. You may have a high forecast error on items that only are shipped a few units each month. Typically items with such spotty volumes are the hardest ones to forecast. Such items should be dealt with using an alternative supply chain strategy instead of focusing on their demand forecast accuracy. Hence Mean Percent Error could create incorrect incentives.

8. In particular product-market scenarios there could be situations whereby, depending on difference between what I bought the product at and what I could sell it at, I could actually make profit for the organization but with higher forecast error.
Is there a merit in calculating forecast accuracy for dollarized sales and decomposing it into Unit error and Price error?

There is every merit in tracking Forecast Accuracy in any organization. In fact, we will go on to say that the single most important metric in the entire organization is Sales Forecast accuracy. Unattended forecast error can be the root of most organization ills. Although it can manifest in many other forms, the diagnostic process has to be diligent to observe the root of the forecast error.

In this specific situation, what you really need to observe is the Unit forecast error which will abstract away from profit margins. So for performance measurement reasons, you should break this into two variance measures, namely the unit forecast error and the price variance or the profit variance.

You may want to reward the sales person who actually met his sales targets in units (or exceeded slightly) and exceeded the profit variance significantly. So the reward system will motivate unit sales within a smaller tolerance but profit margins with a larger tolerance.

Remember that a substantial positive unit variance is not necessarily a good thing. If the Sales forecast is selling 100K gallons of fuel while the actual sales performance was 200K gallons, the supply chain may be unprepared to meet this sales reality and may have to go through unnecessary expediting costs.

In the case of price variance, there is more tolerance but an exceedingly high price variance needs to be examined as well. This may earn a bad reputation for the firm as opportunistic. This may also illustrate a bad price forecast.

9. Why do you measure accuracy/error as forecast-actual / actual and not over forecast?

Historically Sales groups have been comfortable using forecast as a denominator, given their culture of beating their sales plan. Since most of the demand planning evolved from Sales function, MAPE was also measured this way. So this was mostly cultural. In such a scenario, Sales/Forecast will measure Sales attainment. For example, sales of 120 over 100 will mean a 120% attainment while the error of 20% will also be expressed as a proportion of their forecast. So it was more of a convenience for Sales Management.

However, more scientifically, the denominator is designed so that it will control functional bias in the forecasting process. Since Supply Chain is the customer of the forecast and directly affected by error performance, an upward bias by Sales groups in the forecast will cause high inventories. So if Demandplanning reports into the Sales function with an implicit upward bias in the forecast, then it is appropriate to divide by the Actual Sales to overcome this bias. Using Actuals is also ideal because it is not under the control of the forecaster. If we use forecast as the denominator, the forecaster can improve accuracy marginally by consistently over-forecasting.

But there is a trend in the industry now to move Demandplanning functions into the Supply Chain. If Supply Chain is held responsible for inventories alone, then it will create a new bias to underforecast the true sales. If MAPE is using Actuals, then you can improve forecast accuracy by under-forecasting while the inventories can be managed below target.

10. In calculating safety stock, you state that lead times are determined externally. Is the lead time given to you by the customer (which could vary enormously from customer to customer for the same SKU) or internal lead times for replenishment of inventory of the SKU?

Depending on where the safety stock policy is set, the leadtimes are determined by the production or distribution process. For example, if Plant A takes three months to produce and ship a product to Distribution point XY, the lead time for safety stocks here is three months for setting inventory policy at Distribution Point XY. To be consistent, the safety stock at XY is based on the demand forecast error at this DC. This demand forecast error will be a function of the individual customer demand forecast errors at this DC.

To answer this question, the leadtime for setting safety stock at this DC does not depend on the customer lead time, just the production and transportation internal lead time. However, the forecast error at this DC will be subject to the vagaries of fluctuating customer demand. The key is to remember that the safety stock is set based on the source lead time. The following chart may be helpful:

In the above is XY is the our Distribution Point and YZ is the factory. MX is the customer's Receiving point. The lead time for the customer to receive the product is 3 weeks from our Distribution Point XY. The lead time for our DC to receive the product from the factory including production and shipping is 5 weeks. With this set up,

1. The safety stock in XY will use LT = 5 weeks. It does not matter what the lead time is between XY and MX, the customer's distribution point.

2. The safety stock in MX will use LT = 3 weeks. The customer DC MX does not care about the manufacturer's internal lead time.

If you carefully notice, we assume that the factory does not have any finished goods inventory of its own and all of the finished product is shipped to the DC. If the factory does carry its own inventory, then the LT at XY will be shorter than five weeks.

11. When calculating MAPE what is recommended when actuals are positive but forecast is 0 (for example when clearing obsolete stock) Currently in these circumstances we make the forecast match the actuals so as not to penalize the forecaster for something that was not forecastable. Is this the correct way to measure these scenarios?

That is an excellent practical question in demand management.

Our recommendation is to exclude the Obsolete Skus from measurement and in computing the aggregate MAPE as a performance measure for the planner or for the Sales Manager responsible. Clearing obsolete stock is a supply management activity not a demand forecasting activity. Demand Plans are externally focused and are a representation of what the market wants. The obsolescence of stock may be a result of inferior demand forecasting in the past but has nothing to do with the demand for such stock in the current period. In essence, there is no demand for that stock - either it is sold for scrap or just donated.

Doing what you are doing will have a downside as well. This will artificially inflate the forecast performance of the planner on a weighted basis and hence hide poor forecasting on active open stock items. For example, you sell 25K units of active SKU for which you forecasted only 5K. Then you have 100K of obsolete stock you sell. Under your measure the MAPE will result in a 16% error or an 84% accuracy. In reality the MAPE on your active SKU is 80% or 20% accuracy. The 100% accuracy on the obsolescence is a manufactured number but wrongly aggregates to influence the divisional MAPE.

12. How MAPE and WMAPE is calculated and what is the statistical interpretation of both. Which one should be used in a CPG company to calculate Forecast error. Is there any better alternative that will allow us to take a quicker action and get quicker results?

MAPE calculated in the above is actually a volume-weighted MAPE. Simple MAPE as a percentage average as used by academic statisticians is neither useful nor meaningful for performance measurement in supply chains. You could also weight the MAPE either by Price or by subjective weights assigned on the basis of criticality to supply chain.

As a time measure over the last three to six months, you may also want to weight this by how close this is to the recent period. A volume weighted MAPE is a pretty good measure in CPG companies. I would also think about using forecast bias measured as (Actual - Forecast) / Actual summed across all the items.

You may want to review our on-demand course on Value Chain Metrics At http://valuechainplanning.com/elearning/.

Do you have a Question? You can post your questions here for a customized response. An Archive of past questions and informative discussions are available for registered users of this site. Please contact us with your information for access to the archives along with a reference to the DP serial number of each question.