Finance - P&L Tracking – Customised & Nested Pivot Tables

Finance - P&L Tracking – Customised & Nested Pivot Tables

In this sample, we track Actuals against Budgeted amounts, for each P&L line, showing variances too, in a Pivot Table. Simple Pivot Tables are relatively easy to create and use, but for this file we add the condition of also needing to show the additional lines such as ‘Gross Profit’, ‘Operating Income’, which do not appear in the source GL data - in effect we need to create a highly-customised customised Pivot Table.

Data Inputs

For this sample, we take the following data inputs:

Dimensions

The dimension tables will be determined by the current structure of the business

Accounts Structure

This is where we define the layout and mappings of the P&L lines to different Headings and Sub Accounts

General Ledger

The GL here contains both the Actuals and Budgeted data for each month, so both datasets are already available to us in a nice, easy to work with format, in the same table.

P&L Tracking Measures

The following specific measures are shown:

1. Actuals
2. Budget
3. Variance $
4. Variance %

Dashboard - Snippet

Unlike most of our other samples, the final output here in presented in Excel itself, rather than through a Power BI dashboard – which is great for visuals, but not as strong with tables. When it comes to just pivot tables, it is a matter of preference.

The final file can also be downloaded here, if you want to review the formulae in detail: Download Sample cloud_download

Click to view large-sized image

Data Model - Snippet

The final data model used is shown here:

Click to view large-sized image

Measures

SUM GL Amt

SUM GL Amt :=
SUM ( 'General_Ledger'[Amount] )

GL Amt (Correct Signs)

GL Amt (Correct Signs) :=
SUMX ( Accounts, [SUM GL Amt] * Accounts[Report Sign] )

Actual Amt

Actual Amt :=
CALCULATE ( [GL Amt (Correct Signs)], Scenarios[ScenarioName] = "Actual" )

Rev Act

Rev Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Revenue" )

CoS Act

CoS Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Cost of Sales" )

GP Act

GP Act :=
[Rev Act] + [CoS Act]

OpEx Act

OpEx Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Operating Expenses" )

OI Act

OI Act :=
[GP Act] + [OpEx Act]

OI&E Act

OI&E Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Other Income & Expense" )

D&A Act

D&A Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Depreciation & Amortization" )

II&E Act

II&E Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Interest Income/Expense" )

Taxes Act

Taxes Act :=
CALCULATE ( [Actual Amt], Headers[Header] = "Taxes" )

NI Act

NI Act :=
[OI Act] + [OI&E Act] + [D&A Act] + [II&E Act] + [Taxes Act]

IS Subtotal Act

IS Subtotal Act :=
IF (
    COUNTROWS ( VALUES ( Headers[Header] ) ) = 1,
    SWITCH (
        VALUES ( Headers[Header ID] ),
        1, [Rev Act],
        2, [CoS Act],
        3, [GP Act],
        4, [OpEx Act],
        5, [OI Act],
        6, [OI&E Act],
        7, [D&A Act],
        8, [II&E Act],
        9, [Taxes Act],
        10, [NI Act],
        BLANK ()
    ),
    BLANK ()
)

Actual Total

Actual Total :=
IF (
    MAX ( Headers[Summary] ) = 1
        && (
            MAX ( Headers[Show Detail] ) = 1
                || COUNTROWS ( VALUES ( Subheader[Subheader] ) ) > 1
        ),
    [IS Subtotal Act],
    [Actual Amt]
)

Budget Amt

Budget Amt :=
CALCULATE ( [GL Amt (Correct Signs)], Scenarios[ScenarioName] = "Budget" )

Rev Bud

Rev Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Revenue" )

CoS Bud

CoS Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Cost of Sales" )

GP Bud

GP Bud :=
[Rev Bud] + [CoS Bud]

OpEx Bud

OpEx Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Operating Expenses" )

OI Bud

OI Bud :=
[GP Bud] + [OpEx Bud]

OI&E Bud

OI&E Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Other Income & Expense" )

D&A Bud

D&A Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Depreciation & Amortization" )

II&E Bud

II&E Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Interest Income/Expense" )

Taxes Bud

Taxes Bud :=
CALCULATE ( [Budget Amt], Headers[Header] = "Taxes" )

NI Bud

NI Bud :=
[OI Bud] + [OI&E Bud] + [D&A Bud] + [II&E Bud] + [Taxes Bud]

IS Subtotal Bud

IS Subtotal Bud :=
IF (
    COUNTROWS ( VALUES ( Headers[Header] ) ) = 1,
    SWITCH (
        VALUES ( Headers[Header ID] ),
        1, [Rev Bud],
        2, [CoS Bud],
        3, [GP Bud],
        4, [OpEx Bud],
        5, [OI Bud],
        6, [OI&E Bud],
        7, [D&A Bud],
        8, [II&E Bud],
        9, [Taxes Bud],
        10, [NI Bud],
        BLANK ()
    ),
    BLANK ()
)

Budget Total

Budget Total :=
IF (
    MAX ( Headers[Summary] ) = 1
        && (
            MAX ( Headers[Show Detail] ) = 1
                || COUNTROWS ( VALUES ( Subheader[Subheader] ) ) > 1
        ),
    [IS Subtotal Bud],
    [Budget Amt]
)

Variance $

Variance $ := [Actual Total] - [Budget Total],

Variance %

Variance % := DIVIDE ( [Variance $], [Budget Total] )