AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

Models of real life

Week 16 · Unit 7.4: The power of data · 3 lessons
1

Spreadsheet models

Computing 7: Unit 7.4, pp. 112–149
GoalExplain what a data model is and use formulae to link the factors in a spreadsheet model.
New words
data model · a copy of a real situation built from data, with rules that link the datafactor · an important item of data that stands for part of the situationformula · a calculation in a spreadsheet cell, which starts with an equals signcell reference · the column letter and row number of a cell, such as B2
Explanation

A model is a simple copy of a real situation that we build with data. It helps us understand a problem and make decisions. The things that matter in the situation are called factors. In a spreadsheet model, each factor goes into a cell as a heading or a number, and formulae tie the factors together. A formula starts with =, and uses cell references such as B2 and functions such as SUM. When you change one number, every formula that uses it updates by itself. That lets you ask “what if?” questions without doing the sums again.

D5fx=SUM(D2:D4)
ABCD
1PlantNumberPriceIncome
2basil10330
3mint8216
4tomato6424
5Total70
Worked examples
D2fx=B2*C2
ABCD
1PlantNumberPriceIncome
2basil10330
3mint8216
The income for each plant links two factors. In D2 the formula is =B2*C2: number sold times price. The formula is copied down for the other plants.
C2fx4
ABCD
1PlantNumberPriceIncome
2basil10440
3mint8216
4tomato6424
5Total80
A “what if?” question. What if the price of basil goes up from 3 to 4? Only cell C2 is changed. The income for basil becomes 40 and the total becomes 80, and nobody has to do the sums again.
FactorKindHow it is found
money in the club fundinputtyped in
cost of one potinputtyped in
number of potsinputtyped in
total costresultnumber × cost of one pot
money leftresultmoney in the fund − total cost
Factors of a model that helps decide whether the garden club can buy 30 pots. Inputs are typed in. Results are found by formulae.
Class activity

Paper spreadsheet (no computer): rule a grid on squared paper with columns A to D and rows 1 to 5. Model a snack stall: type the snack names in column A, the number sold in B, the price in C, and write the formula D = B × C in the heading. Work out each income by hand and add a total. Now change one price with a coloured pencil. Which cells must you rub out and work again? That is what a spreadsheet does by itself. If you have a computer or tablet, an adult can help you build the same table in a spreadsheet.

Practice
1
Complete the class-trip model. What formula goes in D2, and what are the values of D2, D3 and D4?
ABCD
1ItemCost eachNumberTotal
2bus ticket325?
3museum entry425?
4Total?
2
In the same model, five pupils cannot come, so the number falls from 25 to 20. What are the new totals?
3
List four factors in a model that helps a class decide how many cakes to bake for a bake sale.
4
Hana typed B2*C2 in cell D2, and the cell shows the words B2*C2 instead of a number. What is wrong?
D2fxB2*C2
ABCD
1ItemPriceNumberTotal
2pencil case36B2*C2