profit and loss statement in pivot table

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)