中文（繁體）
中文（简体）
English
S4 ICT: Spreadsheet Modelling (Paper 1 (basics))
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 1 (basics))
Paper 1 (basics): 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)
- FoundationHow should input cells and calculation cells be kept apart in a spreadsheet model?
- FoundationUnit price is in B2 (12.5) and quantity in C2 (8). Give the formula and the result for the total in D2.
- FoundationWhat does a spreadsheet return for =2+3*4, and how would you add before multiplying?
- IntermediateA 5% tax rate sits in C1 and a bill of 480 in B2. Give the formulas and results for the tax and the total.
- IntermediateIf B2 holds =B2*$C$1, what does the formula become when copied to row 5 of the same column?
- IntermediateA growth model has revenue rising from 1.00 million to 1.15 million. Give the formula and the growth rate.
- ChallengeA saver deposits $1,200 at each month end for 12 months at 0.5% monthly interest. Find the balance using the annuity formula (two decimals).
- ChallengeFixed cost is $3,000, unit cost $25 and price $45. How many units break even, and how many give a $1,500 profit?
- ChallengeIn a what-if test the price rises 10% while volume falls 20%. By what percentage does revenue change?
- Error redoA student types 100 over the formula in D2 and says the answer is the same anyway. What is wrong?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | Keep inputs together in one coloured block and let calculation cells hold formulas only, never typed numbers | A model must be able to recompute, so a number typed over a formula stops every later what-if update. |
| 2 | =B2*C2, giving 100 | Referring to cells rather than typing the multiplication by hand lets the total recompute when price or quantity changes. |
| 3 | It returns 14; write =(2+3)*4 to get 20 | Spreadsheets follow multiplication before addition, and brackets override that order. |
| 4 | Tax =B2*$C$1 gives 24; total =B2*(1+$C$1) gives 504 | The rate is a fixed assumption, so $C$1 locks it and filling down never points at the wrong cell. |
| 5 | =B5*$C$1 | The relative part follows the row while the dollar-locked C1 stays fixed. |
| 6 | =(115-100)/100 gives 0.15, that is 15% | A growth rate divides the change by the original value, not by the new one. |
| 7 | 1200 x ((1.005^12 – 1) / 0.005) = 14802.67 | The future value of an annuity compounds each deposit to the final date and adds them, hence ((1+r)^n-1)/r. |
| 8 | Break even at 3000/20 = 150 units; a $1,500 profit needs (3000+1500)/20 = 225 units | Break-even divides fixed cost by unit contribution, and a target profit simply joins fixed cost in the numerator. |
| 9 | 1.1 x 0.8 = 0.88, so revenue falls 12% | Percentage changes multiply, not add: 1.1 x 0.8 is the multiplier on revenue. |
| 10 | Wrong: typing a number kills recomputation, so changing price or quantity leaves D2 untouched | A model earns its value by recomputing, so calculation cells must keep formulas rather than fixed results. |
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.