How to create a P&L with Pivot Table (with Gross Profit, Net Profit and Margins calculations)

Create a new Excel workbook

Get the data source. Assuming that it is clean – meaning that the headers are on the first row and one row only.

Send the data into Power Query.

• In the Data tab, click on Get Data and choose Excel workbook.

• Remove the unwanted columns. Rows left behind should be accounting codes, accounting description, Teams (Optional), Amount and Accounting Period (i.e. accounting dates not month)

Create a first and second level accounting entries with the COA table

• Within Power Query, click on an empty space on the left panel.
• RIght click and select New Query -> Excel workbook.
• Select the excel file containing the COA table.
• Delete the unwanted columns in the COA table

Merge the 2 tables as one a new query.

• In the home tab (within Power Query), click on merge query as new
• Select the common column COA 3 for merging
• Delete the COA in the main data
• RE-arrange the COA 1, 2, and 3

Group and calculate the total by COA 1 and COA 2

• Select the columns except Amount
• Go to the Home tab.
• Select Group by
• change the calculation to SUM and the column to Amount

Pivot the columns using COA column

• Pivot COA1 name, use Amt column as values

• Replace all the null values as zero in Revenue, COGS and Opex columns
• Remove the original columns with null
• Create new Net Profit and Gross Profit columns
• Go to the New Column tab
• Select Custom Column icon
• Name the new column Gross Profit. Add the revenue to COGS. It is an addition because they have different signs
• Name another new column Net Profit. Add the Gross Profit to Opex.
• Unpivot the columns Revenue, COGS, Opex, Gross Profit and Net Profit
• Rename the Values column as “Amount”
• Filter the numbers that are non-zero
• Rename the header to Match COA 1 name

Unpivot the columns

Remove the details for GP and NP (Add conditional columns to remove details for NP and GP)