F202 TA1: Principles of Spreadsheet Modelling
What a spreadsheet model is
A spreadsheet model is a simplified version of a real situation, built from numbers and formulas, so that you can ask what happens when something changes. A table of last year's sales is not a model: it records what happened. A sheet where you type next year's price and read off next year's profit is a model, because the outputs are calculated from inputs you can change.
The parts of every model
| Part | What it is | Example |
|---|---|---|
| Input variable | A value the user is expected to change, to explore a question | The ticket price |
| Assumption | A value the model takes as true, which may turn out to be wrong | 600 people would come if it were free, and 12 fewer come for every £1 on the price |
| Constant | A value that does not change while the model is in use | The licensed capacity of 400 people |
| Process | A formula that turns inputs into results | Tickets sold, revenue, costs |
| Output | A result the client reads and acts on | Profit, the break-even price, a chart |
The worked example used across this unit is invented, and deliberately unlike any OCR brief: Harbour Lights, a one-day community music festival deciding what to charge for a ticket. A survey of local residents suggests demand of 600 − 12 × price. The field is licensed for 400 people. Each attendee costs £5 in wristbands, toilets and security, and the stage, the licence and the insurance cost £4,000 whatever happens.
A model calculates perfectly from whatever it is given. If the demand assumption is wrong, every output is wrong with complete precision. That is why each assumption needs a source you can name, and why evaluating a model means questioning its assumptions as well as checking its formulas.
Modelling vocabulary
- What-if analysis: changing an input to see the effect on the outputs.
- Goal seeking: working backwards from the output you want to the input that produces it.
- Scenario: a named set of input values, such as pessimistic, expected and optimistic.
- Sensitivity analysis: changing one assumption a little at a time to see how much the result depends on it.
- Forecast: using the model to predict a future value, such as next month's bank balance.
- Optimisation: finding the input that gives the best output, such as the price that makes the most profit, often within limits.
What models are used for
| Organisation | Question | What the model does |
|---|---|---|
| A sports club | Can we afford a new minibus? | A cash flow forecast, with the loan repayment worked out by PMT |
| A school | What must a trip cost so every expense is covered? | Break-even on the number of students who sign up |
| A café | What happens to profit if milk goes up 20%? | What-if analysis on one cost |
| A charity | How many volunteers do we need on each shift? | A staffing model driven by expected visitors |
| A small online shop | When must we reorder each product? | Stock levels, lead times and reorder points |
| An event organiser | Which ticket price makes the most profit? | Demand that changes with price, plus scenarios |
Who uses a model, and why that matters
The person who builds a model is usually not the person who uses it. The client may be a manager who only reads the dashboard. The end users may be volunteers who type the inputs and are not confident with spreadsheets. A model is designed for the people who will use it, which is why planning in TA2 starts with the client and the users rather than with the formulas.
Spreadsheets: strengths, limits and risks
Why a spreadsheet
- Immediate recalculation: change one cell and everything that depends on it updates, which is exactly what what-if analysis needs.
- Flexible and quick to build: a working model can exist in an afternoon.
- Familiar: most clients already have spreadsheet software and know the basics.
- Built-in tools: functions, charts, validation, Goal Seek, scenarios, pivot tables and macros, with no programming environment to install.
- Transparent: a user can click any cell and see the formula behind it.
When a spreadsheet is the wrong tool
| Need | Better tool | Why |
|---|---|---|
| Millions of records, edited by many users at once | A database | Integrity rules, controlled access, no practical row limit |
| The same processing of large data, repeated exactly | Python or R | Code can be tested, version controlled and re-run |
| Live reports for a whole organisation | A business intelligence tool such as Power BI or Tableau | Live data connections, permissions, shared dashboards |
| A calculation that must never be altered | A program or a locked system | Somebody can always edit a spreadsheet |
An Excel worksheet holds at most 1,048,576 rows and 16,384 columns, and the older .xls format only 65,536 rows. Limits like these are invisible until the day the data is bigger than they are.
Spreadsheet risk is real
Errors in spreadsheets are common, quiet and expensive, because a wrong formula still produces a confident-looking number.
- In 2020, almost 16,000 positive COVID-19 test results in England were left out of the daily figures, because data was being handled in the old
.xlsformat and ran out of rows. - A widely cited 2010 economics paper on government debt and growth was found in 2013 to contain a spreadsheet error: an average had been calculated over the wrong range of rows, leaving countries out.
- In 2020 scientists renamed a set of human genes, such as MARCH1 and SEPT1, because spreadsheet software kept converting the names into dates.
Every one of those errors produced a believable result. The defence is the same each time: plan the expected results before building, test with data you have worked out by hand, and keep inputs, calculations and outputs where they can be checked.
The features you will build with
| Feature | What it is for | Example in the festival model |
|---|---|---|
| Cell reference | Using a value from another cell, so it is stored once | =B3*B10 |
| Absolute reference | A reference that must not move when the formula is copied | =A2*$F$1 |
| Named range | A meaningful name for a cell or range | TicketPrice, Capacity |
| Formula | A calculation you write | =Revenue-TotalCosts |
| Function | A built-in calculation | SUM, MIN, IF, XLOOKUP |
| Data validation | Restricting what can be entered | The price must be from £5 to £60 |
| Conditional formatting | Formatting that changes with the value | Profit turns red below zero |
| Lookup | Finding a value in a table | The price of a Concession ticket |
| Chart | Showing a pattern visually | Profit against price |
| Pivot table | Summarising a long list | Tickets sold by type |
| What-if tools | Goal Seek, Scenario Manager, data tables | The price that gives a £1,000 profit |
| Macro | Automating a sequence of steps | A button that runs Goal Seek |
| Protection | Stopping cells from being changed | Formulas locked, inputs open |
| Sort and filter | Putting a list in order, or showing part of it | The bookings for one date |
| Freeze panes | Keeping headings visible while scrolling | The header row of a long list |
Data types and formats
| Type | Stored as | Shown as |
|---|---|---|
| Number | A value | 400, 1,250.5 |
| Currency | A number with a currency format | £27.50 |
| Percentage | A fraction, displayed multiplied by 100 | 0.1 shown as 10% |
| Date and time | A serial number counting days from a starting date | 14/06/2026, 19:30 |
| Text | Characters | Concession, HL-2026-0047 |
| Boolean | TRUE or FALSE | The result of =B3>=5 |
A cell showing £27.50 may hold 27.4999. Add up a column of cells like that and the total can be a penny different from the values on screen. Where money has to add up exactly, use ROUND in the formula rather than relying on the format.
Sources and further reading
- OCR Cambridge Advanced National in IT: Data Analytics (H019, H119): the qualification page, with the specification and assessment materials.
- The H019 and H119 specification (PDF): F202's content, topic area by topic area. Check that your version matches.
- Microsoft Excel help and learning: official guidance on every function and tool.
- Google Sheets function list: every Sheets function, with examples.
- LibreOffice Help: documentation for LibreOffice Calc, a free alternative.
- GCF Global Excel tutorials: free step-by-step lessons, from the basics upwards.
- European Spreadsheet Risks Interest Group: research into spreadsheet errors and how to prevent them.
Key terms you must be able to define
- Spreadsheet model
- A simplified version of a real situation, built from inputs, formulas and outputs, used to ask what happens when something changes.
- Input variable
- A value the user is expected to change in order to explore a question.
- Assumption
- A value the model treats as true, which may later prove to be wrong.
- Constant
- A value that stays fixed while the model is in use.
- What-if analysis
- Changing one or more inputs to see the effect on the outputs.
- Goal seeking
- Working backwards from a target output to the input value that produces it.
- Scenario
- A named set of input values, such as pessimistic, expected and optimistic.
- Sensitivity analysis
- Changing one assumption at a time to see how strongly the result depends on it.
- Relative reference
- A cell reference that changes when the formula is copied.
- Absolute reference
- A cell reference fixed with dollar signs, such as $F$1, so it does not change when copied.
- Mixed reference
- A reference with only the column or only the row fixed, such as $A2 or B$1.
- Named range
- A meaningful name given to a cell or range and used in formulas instead of its address.
Flashcards
Reach the top grade on this topic
- Use the vocabulary precisely: input variable, assumption, constant, process, output
- Explain why a spreadsheet suits the client's problem, and say where it would not
- Give every assumption a source the client could check
- Name each feature and say what it does for this client, not just that you used it
- Know the risks of spreadsheets and show how your design reduces them
Mistakes that cost marks here
- Calling any table of numbers a model. A model calculates its outputs from inputs you can change.
- Typing numbers into formulas, such as =B3*5, so an assumption is hidden where nobody can see or change it.
- Listing spreadsheet features with no explanation of what each one does for the client.
- Treating the model's output as fact, when it is only as good as its assumptions.
- Forgetting that formatting changes the display, not the stored value.
- Copying a formula without checking which references should have been absolute.
The first spreadsheet program for personal computers, VisiCalc, came out in 1979 for the Apple II. People bought the computer just to run it, which made it one of the first 'killer applications' in computing.
Knowledge check
Check you could explain this
- LAn input variable is a value the user changes to explore a question
- LA valid example of an input variable
- LAn assumption is a value taken as true that may be wrong or is an estimate
- LA valid example of an assumption, ideally with its source
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.
- LAn absolute reference does not change when a formula is copied
- LThis prevents copied formulas pointing at the wrong or empty cells
- LA named range makes a formula readable or self-documenting
- LEach value is stored once, so changing it updates every formula
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.
- LA spreadsheet recalculates immediately, which suits what-if planning
- LIt is familiar, available and cheap for a small charity
- LIt is quick and flexible to build and change
- LThe data volume is small, so a database's strengths are not needed
- LA risk: undetected errors, overwriting or version confusion
- LHow the risk is managed
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.