Personal Budget Planner with Savings Goal Tracker

A personal budget planner helps you plan monthly income and spending, track savings, and see variance. You build it in Excel and use Copilot and ChatGPT for the budget formulas and saving tips.

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 plans income, fixed and variable expenses, a savings goal, and shows actual vs planned.
• Where it is used: Personal money management for individuals and families.
• What you learn: SUM, savings math, variance, charts, and AI prompt writing.

Features

• Income and expense planning
• Fixed vs variable split
• Savings goal and actual saved
• Variance vs plan
• AI saving tips

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• A rough idea of your monthly income

Folder Structure

personal-budget-planner.xlsx
│
├── Sheet: Plan    (Category, Planned, Actual, Variance)
├── Sheet: Summary (income, saved, goal)
└── Module: PerMacros (optional VBA)

• Plan: the budget lines.
• Summary: the headline numbers.
• PerMacros: optional helpers.

Step-by-Step Development

Step 1 – Income and expense plan

What are we building?

A table with planned and actual per category, split into income and expense.

Why do we need it?

You compare the plan to reality per line.

How do we build it?

=SUMIFS(Plan[Actual], Plan[Type], "Expense")

• What it does: sums actual amounts where type is Expense.
• Why needed: total spent this month.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write SUMIFS that sums Actual where Type is Expense in a budget table."

Step 2 – Savings and variance

What are we building?

A variance column and a saved amount.

Why do we need it?

Savings is what is left after expenses.

How do we build it?

=[@Planned]-[@Actual]
=IncomeTotal - ExpenseTotal

• First formula: planned minus actual per line.
• Second formula: income minus total expenses equals saved.
• Why needed: shows both line gaps and money left.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Write variance as planned minus actual, and saved as income minus total expenses."

Step 3 – Savings goal check

What are we building?

A check against a savings goal.

Why do we need it?

Tells you if you hit the target.

How do we build it?

=IF(Saved >= Goal, "Goal met", "Short by " & TEXT(Goal-Saved, "0.00"))

• What it does: compares saved to goal and reports the gap.
• Important line: TEXT(Goal-Saved, "0.00") formats the shortfall.
• Why needed: a clear goal status.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write an Excel IF that says Goal met if saved >= goal, else Short by the difference formatted to 2 decimals."

Step 4 – Chart and AI tips

What are we building?

A plan vs actual chart and AI saving advice.

Why do we need it?

Visuals and tips improve habits.

How we build it?

Chart Planned vs Actual, then paste the plan into ChatGPT.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"From this monthly budget, give three practical tips to save more."

Complete Source Code

Plan formulas

=SUMIFS(Plan[Actual], Plan[Type], "Expense")
=[@Planned]-[@Actual]
=IncomeTotal - ExpenseTotal
=IF(Saved >= Goal, "Goal met", "Short by " & TEXT(Goal-Saved, "0.00"))

AI prompts

ChatGPT: "SUMIFS Actual where Type Expense."
Copilot: "Variance planned minus actual; saved income minus expenses."
ChatGPT: "IF goal met else short by difference."
ChatGPT: "Three tips to save more from this budget."

Running the Project

1. Open personal-budget-planner.xlsx.
2. Enter planned and actual amounts.
3. Summary shows saved and goal status; chart updates.
4. Expected output: a clear personal budget view.

Testing

Test Case 1

Input: Income 2000, Expense 1500. Expected Output: Saved 500. Explanation: 2000 − 1500.

Test Case 2

Input: Goal 600, Saved 500. Expected Output: "Short by 100.00". Explanation: Goal not met; gap shown.

Test Case 3

Input: Goal 400, Saved 500. Expected Output: "Goal met". Explanation: Saved exceeds goal.

Common Errors

Saved negative

Problem: Shows a loss. Reason: Expenses exceed income. Solution: Lower planned expenses or raise income.

Text not formatted

Problem: Shortfall shows as a number without label. Reason: Missing TEXT wrap. Solution: Use TEXT(...,"0.00") inside the string.

Chart off

Problem: Bars wrong. Reason: Selected one column. Solution: Select both Planned and Actual.

Improvements

• Add weekly breakdown
• Use Power Automate to log bank imports
• Ask ChatGPT for a debt payoff plan
• Add an emergency fund tracker
• Export to PDF

Challenge Exercises

1. Add a percent-saved formula (saved / income).
2. Write a formula for the category with the largest variance.
3. Use ChatGPT to write VBA that resets Actual each month.
4. Build a 6-month savings trend chart.
5. Let ChatGPT propose a new plan to hit your goal.