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)