Budget Vs Actual – Analyzing Profit and Loss Statements in Excel using Pivot tables

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.

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

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:

profit and loss variance 1

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.

profit and loss variance 1

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

profit and loss variance 2

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.

profit and loss variance 3

Step 6: If you can’t see the percentages appearing correctly then you can change the number formatting to show the percentages correctly.

profit and loss variance 4

If you are an accountant and gear up your Excel skills then don’t miss following tutorials:

  1. How to make a basic Tax calculator in Excel
  2. Using SUMIF with Dates in Excel
  3. How to change currency format in Excel? How to have Pakistani Rupees (Rs) currency sign in excel?
  4. Excel Custom Shortcuts – Reaping real benefits of Quick Access Toolbar
  5. How to show 0 (digit) as text ZERO or Nil or anything in Excel

4 COMMENTS

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

  2. This is what i have been waiting for. thanks

  3. sir,

    awsome thanks a lot

Comments are closed.