Cover lesson · IT · Year 11 · Advanced

Spreadsheet break-even model: Coffi'r Prom

DCF: Data and computational thinking → Problem-solving and modelling

Name: Class: Date:

Cover teacher — you need no subject knowledge

Print: this sheet, one per pupil, and the stimulus sheet “Business brief: Coffi'r Prom”, one between two. No computers are needed: pupils write formulas and work out values by hand, with a calculator. If you are in a computer room, pupils can build the model for real in Excel or Google Sheets; the sales forecast is in coffee-cart-forecast.csv.

Run: 5 min read the brief together · 8 min Step 1 · 10 min Step 2 · 10 min Step 3 · 8 min Step 4 · 8 min Step 5 · 8 min Step 6 · 3 min Step 7. Total 60 minutes.

Collect: this sheet. The answer key is the worked example. The key number to check is that the break-even point is 400 cups; if a pupil has a different number, their Step 1 is wrong and everything after it will be too.

What you are doing

Owain sells coffee from a cart on the promenade in Aberystwyth. You are going to design a spreadsheet model of his business: write the formulas, work out what they would show, draw the break-even graph, find and fix the mistakes in his cousin Rhys's version, and then use the model to answer “what if?” questions about prices, costs and the busy summer months.

A model is a simplified version of something real that you can change and test. The break-even point is the number of cups where money in exactly equals money out: sell fewer and he loses money, sell more and he makes a profit.

Step 1 — inputs and the break-even formula

Use the layout in Rhys's printout: fixed costs in B2, price in B3, variable cost in B4, forecast in B5. Type the totals from the brief (720 and 1.20) straight into B2 and B4.

(a) Each cup sells for £3.00 and costs £1.20 to make. How much does each cup contribute towards paying the fixed costs?

(b) Use your answer to (a) to work out how many cups pay off £720 of fixed costs. Then write the formula for B6 using cell references only, no numbers except the 0 in ROUNDUP.

(c) If the variable cost rose to £1.35, the calculation gives 436.36 cups. Why must the model use ROUNDUP and not ROUND here?

Step 2 — the model table

Write the formulas for row 9 so that they give the right answer when dragged down to row 16. Use $ wherever a reference must not move.

Cell	Formula
B9 — Revenue
C9 — Total costs
D9 — Profit
E9 — Result: “Profit”, “Break even” or “Loss”

Now work out what the sheet would show.

Row	A: Cups sold	B: Revenue (£)	C: Total costs (£)	D: Profit (£)	E: Result
9	0
10	100
11	200
12	300
13	400
14	500
15	600
16	700

Step 3 — the break-even graph

Plot revenue and total costs from your table as two straight lines, and draw the fixed costs as a flat line. Circle the point where revenue and total costs cross and label it with its value. Write LOSS in the gap between the lines to the left of that point and PROFIT in the gap to the right. Make the three lines different styles (solid, dashed, dotted) so the graph works in black and white.

Step 4 — debug Rhys's model

Look at Rhys's printout on the brief. Row 9 is right, but row 12 says Owain makes a profit selling 300 cups.

(a) When Rhys dragged =B2+A9*B4 down one row, what formula ended up in C10? Use it to explain where 52003 came from.

(b) Why is “Row 9 is right, so the rest must be right too” a bad way to test a spreadsheet? How should he have tested it?

(c) Even with column C fixed, Rhys's formula in E9 gives the wrong word in one row of the table. Which row, and why? (Hint: what is D13?)

Step 5 — what if?

For each change, say which input cell you would change, then work out the new break-even point. Start from the original figures each time.

What if…	Cell and new value	Working	New break-even (cups)
1. He puts the price up to £3.20
2. Milk goes up by 15p a cup
3. The council puts the pitch licence up by £90 a month
4. Changes 1 and 2 together

Which single change hurts Owain most? Which change could he actually control?

Step 6 — the whole year, and the queue problem

(a) Use the forecast on the brief. In which months does Owain make a loss? What is his profit for the whole year? (Hint: add up all twelve months of cups first. Profit = total cups × the amount from Step 1(a) − twelve months of fixed costs.)

(b) Above 600 cups he must pay a helper £300. Rewrite the C9 formula so it adds the £300 only when column A is more than 600.

(c) With the helper, work out his profit for June (600 cups) and July (700 cups). Explain why selling more cups can mean less profit, and what the new yearly profit is.

Step 7 — the limits of the model

Give two things this model assumes that are not true in real life, and say how each could make Owain's decisions wrong.

Step by step

5 min — Read the brief. Find the fixed costs, the variable cost and the price.
8 min — Step 1: contribution per cup, break-even point and the formula for B6.
10 min — Step 2: write the four formulas for row 9, then fill the table.
10 min — Step 3: draw the graph, mark break-even, label the profit and loss areas.
8 min — Step 4: find and explain Rhys's two mistakes.
8 min — Step 5: four what-if scenarios. On a computer, Goal Seek can find the price that gives any break-even you choose.
8 min — Step 6: the year's forecast and the helper rule.
3 min — Step 7: the model's limits.

Success criteria

I can calculate a contribution per unit and a break-even point, and write the formula for it using cell references.
I can write formulas that still work when copied down, using absolute references where a cell must not move.
I can draw a break-even graph with the crossing point, the profit area and the loss area clearly labelled.
I can find an error in someone else's model, explain exactly what it calculated instead, and fix it.
I can use the model to test “what if?” changes, including a step in costs written with IF, and explain the results for the business.
I can name assumptions the model makes and explain how they could mislead the person using it.

If you finish early

Owain's margin of safety is how far his forecast is above break-even. Work it out for May, in cups and as a percentage of May's forecast.
What price would he need to charge to break even at 300 cups a month? Is that price realistic for a cup of coffee on a promenade?
Design one extra column for the model table that would help Owain decide whether to hire the helper at all. Write its heading and its row 9 formula.