This is last part of Profit & Loss Reporting using Excel series, written by Yogesh
Data sheet structure for Preparing P&L using Pivot Tables
Preparing Pivot Table P&L using Data sheet
Adding Calculated Fields to Pivot Table P&L
Exploring Pivot Table P&L Reports
Quarterly and Half yearly Profit Loss Reports in Excel
Budget V/s Actual Profit Loss Report using Pivot Tables
This is continuation of our earlier post Preparing Quarterly and Half yearly P&L using grouping option. You can also do budget v/s actual comparison using Pivot Tables.
For this we have to add one more column to our data. I have added column Data Source to the end of data table. Existing data is marked as Actual and I have added more data rows which are marked as Budget. You can download new file with updated data and basic Pivot P&L
File : Updated Data with Budget.xls [mirror]
We will convert this basic P&L report into Budget V/s Actual Comparison with following steps
Step 1 – Change data source / increase data range
Click PivotTable Tools > Change Data Source
Update Table range as Data!$A$1:$O$481.
Now your P&L report will show single figure which included budget and actual both. We will separate them in our next steps.
Step 2 – Separate Budget and Actual by adding Data Source filed to the column area
You can drag and show Budget column before the Actual . Hide the GrandTotal column by right click on GrandTotal > Click Remove GrandTotal
Step 3 – Add calculated item
Select Data Source filed then Click PivotTable Tools > Formulas > Calculated Item
In case you forget to select Data Source filed on PivotTable , calculated item will remain disabled. So make sure that you have selected Data Source filed before getting calculated item option
You may get error message like above saying “This PivotTable report field is grouped. You cannot add calculated item to grouped filed.”
This is due to grouping we have done in our PivotTable, you will need to ungroup all those fields before adding calculated item.
Once you have ungrouped all the grouped fields you will be able to add calculated item.
- Name : Variance
- Formula : Actual – Budget
- Click Add > OK
Now your budget vs actual PivotTable P&L Report is ready.
Do not forget to hide GrandTotal Column otherwise Pivot Table will add values of calculated item ( Variance) also to it. PivotTable Report treats calculated item as another row. So you need to be careful while using them, avoid using total values. These can mislead you.
The final Budget vs. Actual Profit Loss Report:
The final version of the report should look like this:
Download Final Budget vs. Actual Pivot Report
Click here to download the pivot report example file and play with it. [Mirror]
What Next?
This is final post on this series.
Meanwhile, make sure you have read the first 5 parts of this series – Data sheet structure, Preparing P&L Pivot Table, Adding Calculated Fields, Exploring Profit Loss Report Pivot and Quarterly and Half yearly Profit Loss Reports in Excel.
Also check out the Excel Pivot Tables – Tutorial, Pivot Table Tricks, Grouping Dates in Pivot Reports and Budget vs. Actual Charts articles to get more ideas
Thank you Yogesh:
- Many Thanks to Yogesh for these excellent posts on Pivot Reports. He used his industry knowledge, expertise to teach us several valuable lessons. Thank you Yogesh.
- Please share your feedback and ideas for this series using comments. Yogesh and I will reply to your questions.
Yogesh is an accountant with 13 years of experience in India and abroad. His specialties are budgeting and costing, supplier accounting, negotiation of contracts, cost benefit analysis, MIS reporting, employees accounting. He writes about excel at http://www.yogeshguptaonline.com/
