Budget Planner with Variance & Overspend Alerts

A budget planner compares what you planned to spend against what you actually spent, and shows the difference. You build it in Excel and use Copilot and ChatGPT to write the variance and alert formulas.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It holds planned and actual amounts per category and calculates the variance (planned minus actual).
• Where it is used: Household budgeting, project budgets, and department planning.
• What you learn: subtraction across columns, conditional formatting, charts, and AI-assisted formula writing.

Features

• Planned vs actual per category
• Variance and percentage difference
• Red highlight when you go over budget
• A chart comparing planned and actual
• AI tips on balancing the budget

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• A simple list of budget categories
• Comfort with basic arithmetic in cells

Folder Structure

budget-planner.xlsx
│
├── Sheet: Budget   (Category, Planned, Actual, Variance, %)
├── Sheet: Chart    (planned vs actual chart)
└── Module: BudMacros (optional VBA)

• Budget: the main planner.
• Chart: the visual comparison.
• BudMacros: optional helper code.

Step-by-Step Development

Step 1 – Set up planned and actual columns

What are we building?

A table with Category, Planned, and Actual columns.

Why do we need it?

You cannot compare spend until both numbers exist side by side.

How do we build it?

Type the headers and enter your numbers. Turn the range into a table named Bud.

=[@Planned]-[@Actual]

• What it does: finds the difference for each row.
• Important line: [@Planned]-[@Actual] uses structured references for the same row.
• Why needed: a positive variance means you spent less than planned.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write an Excel formula for variance as planned minus actual using a table named Bud."

Step 2 – Calculate percentage difference

What are we building?

A % Diff column showing variance as a percentage of planned.

Why do we need it?

A raw number hides whether 20 over is small or large.

How do we build it?

=IFERROR(([@Planned]-[@Actual])/[@Planned], 0)

• What it does: divides the variance by planned spend.
• Important line: IFERROR returns 0 when planned is 0, avoiding a divide-by-zero crash.
• Why needed: percentages make categories comparable.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Add a percent difference column for planned minus actual divided by planned, with error handling."

Step 3 – Highlight overspend with conditional formatting

What are we building?

Cells that turn red when actual is greater than planned.

Why do we need it?

Red draws the eye to problem categories.

How do we build it?

1. Select the Variance column.
2. Home > Conditional Formatting > Highlight Cells Rules > Less Than and enter 0.

No formula; set through the ribbon rule "Variance < 0".

• What it does: any negative variance is shaded red.
• Why needed: quick visual warning without reading numbers.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"In Excel, how do I highlight a cell red when its value is less than zero using conditional formatting?"

Step 4 – Chart and AI advice

What are we building?

A clustered column chart of planned vs actual and an AI note.

Why do we need it?

Bars show the gap at a glance.

How we build it?

Select Planned and Actual columns, then Insert > Clustered Column.

Use AI (Copilot & ChatGPT)

Paste the Budget table into ChatGPT:

"Which categories are most over budget and how should I rebalance? Two sentences."

Complete Source Code

Budget table formulas

=[@Planned]-[@Actual]
=IFERROR(([@Planned]-[@Actual])/[@Planned], 0)

Conditional formatting rule

Apply to Variance column: format cells Less Than 0 with red fill.

AI prompts

ChatGPT: "Variance formula planned minus actual for table Bud."
Copilot: "Percent difference with error handling."
ChatGPT: "Conditional formatting red when less than zero."
ChatGPT: "Which categories are most over budget?"

Running the Project

1. Open budget-planner.xlsx.
2. Fill Planned and Actual for each category.
3. Variance and % Diff calculate; overspent rows turn red.
4. Expected output: a clean comparison table and a column chart.

Testing

Test Case 1

Input: Planned 100, Actual 80. Expected Output: Variance 20, % Diff 20%. Explanation: Spent less than planned, so variance is positive.

Test Case 2

Input: Planned 100, Actual 120. Expected Output: Variance -20 (red), % Diff -20%. Explanation: Overspend shows negative and triggers the red rule.

Test Case 3

Input: Planned 0, Actual 50. Expected Output: Variance -50, % Diff 0 (not an error). Explanation: IFERROR catches the divide-by-zero.

Common Errors

#DIV/0! in percent column

Problem: Error when planned is zero. Reason: Dividing by zero. Solution: Wrap in IFERROR as shown in Step 2.

Nothing turns red

Problem: Overspend not highlighted. Reason: The rule compared the wrong column. Solution: Re-apply the rule to the Variance column with "Less Than 0".

Chart shows one bar

Problem: Planned and actual not side by side. Reason: Only one column was selected. Solution: Select both Planned and Actual before inserting the chart.

Improvements

• Add a total row for overall planned vs actual
• Set monthly vs yearly views with a slicer
• Use Power Automate to remind you when a category is over
• Ask ChatGPT to propose a corrected budget
• Export to PDF for a meeting

Challenge Exercises

1. Add a "Saved" total that sums all positive variances.
2. Build a formula that shows the single worst category using XLOOKUP.
3. Use ChatGPT to write a VBA button that resets Actual to zero.
4. Add a second table comparing this month's budget to last month's.
5. Let ChatGPT read your data and suggest a new planned amount per category.