中文（繁體）
中文（简体）
English
S4 ICT: Spreadsheet Modelling (Paper 4 (error clinic))
Tutor Prep Guide · spreadsheet modelling: separating inputs from assumptions, formulas and absolute references, what-if analysis, goal seek, scenario comparison, data validation, break-even and sensitivity
S4 ICT: Spreadsheet Modelling (Paper 4 (error clinic))
Paper 4 (error clinic): ten spreadsheet modelling questions with answers and one-line notes.
Direct answers Level: S4 ICT; format: inputs versus assumptions, formulas and references, what-if analysis, goal seek, depreciation and sensitivity.
01 Paper details (check answers afterwards)
| Item | Detail |
|---|---|
| Questions | 10 |
| Suggested time | 20 |
| Difficulty mix | Foundation 3 · Intermediate 3 · Challenge 3 · Error redo 1 |
| Chapter | Chapter: S4 ICT: Spreadsheet Modelling |
| Level | S4 |
02 Questions (10 items)
- FoundationA formula reads =SUM(B2:B5) but the data runs to B8. What happens?
- FoundationFor a 5% tax, do you multiply by 0.05 or by 1.05?
- FoundationIf one cell in =AVERAGE(B2:B6) contains the text “absent”, what happens to the average?
- IntermediateWhat happens if a 0.5% monthly rate is entered as 0.5 in a compound interest formula?
- IntermediateA data table shows the same figures after the inputs change. What is the most common cause?
- IntermediateFilling a formula down without the dollar sign on a shared assumption cell causes what?
- ChallengeA break-even calculation gives 149.3 units. How many units should the answer state?
- ChallengeThe model shows revenue down 12% while a colleague adds +10% and -20% to get -10%. Which is right and why?
- ChallengeAn overtime model is written as =hours*60*1.5. What is wrong?
- Error redoSomeone claims a model returning numbers must be correct. What is wrong?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | Only B2:B5 is added, so B6:B8 are missed and the total is too small | The range must cover all the data, so recheck it after inserting rows or adding entries. |
| 2 | Use 0.05 for the tax itself; 1.05 gives the gross including tax, and the two must not be mixed up | Distinguish the added portion from the gross amount: one is the tax, the other the tax-inclusive price. |
| 3 | AVERAGE skips the text, so the divisor is one smaller and the average comes out too high | The divisor must match the intended headcount, or averages will not reconcile with the count. |
| 4 | 0.5 means 50%, not 0.5%, so the balance explodes; it should be 0.005 | Percentages divide by 100 before entering a formula, and a display format is not the stored value. |
| 5 | Calculation is set to manual (F9 not pressed), or the input cell is not actually linked to the formula | Check the calculation mode and the links first, then suspect the formula. |
| 6 | The reference drifts row by row, so later rows read the wrong cell and return 0 or #DIV/0! | Lock fixed assumptions with dollar signs so filling down leaves them in place. |
| 7 | 150 units: units round up because 149 still loses money | Discrete units round up, since rounding down leaves the operation short. |
| 8 | -12%: percentage changes multiply, 1.1 x 0.8 = 0.88 | Percentage changes compound by multiplication, the most common arithmetic trap in modelling. |
| 9 | Every hour is paid at 1.5 times, inflating pay for the first 40 hours; the two bands must be split | Segmented rules need IF or MIN/MAX so the premium applies only to the intended band. |
| 10 | Wrong: it needs totals checks, extreme-value tests and unit checks, because plausible output is not proof | Producing output is not correctness; a model is trusted through cross-checks, not impressions. |
04 What to do after marking
- After marking, redo only the wrong items and write down which step went wrong (formula, unit, or misread number).
- Repeat the same item two days later; move to the next paper only when both attempts are correct.
- Parents only need the answer and note columns; there is no need to explain every item.
A model earns its keep by recomputing: keep inputs apart from formulas, lock fixed assumptions with dollar signs, multiply percentage changes instead of adding them, and still cross-check totals and try extreme values before trusting the output.Tutor Registration →
Questions and answers are written originally by the TutorZone team and are not taken from any school, publisher or examination authority paper. Every value is checked by computer.
© TutorZone · www.tutorzone.com.hk · For personal study and teaching use only.