Data Analytics · F202 TA5

F202 TA5: Evaluating a Spreadsheet Model

Section A: 10 multiple choice questions, 10 marks. Section B: 3 written questions, 13 marks. Total 23 marks.

Name

Class

Date

Mark / 23

Section A

Circle the letter of the correct answer. One mark each.

1What should every row of an evaluation contain?1
1. AThe requirement, a verdict, the evidence and an improvement
2. BA description of the formulas
3. CA screenshot only
4. DHow long each part took
2Which is evidence that a requirement was met?1
1. AIt was finished on time
2. BI am happy with it
3. CTests 1, 2 and 8 match the hand calculations
4. DThe model looks professional
3Who should give feedback on the model?1
1. AFriends
2. BNobody: the tests are enough
3. COnly the developer
4. DThe client and the real users
4Which question gets the most useful feedback?1
1. ACould you find the break-even price, and how long did it take?
2. BWould you use it?
3. CIs it good?
4. DDid you like it?
5Which is a limitation of the festival model?1
1. AIt validates the price
2. BIt assumes demand falls by exactly 12 people for every £1
3. CIt uses named ranges
4. DIt has a dashboard
6What makes an improvement specific?1
1. AIt is ambitious
2. BIt names the change, how it would be made and what it would achieve
3. CIt mentions more features
4. DIt would take a long time
7Why is 'partly met' acceptable in an evaluation?1
1. AAn honest judgement backed by evidence shows understanding
2. BIt avoids losing marks
3. CIt means the requirement was wrong
4. DIt is the same as fully met
8What does reflection on your process look at?1
1. AThe marking criteria
2. BThe spreadsheet's features
3. CHow you worked, what you would change and why
4. DThe client's business
9Two of three volunteers could not find Goal Seek. Which improvement is best?1
1. ARemove Goal Seek
2. BTell users to read the manual
3. CAdd more colour
4. DA dashboard button that runs Goal Seek with a macro
10Why evaluate the assumptions as well as the formulas?1
1. AFormulas cannot be evaluated
2. BAssumptions are easier to evaluate
3. CAssumptions belong in the user guide
4. DCorrect formulas still give wrong answers if the assumptions are wrong

Section B

Answer in the space provided. The number of marks tells you how many separate points to make.

1ExplainExplain why an evaluation should be organised around the client's requirements.3
2DescribeDescribe two limitations of a spreadsheet model like the festival ticket model, and suggest an improvement for each.4
3EvaluateEvaluate the usefulness of user feedback when evaluating a spreadsheet model.6

Mark scheme

Section A

1A2C3D4A5B6B7A8C9D10D

Section B

1 Explain why an evaluation should be organised around the client's requirements. 3

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

The requirements define what success means for this client
Each requirement can be given a verdict backed by evidence
It shows what is unmet, so improvements are targeted

Model answer. 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.

2 Describe two limitations of a spreadsheet model like the festival ticket model, and suggest an improvement for each. 4

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

A first limitation of the model
A specific improvement for it
A second limitation of the model
A specific improvement for it

Model answer. 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.

3 Evaluate the usefulness of user feedback when evaluating a spreadsheet model. 6

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

Feedback reveals usability problems the developer cannot see
Watching real tasks gives specific evidence
It shows whether the model meets the client's real needs
Feedback from friends or a tiny sample can be biased
Opinions are weaker than observed evidence, and questions must be specific
A justified overall judgement

Model answer. 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.