MskProdComputing
Back to the topic

Data Analytics · F202 TA3

F202 TA3: Creating the 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. 1What does =VLOOKUP(B2,Tickets,2) do when the fourth argument is left out?1
    1. AIt always shows an error
    2. BAn exact match
    3. CAn approximate match, which can return a wrong answer with no error
    4. DIt looks in column 4
  2. 2Which formula gives 20% for 20 or more, 10% for 10 or more, otherwise 0%?1
    1. A=IF(B2>=20,20%,IF(B2>=10,10%,0%))
    2. B=IF(B2>=20,10%,IF(B2>=10,20%,0%))
    3. C=IF(B2>20,20%,10%)
    4. D=IF(B2>=10,10%,IF(B2>=20,20%,0%))
  3. 3What must you do before protecting a sheet, so users can still enter inputs?1
    1. ARemove the validation
    2. BLock the input cells
    3. CHide the formulas
    4. DUnlock the input cells
  4. 4Which tool shows the profit for every price from £10 to £50 in one grid?1
    1. AA data table
    2. BA macro
    3. CConditional formatting
    4. DGoal Seek
  5. 5Goal Seek finds £18.04 starting from £20, and £36.96 starting from £35. Why?1
    1. AThe model has an error
    2. BValidation changed the result
    3. CIt changes one input from its starting value and stops at the first answer it reaches
    4. DGoal Seek is random
  6. 6Which validation error style refuses an invalid value outright?1
    1. AInformation
    2. BWarning
    3. CInput message
    4. DStop
  7. 7An Excel workbook containing macros must be saved as:1
    1. A.pdf
    2. B.xlsm
    3. C.csv
    4. D.xlsx
  8. 8What does IFERROR do?1
    1. AFixes the error in the formula
    2. BReturns an alternative value when a formula produces an error
    3. CTurns off validation
    4. DFinds every error in the sheet
  9. 9Why must a pivot table be refreshed?1
    1. AIt does not recalculate when its source data changes
    2. BIt deletes itself after a day
    3. CRefreshing removes duplicates
    4. DRefreshing adds a chart
  10. 10Which chart best shows profit against ticket price?1
    1. AA doughnut chart
    2. BA line or scatter chart
    3. CA pie chart
    4. DA radar chart

Section B

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

  1. 1ExplainExplain how data validation and cell protection together make a spreadsheet model more robust.4
  2. 2CompareCompare Goal Seek and Scenario Manager as ways of carrying out what-if analysis.4
  3. 3ExplainA model uses =VLOOKUP(B2,Tickets,2) to find ticket prices. Some prices come out wrong, but no error is shown. Explain why this happens and how it should be fixed.5

Mark scheme

Section A

1C2A3D4A5C6D7B8B9A10B

Section B

1 Explain how data validation and cell protection together make a spreadsheet model more robust. 4

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

  • Validation restricts what can be entered, such as a range or a type
  • So invalid data is rejected before it reaches the calculations
  • Protection locks formula cells so they cannot be overwritten
  • Together, only valid data goes in, and only in the right places

Model answer. Data validation restricts what each input cell will accept, for example a decimal from 5 to 60 for a ticket price or a choice from a list for a ticket type, so invalid or wrongly typed data is rejected before it can reach any calculation. Cell protection locks every formula cell and protects the sheet, so a user cannot type over a formula by accident, which would replace a calculation with a fixed number and break the model without any warning. Together they mean that data can only be entered in the input cells, and only valid data at that, which removes the two most common ways a working model is broken by its users.

2 Compare Goal Seek and Scenario Manager as ways of carrying out what-if analysis. 4

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

  • Goal Seek works backwards from a target output to an input
  • Goal Seek changes only one input and finds one answer
  • Scenario Manager stores several named sets of input values
  • Scenario Manager compares the outputs of the scenarios, such as in a summary

Model answer. Goal Seek works backwards: you give it a target for one formula cell, such as a profit of 1,000 pounds, and it changes a single input cell, such as the price, until the target is reached. It answers 'what input do I need?', but it only changes one value and finds one answer, which depends on where it started. Scenario Manager works forwards with several inputs at once: it stores named sets of values, such as pessimistic, expected and optimistic, and produces a summary comparing their outputs side by side. It answers 'what range of results might we see?', which makes it the better tool for judging risk, while Goal Seek is the better tool for setting a target.

3 A model uses =VLOOKUP(B2,Tickets,2) to find ticket prices. Some prices come out wrong, but no error is shown. Explain why this happens and how it should be fixed. 5

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

  • The fourth argument is missing, so VLOOKUP makes an approximate match
  • Approximate matching needs a sorted table and returns the nearest lower value
  • It always returns something, so no error appears
  • Fix: add FALSE, or use XLOOKUP or INDEX with MATCH
  • Retest every ticket type, and handle values not found

Model answer. The formula leaves out VLOOKUP's fourth argument, and when it is missing VLOOKUP does an approximate match. Approximate matching assumes the first column is sorted and returns the row with the largest value not greater than the one searched for, so with an unsorted list of ticket type names it can land on the wrong row. Because an approximate match always returns something, there is no error to warn anyone, and the wrong price looks perfectly believable. The fix is to ask for an exact match, =VLOOKUP(B2,Tickets,2,FALSE), or to use =XLOOKUP(B2,Tickets[Type],Tickets[Price]), which matches exactly by default. The lookup should then be retested with every ticket type and with a value that is not in the table, which should now give #N/A, handled with IFERROR and a clear message for the user.