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:

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.