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).
Table of Contents
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 methodto 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.
Emphasis of fact
For the purpose of this tutorial I am using the same data set that I have used in otherconnectedtutorials as I believe it will make much more sense.
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.
Download Excel Tutorial Workbook
To better understand the steps discussed in this tutorial download this Excel workbook that provides you the sample data necessary to run through the process of preparing profit or loss statements using pivot tables
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:
- Sales
- CoGS
- Admin Exp
- Selling Exp
- Fin Cost
- Tax
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 forgross profit or loss, operating profit or loss, profit before tax and profit after tax.To get this done we will take help ofcalculated fieldsinside 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 toadd 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.
Check out following tutorials to be a better accountant at work with Excel:
- Top 10 Excel keyboard shortcuts for Accountants
- Creating Timelines in Excel [How To]
- Variance Analysis in Excel – Making better Budget Vs Actual charts
- Dealing with Telephone numbers in Excel – Dialing in the correct format
