3 Replies Latest reply: Jun 10, 2016 1:02 AM by Prasenjit Gupta

# Displaying KPIs for Forecast Variance

I have the following sample dataset.

DateForecasted ValueActual ValueDifferenceMod(Difference)Mod(Difference)/Forecasted Value as %
1-Jan-16111101091%
2-Jan-161028880%
3-Jan-16936667%
4-Jan-16844450%
5-Jan-16752229%
6-Jan-1666000%
7-Jan-1657-2240%
8-Jan-1648-44100%
9-Jan-1639-66200%
10-Jan-16210-88400%
11-Jan-16111-10101000%
Sum6666060

I want to include the following calculation results as KPIs (Please review the attached excel for complete formula details)

Overall % Variance calculated based on Sum(Mod(Variance)) / Sum(Forecasted Value) - 90.91%

Average % Variance calculated based on Average(Sum(Mod(Difference)/Forecasted Value)) - 186.92%

What should be the calculation for displaying the above values as KPIs?

• ###### Re: Displaying KPIs for Forecast Variance

Overall % looks good to me, you jut need to use fabs to find the absolute value for variance -> Sum(fabs(Variance))/Sum([Forecasted Value])

or this if Variance is calculated as well:

Sum(fabs([Forecasted Value] - [Actual Value]))/Sum([Forecasted Value])

• ###### Re: Displaying KPIs for Forecast Variance

The Average % can be calculated using this:

=Num(Avg(fabs([Forecasted Value] - [Actual Value])/[Forecasted Value]), '##.00%')

Overall%

=Num(Sum(fabs([Forecasted Value] - [Actual Value]))/Sum([Forecasted Value]), '##.00%')

• ###### Re: Displaying KPIs for Forecast Variance

Thank you, I will check it and will respond back over the weekend.