This tutorial is effectively a continuation of last tutorial on profit and loss statements using pivot tables in which we learnt how to make a report in Excel using pivot tables feature to make income statement in few steps quickly.
Today we will learn how to do budget vs actual variance analysis of profit and loss statement (Income Statement) using pivot tables. And it is super easy and super fast to do.
Variance analysis of Profit and Loss Statement
Step 1: Open the file and go to “Actual and buget” tab. Make the profit and loss statement following the steps mentioned in the tutorial: Making Profit and Loss Statements in Excel using Pivot tables. You should have pivot report as follows:
Step 2: Once the profit and loss statement is in place, drag the “data” field to the columns quadrant and pivot report will instantly update in actual and budget column.
Step 3: Click on any of the heading (Actual or Budget) and go to Analyze tab > calculations group > click Fields, Items & Sets drop down button > Calculated item. A dialogue box will appear.
Step 4: Now we need to insert variance column. In the name field write “Var. Amt.”. Move cursor to formula field after equal sign. Double click on Actual field punch “-” key on the keyboard and double click budget field and click OK. This will insert a new column with variance calculation
Step 5: To make data even more meaningful, we can insert Variance percentage column as well. To do this, we need to insert another calculated item. So click on any heading (its important in order to add calculated item otherwise option will stay disabled) and go to Analyze tab > calculations group > click Fields, Items & Sets drop down button > Calculated item. In the name field put “Var. %” and in the formula insert Var. Amt field put a “/” sign and then double click budget field to complete the formula. Click OK and now % column will also be inserted.
Step 6: If you can’t see the percentages appearing correctly then you can change the number formatting to show the percentages correctly.
If you are an accountant and gear up your Excel skills then don’t miss following tutorials:
- How to make a basic Tax calculator in Excel
- Using SUMIF with Dates in Excel
- How to change currency format in Excel? How to have Pakistani Rupees (Rs) currency sign in excel?
- Excel Custom Shortcuts – Reaping real benefits of Quick Access Toolbar
- How to show 0 (digit) as text ZERO or Nil or anything in Excel
Hello Hassan,
Thanks for the good lesson but on Var. % for values under calculated fields the percentage do not obtained as per formula on the calculated Items e.g. Gross Profit/ (Loss) the value is 5.45% instead of 94.25%. How do you solve this?
good
This is what i have been waiting for. thanks
sir,
awsome thanks a lot