F202 TA4: Testing and Delivering the Outcomes
Testing the model
Testing is carrying out the test plan, recording what actually happened, fixing what failed, and testing it again.
Evidence that convinces
- A screenshot of the inputs and the outputs for each test, labelled with the test number.
- The formula view of the calculation being tested: Formulas, Show Formulas, or Ctrl+` in Excel.
- Before and after screenshots of anything that failed and was fixed, plus the retest.
- Annotations saying what each screenshot proves.
Tools that find faults
| Tool | What it does |
|---|---|
| Trace Precedents | Draws arrows to the cells a formula uses |
| Trace Dependents | Draws arrows to every cell that uses this one |
| Evaluate Formula | Steps through a formula one part at a time |
| Error Checking | Flags inconsistent formulas and numbers stored as text |
| Show Formulas | Displays every formula instead of its result |
Error values, and what they usually mean
| Error | Meaning | A typical cause in a model |
|---|---|---|
#DIV/0! | Division by zero | Break-even tickets when the price equals the cost per head |
#N/A | A value is not available | A lookup that found nothing |
#VALUE! | The wrong type of value | Text in a cell used in arithmetic |
#REF! | A reference is no longer valid | A row or sheet the formula used has been deleted |
#NAME? | An unrecognised name | A misspelt function or an undefined named range |
#NUM! | An impossible number | A calculation with no valid answer |
##### | The column is too narrow | Not an error in the value at all: widen the column |
| Circular reference | A formula depends on itself | A cost calculated from the profit it feeds into |
A log in which every test passes first time is not believable, and suggests the tests were chosen to pass. Record the failure, the cause, the fix and the retest: that is the process the assessment is looking for.
User acceptance testing
Give the client or real users tasks, not a tour: "The committee wants to know the profit if only 400 people would come even for free. Find out." Watch, do not help, and note every hesitation. Then record what they said and what you changed because of it.
Presenting the outcomes
The client did not ask for a spreadsheet. They asked a question, and the model exists to answer it. Delivering the outcomes means presenting that answer clearly enough to act on.
A recommendation built from the model
At the expected demand, a price of £27.50 makes the most profit, £2,075, from 270 tickets. The profit curve is flat near the top, so a round £25 or £30 makes £2,000, only £75 less. The risk is in the demand survey: if 10% fewer people want to come, profit at £27.50 falls to £725, and in the pessimistic scenario the festival loses £845. I recommend £25. It sells 300 tickets at the expected demand, keeps the event affordable, and does better than £27.50 if demand is weak: £800 rather than £725 with 10% less demand, and a loss of £700 rather than £845 in the pessimistic case. Before deciding, the committee should test the survey with a short pre-sale.
What makes that a strong recommendation:
- It answers the client's question directly, in the first sentence.
- It quotes the model's outputs, with units.
- It states the uncertainty, using the scenarios and the sensitivity analysis.
- It makes a decision and justifies it with the numbers.
- It says what would make the decision more certain.
Choosing how to present
| Audience | Format |
|---|---|
| A busy client who wants a decision | A one-screen dashboard and a short written recommendation |
| A committee meeting | Three or four slides: the question, the answer, the risk, the recommendation |
| The people entering data | A user guide with annotated screenshots |
| Whoever maintains the model | Technical documentation |
The user guide and the technical documentation
Two documents, for two different readers.
| User guide | Technical documentation | |
|---|---|---|
| Reader | The people who use the model | Whoever maintains or extends it |
| Purpose | Use it correctly without help | Understand how it works and change it safely |
| Contents | What the model is for; opening it and enabling macros; entering inputs; running scenarios and Goal Seek; reading the outputs; what each message means; what to do when something goes wrong | The sheet structure; every named range; every formula and why it is there; the validation rules; the macro code, commented; protection, and where passwords are kept; assumptions and their sources; the version history |
| Style | Plain English, numbered steps, annotated screenshots | Precise and complete; tables are fine |
Writing a user guide people can follow
- One task per section, named the way a user would say it: "Change the ticket price".
- Numbered steps, one action in each.
- A screenshot for every step that involves finding something, with the place to click marked.
- What the user should see when it has worked.
- The common problems, and the fix for each: "The cell will not accept my value" means the value is outside the allowed range.
Test the guide the way you test the model: give it to somebody who has never seen the model, and watch them follow it.
Save numbered versions as you go, festival-model-v01.xlsx, v02 and so on, and keep a short log of what changed in each. It protects against lost work, shows how the model developed, and lets you go back when a change makes things worse.
Sources and further reading
- Microsoft Excel help and learning: formula auditing, error values and protection.
- GOV.UK: writing for GOV.UK: how to write plain English instructions that people actually follow.
- Nielsen Norman Group: 10 usability heuristics: principles for designing a dashboard that is easy to use.
- W3C: introduction to web accessibility: why accessibility matters, which applies to dashboards and guides as much as to websites.
- WebAIM contrast checker: check the colours on your dashboard can be read.
Key terms you must be able to define
- Test log
- The record of each test carried out, with expected and actual results and any action taken.
- Trace precedents
- An auditing tool that shows the cells a formula takes its values from.
- Trace dependents
- An auditing tool that shows every cell that uses a given cell.
- Error value
- A result such as #DIV/0! or #N/A that shows a formula could not be calculated.
- Circular reference
- A formula that depends, directly or indirectly, on its own cell.
- Dashboard
- A single screen summarising the outputs a client needs to make a decision.
- Recommendation
- A justified decision for the client, drawn from the model's outputs and their uncertainty.
- User guide
- Documentation that tells users how to operate the model, in plain language.
- Technical documentation
- Documentation that explains how the model works, so it can be maintained and changed.
- Version control
- Saving numbered versions with a record of changes, so earlier versions can be recovered.
Flashcards
Reach the top grade on this topic
- Carry out the whole test plan and record actual results honestly, failures included
- Show the fix and the retest for every failure
- Use the formula auditing tools and the formula view as evidence
- Present a recommendation that answers the client's question and states the uncertainty
- Write a user guide for users and technical documentation for maintainers, and test both
Mistakes that cost marks here
- A test log in which every test passed first time.
- Screenshots with no explanation of what they prove.
- Presenting the spreadsheet itself as the outcome, with no recommendation.
- Reporting one profit figure as if it were certain, ignoring the scenarios.
- A user guide written in technical language, or technical documentation with no formulas in it.
- Hiding errors with IFERROR instead of fixing their cause.
In 2012 a bank's risk model, built partly by copying and pasting between spreadsheets, divided by a sum where it should have divided by an average. It understated the risk on trades that went on to lose billions of dollars.
Knowledge check
Check you could explain this
- LEach test's number, purpose and test data
- LThe expected result, worked out before the test
- LThe actual result, with screenshots or the formula view
- LFailures, with the fix and a retest
Each test should be recorded with its number, its purpose and the exact test data used, so it can be repeated. The expected result should have been worked out by hand before the test was run, so there is an independent answer to compare against. The actual result should be recorded and backed by evidence, such as a screenshot of the inputs and outputs and the formula view of the calculation being tested, annotated to say what it shows. Where a test fails, the record should give the cause, the change made and the result of the retest, because a fix that has not been retested has not been shown to work.
- LA user guide is for the people who use the model
- LIt explains tasks in plain language, with steps and screenshots
- LTechnical documentation is for whoever maintains or changes the model
- LIt explains how it works: formulas, named ranges, macros, validation
A user guide is written for the people who will use the model, such as volunteers entering a price, and explains how to carry out each task in plain language, with numbered steps, annotated screenshots and what to do when something goes wrong. Technical documentation is written for whoever has to maintain or change the model later, and explains how it works rather than how to use it: the structure of the sheets, every named range and formula and why it is there, the validation rules, the macro code and the assumptions with their sources. The difference is the reader: one needs to operate the model without help, the other needs to change it without breaking it.
- LThe result depends on assumptions, especially the demand survey
- LSensitivity: profit depends strongly on demand
- LThe pessimistic scenario makes a loss at that price
- LThe curve is flat near the top, so other prices are nearly as good
- LOther factors outside the model matter
- LThe recommendation should give a range or a justified choice with its uncertainty
£27.50 is only the best price if the model's assumptions are right, and the most important assumption, how many people come at each price, is an estimate from a survey. The sensitivity analysis shows how much rests on it: if 10% fewer people want to come, profit at £27.50 falls from £2,075 to £725, and in the pessimistic scenario the event makes a loss of £845. The profit curve is also flat near the top, so £25 and £30 both make £2,000, only £75 less than the maximum, and £25 actually does better than £27.50 when demand is weak. There are also things the model does not include at all, such as whether the price is affordable for a community event, the weather and competing events. So rather than a single number, the client should be given a recommendation with its reasoning and its risk: for example £25, with the evidence that it gives up little profit in the expected case and loses less if demand disappoints, and a suggestion to test the survey with a pre-sale before committing.