MskProdComputing
Back to the topic

Data Analytics · F202 TA4

F202 TA4: Testing and Delivering the Outcomes

Section A: 10 multiple choice questions, 10 marks. Section B: 3 written questions, 14 marks. Total 24 marks.

Name

Class

Date

Mark  / 24

Section A

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

  1. 1A cell shows #####. What is the most likely cause?1
    1. ADivision by zero
    2. BThe column is too narrow to display the value
    3. CA deleted reference
    4. DA lookup found nothing
  2. 2What does #REF! usually mean?1
    1. AThe value is text
    2. BThe number is too large
    3. CA cell or sheet the formula referred to has been deleted
    4. DA function name is misspelt
  3. 3What does #NAME? usually mean?1
    1. ADivision by zero
    2. BThe column is too narrow
    3. CA circular reference
    4. DA function or named range is misspelt or not defined
  4. 4Which tool shows the cells a formula takes its values from?1
    1. ATrace Precedents
    2. BGoal Seek
    3. CFreeze Panes
    4. DTrace Dependents
  5. 5Why should a test log include failed tests?1
    1. AIt makes the log longer
    2. BFailures earn marks on their own
    3. CThe plan requires at least one failure
    4. DIt shows genuine testing, with the fault, the fix and the retest
  6. 6What is user acceptance testing?1
    1. AThe software checks for errors automatically
    2. BReal users carry out tasks to check the model meets their needs
    3. CThe developer tests each formula
    4. DValidation rejects bad data
  7. 7Which belongs in the technical documentation rather than the user guide?1
    1. AHow to enable macros when the file opens
    2. BHow to change the ticket price
    3. CEvery named range and formula, with what each does
    4. DWhat the dashboard numbers mean
  8. 8What makes a recommendation strong?1
    1. AIt lists every feature used
    2. BIt answers the question, quotes the outputs and states the uncertainty
    3. CIt avoids mentioning risk
    4. DIt repeats the requirements
  9. 9A formula's result depends on its own cell. This is:1
    1. AA nested function
    2. BA named range
    3. CAn absolute reference
    4. DA circular reference
  10. 10What is the best evidence that a formula works?1
    1. AIts output matches a result worked out by hand for the same inputs
    2. BThe client said it was fine
    3. CIt shows no error value
    4. DIt looks reasonable

Section B

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

  1. 1DescribeDescribe the evidence that should be recorded when testing a spreadsheet model.4
  2. 2ExplainExplain the difference between a user guide and technical documentation for a spreadsheet model.4
  3. 3ExplainThe festival model shows that a ticket price of £27.50 makes the most profit at the expected demand. Explain why the client should not simply be told to charge £27.50.6

Mark scheme

Section A

1B2C3D4A5D6B7C8B9D10A

Section B

1 Describe the evidence that should be recorded when testing a spreadsheet model. 4

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

  • Each test's number, purpose and test data
  • The expected result, worked out before the test
  • The actual result, with screenshots or the formula view
  • Failures, with the fix and a retest

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

2 Explain the difference between a user guide and technical documentation for a spreadsheet model. 4

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

  • A user guide is for the people who use the model
  • It explains tasks in plain language, with steps and screenshots
  • Technical documentation is for whoever maintains or changes the model
  • It explains how it works: formulas, named ranges, macros, validation

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

3 The festival model shows that a ticket price of £27.50 makes the most profit at the expected demand. Explain why the client should not simply be told to charge £27.50. 6

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

  • The result depends on assumptions, especially the demand survey
  • Sensitivity: profit depends strongly on demand
  • The pessimistic scenario makes a loss at that price
  • The curve is flat near the top, so other prices are nearly as good
  • Other factors outside the model matter
  • The recommendation should give a range or a justified choice with its uncertainty

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