F202 TA5: Evaluating a Spreadsheet Model
Evaluating against the requirements
An evaluation is a judgement backed by evidence. Go through the requirements one at a time. For each, say whether it was met, point to the evidence, and say what you would improve.
Writing one row well
| Weak | Strong |
|---|---|
| "The model calculates profit well." | "R1 required profit for any price from £5 to £60. It is fully met: tests 1, 2 and 8 match my hand calculations to the penny, and tests 4 and 5 confirm both ends of the range." |
| "Some users found it hard." | "R6 required that a volunteer could find the price for a target profit without help. It is partly met: two of three volunteers could not find Goal Seek in the menus. A dashboard button that runs it would take the menus out of the task." |
Fully, partly or not met; the test, the feedback or the screenshot that proves it; and what you would change. Three parts, every requirement, every time.
Feedback, limitations and improvements
Getting feedback worth using
- Ask the client and the users, not your friends.
- Ask specific questions: "Could you find the break-even price? How long did it take?" rather than "Did you like it?"
- Watch someone use the model, as well as asking them about it.
- Record what was said, criticism included, and what you did about it.
The limitations of the model itself
Every model simplifies. Saying where yours does is evidence of understanding, not an admission of failure.
| Limitation | Its effect | An improvement |
|---|---|---|
| Demand falls by exactly 12 people per £1 | Real demand is rarely a straight line | Fit the line to more survey data, or check it with a pre-sale |
| One price for everybody | It ignores child and concession tickets | Add ticket types with their own demand, using a lookup |
| No weather and no competing events | The biggest risks to attendance are outside the model | Add a weather scenario with lower demand |
| Costs are fixed per head | Security costs really rise in steps, not smoothly | Add a lookup of stewards needed per 100 attendees |
| A survey of 150 residents | A small sample that may not be representative | Collect a larger sample, or compare with similar events |
Improvements that score
An improvement is specific when it names the change, how it would be made and what it would achieve: "Add a weather drop-down that applies a 30% fall in demand from a lookup table of weather scenarios, so the committee can see the risk of a wet day before setting the price." A list of things you would have done with more time is not an improvement.
Reflecting on your own process
The last part of an evaluation looks at how you worked, not only at what you made.
- The plan against reality: which stages took longer than planned, and why.
- Decisions you would change: "I built the dashboard before the calculations were tested, so when test 5 changed the tickets formula I had to rebuild two charts."
- Skills: what you can do now that you could not before, with an example from the model.
- Next time: what you would do differently, specifically.
"I learned a lot and managed my time well" could be written by anyone about anything. Reflection counts when it is specific to your model, names a real decision, and says what you would change.
Sources and further reading
- The H019 and H119 specification (PDF): what TA5 expects an evaluation to cover.
- Nielsen Norman Group: why you only need to test with 5 users: the research behind small, repeated rounds of user testing.
- Office for National Statistics: published data to compare your assumptions against.
- Microsoft Excel help and learning: how to build the improvements you propose.
Key terms you must be able to define
- Evaluation
- A judgement of how well the model meets its requirements, backed by evidence.
- Verdict
- Whether a requirement is fully, partly or not met.
- Evidence
- A test result, piece of feedback or screenshot that supports a verdict.
- Limitation
- A way in which the model simplifies or cannot handle the real situation.
- Improvement
- A specific change, how it would be made, and what it would achieve.
- Feedback
- Comments and observations from the client and users about the model.
- Reflection
- An analysis of how you worked, what you would change and why.
Flashcards
Reach the top grade on this topic
- Give every requirement a verdict, with evidence from tests or feedback
- Use feedback from the client and real users, quoted, criticism included
- Explain the model's limitations, including its assumptions
- Propose improvements that name the change, the method and the effect
- Reflect on your own process specifically, with real decisions you would change
Mistakes that cost marks here
- Saying the model 'works well' without referring to any requirement.
- Claiming everything was fully met, with no evidence.
- Feedback from friends, or questions like 'did you like it?'
- Improvements that are a wish list rather than specific changes.
- Ignoring the assumptions, as if correct formulas guaranteed correct answers.
- Generic reflection that could describe any project.
Research by Jakob Nielsen suggested that testing with about five users finds most of the usability problems in a design, which is why several small rounds of testing beat one big one.
Knowledge check
Check you could explain this
- LThe requirements define what success means for this client
- LEach requirement can be given a verdict backed by evidence
- LIt shows what is unmet, so improvements are targeted
The requirements are the agreed definition of success for this client, so judging the model against them evaluates whether it does what it was built to do, rather than whether its maker likes it. Working through them one at a time means each can be given a clear verdict backed by specific evidence, such as a test result or a user's feedback. It also shows exactly which needs are only partly met or not met, so the improvements proposed are targeted at real gaps rather than being a general wish list.
- LA first limitation of the model
- LA specific improvement for it
- LA second limitation of the model
- LA specific improvement for it
The model assumes demand falls by exactly 12 people for every pound added to the price, but real demand is rarely a straight line, so the model could be improved by collecting more survey responses at different prices and fitting the line to them, or by checking it against a small pre-sale. It also ignores the weather, which is probably the biggest single risk to attendance at an outdoor event, so a weather drop-down could apply a fall in demand taken from a lookup table of weather scenarios, letting the committee see the risk of a wet day before setting the price.
- LFeedback reveals usability problems the developer cannot see
- LWatching real tasks gives specific evidence
- LIt shows whether the model meets the client's real needs
- LFeedback from friends or a tiny sample can be biased
- LOpinions are weaker than observed evidence, and questions must be specific
- LA justified overall judgement
User feedback is valuable because the person who built a model is the one person who cannot experience it fresh: they know where every input is and what every output means, so they are blind to the moments a new user hesitates. Watching a real user attempt a task, such as finding the price for a target profit, produces specific evidence, like two of three volunteers being unable to find Goal Seek, which points straight at an improvement. The client's feedback also shows whether the model answers the question they actually needed answered, which testing the formulas cannot. Its usefulness depends on how it is gathered, though. Feedback from friends tends to be polite rather than honest, a sample of one or two users may be unrepresentative, and vague questions such as 'did you like it?' produce opinions rather than evidence. Overall, feedback is one of the most useful sources of evidence in an evaluation, but only when it comes from the real client and users, is gathered with specific tasks and questions, and is recorded honestly alongside the test results rather than replacing them.