A well-built model keeps three things apart: inputs you can change, calculations that use them, and outputs you read. In practical tasks this appears whenever you are asked to build a sheet that “can be used again with different values”.
This lesson opens the module on spreadsheet modelling and charts and underpins every later lesson.
Why keep the three parts apart?
When a price is typed inside ten formulas, changing it means finding and editing ten formulas. Miss one and the sheet quietly disagrees with itself.
When the price lives in one labelled cell, every formula points at that cell. You change it once, and the whole sheet follows.
How to lay out a model, step by step
- List the inputs. These are the facts the task gives you that could change.
- Give each input a labelled cell in one block, with the unit in the label, such as “Price (RM)”.
- Write each calculation using cell references only, in a second block.
- Put the outputs the question asks for in a third block, with clear headings.
- Change an input and check that every dependent result moves in the right direction.
Worked example
A school club sells badges. The task says: price RM2.50, 120 badges sold, cost RM1.20 per badge. Find revenue, total cost and profit.
Inputs:
| Cell | Label | Value |
|---|---|---|
| B3 | Price (RM) | 2.50 |
| B4 | Badges sold | 120 |
| B5 | Cost each (RM) | 1.20 |
Calculations and outputs:
| Cell | Label | Formula | Result |
|---|---|---|---|
| B8 | Revenue (RM) | =B3*B4 | 300.00 |
| B9 | Total cost (RM) | =B5*B4 | 144.00 |
| B10 | Profit (RM) | =B8-B9 | 156.00 |
Check by hand: 2.50 × 120 = 300, 1.20 × 120 = 144, 300 − 144 = 156.
What-if test: set B3 to 3.00. Revenue becomes 3.00 × 120 = 360, profit becomes 360 − 144 = 216. The sheet should show exactly those numbers.
The mistake to watch for
A common slip is to type the number straight into the formula.
Mistaken formula in B8: =2.5*120
It gives 300 today. After B3 is changed to 3.00, B8 still shows 300, and the profit is wrong.
The correction is =B3*B4. A quick test catches the problem: change one input, and see whether every result you expect to move has moved. If one result stays still, a number is hard-coded somewhere.
Check yourself
1. In the badge model, which cells are inputs: B3, B4, B5, B8, B9 or B10?
Show answer
B3, B4 and B5. They are typed values that a person can change. B8, B9 and B10 are calculated from them.
2. Cost each rises to RM1.50 in B5. What is the new profit, and which formulas do you edit?
Show answer
Total cost = 1.50 × 120 = 180. Profit = 300 − 180 = RM120.00. You edit no formulas, only B5.
3. Write a formula for profit margin in B11 (profit divided by revenue), and give its value for the original inputs.
Show answer
=B10/B8. The value is 156 / 300 = 0.52, which is 52% once the cell is formatted as a percentage.
Where this leads next
With the layout settled, the next step is to show your outputs visually: create a chart suited to the data relationship. The ICT practical task and evidence checker helps you confirm that your layout is clear before you hand a task in.
Some students understand the layout in class but still hard-code a number under time pressure. A teacher in online one-to-one ICT tuition can watch you build a model live and point out that habit as it happens.