Budget vs. Actual Profit Loss Report using Pivot Tables

Posted on April 21st, 2010 in Learn Excel , Pivot Tables & Charts - 14 comments

This is last part of Profit & Loss Reporting using Excel series, written by Yogesh
Budget vs. Actual Profit Loss Report using Pivot Tables

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

Add Calculated Item to PivotTable Report

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 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.

Making Budget vs. Actual Profit Loss Report using Excel Pivot Tables

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:

Budget vs. Actual Profit Loss Report

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 Gupta - CA, Excel BloggerYogesh 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/
Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

14 Responses to “Budget vs. Actual Profit Loss Report using Pivot Tables”

  1. Dau says:

    Good Work, Yogesh & Chandoo! Thanks.

  2. Abdul Kader says:

    Hi everybody,
    first sorry I am late to say something about this topic;actually I was waiting last part
    second I am not accountant I am an Engineer
    third “”"”Very Important”"” the idea is not about Loss but I am sure it is profit
    Based on third it shows:
    1- How to use EXCEL
    2- How to use pivot TABLES
    3- How to collect and arrange DATA
    4- How to make reports

    Many Thanks

  3. UB says:

    Hi Yogesh and Chandoo,

    Thank you for sharing your knowledge!
    You guys are great!

  4. Alejandro says:

    thanks chandoo and yogesh, thanks for you lessons, are great!….i have a idea for a budget. I try to do it….. thanks for all

  5. SAUL ESPINOZA says:

    Thanks a lot for sharing the most powerful tool worldwide “knowledge”
    Warm greetings from Peru

  6. juanito says:

    Hi -
    This is a really great article because it’s a simple and common thing you’d want to do with a pivot table but not at all obvious how to do it! So – muchas gracias to Chandoo and Yogesh!
    One thing – I couldn’t get past the group error in the sample file. I would click on ungroup but it didn’t seem to have any effect. I’d appreciate it if anybody has any pointers here.

    -Juanito

  7. Adam says:

    Hi Chandoo

    I am also having the group error. Can’t seem to ungroup? Appreciate if you explain further on the steps required in order to get to calculated items.

    Many thanks and keep up the great work.

    Cheers
    Adam

  8. Catherine says:

    Hi Chandoo,

    I’m struggling resolving the problem depicted below:
    I have a set of data, with (among others) a “Region” field (can be APJ, EMEA, or AMS), and a “Country” field.
    Unfortunately, I need to group data by the following 4 Regions: APeJ, Japan, EMEA and AMS.

    I first tried to make a pivot with Region and Country in the rows (or columns), and then group Country data as per the above.
    Alas, as soon as I have a new Country that appear in my data set, my groupings are broken, and I have to redo the job of ungrouping, grouping etc.

    I thought I could try to use calculated item, by adding first a new column to my dataset concatenating Region_Country, and create an “APeJ” calculated item that would sum all the “APJ_*” and substract the “APJ_Japan”, but again, no clue, as I can’t find a way to use any wild card in those formulas.

    Given that I already found extremely helpful tips and tricks in your site that helped me manage that bunch of data, I’m pretty sure you’ll have a bright idea on how I can solve that one!

    Thanks in advance for your lights!

    • Chandoo says:

      Hi Catherine…

      In such cases, I advice using an additional column in the data itself. You can set-up a grouping table else where with country in first column, region in second column. And then in the data, you can add an extra column and use VLOOKUP to fetch the region based on the country.

      Then feed this entire data (with extra column) to pivot table and use the extra column to group the data.

      • Catherine says:

        Hi Chandoo,

        Thank you for your prompt answer.
        I finally came to the same conclusion – after a rest :-) . I was probably too tired Friday evening (it was rather late), having spent hours in manipulating all my surveys data so as to pull rolling averages, make nice graphs and so on, and was trying to find a complex solution when there was a simple one.

        Thanks again,
        Catherine

  9. Tzu says:

    Hey,

    Great post!

    I for example have different database structure with the following fields :

    Date, Expense, Income, Sum (Income – Expense), Category (Sales, Cost of Goods and etc).

    Creating a P&L report for the whole year works great. Including gross margin % and etc.

    Though, creating P&L report by QTR/Month is becoming impossible since i get the following error : “This PivotTable report field is grouped. You cannot add calculated item to grouped filed.”

    Is there a solution for this kind of problem?
     

  10. klumsyboy says:

    Like Adam and Juanito, I also cannot ungroup.

    Would appreciate it if you can add a few more lines and a screenshot or two on where to put the mouse cursor to ungroup. 

  11. klumsyboy says:

    Hi,  I have figured out the ungrouping problem. One of the earlier steps was to group by month, if you pull the month back down to the column then right click and then select ungroup, then pull the month back up so you end up with just data source and budget/actual as the headings, then you can continue on.

  12. Kent Lau says:

    To solve the ungroup problem, my method is:
    Copy the “data” sheet to a whole new Excel workbook
    and directly work on Part 6.

    And since it is a fresh copy, Excel don’t show me the “can’t ungroup” problem. Hope this help.

    Thank you Yogesh for this wonderful tutorial.

    Kent, Malaysia

  13. felipe says:

    Just when i thought pivots were awesome i learn about inserting the calculated fields and that makes them more awesome. chandoo where have you been all my life.

Leave a Reply