lesson 3.7.6 project: personal budget model

Creating a Tool to Track Income and Expenses

Ever wondered where your money goes? 🤔 Today, you get to be your own Financial Analyst! We're going to use all the spreadsheet superpowers you've learned – formulas, functions, and cool formatting tricks – to build your very own personal budget model. This isn't just about numbers; it's about creating a tool to help you understand and manage your money, just like professionals do in many careers! 📊💰

Learning Outcomes

The Building Blocks (Factual Knowledge)

Recall the difference between relative and absolute cell references.

Describe the purpose of functions like

SUM

and

IF

Describe how conditional formatting highlights data based on rules.

The Connections and Theories (Conceptual Knowledge)

Analyse how a spreadsheet model represents a real-world financial situation (income vs. expenses).

Evaluate how changing values in the model (like income or spending) affects the overall budget outcome ("what-if" analysis).

Apply the concept of a model for prediction and planning.

The Skills and Methods (Procedural Outcomes)

Apply formulas and functions (

SUM

, basic arithmetic, potentially

IF

) to perform calculations in a budget.

Apply relative and absolute cell referencing appropriately.

Create a personal budget model using spreadsheet software, incorporating formatting and conditional formatting for clarity.

Digital Skill Focus: C.3.2.1 Spreadsheet Modelling and Analysis - Create a simple data model to answer a "what-if" question.

Okay, let's put everything we've learned about spreadsheets together to make something really useful: a personal budget model! 📝

A budget helps you understand how much money you have coming in (income), how much you're spending (expenses), and how much you have left over (savings or deficit). Building this in a spreadsheet means we can use formulasI have no idea what this means and functionsI have no idea what this means to do the calculations automatically and even ask "what-if" questions.

We'll build a simple model that tracks monthly income and expenses. Remember those key skills we've practised?

✅ Formulas: For basic calculations.
✅ Functions: Especially

SUM

to easily total columns of numbers.
✅ Formatting: Making it look clear and easy to read.
✅ Cell Referencing: Using relative and absolute references if needed.
✅ Conditional Formatting: To automatically highlight cell contents.

Your final budget spreadsheet will (hopefully) look like this...

What a lovely spreadsheet!

...but you are going to have to work hard to get there!!

Task My Awesome Personal Budget Planner

Are you ready for a challenge?

Get organised

Download the Personal Budget Model Template which contains the structure but not the formulae, functions, data or formatting.

Remember to click the Enable Editing button on the yellow toolbar if it appears.

Organise your workspace!

Add a hint of automation - formulas and functions

The formulas and functions are what makes the spreadsheet work. You can copy and paste them from here if it's easier but typing is always better...

In cell B3 add the function

=SUM(B5:B20)

In cell C3 add the function

=SUM(C5:C20)

These two formulae take care of working out the totals of your income (Column B) and your expenses (Column C). They only add up to row 20 though...

In cell D3 add the formula

=A3+B3-C3

This calculates the Carried Forward (C/F) amount, i.e. what you had at the beginning of the month add your income, take away your expenses.

In cell D5 add the formula

=$A$3+SUM($B$5:B5)-SUM($C$5:C5)

Copy this formula down to cell D20 using the little square copy handle.

Now, this is the tricky one. It works out the running balance by taking the 'brought forward' balance (cell A3), adding the

SUM

of the income in column B and taking away the sum of the income in column C but, only up to the row that the formula is in. Look carefully at the absolute cell references (with the

) and see if you can work out what's happening.

Oh, I see!!

Add some fixed formatting.

Shade cell B3 in light green. This is conventional for positive values.

Shade cell C3 in light red. This is conventional for negative values.

Add a sprinkle of magic - conditional formatting

Format cell A3

Click on cell A3.

Choose Conditional Formatting > Highlight Cell Rules > Greater Than...

Enter

and choose Green fill with light green text.

Click OK.

Choose Conditional Formatting > Highlight Cell Rules > Equal To...

Enter

and choose Yellow fill with dark yellow text.

Click OK.

Choose Conditional Formatting > Highlight Cell Rules > Less Than...

Enter

and choose Red fill with light red text.

Click OK.

Now that we have applied these rules to cell A3, we can actually copy and paste just the formatting to the other cells that need it!

Format the other currency cells

Make sure you are still in cell A3.

DOUBLE CLICK on the Format Painter tool.

Click on cell D3. This will apply the same formatting to the cell.

Click on cell D5, hold your mouse button down and drag your mouse down to cell D20.

IMPORTANT: Press the ESC key to deactivate the Format Painter.

True, you could have copied and pasted the formats using CTRL + C and then Right Click > Paste Special > Formats only but this is just as easy.

Let's get busy with some data

Now it's time to experiment with your model!

Add a Brought Forward (B/F) balance to cell A3. This is the amount of money you started with.

Add some income and expenditure to the spreadsheet - use this completed version to help you if you are struggling...

What a lovely spreadsheet!

Hungry for more?

The only issue with your awesome budget planner is that the cells in column D still show the running balance even if there is no income or expenditure in the row. This looks messy, doesn't it?

We can use another really useful function to prevent this happening. Change the formulae in cell D5 to this...

=IF(A5="","-",$A$3+SUM($B$5:B5)-SUM($C$5:C5))

...and drag the formula down to cell D20. You should now see that the cells in column D will only show the running balance if there is data for income or expenditure in the row. Much neater!

Outcome: I can create a personal budget spreadsheet that correctly calculates my total income, total expenses, and the difference (savings or deficit), using formulas, functions, and clear formatting, including conditional formatting to highlight the final result.