Forecasting Accuracy in Excel

[manishc1989]

Hi All,

Need to know how to calculate forecasting accuracy in excel and which is the best way to measure forecast accuracy. Quick response will be highly appreciated.

Sample Data :

	Actual P1 Calls	Forecasted P1 Calls	Forecasting Accuracy % ???
1-Jul	500	600
2-Jul	600	900
3-Jul	480	400
4-Jul	465	500
5-Jul	789	750
6-Jul	450	500
7-Jul	578	400
8-Jul	586	600
9-Jul	595	700
10-Jul	603	600
11-Jul	612	450
12-Jul	621	650
13-Jul	629	675
14-Jul	638	462
15-Jul	647	650
16-Jul	655	750
17-Jul	664	485
18-Jul	672	760
19-Jul	681	741
20-Jul	690	400
21-Jul	698	625
22-Jul	707	650
23-Jul	716	700
24-Jul	724	900
25-Jul	733	950
26-Jul	741	700
27-Jul	750	650
28-Jul	759	780
29-Jul	767	450
30-Jul	776	850
31-Jul	785	950

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Regards,
Manish Chopra

 

[jim may]

Why not provide for us what you expect the answer(s) in Column D to be in the first 5 or 6 cell, if they are representative.

 

[Matty]

Maybe:

=1-ABS(C2-B2)/C2

Which says a forecast of 600 and an actual of 500 is 83% accurate.

Matty

 

[manishc1989]

Thanks Matty,

Really appreciate your Help . Could you please elaborate more about the logic used in the formula.

 

[Matty]

Thanks Matty,

Really appreciate your Help . Could you please elaborate more about the logic used in the formula.

No problem.

One of the most common ways of calculating forecast accuracy is to calculate the absolute difference between the forecast and the actual, then divide this amount by the forecast. This value is then taken away from 1 to give us the accuracy (represented as a percentage).

Matty

 

[manishc1989]

No problem.

One of the most common ways of calculating forecast accuracy is to calculate the absolute difference between the forecast and the actual, then divide this amount by the forecast. This value is then taken away from 1 to give us the accuracy (represented as a percentage).

Matty

Thanks. Got it. You rock!

 

[Matty]

Good. Glad it's what you were after.

Matty

 

[IsabelMatilde]

No problem.

One of the most common ways of calculating forecast accuracy is to calculate the absolute difference between the forecast and the actual, then divide this amount by the forecast. This value is then taken away from 1 to give us the accuracy (represented as a percentage).

Matty

If the Actual or the Forecast have negative numbers, the formula does not work. Correct?.....