Info

something like Avg Price. Note that this name must be different than the name for the calculated field.

The following code produces the report shown in Figure 12.26:

Sub AccountingReport() Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long

Set WSD = Worksheets("Pivot Table")

1 Delete any prior pivot tables For Each PT In WSD.PivotTables

PT.TableRange2.Clear Next PT

1 Define input area and set up a pivot cache FinalRow = WSD.Cells(65536, 1).End(xlUp).Row Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=PRange.Address)

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("J2"), _

TableName:="PivotTable1") PT.ManualUpdate = True 1 Set up the row fields

PT.AddFields RowFields:="Product", ColumnFields:="Data"

1 Define Calculated Fields

PT.CalculatedFields.Add Name:="AveragePrice", Formula:="=Revenue/Quantity" PT.CalculatedFields.Add Name:="AverageCost", Formula:="=COGS/Quantity" PT.CalculatedFields.Add Name:="GP_Pct", Formula:="=Profit/Revenue"

' Set up the data fields With PT.PivotFields("Quantity") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0" .Name = "Total Qty" End With

With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 2 .NumberFormat = "#,##0" .Name = "Total Revenue" End With

With PT.PivotFields("AveragePrice") .Orientation = xlDataField .Function = xlSum .Position = 3

.NumberFormat = "#,##0.00" .Name = "Avg Price" End With

With PT.PivotFields("COGS")

.Orientation = xlDataField .Function = xlSum .Position = 4 .NumberFormat = "#,##0" .Name = "Total COGS" End With

With PT.PivotFields("AverageCost") .Orientation = xlDataField .Function = xlSum .Position = 5

.NumberFormat = "#,##0.00" .Name = "Avg Cost" End With

With PT.PivotFields("Profit") .Orientation = xlDataField .Function = xlSum .Position = 6 .NumberFormat = "#,##0" .Name = "Gross Profit" End With

With PT.PivotFields("GP_Pct") .Orientation = xlDataField .Function = xlSum .Position = 7 .NumberFormat = "#0.0%" .Name = "GP%" End With

1 Ensure that we get zeroes instead of blanks in the data area PT.NullString = "0"

1 Calc the pivot table PT.ManualUpdate = False PT.ManualUpdate = True

End Sub

Figure 12.26

All these calculations are straightforward and produce a useful report that enables the CFO to measure product line profitability.

0 0

Post a comment