Pivot table to calculate variance showing budget and actual.

[YY san.]

Hi,
I know this is rather simple, but I just could not get it right. I have this
data:

Expenese Type Amt Scenario
Staff cost 200 Budget
Admin cost 75 Budget
Office rent 5000 Budget
Staff cost 100 Actual
Admin cost 45 Actual
Office rent 300 Actual
I am able to get the difference using the pivot table "Difference from", but
it only showed that single column. I would like my pivot table to show:

Expenese Type Budget Actual Variance
Staff cost 200 100 100
Admin cost 75 45 30
Office rent 5000 300 4700

Appreciate all guidance and hints.
Thanks!

 

[trip_to_tokyo]

EXCEL 2007

I have just put up a file for you at:-

http://www.pierrefondes.com/

Item number 95 (towards the top of my home page at the time of posting).

This appears to give you what you want.

If you agree please hit Yes.

Thanks.

 

[Ashish Mathur]

Hi,

You should convert the items under the Scenario column I.e. Budget and
Actual to columns instead. After the restructuring, there would be 4
columns in your source data - Expenses type, Amt, Base and Actual. Now
create a pivot from this and then you may write a calculated field

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com