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.
Here I am listing the tutorials in the order that should be read and summarizing what is achieved in them:
- Normalize data either using power query, pivot table or formulas
- Combine scattered data in one table using append tool in power query
The tutorial file that I am providing with this tutorial is actually what we have after we had it normalized and appended.
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.
I have read your article with great interest.
There are 2 issues that I would like more understanding
In the P&L Account I would like to see firstly more than 1 trading activity – say Commercial Sales less COGS and Domestic Sales less COGS with Gross Profits and secondly the expense items and a total of the expense items
This is not a reply. This is a question and it is this:
Are you able to prepare a complete P&L Statement from an entire 12-month bank statement using pivot tables? The 12-month bank statement consists of 1000 lines of entries (debits and credits).
Will appreciate your answer with either Yes or No.
This really depends on the way data is arranged. For pivot tables to work the way we want data has to be arranged in particular form. Although I have discussed the way data can be adjusted so to great extent I can say that yes at least P&L can be done using pivot tables with a little effort.
I am a Bookkeeper and have a clarification can you tell me how do you merge diffrententites of P&L into one the information is not the same. the enties are all in diffrent location. Thanks sandy
your tutorial are very helpful following.
Thanks so much Fazal for your tutorials. They have been very helpful to me. Hasaan, do you have the videos of your tutorials please? I am Dominic from Ghana
Welcome to PakAccountants and thank you for visiting and commenting.
No I haven’t made any video-tutorials specific to Excel yet but its definitely something in the pipeline. Will keep you posted.
Why you use row()-3 in the index function as follow: index($A$1:$A$500,$E$1+row()-3)
Ammm! where I used this formula in above tutorial? If you can point me there I might help you out. Thanks for visiting