Presentation: Spreadsheet Break-Even Model

Slide 1 of 7 · Settling starter · IT · Year 11

5 minutes

Spreadsheet break-even model: Coffi’r Prom

Owain Jenkins sells coffee from a converted cart on the promenade in Aberystwyth. He wants a spreadsheet model that tells him how many cups he must sell each month before he starts making money, and what happens to that number if his prices or costs change.

While the register is called: write your name, class and date on your worksheet, then read the business brief. Find the total fixed costs, the total variable cost per cup and the selling price.

Starter question: What are those 3 figures, and which of them is paid whether he sells 0 cups or 1,000?

DCF: Data and computational thinking → Problem-solving and modelling No computers needed · formulas written by hand, calculator for values

Supply teacher script (no subject knowledge needed)

Hand out: one worksheet per pupil and one business brief (Business brief: Coffi’r Prom) between two. Pupils need a pen, a calculator and a ruler for the graph. Coffi’r Prom, Owain Jenkins and Rhys are invented; the figures are realistic.

Say aloud: “Name, class and date at the top. While I take the register, read the brief with your partner and find three numbers: the total fixed costs per month, the total variable cost per cup, and the selling price. Which of the three does Owain pay even if he sells nothing?”

Starter answer: fixed costs £720 a month, variable cost £1.20 a cup, price £3.00 a cup. The fixed costs (licence, insurance, loan, card reader) are paid every month whether he sells 0 cups or 1,000.

Slide 2 of 7 · Today’s brief and key words · IT · Year 11

2 minutes

Your brief: design the model, draw the graph, debug Rhys, ask “what if?”

You will write the formulas for Owain’s model, work out what they would show, draw the break-even graph, find and fix the mistakes in his cousin Rhys’s version, then use the model to test changes to prices, costs and the busy summer months.

Model

A simplified version of something real that you can change and test.

Break-even point

The number of cups where money in exactly equals money out. Fewer: a loss. More: a profit.

Absolute reference

$B$2 never changes when copied down. A relative reference such as B2 becomes B3 in the next row.

ROUNDUP and IF

=ROUNDUP(number,0) always rounds up to the next whole number. =IF(test,value if true,value if false) chooses between 2 results and can be nested.

DCF: Data and computational thinking → Problem-solving and modelling Layout: B2 fixed costs, B3 price, B4 variable cost, B5 forecast, B6 break-even

Slide 3 of 7 · Steps 1 and 2: the formula and the table · IT · Year 11

17 minutes

Step 1 & Step 2: the break-even formula, then the model table

Step 1 (8 min): (a) how much each £3.00 cup contributes after its £1.20 cost; (b) how many cups pay off £720, and the formula for B6 using cell references only; (c) why ROUNDUP, not ROUND, when the calculation gives 436.36.

Step 2 (9 min): write the 4 formulas for row 9 so they still work dragged down to row 16, using $ wherever a reference must not move. Then fill in the table of values.

Row	A: Cups sold	B: Revenue (£)	C: Total costs (£)	D: Profit (£)	E: Result
9	0	0.00	720.00	−720.00	Loss
10	100	300.00	840.00	−540.00	Loss

Steps 1 and 2 on your worksheet · 17 minutes Profit should rise by exactly £180 every row

Supply teacher script (no subject knowledge needed)

Say aloud: “8 minutes for Step 1, then 9 for Step 2. Rows 9 and 10 of the table are on the board so you can check your formulas against them. Every dollar sign must have a reason: do not sprinkle them everywhere. The table checks itself: profit should go up by the same amount each row, and the row where profit is zero must match your break-even answer.”

Quick check, Step 1: (a) £3.00 − £1.20 = £1.80 a cup. (b) 720 ÷ 1.80 = 400 cups; B6: =ROUNDUP(B2/(B3-B4),0), and the brackets matter. (c) He cannot sell part of a cup; ROUND gives 436 and at 436 cups he is still 60p short (436 × 1.65 = £719.40), so only ROUNDUP gives 437, the first whole number that covers his costs.

Quick check, Step 2: B9 =A9*$B$3; C9 =$B$2+A9*$B$4; D9 =B9-C9 (no $ signs); E9 =IF(D9>0,"Profit",IF(D9=0,"Break even","Loss")). Values: 200 cups 600 / 960 / −360 Loss; 300 cups 900 / 1,080 / −180 Loss; 400 cups 1,200 / 1,200 / 0 Break even; 500 cups 1,500 / 1,320 / 180 Profit; 600 cups 1,800 / 1,440 / 360 Profit; 700 cups 2,100 / 1,560 / 540 Profit.

Slide 4 of 7 · Steps 3 and 4: the graph, then debug Rhys · IT · Year 11

17 minutes

Step 3 & Step 4: draw the break-even graph, then debug Rhys’s model

Step 3 (10 min): on the grid, plot revenue and total costs from your table as 2 straight lines and draw fixed costs as a flat line at £720. Use solid, dashed and dotted lines so it works in black and white. Circle the crossing point and label it with its value. Write LOSS to the left and PROFIT to the right. Add a title and a key.

Step 4 (7 min): Rhys’s printout: row 9 is right, but row 12 says 300 cups makes a profit. (a) what formula ended up in C10 when =B2+A9*B4 was dragged down, and where 52003 came from; (b) why “row 9 is right, so the rest must be” is a bad test; (c) which row Rhys’s E9 formula gets wrong even once column C is fixed.

Point to plot	Revenue line	Total costs line
0 cups	£0	£720
700 cups	£2,100	£1,560

Steps 3 and 4 on your worksheet · 17 minutes Straight lines: 2 points each with a ruler

Supply teacher script (no subject knowledge needed)

Say aloud: “10 minutes for the graph. Both lines are straight, so plot the 0 and 700 points on the board with a ruler, then check they pass through your other table values. Then 7 minutes for Step 4: work out exactly what Rhys’s dragged formula calculated. Reproduce his wrong number, do not just say it is wrong.”

Quick check, Step 3: the revenue and total costs lines cross at 400 cups, £1,200, circled and labelled; fixed costs are a flat line at £720; LOSS between the lines to the left of 400, PROFIT to the right; 3 different line styles; a title such as “Coffi’r Prom: break-even at £3.00 a cup” and a key.

Quick check, Step 4: (a) every reference was relative, so C10 became =B3+A10*B5: price plus 100 cups times the forecast, 3.00 + 100 × 520 = 52,003. By row 12 it is =B5+A12*B7 and B7 is empty, so 520 + 300 × 0 = 520, the false £380 profit. Fix: =$B$2+A9*$B$4. (b) Row 9 is the one row where a wrong formula still looks right because A9 is 0, so the variable-cost part is zero whatever cell it points at; he should check a row he can do in his head (100 cups: 720 + 120 = £840) and check the zero-profit row matches B6. (c) Row 13: D13 is exactly 0, and 0 is not greater than 0, so =IF(D9>0,"Profit","Loss") calls break-even a loss; the nested IF fixes it.

Slide 5 of 7 · Steps 5 to 7: what if, the whole year, the limits · IT · Year 11

15 minutes

Steps 5, 6 & 7: what if?, the whole year and the queue problem, the limits

Step 5 (6 min): for each change name the input cell and its new value, show the working and give the new break-even. Start from the original figures each time. Then: which change hurts most, and which could Owain control?

Step 6 (6 min): (a) using the forecast, which months make a loss and what is the year’s profit (total cups × £1.80 − 12 × £720); (b) rewrite C9 to add the £300 helper only when column A is more than 600; (c) profit for June (600 cups) and July (700 cups), why more cups can mean less profit, and the new yearly profit.

Step 7 (3 min): 2 things the model assumes that are not true in real life, and how each could make Owain’s decisions wrong.

What if…	Cell and new value	Working	New break-even
1. He puts the price up to £3.20	B3 = 3.20	720 ÷ (3.20 − 1.20) = 720 ÷ 2.00	360 cups

Steps 5, 6 and 7 on your worksheet · 15 minutes Change 1 input at a time, from the original figures

Supply teacher script (no subject knowledge needed)

Say aloud: “About 6 minutes each for Steps 5 and 6, then 3 for Step 7. Scenario 1 is on the board. For each one, name the cell you would change, show the division and round up. In Step 6, add the twelve months of cups first. Step 7 is two sentences each: the assumption, and the decision it could get wrong.”

Quick check, Step 5: 2 B4 = 1.35, 720 ÷ 1.65 = 436.36, 437 cups. 3 B2 = 810, 810 ÷ 1.80 = 450 cups. 4 B3 = 3.20 and B4 = 1.35, 720 ÷ 1.85 = 389.19, 390 cups. The licence rise hurts most (adds 50 cups a month, against 37 for milk); the one thing Owain controls is his price, and a 20p rise more than cancels the milk increase, though fewer people might buy at £3.20 and the model cannot tell him that.

Quick check, Steps 6 and 7: (a) loss months are the 5 below 400 cups: January (300), February (320), March (380), November (340), December (380); total cups 5,720; profit 5,720 × 1.80 − 8,640 = £1,656. (b) C9: =IF(A9>600,$B$2+300,$B$2)+A9*$B$4. (c) June 1,800 − (720 + 720) = £360; July 2,100 − (1,020 + 840) = £240; the 601st cup triggers the whole £300 and 100 extra cups only bring £180 of contribution, so July earns £120 less; he needs 767 cups to get back to June’s profit; July and August both pay the helper, so the year falls by £600 to £1,056. Step 7: any 2 of: it assumes every cup sells at £3.00 (a price rise could lose customers); it assumes the forecast is right (a wet August could cut sales well below 720); it assumes the cost per cup never changes (bulk buying in summer makes each cheaper).

Slide 6 of 7 · Self and partner check · IT · Year 11

2 minutes

Self check: does every $ sign have a reason?

Swap sheets with a partner. Tick each success criterion their work meets. Then correct 1 thing on your own sheet, for example a formula that would break when dragged down.

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.

2 minutes · partner check Finished early? Margin of safety for May, in cups and as a percentage

Supply teacher script (no subject knowledge needed)

Say aloud: “Swap with your partner. Check their break-even is 400, their table rises by £180 a row, and their row 9 formulas have dollar signs on B2, B3 and B4 only. Tick the criteria they have met, hand it back and correct one thing on your own sheet.”

Extension answers: 1 May forecast 520 cups, break-even 400, so the margin of safety is 120 cups, which is 120 ÷ 520 = about 23% of May’s forecast. 2 To break even at 300 cups each cup must contribute 720 ÷ 300 = £2.40, so the price would be £1.20 + £2.40 = £3.60; accept a reasoned view on whether that is realistic on a promenade. 3 Any sensible extra column with a heading and a row 9 formula, for example “Profit with helper” using the Step 6(b) formula so the two profits can be compared.

Slide 7 of 7 · Plenary review · IT · Year 11

2 minutes

Quick-check answers before handing in

Check your key figures before your worksheets are collected.

Question	Correct answer
What is the contribution per cup, and the break-even point?	£1.80 a cup; 400 cups a month, from =ROUNDUP(B2/(B3-B4),0).
What should the total costs formula in C9 be so that it works when dragged down?	*=$B$2+A9$B$4*. Rhys’s relative references turned C10 into =B3+A10B5, giving 52,003.
What is Owain’s profit for the year, before and after paying the helper?	£1,656 before; £1,056 once July and August each pay £300.

Check your name and class are on your worksheet Leave business briefs neat on the desk