中文（繁體）
中文（简体）
English
S4 ICT: Spreadsheet Modelling (Paper 3 (challenge))
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 3 (challenge))
Paper 3 (challenge): 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)
- FoundationNominal revenue grows 4% a year while inflation runs at 3%. What is the real growth rate?
- FoundationRevenue rises from 0.80 million to 1.00 million over three years. Find the compound annual growth rate (two decimals).
- FoundationWhy add a totals check, such as segment revenue summing to total revenue?
- IntermediateCash flows of $30,000, $40,000 and $50,000 arrive in years 1 to 3 with an 8% discount rate. Give the present value total and the NPV against a $95,000 outlay (two decimals).
- IntermediateThree scenarios: 40% chance of $20,000 profit, 35% chance of $5,000 and 25% chance of a $12,000 loss. Find the expected value.
- IntermediateAn investment of $120,000 returns $28,000 net cash a year. How long is the payback period (two decimals)?
- ChallengeRevenue is 2.00 million this year, grows 15% in each of the next two years then 5% in the two after. Find year 5 revenue in millions (two decimals).
- ChallengeA 10% price rise brings a 60% chance of a 20% volume drop and a 40% chance of unchanged volume. What is the expected revenue change?
- ChallengeDemand runs Q = 1200 – 20P with unit cost $15 and fixed cost $4,000. Find the profit-maximising price, quantity and profit.
- Error redoSomeone adds the yearly cash flows and subtracts the outlay, calling discounting academic. What is wrong?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | (1.04/1.03)-1 is about 0.97% | Real growth divides the nominal factor by the inflation factor; subtracting gives only an approximation. |
| 2 | (100/80)^(1/3)-1 is about 7.72% | Compound growth needs a root; dividing total growth by the number of years overstates it. |
| 3 | An independent formula verifying the total exposes a range that misses or repeats a row | Cross-checking is the cheapest error detector, far faster than reading every formula. |
| 4 | Present value total $101762.94, NPV $6762.94 | Each flow is divided by (1+r)^n; adding the raw flows would ignore the time value of money. |
| 5 | 0.4 x 20000 + 0.35 x 5000 – 0.25 x 12000 = $6750 | Expected value weights each outcome by its probability, with losses entered as negatives. |
| 6 | 120000/28000 is about 4.29 years; after four years only 112,000 has come back | Payback divides the outlay by the annual inflow and checks that cumulative cash really turns positive. |
| 7 | 200 x 1.15^2 x 1.05^2 is about 291.61 million | Segmented growth multiplies year by year, and each segment can be collapsed into one factor. |
| 8 | 0.6 x 0.88 + 0.4 x 1.1 = 0.968, an expected fall of 3.2% | Weight the multipliers by probability first, then subtract one to read the expected change. |
| 9 | P* = (1200+20×15)/(2×20) = $37.5, Q = 450, profit = $6125 | Profit is a quadratic in price, so its vertex is the optimum, which marginal revenue equals marginal cost confirms. |
| 10 | Wrong: undiscounted flows overstate future value, so each period must be divided by (1+r)^n | Time value is the core of the model; skipping it makes long-dated projects always look better. |
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.