MskProdComputing
Back to the topic

Data Analytics · F202 TA2

F202 TA2: Planning the Design of 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.

  1. 1Which requirement can be tested?1
    1. AThe model should look professional
    2. BA volunteer can change the price and read the profit without help
    3. CThe model should be good
    4. DThe model should be easy to use
  2. 2Which of these is a functional requirement?1
    1. AText must have strong contrast
    2. BThe model must calculate the break-even price
    3. COnly the input cells can be edited
    4. DIt must run in the client's spreadsheet software
  3. 3Why separate inputs, calculations and outputs onto different sheets?1
    1. AIt makes the file smaller
    2. BIt hides the formulas from the client
    3. CUsers know where to type, and the calculations can be checked and locked
    4. DSpreadsheet software requires it
  4. 4What does a data dictionary record?1
    1. AEach field's name, type, format, validation and source
    2. BThe result of every formula
    3. CThe test results
    4. DThe client's contact details
  5. 5The valid prices are £5 to £60. Testing with £60.00 uses:1
    1. ABoundary data
    2. BNormal data
    3. CErroneous data
    4. DInvalid data
  6. 6Typing 'twenty' into the price cell is a test with:1
    1. ABoundary data
    2. BNormal data
    3. CErroneous data
    4. DExtreme data
  7. 7Why work out expected results by hand before testing?1
    1. AIt is faster than testing
    2. BThe spreadsheet cannot calculate them
    3. CIt avoids the need for test data
    4. DOtherwise there is nothing independent to compare the model's output against
  8. 8Why should the status show the word PROFIT as well as a green fill?1
    1. ASpreadsheets cannot colour text
    2. BSo the meaning does not depend on colour alone
    3. CWords recalculate faster
    4. DGreen cannot be printed
  9. 9Which is user acceptance testing?1
    1. AThe software checks for circular references
    2. BThe developer checks every formula
    3. CThe client's users try real tasks to confirm the model meets their needs
    4. DValidation rejects bad data
  10. 10Where should an assumption such as cost per head be stored?1
    1. AIn a cell comment
    2. BTyped into every formula that uses it
    3. CIn the file name
    4. DIn its own labelled input cell that formulas refer to

Section B

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

  1. 1ExplainExplain why a test plan should be written before a spreadsheet model is built.3
  2. 2DescribeDescribe what should be included in the design of an input sheet for a spreadsheet model.4
  3. 3ExplainA café owner says: "I want a spreadsheet to help me plan my prices." Explain how you would turn this into requirements that can be tested. Give two examples of testable requirements.6

Mark scheme

Section A

1B2B3C4A5A6C7D8B9C10D

Section B

1 Explain why a test plan should be written before a spreadsheet model is built. 3

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

  • Tests are based on the requirements, not on what the build happens to do
  • Expected results are worked out independently, by hand
  • Problems are found early, and invalid or boundary cases are planned in

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

2 Describe what should be included in the design of an input sheet for a spreadsheet model. 4

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

  • The layout and position of labels and input cells
  • Data types, formats and units
  • Validation rules and input messages
  • Colour coding, protection, or annotations linked to requirements

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

3 A café owner says: "I want a spreadsheet to help me plan my prices." Explain how you would turn this into requirements that can be tested. Give two examples of testable requirements. 6

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

  • Find out more from the client, such as by an interview
  • Identify the users, the inputs and the outputs
  • Make each requirement specific and measurable
  • A first valid testable requirement
  • A second valid testable requirement
  • Number the requirements so tests and evaluation can refer to them

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