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)
A
B
C
D
1
Plant
Number
Price
Income
2
basil
10
3
30
3
mint
8
2
16
4
tomato
6
4
24
5
Total
70
Worked examples
D2fx=B2*C2
A
B
C
D
1
Plant
Number
Price
Income
2
basil
10
3
30
3
mint
8
2
16
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
A
B
C
D
1
Plant
Number
Price
Income
2
basil
10
4
40
3
mint
8
2
16
4
tomato
6
4
24
5
Total
80
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.
Factor
Kind
How it is found
money in the club fund
input
typed in
cost of one pot
input
typed in
number of pots
input
typed in
total cost
result
number × cost of one pot
money left
result
money 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?
A
B
C
D
1
Item
Cost each
Number
Total
2
bus ticket
3
25
?
3
museum entry
4
25
?
4
Total
?
D2 is =B2*C2, which is 3 × 25 = 75. D3 is =B3*C3, which is 4 × 25 = 100. D4 is =SUM(D2:D3), which is 75 + 100 = 175.
2
In the same model, five pupils cannot come, so the number falls from 25 to 20. What are the new totals?
Bus tickets 3 × 20 = 60. Museum entries 4 × 20 = 80. New total 60 + 80 = 140. Only the number cells change; the formulae stay the same.
3
List four factors in a model that helps a class decide how many cakes to bake for a bake sale.
For example: the number of people expected, the cost of ingredients for one cake, the price you will charge, the money you want to raise, the number of cakes each person might buy. (Any four sensible factors.)
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
A
B
C
D
1
Item
Price
Number
Total
2
pencil case
3
6
B2*C2
A formula must start with an equals sign. Without it, the spreadsheet treats the cell as text. Hana should type =B2*C2.