Being an accountant myself trust me I hate numbers and all that journals and “T” accounts. But its necessary to get to much meaningful financial statements like Statement of Financial Position (Balance sheet) and Profit and loss Statement (Income statement).
Excel’s ability to store data in rows and columns is exceptionally flexible and Pivot tables capitalize on it beautifully. Today we will take a sample data and learn how to make a profit and loss statement in minutes (if not seconds).
Potential data problems
IF we intend to make profit and loss statement using pivot table then data has to be structured in a certain way i.e. “columnar”. In pivot tables, each column and its header will become a criteria that can be used to produce a report. If your data is not arranged in meaningful columns then even pivot table will be of less help.
Two main problems we often encounter are:
Data is not columnar. To manage this situation we can either use formula method, power query method, or even use pivot tables method to structure data. Check the links to learn more about these methods.
Data is scattered in different worksheets, workbooks or files of different programs. To solve this problem we can use simple copy/paste, LOOKUP functions or again employ power query to combine data using different tools available. One of such tool append data using power query.
Once these issues are resolved we can continue with pivot tables to generate profit and loss reports easily.
Watch the walkthrough video or continue reading!
Making your first P&L Statement in Pivot tables
Step 1: Download and open the tutorial workbook. Go to Actual only worksheet. Select the range and go to Insert tab > click pivot tables and click OK button. A new worksheet will be inserted with report generation capability.
Step 2: Move the fields in the following order to values quadrant one after the other:
- Admin Exp
- Selling Exp
- Fin Cost
Step 3: You will see that value field is inserted in the columns quadrant automatically. Drag it to rows column.
Step 4: Now that we have the figures in place we need subtotals for gross profit or loss, operating profit or loss, profit before tax and profit after tax. To get this done we will take help of calculated fields inside pivot tables.
Having an active cell inside pivot table, click analyze tab > calculations group > click Fields, Items, Sets > click Calculated field. In the name field type “Gross profit/(loss)”. Have the cursor in formula bar after “=” sign and double-click “sales” hit minus “-” key on the keyboard and then double click “CoGS” field. Click OK button. Move the newly inserted field under CoGS field.
Step 5: Add another calculated field for operating profit/loss calculation and move it to appropriate place. Following animation will help
Step 6: Add another calculated field for Profit/(loss) before tax. And after that final field will be regarding Profit/(loss) after tax.
Once these calculated fields are added, we are now ready to format the profit and loss statement.
Step 7: See the “Sum of” with all the fields in the start. That’s ugly! Select the first column and hit Ctrl+H. In “Find what” field type Sum of and leave “Replace with” field empty. Click OK and boom!
Step 8: Select the column containing values and hit Ctrl+1 to open Number format options window. On the left hand side click “number”. Check mark “1000 separator” option. Also select the appropriate negative number format. Click OK to update the formats
Step 9: Now to make it more understandable we need to add few lines to emphasize subtotals like gross profit, operating profit, profit before tax and profit after tax. I used borders to add the visual ease.
So here you have a very basic profit or loss statements all ready in few minutes. Pivot tables has really saved us from many different LOOKUP functions and conditional SUM formulas.