Pivot Table Percentage of Grand Total Column

[Jacob]

Here is my problem. I am using Pivot Table to summarize financial data
to preform a margin analysis on it. Currently I receive a dump of data
from the system which turn is put into the pivot table. The way the
pivot table is currently displayed is as follows:

Sum of Amount Account
Customer 40000 43000 50000 Grand Total
ABC $4,000.00 $(50.00) $(3,000.00) $950.00
PDQ $6,000.00 $(350.00) $(4,000.00) $1,650.00

XYZ $5,000.00 $(500.00) $(3,500.00)
$1,000.00
Grand Total $15,000.00 $(900.00) $(10,500.00) $3,600.00

The 40000 and 43000 are the revenue, 50000 being the cost and Grand
Total is the Margin. What I would like it to do is calculate a
percentage of the Grand Total / (40000+43000) and look something like
this:

Sum of Amount Account
Customer 40000 43000 50000 Grand Total
Margin
ABC $4,000.00 $(50.00) $(3,000.00) $950.00 24%
PDQ $6,000.00 $(350.00) $(4,000.00) $1,650.00
29%
XYZ $5,000.00 $(500.00) $(3,500.00)
$1,000.00 22%
Grand Total $15,000.00 $(900.00) $(10,500.00) $3,600.00 26%

Is there an easy way if at all way to do this with the pivot table.
Right now I have to write the formula outside the pivot able to just
reference the individual cells. Problem with this is if the pivot
table changes after refreshing the data then I may lose that formula or
not cover all the new cells. Help please..

 

[steven1001]

To create pivot table in the layout you describe, it would appear that
the data has come out of a ledger and is probably in a format like...

Cust Acc $$
ABC 40000 4000
ABC 43000 -50
ABC 50000 -3000
PDQ 40000 6000
PDQ 43000 -350
PDQ 50000 -4000
XYZ 40000 5000
XYZ 43000 -500
XYZ 50000 -3500

Presumably the data is accessible somewhere so you can add some
'helper' columns beside the data. Add some extra columns so you get
the data in the format..
Cust Acc $$ Rev COGS
ABC 40000 4000 4000 0
ABC 43000 -50 -50 0
ABC 50000 -3000 0 -3000
PDQ 40000 6000 6000 0
PDQ 43000 -350 -350 0
PDQ 50000 -4000 0 -4000
XYZ 40000 5000 5000 0
XYZ 43000 -500 -500 0
XYZ 50000 -3500 0 -3500

The value in the column Rev is calculated using a formula..
=IF(LEFT(B2,1)="4",C2,0) and COGS is calculated by replacing the "4"
with "5".

The Margin is the sum of all the values in the $$ column and the
revenue from the Rev column. Drag Rev, COGS and $$ into the data area
of the spreadsheet and then create a calculated field of $$/Rev and
express as a %. If you need to have separate columns for Ac 40000 and
43000 then expand on the above.

Acc (All)
Data
Cust Rev COGS $$ Marg%
ABC 3950 -3000 950 24.05%
PDQ 5650 -4000 1650 29.20%
XYZ 4500 -3500 1000 22.22%
Grand 14100 -10500 3600 25.53%
Total

Hope this helps and would be delighted to hear that there is an easier
way!

regards..

 

[Jacob]

Steven,

Thanks for the response. Your assumptions are correct that I am
getting the data out of the General Ledger. What I have is a data dump
of a say months activities which includes all the invoices adjusting
Journal Entries to those invoices and the accounts associated with
them. I then break the data out into four seperate pivot tables one
each of the four types of sales that we could have. One invoice might
include three different types of sales categories. This is usually
determined by the first three digits of the account code. Because of
this it makes it difficult to preset the margins before putting the
data into a pivot table. I don't know if live data would help with
this situation or not, but let me know I will see what I can do for
you. Thanks for your help.

Jake

 

[steven1001]

The situation you outline is what I like about pivot tables .. the
margin % calc is done dynamically so it does not matter what
products/categories etc that you filter down to because calculation is
still worked out correctly. In the circumstance you detail you can not
calculate the percentages in the underlying data, it is only
'calculable' on the sum of the values in all the rows for a particular
product or product category as some rows will contain Revenue and some
COGS.

We use this approach for all financial reports so that a P&L, a Balance
Sheet or a sales/marging by customer or product/group are each just an
appropriately filtered and organised Pivot table based on the same set
of underlying GL transactions... usually directly connected to a view
of the GL transactions in the db.

Anyway .. I hope it helped

 

[Jacob]

Steven,

This works great, the problem that I am having is that I have about
twenty different revenue and COGS accounts. Is there another way other
than Adding addtionional rows as I would like to stray away from having
twenty more rows.

Thanks,

Jacob

 

[steven1001]

not sure I understand your issue.

If you want to have less rows in the pivot table then you can create a
label that can be used in the pivot table which pre-summarises some of
the data and then use that label to group by. (For example, put a
label which says "Rev" or "COGS" beside each sales and cost of sales
account)

Also, if the data is presently displayed like..

product category
product 1 100
product 2 100
product 3 100
------
300
------
you can 'double-click' the 'product category' heading and the
individual product lines will be 'undisplayed'

Some techniques for manipulating data are discussed in an (old!)
article at www.adaxa.com.au - use the search field to search for the
word 'pivot'.

regards...

 

[kletcho]

You could try throwing the $ volume into the pivot table twice. Take
the second instance of the $ field and go into the field properties.
Select to see further options, then from the drop down chose % to
total. Then you would see both $ and % to total. It gives you a
percent for each account as well, but it is a quick solution to your
question. You could hid ehte columns for any % to totals you don't
want to see.