Hello,

I've been trying to do this in MS Access 2007 but may be you guys can figure out a way to do this in Excel. It'd be way awesome to do it in a pivot table and automatically roll up the groups (you'll see in problem statement below). I started in Excel but moved to Access to create over 50 subqueries.

My hair is literally turning white trying to figure this out. I'm not sure what is the best way to do this so I hope you experts can help me. I'm not at work so I don't have the examples with me. It's not that complicated.

Basically I have a table called "EDW" which is an SAP extraction. It has a list of items, each item has a date for how many historical demands there were and how much was the absolute error (abs(Historical Demand - Total Forecast) for that month.

Example:

Item - Month - Historical Demand - Absolute Error - Group
X 03/01/2012 100 50 A
X 04/01/2012 100 50 A
X 05/01/2012 100 50 A
X 06/01/2012 100 50 A
X 07/01/2012 100 50 A
X 08/01/2012 100 50 A
X 09/01/2012 100 50 A
X 10/01/2012 100 50 A
X 11/01/2012 100 50 A
X 12/01/2012 100 50 A
X 01/01/2013 100 50 A
X 02/01/2013 100 50 A
X 03/01/2013 100 50 A
X 04/01/2013 100 50 A
X2 03/01/2012 100 50 B
X2 04/01/2012 100 50 B
X2 05/01/2012 100 50 B
X2 06/01/2012 100 50 B
X2 07/01/2012 100 50 B
X2 08/01/2012 100 50 B
X2 09/01/2012 100 50 B
X2 10/01/2012 100 50 B
X2 11/01/2012 100 50 B
X2 12/01/2012 100 50 B
X2 01/01/2013 100 50 B
X2 02/01/2013 100 50 B
X2 03/01/2013 100 50 B
X2 04/01/2013 100 50 B


There are about 9,000 items and 24 months of data and probably 12 groups, then those groups have a group, and those groups actually have a group.

Think of it as Toyota Corporate having 2 brands of Cars... Toyota and Lexus thats your highest group, then you might have a set of models like (SUV, Sedan, Sports Car), then under that group you have the specific models, then under that group you have the parts of the car. Basically we want to know the forecast accuracy at the specific models, then again rolled up the set of models, then rolled up to either Lexus or Toyota then all the way up to Toyota Corporate.


Now the formula is as such:

MAPE: Sum (Last 12 Months of Error / Sum of last 12 Months of Historical Demand)*100

then get your Group Weight.... So, it'll be the Sum of Demands for each item X / Sum of Demands over the Group

Then its MAPE * Group Weight

So the main challenge I'm facing before I get to the stacking of the groups is getting the sum of the rolling 12 months.

It'd mean the world to me if you'd help! I'd buy you guys so much beer.

Thank you!!!