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 :

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

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?.....