lesson 3-7-2 the school tuck shop model

Building a Simple Financial Model

Have you ever wondered how a shop knows if it's making money? Or how your favourite YouTuber figures out how much they've earned? They don't just guess! They use tools to build a model of their business. Today, you're going to become a business analyst and build your very own financial model for a school tuck shop. You'll learn how to use spreadsheet formulas to track costs, revenue, and—most importantly—profit! This is a skill that Financial Analysts and Data Scientists use every day to help businesses make smart, data-driven decisions.

Learning Outcomes

The Building Blocks (Factual Knowledge)

I can recall what a spreadsheet is and what its basic components are (cell, row, column).

I can describe the difference between a formula (a calculation I write myself) and a function (a built-in calculation like SUM)

The Connections and Theories (Conceptual Knowledge)

I can describe what a computational model is and why it is a simplified representation of reality.

I can explain how a spreadsheet can be used to model a real-world system like a shop.

Digital Skill Focus: Spreadsheet Formulae and Modelling.

The Skills and Methods (Procedural Outcomes)

I can apply simple arithmetic operators (+, -, /, *) to create formulas.

I can apply the built-in SUM function to calculate a total.

I can create a simple financial model to answer "what-if" questions.

What is a Financial Model?

Welcome, future business tycoons! Today, we're exploring computational models – simplified versions of real-world situations that aid understanding and prediction. Imagine a London Underground map: it's an abstraction, showing only essential information. Spreadsheet models work similarly. We'll build a simple financial model using spreadsheets to track revenue and costs, determining profit. Professionals use these models to answer crucial 'what-if' questions, like "What if we sell more?" or "How do cost increases affect profit?

Formulas vs Functions

In a spreadsheet, we use two main tools to do calculations:

A Formula is a calculation you write yourself. It always starts with an equals sign (=). For example,

=B2*C2

is a formula that multiplies the value in cell B2 by the value in cell C2.

A Function is a pre-built calculation that the spreadsheet software already knows. For example,

=SUM(D2:D5)

is a function that adds up all the numbers in the cells from D2 to D5.

We'll be using both of these today to build our tuck shop model.

Your teacher will now demonstrate how to set up the basic structure of the tuck shop model. They will show you how to:

Create the column headings.

Freeze rows.

Format cells.

Enter example data.

Write the formulas to calculate Total Revenue, Total Cost, and Profit for one item.

Use the fill handle (the little square at the corner of the cell) to copy the formulas down for all the other items.

Toggle 'display formulae' by pressing CTRL + `

Pay close attention, as you'll be building your own very soon!

Task 1 Tuck Shop Tycoon

It's time to build your own financial model and see if you can make your tuck shop profitable! I hope you were paying attention to your teacher when they demonstrated how to do this!

Prepare your tools

Open your spreadsheet software. Make sure you are familiar with the following:

Sheet tabs;

Column headings;

Row headings;

How to start a formula in a cell.

Set up your sheet

Create the following column headings in the first row (Row 1), starting in cell A1:

Item

Cost Price

Selling Price

Quantity Sold

Total Cost

Total Revenue

Profit

Enter the data and format the sheet

Enter this data carefully into your spreadsheet...

Now format the sheet to make it look more professional.

Freeze the top row so that, when your sheet gets longer, you don't lose the headings.

Embolden the text in Row 1.

Wrap the text in Row 1 and align it to the top of the cell.

Change the column widths to make the text in the headers fit more neatly and not to waste so much space.

Centre the text in Columns B through G.

Format the cells B2:C4 as 'currency'.

Format the cells E2:G4 as 'currency'.

Your sheet should look like this...

Automate!!

Now, let's bring the model to life with formulas! Work through these instructions carefully.

Click in cell

E2

To calculate the total cost for crisps sold, type the formula

=B2*D2

and press Enter. This multiplies the Cost Price by the Quantity Sold.

Click back on cell

E2

. See the small square in the bottom-right corner? This is the fill handle. Click and drag it all the way down to E4 to copy the formula for the other items. If you look carefully in the cells, you should see that the reference to the cells the formula works on has changed as well.

In cell

F2

, calculate the total revenue for crisps sold by typing the formula

=C2*D2

and pressing Enter.

Use the fill handle to copy this formula all the way down to F4.

Finally, in cell

G2

, calculate the profit for crisps sold with the formula

=F2-E2

and press Enter.

Use the fill handle to copy this formula all the way down to G4.

By the time you've finished, your spreadsheet should look like this:

🧪 Experiment!

Change some of the numbers in the 'Quantity Sold' column. What happens to your revenue and profit figures?

⭐ Challenge

Can you calculate the total profit for all items? In cell G5, use the function

=SUM(G2:G4)

to add up all the values in the profit column.

Show it off

Ask your teacher to check your spreadsheet. A little tip, pressing CTRL + ` will toggle 'display formulae'.

Outcome: I have created a working spreadsheet model for a school tuck shop. My model uses formulas to automatically calculate revenue, costs, and profit. I can change the input data and watch the outputs update in real time.

What-If?

You've just built a model! The real power of a model is using it to ask "What-if?" questions. This is how businesses make decisions.

What if we sold 100 bags of crisps instead of 50?

What if the cost price of chocolate bars went up to £0.50? How would that affect our profit?

By changing the input numbers in your model, you can instantly see the impact on your profit. This is much faster and cheaper than trying things out in the real world!