F202 TA1: Principles of Spreadsheet Modelling

1 Explain the difference between an input variable and an assumption in a spreadsheet model. Use an example of each. 4

One mark for each of the following, to a maximum of 4.

• An input variable is a value the user changes to explore a question
• A valid example of an input variable
• An assumption is a value taken as true that may be wrong or is an estimate
• A valid example of an assumption, ideally with its source

Model answer. An input variable is a value the user is expected to change in order to explore a question, such as the ticket price in a pricing model: the whole point of the model is to try different prices and compare the results. An assumption is a value the model takes as true for the purpose of the calculation, even though it is an estimate that may turn out to be wrong, such as the assumption that 12 fewer people will come for every pound added to the price. The difference matters because an assumption needs a source the client can check, here a survey of residents, and an evaluation has to question it, whereas an input variable is simply set by the user.

2 Explain why named ranges and absolute references make a spreadsheet model more reliable. 4

One mark for each of the following, to a maximum of 4.

• An absolute reference does not change when a formula is copied
• This prevents copied formulas pointing at the wrong or empty cells
• A named range makes a formula readable or self-documenting
• Each value is stored once, so changing it updates every formula

Model answer. An absolute reference such as $F$1 stays fixed when a formula is copied, so a cost per head stored in one cell is used by every row; with a relative reference the copies would slide down to empty cells and silently calculate zero. A named range does the same job and adds meaning: =TicketsSold*CostPerHead can be read and checked as a sentence, whereas =B10*$F$1 has to be traced cell by cell, so errors are far easier to spot. Both mean each value is stored in exactly one place, so changing an assumption updates every formula that depends on it, with no copies left behind holding an old value.

3 A small charity is deciding whether to plan its fundraising budget with a spreadsheet model or a database. Justify the use of a spreadsheet model, and describe one risk the charity must manage. 6

One mark for each of the following, to a maximum of 6.

• A spreadsheet recalculates immediately, which suits what-if planning
• It is familiar, available and cheap for a small charity
• It is quick and flexible to build and change
• The data volume is small, so a database's strengths are not needed
• A risk: undetected errors, overwriting or version confusion
• How the risk is managed

Model answer. A spreadsheet model suits the charity because budget planning is a what-if problem: trustees want to see what happens if an event raises less than hoped or a grant is late, and a spreadsheet recalculates every total the moment an input changes. It is also software the charity already has and its volunteers already know, so it costs nothing and needs little training, and it can be built and adapted quickly as plans change during the year. A database would be the better choice for thousands of donor records edited by many people at once, but a budget is a few hundred figures used by one or two people, so its strengths would be wasted. The main risk is an undetected error: a formula that misses a row or a value typed over a formula still produces a believable total, and decisions would be made on it. The charity should manage that by testing the model against totals worked out by hand, validating the input cells, protecting every formula cell, and keeping one clearly named current version so nobody works from an old copy.