中文（繁體）
中文（简体）
English
S4 ICT: Spreadsheet Modelling (Paper 2 (intermediate))
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 2 (intermediate))
Paper 2 (intermediate): 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)
- FoundationWhat does data validation do in a model?
- FoundationWhat does =IF(B2>=60,”pass”,”fail”) return when B2 is 58?
- FoundationWhat does Scenario Manager give you over changing one assumption at a time?
- IntermediateGoal Seek: with fixed cost $4,800 and unit contribution $24, how many units break even, and what contribution is needed if only 160 units can be sold?
- IntermediateStraight-line depreciation: cost $12,000, residual $2,000, useful life 5 years. Give the annual charge and the book value at the end of year 3.
- IntermediateAn overtime model pays $60 per hour with time-and-a-half beyond 40 hours. Find the pay for 46 hours.
- ChallengeSensitivity: price $50, variable cost $30 and fixed cost $6,000. If price falls 8%, how far does break-even volume rise?
- ChallengeWorking backwards: to hold $50,000 after 36 monthly deposits at 0.4% monthly, what monthly payment is needed (two decimals)?
- ChallengeTwo channels: A costs $8 per click at 3% conversion, B costs $15 per click at 5%. Which has the lower cost per acquisition over 100 clicks?
- Error redoSomeone multiplies fixed cost by volume, arguing costs should scale with size. What is wrong?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | It restricts an input cell to a range or list, such as positive whole numbers for volume, blocking bad assumptions at the source | A wrong assumption poisons every downstream result, so stopping it at the input is cheaper than checking later. |
| 2 | “fail” | IF tests the condition first, returning the second argument when true and the third when false. |
| 3 | A scenario stores a whole set of assumptions and switches between them for comparison, while single edits show one result and are easy to leave half-changed | Decisions move price, cost and volume together, so saving the full set prevents a half-changed model. |
| 4 | 4800/24 = 200 units; 4800/160 = $30 | Goal Seek solves backwards from a target, giving the volume or price that hits it. |
| 5 | Annual (12000 – 2000)/5 = $2000; book value after year 3 is 12000-6000 = $6000 | Straight-line spreads the depreciable amount evenly, and book value is cost minus accumulated depreciation. |
| 6 | 40 x 60 + 6 x 90 = 2940 | Split normal and overtime hours with IF or MIN/MAX; never multiply all hours by 1.5. |
| 7 | From 6000/20 = 300 units to 6000/16 = 375 units, a rise of 25% | A fifth off the contribution pushes break-even up by 1/0.8, that is 25%, because the relationship is reciprocal. |
| 8 | 50000 / ((1.004^36 – 1) / 0.004) = $1294.06 | The payment is the target divided by the annuity factor, using the monthly rate and the number of months. |
| 9 | A: 800/3 = $266.67 against B: 1500/5 = $300.00, so A is cheaper | Compare cost per acquisition, not conversion rate alone: a higher rate can still cost more per customer. |
| 10 | Wrong: fixed cost does not vary with volume, so multiplying it inflates cost and ruins the break-even point | A model must separate fixed from variable behaviour; only variable cost accrues per unit. |
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.