Presentation: Digital Budgeting for a Trip

Slide 1 of 7 · Settling starter · Maths · Year 8

3 minutes

Digital budgeting for a trip: 11 costs, 1 budget of £900.00

You have a worksheet with a cost list of 11 items for a Year 8 trip to Techniquest in Cardiff Bay. Today you build a spreadsheet that works out every line total, the grand total, the money left and the cost per pupil, then write a recommendation to the head of year.

While the register is called: write your name, class and date on your worksheet, log on, and read the cost list in the table on your sheet.

Starter question: Which items in the list are charged once, and which are charged per pupil?

DCF: Data and computational thinking → Data and information literacy Log on now · open Microsoft Excel or Google Sheets

Supply teacher script (no subject knowledge needed)

Hand out: one worksheet per pupil. Pupils log on and open Microsoft Excel or Google Sheets. The cost list file welsh-trip-costs.csv may be in the shared area; if it is not, or the computers are down, the whole 11-row list is printed on the worksheet and pupils type it in or use a calculator on paper. The trip, the prices and the head of year are invented.

Say aloud: “Name, class and date at the top. Log on and open Excel or Google Sheets. While I take the register, read the cost list on your sheet and decide which items are paid once and which are paid for every pupil.”

Starter answer: charged once: coach hire (£395.00), coach parking (£8.00), the Forces and Motion workshop (£45.00), first aid kit refill (£6.40), contingency fund (£20.00). Charged per pupil: Techniquest pupil admission (£8.50 each, 30 pupils) and the Science Theatre show supplement (£2.00 each, 30 pupils). The lunches (12), tabards (3 packs) and photocopying (66 sheets) are charged by quantity but not per pupil.

Slide 2 of 7 · Today’s brief and key words · Maths · Year 8

2 minutes

Your brief: build the budget with formulae, then recommend

Year 8 has been offered a day trip to Techniquest. The head of year has agreed £900.00 for 30 pupils and 3 members of staff. You will type the 11 items into a spreadsheet, write 4 formulae, answer 5 budget questions and write 3 or 4 sentences saying whether the trip fits and 1 thing you would change.

Formula

An instruction typed into a cell that starts with an equals sign, such as =B2*C2. The spreadsheet does the sum and updates it if a number changes.

Line total

The cost of 1 row of the budget: unit cost multiplied by quantity. Row 3 is £8.50 times 30.

Range

A block of cells written with a colon, such as D2:D12. =SUM(D2:D12) adds all 11 line totals in 1 go.

DCF: Data and computational thinking → Data and information literacy LNF: Numeracy → Number → Financial literacy

Slide 3 of 7 · Steps 1 and 2: enter the data · Maths · Year 8

10 minutes

Step 1 & Step 2: headings in row 1, the 11 items in rows 2 to 12

Column A = Item, B = Unit cost, C = Quantity, D = Line total. Type the 11 items from your cost list into rows 2 to 12, exactly as they are on the sheet. Leave column D empty for now. Copy row 1 and row 2 from the board.

Row	A · Item	B · Unit cost	C · Quantity	D · Line total
1	Item	Unit cost	Quantity	Line total
2	Coach hire, 49-seat, school to Cardiff Bay and return	395.00	1	(leave empty)

Steps 1 and 2 on your worksheet · 10 minutes Row 13 is GRAND TOTAL · type 900 into G2 as the budget

Supply teacher script (no subject knowledge needed)

Say aloud: “Row 1 is your headings: Item, Unit cost, Quantity, Line total. Rows 2 to 12 are the 11 items from the cost list, in the same order as your sheet. Type the numbers without the pound sign. Row 13, column A, type GRAND TOTAL. In cell G2 type 900, the budget. If the file welsh-trip-costs.csv is in the shared area you can open it instead and the first 3 columns are already filled.”

Walk the room: check row numbers match the worksheet, because the formulae later depend on it: row 2 coach 395.00 qty 1, row 3 pupil admission 8.50 qty 30, row 4 adult admission 0.00 qty 3, row 5 show supplement 2.00 qty 30, row 6 packed lunch 2.85 qty 12, row 7 parking 8.00 qty 1, row 8 tabards 11.99 qty 3, row 9 workshop 45.00 qty 1, row 10 first aid 6.40 qty 1, row 11 photocopying 0.06 qty 66, row 12 contingency 20.00 qty 1. Pupils without a computer use the printed table and a calculator.

Slide 4 of 7 · Step 3: the 4 formulae and the line totals · Maths · Year 8

15 minutes

Step 3: write the 4 formulae, then fill column D and the grand total

Write each formula in the formula box on your worksheet, then type it into the spreadsheet. The first row is on the board: copy it. Fill D2 down to D12, then write the grand total formula in D13.

Cell	What it must work out	Formula	Shows on screen
D2	The line total for row 2 (unit cost times quantity)	*=B2C2**	£395.00
D13	The grand total of all 11 line totals	Use SUM with a range	?
G3	Money left: the £900.00 budget in G2 minus the grand total	?	?
G4	Cost per pupil, to the nearest penny, for 30 pupils	?	?

Step 3 on your worksheet · 15 minutes Every total is a formula, never a typed number

Supply teacher script (no subject knowledge needed)

Say aloud: “D2 is on the board: =B2*C2, the star means multiply. Type it, press Enter, and it should show 395. Now copy it down to D12: click D2, drag the small square in the corner down to row 12, or type =B3*C3 and so on. Then work out the formulas for D13, G3 and G4 and write all 4 in the formula box on your sheet.”

Quick check, formulae: D13 is =SUM(D2:D12) and shows £863.53. G3 is =G2-D13 and shows £36.47. G4 is =ROUND(D13/30,2) and shows £28.78 (=D13/30 on its own is acceptable if they round the answer themselves). Adding 11 cells one at a time, =D2+D3+..., works but does not meet the success criterion about using a range.

Quick check, column D: D2 395.00, D3 255.00, D4 0.00, D5 60.00, D6 34.20, D7 8.00, D8 35.97, D9 45.00, D10 6.40, D11 3.96, D12 20.00. Grand total 863.53. On paper with a calculator the same figures apply: 8.50 times 30 = 255.00, 2.85 times 12 = 34.20, 11.99 times 3 = 35.97, 0.06 times 66 = 3.96.

Slide 5 of 7 · Steps 4 and 5: budget questions, then the recommendation · Maths · Year 8

14 minutes

Step 4 & Step 5: answer the 5 budget questions, then write to the head of year

Step 4 (7 min): read the figures from your sheet to answer the 5 budget questions in the table. Question 1 is on the board as a model. Step 5 (7 min): 3 or 4 sentences to the head of year: say whether the trip fits, quote your total, and name 1 thing you would change.

Question	Your answer
1. What is the grand total cost of the trip?	£863.53, from cell D13.
2. Does it fit inside the £900.00 budget? By how much?	Read cell G3.
3. Which single item is the biggest share of the cost?	Look down column D for the largest figure.

Question 5 hint: if 4 pupils drop out, which costs stay the same, and which costs go down?

Steps 4 and 5 on your worksheet · 14 minutes Quote your figures: £863.53, the money left, the cost per pupil

Supply teacher script (no subject knowledge needed)

Say aloud: “Question 1 is on the board. Answer questions 2 to 5 by reading your own spreadsheet, not by guessing. Then write your recommendation at the end of the sheet: does it fit, what is the total, and what 1 thing would you change.”

Quick check, questions: 1. £863.53. 2. Yes, £900.00 minus £863.53 = £36.47 left over, about 4% of the budget. 3. The coach, £395.00, which is 45.7% of the whole trip, more than pupil admission and the show supplement put together (£315.00). 4. 863.53 divided by 30 = 28.7843, so £28.78 to the nearest penny; a strong answer notices 30 times £28.78 is only £863.40, 13p short, so families should be asked for £28.79 or £29.00. 5. The cost per pupil goes up, to £31.60. Coach, parking, workshop, tabards, first aid and contingency do not get cheaper; those fixed items plus lunches come to £548.53. Only admission and the show are per pupil, £10.50 each, so 26 pupils cost 548.53 + (10.50 times 26) = £821.53, and 821.53 divided by 26 = £31.60. Accept “fixed costs shared between fewer people cost each person more”.

Walk the room, the recommendation: a secure answer says the trip fits (£863.53 against £900.00, £36.47 unspent), recommends going ahead, and names 1 change with a figure: for example the coach is 45.7% of the cost and only 33 of its 49 seats are used, so share it with another class; or ask families for £29.00 rather than £28.78 so the trip is not 13p short. “It seems affordable” with no figures is not secure.

Slide 6 of 7 · Self and partner check · Maths · Year 8

3 minutes

Swap with your partner and check against the success criteria

Tick each success criterion your partner’s work meets. Then correct 1 thing: a typed-in total becomes a formula, or a recommendation with no figures gets the grand total added.

I organised the data into labelled columns so each item, unit cost and quantity sits in the right place.
I used a formula rather than a typed-in number for every total, so the sheet updates itself when a cost changes.
I used a range in my total, =SUM(D2:D12), instead of adding 11 cells one at a time.
I read my own figures back to answer questions about the data, including a cost per pupil.
I drew a conclusion from the numbers and said what I would change, quoting the figures that support it.

3 minutes · partner check Finished early? A 72-seat coach costs £480.00 and 45 pupils go. Better value per pupil?

Supply teacher script (no subject knowledge needed)

Say aloud: “Swap worksheets. Tick the criteria your partner has met. If a total was typed in as a number rather than a formula, write ‘formula’ beside it. If the recommendation has no figures in it, write ‘quote the total’. Swap back and correct 1 thing.” On a computer, clicking a total cell shows whether it holds a formula (starts with =) or a typed number.

Extension answers: the extension tasks are on the worksheet under “If you finish early”. For the 72-seat coach: change B2 to 480.00 and the pupil quantities in C3 and C5 to 45, then read D13 and D13 divided by 45; a correct sheet updates on its own, which is the point of using formulae. The percentage column uses =D2/$D$13 filled down; the coach row shows 45.7%. For the 6 pupils who cannot pay, any cut that keeps the total inside £900.00 is acceptable as long as the exact lines cut are named.

Slide 7 of 7 · Plenary review · Maths · Year 8

3 minutes

Round the room, then quick-check answers before saving and handing in

Everyone names 1 figure from their sheet and what it means, nobody repeats one. Then check these answers.

Question	Correct answer
What is the grand total, and how much of the £900.00 is left?	£863.53, leaving £36.47.
What is the formula for the grand total in D13?	=SUM(D2:D12), a range, not 11 cells added one by one.
What is the cost per pupil to the nearest penny, and why does it rise if pupils drop out?	£28.78. The coach and other fixed costs stay the same, so they are shared between fewer pupils: 26 pupils pay £31.60 each.

Save as trip-budget-INITIALS in the class folder · name and class on your worksheet Worksheets in · nothing is shared outside school