Hi experts...
Firstly, I have an access database which contains some forecasting data.
We are trying to use an excel pivot table linked into the access databse (data import external data, import data) to provide some reporting.
Consider this sample data in the database...
PERIOD Division Material Forecast Actual
1 AA A 225 200
1 BB B 220 240
1 AA C 285 300
1 BB D 290 270
2 AA A 250 230
2 BB B 240 260
2 AA C 250 210
2 BB D 240 275
We are trying to calcualte some performance metrics from this data.
The Metrics are called MAPE and BIAS.
The formula for MAPE is (ABS(ForecastQty-ActualQua
ntity)) / Actual Quantity.
The formula for BIAS is (ForecastQty-ActualQuantit
y)/ Actual Quantity.
When the pivot table rolls up to say the division level the calcualtion should look like this...
Division AA, Period 1 Forecast = 510
Divison AA, Period 1 Actual = 500
Division AA, Period 2 Forecast = 500
Divison AA, Period 2 Actual = 440
The metric calculations follow:
MAPEp1= (abs(510-500))/500 = 2%
MapeP2=(abs(500-440))/440 = 13.64%
Biasp1=(510-500))/500 = 2%
Biasp2=(500-440))/440 = 13.64%
Here is the tricky part...
To calculate the MAPE of division AA,
take the average of MAPEp1 and MAPEp2 = 2% + 13.64% / 2 = 7.82%
To calcualte the BIAS of division AA,
Add the Biasp1 and Bias P2 = 2% + 13.64% = 15.64%.
Rinse and repeat for division BB.
These are the values I am trying to achieve when the pivot table does not contain period or as an alternative as subtotals.
The final result should look like this:
Division MAPE BIAS
AA 7.82% 15.64%
BB 5.14% 10.28%
or like this when period is included....
Division PERIOD MAPE BIAS
AA
1 2.00% 2.00%
2 13.64% 13.64%
AA Total 7.82% 15.64%
BB
1 0.00% 0.00%
2 10.28% 10.28%
BB Total 5.14% 10.28%
Thanks in advance....
Start Free Trial
