Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.4 Finance and Accounting: Budget vs Actual Variance

By Last updated:

Example first. Shahrukh prepares the monthly expense review for a fictional chain of dark stores and needs to show where spending went over budget.

Finance and Accounting project — in 30 seconds

What: A Finance and Accounting tracker: one Table tblBudget, the calculated column Variance, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Finance and Accounting report: Total Budget, Total Actual, Variance %, Heads over Budget are the numbers a manager reads first.

Tiny example: From 6 rows: Total Budget = ₹8,15,000, Total Actual = ₹8,46,400, Variance % = 3.9%, Heads over Budget = 3.

First look at the data (before → after):

Finance and Accounting project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

Head Variance
Rent ₹0
Salaries ₹18,000
Marketing ₹15,500
Utilities -₹2,600
Packaging ₹6,200
Delivery -₹5,700

Expected result — KPI cells (expected results)

KPI Formula Result
Total Budget =SUM(tblBudget[Budget]) ₹8,15,000
Total Actual =SUM(tblBudget[Actual]) ₹8,46,400
Variance % =(SUM(tblBudget[Actual])-SUM(tblBudget[Budget]))/SUM(tblBudget[Budget]) 3.9%
Heads over Budget =COUNTIF(tblBudget[Variance],">0") 3
Before table and formula as text

Before — Table tblBudget (fictional sample rows)

Head City Budget Actual
Rent Pune 1,20,000 1,20,000
Salaries Pune 4,50,000 4,68,000
Marketing Nashik 80,000 95,500
Utilities Nashik 30,000 27,400
Packaging Nagpur 45,000 51,200
Delivery Solapur 90,000 84,300

Formula / click path

Variance: =[@Actual]-[@Budget]
Total Budget: =SUM(tblBudget[Budget])
Total Actual: =SUM(tblBudget[Actual])
Variance %: =(SUM(tblBudget[Actual])-SUM(tblBudget[Budget]))/SUM(tblBudget[Budget])
Heads over Budget: =COUNTIF(tblBudget[Variance],">0")

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblBudget in Table Design › Properties › Table Name.
  2. Add the column Variance with =[@Actual]-[@Budget]; the Table fills it down.
  3. On a KPI sheet type =SUM(tblBudget[Budget]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and Actual in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Head.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a budget-versus-actual report: variance per expense head, total budget and actual, variance % for the whole month, the number of heads over budget, actual spend by city, a chart and a Head slicer.

Calculated column formulas:

Column Formula
Variance =[@Actual]-[@Budget]

KPI cells (check your numbers):

KPI Formula Result
Total Budget =SUM(tblBudget[Budget]) ₹8,15,000
Total Actual =SUM(tblBudget[Actual]) ₹8,46,400
Variance % =(SUM(tblBudget[Actual])-SUM(tblBudget[Budget]))/SUM(tblBudget[Budget]) 3.9%
Heads over Budget =COUNTIF(tblBudget[Variance],">0") 3

PivotTable (expected):

City Sum of Actual
Nagpur ₹51,200
Nashik ₹1,22,900
Pune ₹5,88,000
Solapur ₹84,300
Grand Total ₹8,46,400

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Head and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Finance_Accounting_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of Actual?
  2. Filter the Table on City = Pune (Data › Filter): what is Total Budget for Pune alone?
  3. Add the Clustered Column PivotChart and the Head slicer to your Dashboard sheet.
Answer

Pune (₹5,88,000). For Pune only (2 rows): Total Budget = ₹5,70,000. The chart shows 4 bars, one per city.

Ravindra Bagale's Tip

Write the definition of Total Budget under its KPI card in small text. Two people reading the same dashboard should agree on what the number means.

Loading practice tasks...

  1. Median (Easy): Median of D2:D11.
  2. Range (Easy): Max of E2:E11 minus min.
  3. Count (Easy): Count of numbers in D2:D11.
  4. Spread (Medium): STDEV.S of D2:D11, rounded to 2 decimals.
  5. 90th percentile (Medium): 90th percentile of E2:E11, rounded to 1 decimal.
  6. Upper quartile (Medium): Third quartile of D2:D11, rounded to 2 decimals.
  7. Third largest (Medium): Third largest in E2:E11.
  8. Correlation (Hard): Correlation of D2:D11 and E2:E11, rounded to 3 decimals.
  9. Z-score (Hard): Z-score of D2 vs D2:D11, rounded to 2 decimals.
  10. Coefficient of variation (Hard): STDEV.S ÷ average of E2:E11, rounded to 3 decimals.
  11. Outliers above (Hard): How many in D2:D11 exceed average + 1 standard deviation?

See all practice tasks in the Task Hub →