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/
8 Responses to “Pivot Tables from large data-sets – 5 examples”
Do you have links to any sites that can provide free, large, test data sets. Both large in diversity and large in total number of rows.
Good question Ron. I suggest checking out kaggle.com, data.world or create your own with randbetween(). You can also get a complex business data-set from Microsoft Power BI website. It is contoso retail data.
Hi Chandoo,
I work with large data sets all the time (80-200MB files with 100Ks of rows and 20-40 columns) and I've taken a few steps to reduce the size (20-60MB) so they can better shared and work more quickly. These steps include: creating custom calculations in the pivot instead of having additional data columns, deleting the data tab and saving as an xlsb. I've even tried indexmatch instead of vlookup--although I'm not sure that saved much. Are there any other tricks to further reduce the file size? thanks, Steve
Hi Steve,
Good tips on how to reduce the file size and / or process time. Another thing I would definitely try is to use Data Model to load the data rather than keep it in the file. You would be,
1. connect to source data file thru Power Query
2. filter away any columns / rows that are not needed
3. load the data to model
4. make pivots from it
This would reduce the file size while providing all the answers you need.
Give it a try. See this video for some help - https://www.youtube.com/watch?v=5u7bpysO3FQ
Normally when Excel processes data it utilizes all four cores on a processor. Is it true that Excel reduces to only using two cores When calculating tables? Same issue if there were two cores present, it would reduce to one in a table?
I ask because, I have personally noticed when i use tables the data is much slower than if I would have filtered it. I like tables for obvious reasons when working with datasets. Is this true.
John:
I don't know if it is true that Excel Table processing only uses 2 threads/cores, but it is entirely possible. The program has to be enabled to handle multiple parallel threads. Excel Lists/Tables were added long ago, at a time when 2 processes was a reasonable upper limit. And, it could be that there simply is no way to program table processing to use more than 2 threads at a time...
When I've got a large data set, I will set my Excel priority to High thru Task Manager to allow it to use more available processing. Never use RealTime priority or you're completely locked up until Excel finishes.
That is a good tip Jen...