F202 TA2: Planning the Design of a Spreadsheet Model
Understanding the client and the requirements
Every decision in a model should trace back to something the client needs. Before designing anything, find out:
| Question | Why it matters | Festival example |
|---|---|---|
| Who is the client? | The person whose decision the model supports | The festival committee's chair |
| Who are the end users? | They will type the inputs, so the design has to suit them | Three volunteers, none of them confident with spreadsheets |
| What is the problem? | The question the model must answer | "What should we charge, and how risky is it?" |
| What are the inputs? | Everything that varies or is assumed | Price, survey results, costs, capacity |
| What are the outputs? | What the client will read and act on | Profit, tickets sold, break-even price, a chart |
| What are the constraints? | Limits the solution must respect | It must run in the committee's spreadsheet software, and be ready by March |
Requirements that can be tested
A requirement is only useful if you can later prove whether it was met. "The model should be easy to use" cannot be tested. "A volunteer can enter a new price and read the profit without help" can.
| Weak | Strong, and testable |
|---|---|
| Calculate the profit | R1. Calculate profit for any ticket price from £5 to £60, to the nearest penny |
| Show if it is good | R2. Show PROFIT in green or LOSS in red beside the profit figure |
| Compare options | R3. Compare pessimistic, expected and optimistic scenarios side by side, with a chart |
| Find the right price | R4. Find the price that gives a profit the committee types in |
| Safe | R5. Users can edit only the input cells; every formula is protected |
| Easy to use | R6. A volunteer can change the price, or find the price for a target profit, without help |
Functional and non-functional requirements
- Functional requirements say what the model must do: calculate, look up, validate, compare, chart.
- Non-functional requirements say how well it must do it: how easy to use, how secure, how accessible, which software it must run in.
R1, R2, R3. Every design decision, every test and every paragraph of your evaluation can then point back to a numbered requirement, and whoever marks the work can follow the thread from the brief to the finished model.
Designing the structure and the sheets
One job per sheet
Separating inputs, data, calculations and outputs is the most valuable design decision in a spreadsheet model. Inputs are in one place, so a user knows where to type. Calculations are in one place, so they can be checked and locked. Outputs are in one place, so the client reads them without wading through the workings.
Designing each sheet
Sketch every sheet before building it, on paper or in a drawing tool. A good design shows:
- the layout: where each label, input, output and chart sits
- the data types and formats: currency, percentage, date
- which cells are inputs, and how each is validated
- the colour scheme and fonts, used consistently
- navigation: buttons or hyperlinks between sheets
- annotations explaining each choice and the requirement it meets
Design rules that prevent errors
- No typed numbers inside formulas. Every assumption lives in a labelled cell, and formulas refer to it.
- One formula per column. The same calculation is copied down, not rewritten row by row.
- Units in every label. "Fixed costs (£)", "Capacity (people)".
- Consistent colour coding for inputs, calculations and outputs, explained in a key.
- Never colour alone. The status shows the word PROFIT or LOSS as well as green or red, for users who cannot tell the colours apart.
- A naming convention for files, sheets and named ranges, decided before the first save:
festival-model-v03.xlsx,TicketPrice.
Planning the data and the processing
The data dictionary
A data dictionary describes every input and stored field before it exists, so building becomes a matter of following the plan.
| Name | Data type | Format | Validation | Example | Source |
|---|---|---|---|---|---|
| TicketPrice | Currency | £0.00 | Decimal, 5 to 60 | £27.50 | Entered by the user |
| DemandAtZero | Whole number | 0 | Whole number, 100 to 2,000 | 600 | Residents' survey, May |
| LostPerPound | Whole number | 0 | Whole number, 1 to 50 | 12 | Residents' survey, May |
| Capacity | Whole number | 0 | Whole number, 1 to 2,000 | 400 | Premises licence |
| CostPerHead | Currency | £0.00 | Decimal, 0 to 50 | £5.00 | Suppliers' quotes |
| FixedCosts | Currency | £#,##0 | Decimal, 0 to 50,000 | £4,000 | Suppliers' quotes |
| TicketType | Text | Text | List: Adult, Child, Concession, Family | Adult | Lookup table on the Data sheet |
Where the data comes from
| Source | Example | What to check |
|---|---|---|
| Primary: collected for this purpose | The residents' survey | Was the sample big enough, and representative? |
| Client records | Last year's ticket sales | Is it complete, accurate and still relevant? |
| Secondary: published by someone else | Official statistics from the ONS or data.gov.uk | Is it current, from a reliable publisher, and licensed for reuse? |
If the model holds information about identifiable people, such as names on a booking list, the UK GDPR and the Data Protection Act 2018 apply: collect only what the model needs, and keep it secure. The ICO explains what that means in practice.
Cleaning data before it goes in
Imported data is rarely ready to use. Plan how you will deal with duplicates, blanks, inconsistent spellings, numbers stored as text and dates in mixed formats, using tools such as Remove Duplicates, TRIM, Text to Columns and Find and Replace.
The processing plan
Describe every output in words, then as the formula you intend to write. Writing it in words first catches mistakes in the logic before they become mistakes in the brackets.
| Output | In words | Planned formula |
|---|---|---|
| TicketsSold | Demand at this price, but never more than the capacity | =MIN(Capacity, DemandAtZero-LostPerPound*TicketPrice) |
| Revenue | Tickets sold times the price | =TicketsSold*TicketPrice |
| TotalCosts | Fixed costs plus the cost of each attendee | =FixedCosts+TicketsSold*CostPerHead |
| Profit | Revenue minus total costs | =Revenue-TotalCosts |
| Status | PROFIT if profit is at least zero, otherwise LOSS | =IF(Profit>=0,"PROFIT","LOSS") |
Decisions with several outcomes are clearer as a flowchart than as a formula, and the flowchart belongs in the design: the nested IF diagram in TA3 is an example. Plan each macro as numbered steps too: what starts it, what it does, and what the user sees when it has finished.
The planned TicketsSold formula above has a flaw: a high price gives negative tickets. Testing finds it, and TA3 shows the fix. A plan that changes because a test caught something is evidence of a real process, so keep the original plan and record the change.
Planning the testing
Write the test plan before building, from the requirements, and work out every expected result by hand. Tests written after the build are shaped by what the model already does, so they can only confirm it.
Types of test data
| Type | What it is | Festival example |
|---|---|---|
| Normal | Sensible data the model should accept and process | A price of £27.50 |
| Boundary | Values at the edge of what is allowed, on both sides of the limit | £5.00 and £60.00 accepted; £4.99 and £60.01 rejected |
| Invalid | The right type of data, outside the allowed range | A price of £75 |
| Erroneous | The wrong type of data altogether | A price of "twenty" |
What to test, beyond the inputs
- every formula, against a result calculated by hand
- every validation rule, with data on both sides of its boundary
- every lookup, including a value that is not in the table
- every macro and button
- protection: try to type in a locked cell
- the outputs: do the chart and the dashboard update when an input changes?
- usability: can a real user complete a task without help? This is user acceptance testing.
Test number, purpose, test data, type of data, expected result, actual result, and the action taken when a test fails. Planning tests you expect to be rejected, such as invalid and erroneous data, shows you understand testing rather than demonstrating that the model works.
Sources and further reading
- The H019 and H119 specification (PDF): what TA2 covers. Check it against your version.
- diagrams.net: a free tool for drawing sheet designs, flowcharts and structure diagrams.
- Office for National Statistics and data.gov.uk: reliable published data, most of it free to reuse under the Open Government Licence.
- Information Commissioner's Office: what data protection law requires if your model stores personal data.
- WebAIM contrast checker: check that the colours in your design are readable.
- Microsoft Excel help and learning: how each feature you are planning to use works.
Key terms you must be able to define
- Client
- The person or organisation whose decision the model is built to support.
- End user
- A person who will use the model day to day, such as entering inputs.
- Functional requirement
- Something the model must do, such as calculate a value or validate an input.
- Non-functional requirement
- How well the model must work, such as how easy, secure or accessible it is.
- Data dictionary
- A table describing every field's name, data type, format, validation, example and source.
- Processing plan
- A description of how each output is calculated, in words and as the intended formula.
- Test plan
- A table of planned tests, each with its purpose, test data, type and expected result.
- Normal data
- Sensible data that the model should accept and process correctly.
- Boundary data
- Data at the edge of the allowed range, tested on both sides of each limit.
- Invalid data
- Data of the right type but outside the allowed range, which should be rejected.
- Erroneous data
- Data of the wrong type altogether, which should be rejected.
- User acceptance testing
- Testing in which real users carry out tasks to confirm the model meets their needs.
Flashcards
Reach the top grade on this topic
- Number the requirements and make every one testable
- Separate inputs, data, calculations and outputs, and explain why
- Design every sheet, annotated with the requirement each feature meets
- Complete a data dictionary with types, formats, validation and sources
- Describe each output in words before writing it as a formula
- Write the test plan before building, with expected results worked out by hand
Mistakes that cost marks here
- Requirements that cannot be tested, such as 'easy to use'.
- Designing only the dashboard and leaving the input and calculation sheets to chance.
- Designs with no annotation, so nobody can tell why any choice was made.
- A data dictionary without validation rules or sources.
- Test plans written after the build, with expected results copied from the model's own output.
- Testing only normal data, never the boundaries or invalid entries.
Field audits of real business spreadsheets, summarised by the researcher Raymond Panko, found errors in most of the spreadsheets examined. That is why professional modellers plan and test as carefully as programmers do.
Knowledge check
Check you could explain this
- LTests are based on the requirements, not on what the build happens to do
- LExpected results are worked out independently, by hand
- LProblems are found early, and invalid or boundary cases are planned in
A test plan written first is based on the requirements, so it checks what the model should do rather than what the finished build happens to do. Its expected results are worked out by hand before the model exists, which gives an independent answer to compare against; tests written afterwards tend to copy the model's own output and so confirm whatever it does, including its mistakes. Planning the tests also forces the designer to think about boundary, invalid and erroneous data at the design stage, when adding a validation rule or a limit to a formula costs minutes rather than a rebuild.
- LThe layout and position of labels and input cells
- LData types, formats and units
- LValidation rules and input messages
- LColour coding, protection, or annotations linked to requirements
The design should show the layout of the sheet, with every label and input cell in position, so the builder knows exactly where everything goes. Each input should have its data type, format and unit stated, such as currency to two decimal places in pounds. The validation rule for each input should be given, for example a decimal from 5 to 60 with an input message explaining it, or a drop-down list fed from a named range. Finally, the design should show the colour coding that marks input cells as unlocked and editable, and be annotated to explain each choice and the requirement it meets.
- LFind out more from the client, such as by an interview
- LIdentify the users, the inputs and the outputs
- LMake each requirement specific and measurable
- LA first valid testable requirement
- LA second valid testable requirement
- LNumber the requirements so tests and evaluation can refer to them
The request is a starting point, not a requirement, so I would interview the owner to find out what decision the model is meant to support, who will use it, what data is available and what they need to see. That identifies the inputs, such as the cost of ingredients for each item and the expected number of sales, and the outputs, such as the profit on each item and in total. I would then rewrite each need so that it is specific and measurable, because a requirement is only useful if I can later prove whether it was met. For example: R1, the model calculates the profit per item and for the whole menu for any price the owner enters, to the nearest penny; and R2, a member of staff can change a price and read the new weekly profit without help. Numbering the requirements means each one can be traced to a test in the test plan and to a verdict in the evaluation.