How Much Better are Your Forecasts?


A Financial Technique to Measure Forecast Performance


by Robert Duncan, CFPIM

Duncan Consulting, Ballston Lake, NY

Its annual performance review time. You are to quantify the improvements in your forecasts over the last year.

They are better, but how do you explain it to the boss? Do you say that your MAPE has reduced from 40% to 35%? Or do you say the MAD has declined 7%? If you were a salesman, you wouldn’t have trouble explaining the increased sales dollars. Or if you were the manufacturing manager, you could show the cost dollars saved from improved efficiency. The boss understands these measurements –– money.

You should use the same measurements that everyone else does. Express your accomplishments as a reduction in safety inventory dollars. It is not hard to show the relationship between forecast error and safety stock:

Since you wish to compare your current measurement against some baseline, you could pull out the planned inventory report from the beginning of the year and compare the bottom line with the most recent report. But if your standard cost, lead-time, replenishment frequency, or desired service changes in the interim, you will be comparing apples with oranges.

Here is a technique that you can use to measure only the impact of the forecast error. The results will be expressed in terms of safety stock dollars at the current level of service, current lead-times, and current standard costs. Only the forecast error will be different.

First you need to create a new SKU field called OLDMSE that stands for Old Mean Square Error (used to calculate the standard deviation, unadjusted for lead time). If you use alternative calendars (WEEK, MONTH, QTR, etc) also create an SKU field called OLDREV. In the beginning of the year do an expression edit: OLDMSE = MSE and OLDREV=REVISNS. Every period the revision process changes the MSE (Mean Square Error). If the forecasts become more accurate, MSE decreases. The MSE and safety stock increase if the forecasts become worse.

The OLDMSE and the current MSE ratio the current safety stock dollars:

It also is used to show a percentage improvement.

There is an additional adjustment for calendar changes using the Ratio of OLDREV and REVISNS.

Use the FGS report writer to run the DELTA-SS (delta safety stock) report which shows you the savings and loss from the changed forecast error. To satisfy the boss, all you need to do is show the bottom line by using the I=D print option.

From one of our FGS workshops, we recorded the MSE at the begining of our process using the PHNX sample database. Then we performed 3 months of forecast revisions - time flew by during the exercise, The class had 25 parts with improved forecasts reducing the safety stock by $733 K. And 20 parts had worse error that increased the safety stock by $42 K.

You needn’t worry about inaccurate reports for obsolete or new parts. If the SKU were made obsolete and the MSE is zero, the safety stock scaling would be zero and there would be no change. If the SKU is new and the OLDMSE is zero, the DIVIDEZERO function would give you a zero scale once again.

(The class added an SKU field to the database called NOTES. It is good information when we looked at the detail on the report.)

The biggest benefit of this report is in the detail. Using FGS’s command feature, we create a DELTA-SS list in descending order of Safety Stock $ Savings. A secondary sort is added for percentage where there is no safety stock dollars.

The report is run twice on the list. The first time favorable improvement printed. The second time the unfavorable improvement is run. A negative sign is inserted in the path to print the list in descending increase of safety stock order.

Finally the command ends by bringing up the simulation screen with the biggest dollar increase in safety stock. Here is the most opportunity to improve the few forecasts that cause most of the increase in safety stock target dollars. Review and repair this SKU’s forecast if you can. After filing it, the second worse SKU will show up. You can make a significant improvement quickly. These SKUs have probably hit the demand filter report sometime in the interim, without and change. Run the report again. Not bad, eh?

Another use of this report is to run it on the SKUs that had marketing intelligence. This clearly shows the value of improved forecasts and the cost of decreased intelligence. Run it on the SKU LIST called ‘MKTGINT’. In our workshops we distribute the command file called DELTA-SS.CMD and the report file called DELTA-SS.RPT on the workshop CDs.

For the command file and report definition email

Link to E/Step Software Home Page