I am trying to determine how accurate a forecast is.
For example, a customer requested 18 items. However, they only pick-up or take 6 items. The forecast of their needs is only 33.3% accurate.
Calculating a formula that can handle the reverse scenario is more difficult.
I used the following formula to calculate the "Accuracy" column below. However, you can see that it returns -%'s. Does anyone have a better idea about how this might be done?
=1-((STDEV.P(0, (A2-B2)*2))/A2)
<TBODY>
</TBODY>
For example, a customer requested 18 items. However, they only pick-up or take 6 items. The forecast of their needs is only 33.3% accurate.
- 18 forecasted
- 6 actual
- 33.3% accuracy
Calculating a formula that can handle the reverse scenario is more difficult.
- 16 forecasted
- 25 actual
- 44% accuracy (right?)
I used the following formula to calculate the "Accuracy" column below. However, you can see that it returns -%'s. Does anyone have a better idea about how this might be done?
=1-((STDEV.P(0, (A2-B2)*2))/A2)
Forecast | Actual | Accuracy |
16 | 16 | 100% |
14 | 0% | |
14 | 29 | -7% |
17 | 5 | 29% |
19 | 17 | 89% |
11 | 26 | -36% |
20 | 17 | 85% |
20 | 16 | 80% |
29 | 0% | |
<TBODY>
</TBODY>
Last edited: