Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show forecast accuracy of customer schedules as a percentage
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show forecast accuracy of customer schedules as a percentage
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show forecast accuracy of customer schedules as a percentage
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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show forecast accuracy of customer schedules as a percentage
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show forecast accuracy of customer schedules as a percentage
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" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show forecast accuracy of customer schedules as a percentage
Dave Dobson wrote:
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show forecast accuracy of customer schedules as a percentage
Yes, very helpful, thank you.
I have applied the first formula to give me deviation from forecast. Once again, many thanks. "vezerid" wrote: Dave Dobson wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
show customer based on Weekday | Excel Worksheet Functions | |||
How to show negative percentage? | Excel Worksheet Functions | |||
Show both value and percentage on Waterfall Chart | Charts and Charting in Excel | |||
show in a excel graphic a total percentage for 5 diff data fiels | Charts and Charting in Excel | |||
How can I conditional format to show either a percentage or a whol | Excel Discussion (Misc queries) |