Forecast performance analysis (PowerPivot or Pivot)

[Z_Tags]

I am trying to compare forecast to actual for items sold (units only). Because I have a quantity for forecast and a quantity for actual, how should I set up the master data file to capture the infomration in a way that I can insert a calculated field to show unit difference and % difference whether it is Month over Month or Year over year?


I have it so I can see forecast and actual info side by side in a month but can't put a calculation field in....VERY FRUSTRATED!!!


Thanks a bunch in advance!

Courtney

 

[vijaySharma]

Tags,


I created a very simple sheet


Month----Qty-----Forecast

Jan------151-----165


and so on till Dec.


Create a Pivot...


and insert 2 calculated fields


below is the formula I used...


Unit_Diff = FC-Qty

Pert_Diff =(Qty-FC )/FC


Try this out and let us know.


~VijaySharma