Show forecast accuracy of customer schedules as a percentage

[Guest]

A customer forecasts that they will take 20 units next month. At the end of
the month it turns out that they only took 15 units. How accurate was their
forecast? Can this be displayed as a percentage? The values change every
month and there are many different items to analyse.

Also vice versa, 15 units forecast and 20 units taken.

Version 10.0.2614

 

[vezerid]

If you want to express the actual as a percentage of the forecast, the
formula is:
=actual/forecast.
So, 15 is 15/20 = 75% of forecast 20.

If you want to express the percentage as a deviation from forecast then
you need the difference:
=(actual-forecast)/forecast
So, 15 is -5/20 = -25% less than forecast.

Does this help?
Kostis Vezerides

 

[Guest]

Thanks for quick reply.

Forecast is 15, Actual is 20.
=forecast/actual gives me 75% indeed.

However, if forecast=418 and actual=320, then 418/320=131% when cell is
formatted as percentage. ?

In this instance where actual is less than forecast should I switch the
formula to =actual/forecast? Therefore 320/418=77%.

As I want to see how accurate the customer was in it's forecast, I think 77%
is a better figure (if it is correct). The customer then, was only 77%
accurate, irrespective of whether actual is greater than forecast or vice
versa.

 

[vezerid]

Dave,

First, if you examine the accuracy of the forecast, it must stay in the
denominator.
Second, why are you troubled with percentages greater than 100? A 131%
means exactly this: that the actual was 31% more than the forecast.

The second formula I gave you essentially is maybe better for your
thinking. It would return
-25% for a lower actual and +31% for a higher actual. Both are
percentages on the forecast.

Does this help?
Kostis

 

[Guest]

Ok. So I can see the percentage of what was taken (actual). If this was
less than forecast, I would expect to see a figure such as 75%. If it was
more than the forecast, I would expect to see a figure such as 131%. Or, as
descibed, 31% more than forecast.

Forecast 'accuracy' though.

If forecast=50 and actual=50, then this was 100% accurate.

If forecast=50 and actual=60, then this was not 120% accurate. The value
should be less than 100%, do you see what I mean?

 

[vezerid]

If forecast=50 and actual=50, then this was 100% accurate.

If forecast=50 and actual=60, then this was not 120% accurate. The value
should be less than 100%, do you see what I mean?

If forecast=50 and actual=60 then you are (actual-forecast)/forecast =
(60-50)/50 = 20% INACCURATE or you deviated from your forecast by 20%
upwards.

If forecast=50 and actual=40 then (actual-forecast)/forecast =
(40-50)/50 = -20%. You deviated from your forecast by 20% downwards.

In both cases you have a percentage over the forecast figure, with a
sign.

Now, all this analysis is if you want to express your deviation w/
respect to your forecast. If you want to examine the forecase w/
respect to the actual figure then the actual figure will have to go to
the denominator.

If forecast=50 and actual=60 then you have (forecast-actual)/actual =
-10/60 = -16.67%. You underestimated actual by 16.67%.
If forecast=50 and actual=40 then you have (forecast-actual)/actual =
10/40 = 25%. You overestimated actual by 25%.

131% (depending on what is in the denominator) can be 31%
overestimation.

I hope this helps.
Kostis

 

[Guest]

Yes, very helpful, thank you.

I have applied the first formula to give me deviation from forecast.

Once again, many thanks.