MskProdComputing
KS5H019/H119 F202 TA3 36 min read 10 question quiz 13 marks of written practice

F202 TA3: Creating the Spreadsheet Model

Formulas, functions and references

Operators and the order they work in

OperatorMeaningExample
+ - * /Add, subtract, multiply, divide=Revenue-TotalCosts
^Power=1.05^3 for three years of 5% growth
&Join text=A2&" tickets"
= <> < > <= >=Compare, giving TRUE or FALSE=Profit>=0

Brackets first, then powers, then multiplication and division, then addition and subtraction. =4000+5*270 is 5,350. Write =(4000+5)*270 only if that is what you mean.

Functions worth knowing

CategoryFunctionsExample
Totals and averagesSUM, AVERAGE, MEDIAN, MIN, MAX=AVERAGE(B2:B31)
CountingCOUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS=COUNTIF(Type,"Child")
Conditional totalsSUMIF, SUMIFS, AVERAGEIF, MAXIFS=SUMIFS(Revenue,Type,"Adult",Day,"Sat")
RoundingROUND, ROUNDUP, ROUNDDOWN, INT=ROUNDUP(Visitors/50,0) stewards needed
TextLEFT, RIGHT, MID, LEN, UPPER, TRIM, CONCAT, TEXTJOIN=UPPER(LEFT(A2,3))
DatesTODAY, DATE, YEAR, WEEKDAY, NETWORKDAYS=FestivalDate-TODAY() days to go
LogicIF, IFS, AND, OR, NOT, IFERROR=IF(AND(Profit>0,TicketsSold<Capacity),"OK","Check")
LookupXLOOKUP, VLOOKUP, HLOOKUP, INDEX, MATCH=XLOOKUP(B2,Types,Prices)
FinancePMT, FV=PMT(6%/12,36,-12000) is about £365 a month

The festival model, as built

Spreadsheet formulas
TicketsSold     =MIN(Capacity, MAX(0, DemandAtZero - LostPerPound * TicketPrice))
Revenue         =TicketsSold * TicketPrice
VariableCosts   =TicketsSold * CostPerHead
TotalCosts      =FixedCosts + VariableCosts
Profit          =Revenue - TotalCosts
Status          =IF(Profit >= 0, "PROFIT", "LOSS")
BreakEvenTickets =ROUNDUP(FixedCosts / (TicketPrice - CostPerHead), 0)

Every formula refers to a named cell, so it reads as a sentence. MAX(0, …) was not in the original design: test 5 found that a price of £60 gave −120 tickets, and this is the fix. BreakEvenTickets is the classic break-even calculation: at £27.50 the festival needs 4000 ÷ 22.50, rounded up to 178 tickets, to cover its costs.

Tables and structured references

Formatting a list as a table (Ctrl+T in Excel) gives it a name, makes it grow when rows are added, and lets formulas say Sales[Revenue] instead of $D$2:$D$4001. Charts, pivot tables and validation lists built on a table then pick up new rows by themselves.

Build in the order the data flows

Inputs first, then each calculation in turn, checking each one by hand before building the next. A wrong value found at step two is a minute's work. Found on the dashboard, it could be anywhere.

Decisions and lookups

IF, and the logic around it

=IF(test, value if true, value if false) chooses between two results.

NeedFormula
One test=IF(Profit>=0,"PROFIT","LOSS")
Both conditions must be true=IF(AND(Age>=16,Age<=18),"Concession","Standard")
Either condition is enough=IF(OR(Type="Child",Type="Concession"),"Reduced","Full")
Several outcomesA nested IF, or IFS
A nested IF as a decision flowchartA flowchart of a nested IF that works out a group discount from the group size in cell B2. The first decision asks whether B2 is at least 20; if yes the discount is 20 percent. If no, the second decision asks whether B2 is at least 10; if yes, 10 percent. If no, the third asks whether B2 is at least 5; if yes, 5 percent, and if no there is no discount. The same logic is written as a formula two ways: =IF(B2>=20, 20%, IF(B2>=10, 10%, IF(B2>=5, 5%, 0%))), and with the IFS function as =IFS(B2>=20, 20%, B2>=10, 10%, B2>=5, 5%, TRUE, 0%). Nested IF works in every spreadsheet; IFS is easier to read where it is available. A warning underneath: test the largest threshold first, because in the opposite order a group of 25 would pass the test B2>=5 first and receive only 5 percent.A nested IF is a chain of decisions: group discount by group sizegroup sizein cell B2B2 >= 20yes20% offnoB2 >= 10yes10% offnoB2 >= 5yes5% offnono discount=IF(B2>=20, 20%, IF(B2>=10, 10%, IF(B2>=5, 5%, 0%)))=IFS(B2>=20, 20%, B2>=10, 10%, B2>=5, 5%, TRUE, 0%)The same logic twice: nested IF works everywhere; IFS is easier to read where it exists.Test the largest threshold first. In the other order a group of 25 passes B2>=5 first and gets only 5%.
Draw the decisions before you write the brackets. The flowchart belongs in your design, too.

Lookups

When the answer comes from a table, a lookup is better than a chain of IFs: the table can be read, checked and changed without touching a formula.

Exact and approximate lookupsTwo lookups side by side. On the left, an exact match: a table named Tickets lists Adult at 27 pounds 50, Child at 12 pounds, Concession at 18 pounds and Family, two adults and two children, at 70 pounds. The user types Concession into cell B2, and =XLOOKUP(B2, Tickets[Type], Tickets[Price]), or the older =VLOOKUP(B2, Tickets, 2, FALSE), finds the Concession row and returns 18 pounds. On the right, an approximate match: a table of bands sorted smallest first, with groups from 0 getting no discount, from 5 getting 5 percent, from 10 getting 10 percent and from 20 getting 20 percent. A group of 14 falls between bands, and =VLOOKUP(14, Bands, 2, TRUE) finds the largest band not above 14 and returns 10 percent. The table must be sorted smallest first for this to work. Warnings underneath: leaving out FALSE makes VLOOKUP do an approximate match, which gives wrong answers with no error; #N/A means the value was not found and can be caught with IFERROR, for example showing 'Check the ticket type'; and because prices are stored once in the table, changing the table updates every booking.Two kinds of lookup: find an exact item, or find the band a value falls inEXACT MATCH: the price of a ticket typeTypePriceAdult£27.50Child£12.00Concession£18.00Family (2+2)£70.00table named TicketsB2: Concessiontyped by the user=XLOOKUP(B2, Tickets[Type], Tickets[Price])=VLOOKUP(B2, Tickets, 2, FALSE) → £18.00APPROXIMATE: which discount band?Group fromDiscount00%55%1010%2020%sorted smallest first: essentialgroup of 14between bands=VLOOKUP(14, Bands, 2, TRUE) → 10%finds the largest band not above 14Leave out FALSE and VLOOKUP does an approximate match: wrong answers, and no error to warn you.#N/A means not found. Catch it for the user: =IFERROR(XLOOKUP(…), "Check the ticket type")Prices live once, in the table. Change the table and every booking updates.
A lookup replaces a nest of IFs whenever the answers come from a table.
FunctionUse it whenWatch out for
XLOOKUPYou have a current version of Excel or Google SheetsOlder versions do not have it
VLOOKUPThe value you match is in the first column of the tableCount the column number carefully, and use FALSE for an exact match
HLOOKUPThe table runs across rows instead of down columnsThe same as VLOOKUP
INDEX with MATCHIt must work in every program, or look to the leftTwo functions to get right instead of one
IFERRORA lookup might reasonably find nothingIt hides every error, so use it only where you expect one

=INDEX(Prices, MATCH(B2, Types, 0)) does the same job as the XLOOKUP in the diagram, in any spreadsheet program. The 0 asks MATCH for an exact match.

Validation, formatting, protection and macros

Data validation

Data validation on an input cellValidation set up on one input cell, the ticket price, in three stages. First, before typing: selecting the cell shows an input message, 'Ticket price: enter a price in pounds between 5.00 and 60.00'. Second, the rule itself: allow decimal, data between, minimum 5, maximum 60, error style Stop. Third, a bad value: typing 75 brings up a stop alert titled 'Price out of range' saying ticket prices must be between 5 and 60 pounds, with Retry and Cancel buttons, and the value is refused. Below, six types of validation with an example of each: whole number, capacity between 1 and 2,000; decimal, price between 5.00 and 60.00; list, ticket type chosen from Adult, Child, Concession and Family; date, booking date on or before the festival; text length, a booking reference of exactly 8 characters; and custom, =COUNTIF(Refs, A2)=1 so that no booking reference appears twice. A warning: validation checks what is typed, and a value pasted over the cell can slip past it, so that should be tested as well.Validation on one input cell: the message, the rule and the alert1 BEFORE TYPINGTicket price (£)27.50Ticket priceEnter a price in poundsbetween 5.00 and 60.002 THE RULEAllowDecimalDatabetweenMinimum5Maximum60Error styleStop3 A BAD VALUETicket price (£)75Price out of rangeTicket prices must be between£5 and £60. Please re-enter.RetryCancelWhole numbercapacity: whole number 1 to 2,000Decimalprice: decimal 5.00 to 60.00Listticket type: Adult, Child, Concession, FamilyDatebooking date: on or before the festival dateText lengthbooking reference: exactly 8 charactersCustom=COUNTIF(Refs, A2)=1: no duplicatesValidation checks what is typed. A value pasted over the cell can slip past it, so test that too.
The input message prevents most errors. The alert catches the rest.
  • Give each input cell an input message, so the rule is visible before anybody breaks it.
  • Choose the error style deliberately. Stop refuses the value. Warning and Information let it through after a message, which suits values that are unusual rather than impossible.
  • Feed drop-down lists from a named range, so the list can grow without editing the rule.

Conditional formatting

Formatting that responds to the value: profit in red below zero, a data bar showing tickets sold against capacity, a colour scale across a data table so the best prices stand out. A rule can use a formula, such as =$D2<0 to shade a whole row when its profit is negative.

Use it to draw the eye to what needs attention, and use it sparingly. A sheet where everything is highlighted highlights nothing.

Protection

  1. Unlock the input cells: Format Cells, Protection, untick Locked. Every cell starts locked.
  2. Protect the sheet, so that only the unlocked cells can be edited.
  3. Protect the workbook structure, so sheets cannot be deleted, moved or renamed.
  4. Record any password in the technical documentation, not in the user guide.
Protection is not security

Sheet protection prevents accidents, not determined people, and its passwords can be removed. Confidential data needs an encrypted file and proper access control, not a protected sheet.

Macros

A macro records or codes a sequence of actions so it can be run with one click. Typical uses in a model: run Goal Seek, reset the inputs to their defaults, clear a form, print the dashboard, or move between sheets.

VBA macro
' Finds the ticket price that gives the target profit typed on the dashboard
Sub FindPriceForTarget()
    Range("Profit").GoalSeek Goal:=Range("TargetProfit").Value, _
        ChangingCell:=Range("TicketPrice")
End Sub
  • In Excel, macros are written in VBA, and the file must be saved as .xlsm. Google Sheets uses Apps Script instead, and LibreOffice uses Basic.
  • Assign each macro to a clearly labelled button, and test it like any other feature.
  • Users see a security warning when they open a file containing macros, so the user guide has to tell them what to do.

What-if analysis

What-if analysis is the reason a model exists. There are four ways to do it, and each answers a different kind of question.

ToolThe question it answersWhere it is in Excel
Changing an inputWhat happens if the price is £30?Type a new value
Goal SeekWhat price gives a £1,000 profit?Data, What-If Analysis, Goal Seek
Scenario ManagerHow do the pessimistic, expected and optimistic cases compare?Data, What-If Analysis, Scenario Manager
Data tableWhat is the profit at every price from £10 to £50?Data, What-If Analysis, Data Table

LibreOffice Calc has the same tools under different names: Goal Seek and Scenarios on the Tools menu, and Multiple Operations for data tables. Google Sheets has no built-in Scenario Manager or data table, so there the grid of results is built with ordinary formulas.

What-if model

Change the inputs of a spreadsheet model and watch every output, the break-even prices and the graph recalculate. This tool needs JavaScript, so turn it on to use it. Everything you need to answer the exam question is explained in the text above.

Profit against ticket priceA line graph of profit against ticket price, drawn from the model's own formula: profit equals the smaller of 400 and 600 minus 12 times the price, multiplied by the price minus 5 pounds, minus 4,000 pounds. The horizontal axis runs from 0 to 50 pounds and the vertical axis from a loss of 4,000 pounds to a profit of 2,500. From 5 pounds to 16 pounds 67 the line is straight and rising, because demand is higher than the capacity of 400, so the festival sells out whatever the price and every extra pound is extra profit. It crosses zero, the first break-even point, at exactly 15 pounds. Above 16 pounds 67 the line curves: each price rise brings more per ticket but fewer people. Profit peaks at 2,075 pounds at 27 pounds 50, with 270 tickets sold, then falls, crossing zero again at the second break-even point of 40 pounds 65 and reaching a loss of 4,000 pounds at 50 pounds, where nobody comes. A dashed line joins two points of equal profit: 20 pounds with 360 tickets sold and 35 pounds with 180 sold both make 1,400 pounds, so the higher price earns the same with half the crowd. The area between the two break-even points, where the event makes a profit, is shaded.Profit against ticket price, from the model's own formulasticket price (£)£0£10£20£30£40£50−£4,000−£2,000£0£2,000profit (£)£20: 360 sold£35: 180 soldsame profit, half the crowdmaximum £2,075 at £27.50 (270 sold)break-even £15.00break-even £40.65below £16.67 it sells out:400 tickets, whatever the pricelosslossprofitprofit = MIN(400, 600 − 12 × price) × (price − £5) − £4,000
A chart of the model's output against one input is the quickest way to see the best value, the break-even points and how sensitive the result is.

The chart shows what a single answer cannot. The best price is £27.50, but the curve is flat near the top: £25 and £30 both make £2,000, only £75 less, so the committee can choose a round price and lose very little. Below £16.67 the festival sells out, so lowering the price further only gives money away. And £20 and £35 make the same profit, but £35 does it with half the crowd.

Goal Seek on the ticket modelGoal Seek answering the question: which ticket price gives a profit of 1,000 pounds? The dialog box has three fields: set cell $B$14, the profit; to value 1000, the target; by changing cell $B$3, the ticket price. Beside it, a graph of profit against price between 14 and 42 pounds, with a dashed horizontal line at the 1,000 pound target. The curve crosses the target twice. Starting from a price of 20 pounds, Goal Seek moves the price down and finds 18.04 pounds. Starting from 35 pounds, it moves up and finds 36.96 pounds. Goal Seek changes one input cell by repeated trial until the formula cell reaches the target. It finds one answer, and when a model has two, which one you get depends on where the input started, so the starting value belongs in your evidence, and the answer should be checked by putting it back into the model.Goal Seek works backwards: which price gives a £1,000 profit?Goal SeekSet cell:$B$14profitTo value:1000the targetBy changing cell:$B$3ticket priceOKCancel£15£20£25£30£35£40ticket pricetarget profit £1,000£18.04£36.96start at £20: Goal Seek finds £18.04start at £35: it finds £36.96Goal Seek changes ONE input cell, by repeated trial, until the formula cell reaches the target.It finds one answer. This model has two, and which one you get depends on where the price started.Record the starting value in your evidence, and check the answer by putting it back into the model.
What-if analysis runs forwards: change an input, read the output. Goal Seek runs backwards.
Three scenarios for one modelThree scenarios for the ticket model, all at the same price of 27 pounds 50. Pessimistic: demand at zero pounds of 500 people, a cost per head of 6 pounds and fixed costs of 4,500 pounds give 170 tickets and a loss of 845 pounds. Expected: demand of 600, 5 pounds per head and 4,000 pounds fixed give 270 tickets and a profit of 2,075 pounds. Optimistic: demand of 700, 4 pounds 50 per head and 3,800 pounds fixed give 370 tickets and a profit of 4,710 pounds. A bar chart shows the three profits, the pessimistic bar falling below zero. The single answer is 2,075 pounds; the honest answer is anything from a loss of 845 pounds to a profit of 4,710 pounds. Scenario Manager stores each set of inputs under a name and builds this summary in one click, and a recommendation that survives the pessimistic case is worth more than one that only works in the expected case.Scenarios: the same price, three sets of assumptionsPESSIMISTICdemand at £0500cost per head£6.00fixed costs£4,500price£27.50tickets sold170profit −£845EXPECTEDdemand at £0600cost per head£5.00fixed costs£4,000price£27.50tickets sold270profit £2,075OPTIMISTICdemand at £0700cost per head£4.50fixed costs£3,800price£27.50tickets sold370profit £4,710profit by scenario£0−£845pessimistic£2,075expected£4,710optimisticThe single answer is £2,075. The honest answer is 'anything from a loss of £845 to a profit of £4,710'.Scenario Manager stores each set of inputs under a name and builds this summary in one click.A recommendation that survives the pessimistic case is worth more than one that only works in the expected case.
Scenarios turn one number into a range, which is what a client deciding under uncertainty actually needs.

Sensitivity analysis

Change each assumption by the same amount, 10% here, and see how far the profit moves.

Assumption changed by 10%Profit at £27.50Change
None: the expected case£2,075—
Demand at £0 falls from 600 to 540£725−£1,350
Fixed costs rise from £4,000 to £4,400£1,675−£400
Cost per head rises from £5.00 to £5.50£1,940−£135

The model is far more sensitive to the demand assumption than to either cost, so the survey behind it deserves the closest checking, and the evaluation should say so.

For harder questions, such as the most profitable price that still sells at least 300 tickets, Excel's Solver add-in and LibreOffice's Solver find the best value of an output while keeping to constraints you set.

Charts and pivot tables

Choosing the chart

Choosing the right chartSix chart types drawn small, each labelled with the question it answers and the job it should not be used for. A bar chart compares categories and is wrong for a trend over many points. A line chart shows change over time and is wrong for unordered categories. A scatter plot shows the relationship between two numeric variables and is wrong for categorical data. A histogram shows the distribution of a single numeric variable and is not a way of comparing categories. A stacked bar shows composition within a total but makes the middle segments impossible to compare precisely. A box plot shows spread, median and outliers across groups, and should not be put in front of an audience it cannot be explained to. The pie chart is for one part against a whole with at most about four slices, because people compare angles badly and almost any comparison is clearer as a bar chart.Six charts, and the one question each answersBar chartcomparing categoriesnot for: a trend over many pointsLine chartchange over timenot for: unordered categoriesScatter plotrelationship between two numbersnot for: categoriesHistogramthe distribution of one variablenot for: comparing categoriesStacked barcomposition within a totalnot for: comparing middle segmentsBox plotspread and outliers across groupsnot for: an audience you cannot explain it toAnd the pie chart: one part against a whole, with at most about four slices.People compare angles badly, so almost any comparison is clearer as a bar chart.
If the chart needs a paragraph to interpret, it is the wrong chart.
  • A line or scatter chart shows one quantity against another, such as profit against price.
  • A column or bar chart compares categories, such as tickets by type or profit by scenario.
  • A pie chart shows parts of a whole, and only works with a few slices.
  • Every chart needs a title, axis titles with units, and a legend only if there is more than one series.
  • Start a column chart's axis at zero, or the differences look bigger than they are.
  • Build charts from tables or named ranges, so they update when the data grows.

Pivot tables

A pivot table summarising salesA pivot table summarising a list of ticket sales. The list has columns date, type, quantity and revenue, and six of its rows are shown: 1 June, adult, 2, 55 pounds; 1 June, child, 3, 36 pounds; 2 June, adult, 4, 110 pounds; 2 June, family, 1, 70 pounds; 3 June, child, 2, 24 pounds; 3 June, adult, 1, 27 pounds 50; and a note that there are thousands more. The pivot table is set with type as its rows and the sums of quantity and revenue as its values. It produces: adult 7 tickets, 192 pounds 50; child 5, 60 pounds; family 1, 70 pounds; grand total 13 tickets, 322 pounds 50. The same numbers can be produced by formulas such as =SUMIFS(Sales[Revenue], Sales[Type], "Adult"). A warning: a pivot table does not update by itself and must be refreshed after the data changes, whereas SUMIFS updates automatically, which is why dashboards are usually built from formulas.A pivot table summarises a long list without a single formulaDateTypeQtyRevenue01/06Adult2£55.0001/06Child3£36.0002/06Adult4£110.0002/06Family1£70.0003/06Child2£24.0003/06Adult1£27.50… and 4,000 more rowsPivotTableRows: TypeValues:Sum of QtySum of RevenueTypeQtyRevenueAdult7£192.50Child5£60.00Family1£70.00Grand total13£322.50The same numbers as formulas:=SUMIFS(Sales[Revenue], Sales[Type], "Adult")A pivot table does not update by itself. Refresh it after the data changes, and test that you did.SUMIFS updates automatically, which is why a dashboard is usually built from formulas.
Use a pivot table to explore; use formulas for anything the client relies on.

A pivot table is the quickest way to explore a long list: drag fields into rows, columns and values, and change the view in seconds. For figures the client relies on, formulas such as SUMIFS are safer, because they always recalculate.

Sources and further reading

Key terms you must be able to define

Formula
A calculation written by the user, starting with an equals sign.
Function
A built-in calculation, such as SUM, IF or XLOOKUP.
Nested IF
An IF function placed inside another, to choose between more than two outcomes.
Lookup
A function that finds a value in a table, such as XLOOKUP or VLOOKUP.
Exact match
A lookup that returns a result only when the value is found exactly.
Approximate match
A lookup that returns the largest value not greater than the one searched for, in a sorted table.
Data validation
Rules restricting what can be entered into a cell.
Input message
A note shown when a validated cell is selected, explaining what to enter.
Conditional formatting
Formatting applied automatically when a cell's value meets a rule.
Cell protection
Locking cells and protecting the sheet so that only unlocked cells can be edited.
Macro
A recorded or coded sequence of actions that runs with one command.
Goal Seek
A tool that changes one input cell until a formula cell reaches a target value.
Scenario Manager
A tool that stores named sets of inputs and compares their outputs.
Data table
A grid showing a formula's result for a range of values of one or two inputs.
Pivot table
An interactive summary of a list, grouping and totalling it without formulas.

Flashcards

DefineFormula
AnswerA calculation written by the user, starting with an equals sign.
DefineFunction
AnswerA built-in calculation, such as SUM, IF or XLOOKUP.
DefineNested IF
AnswerAn IF function placed inside another, to choose between more than two outcomes.
DefineLookup
AnswerA function that finds a value in a table, such as XLOOKUP or VLOOKUP.
DefineExact match
AnswerA lookup that returns a result only when the value is found exactly.
DefineApproximate match
AnswerA lookup that returns the largest value not greater than the one searched for, in a sorted table.
DefineData validation
AnswerRules restricting what can be entered into a cell.
DefineInput message
AnswerA note shown when a validated cell is selected, explaining what to enter.

Reach the top grade on this topic

  • Refer to named cells, never to typed numbers, so every formula reads as a sentence
  • Use the right tool for each job: a lookup for a table, IF for a decision, validation for an input
  • Validate every input, with input messages and a deliberate choice of error style
  • Protect the formulas and leave only the inputs editable
  • Use what-if tools to answer the client's question, and explain what each result means
  • Show a range of techniques used well, each one justified by a requirement

Mistakes that cost marks here

Avoid these
  • Typing values into formulas, so changing an assumption means hunting through every cell.
  • VLOOKUP without FALSE, which silently returns approximate matches.
  • Nested IFs that test the smallest threshold first, so the larger bands are never reached.
  • Protecting the sheet before unlocking the input cells, so nobody can enter anything.
  • Running Goal Seek once and reporting the answer without checking it in the model.
  • Charts with no axis titles or units.
  • Using IFERROR to hide errors that should have been fixed.
Fun fact

Excel stores dates as numbers counting days from 1 January 1900, and it treats 1900 as a leap year, which it was not. The error was copied on purpose from an older spreadsheet, Lotus 1-2-3, so that old files would still give the same answers.

Knowledge check

0 of 10 answered
1What does =VLOOKUP(B2,Tickets,2) do when the fourth argument is left out?
2Which formula gives 20% for 20 or more, 10% for 10 or more, otherwise 0%?
3What must you do before protecting a sheet, so users can still enter inputs?
4Which tool shows the profit for every price from £10 to £50 in one grid?
5Goal Seek finds £18.04 starting from £20, and £36.96 starting from £35. Why?
6Which validation error style refuses an invalid value outright?
7An Excel workbook containing macros must be saved as:
8What does IFERROR do?
9Why must a pivot table be refreshed?
10Which chart best shows profit against ticket price?
Score 0 / 10

Check you could explain this

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

Mark scheme0 / 4
  • LValidation restricts what can be entered, such as a range or a type
  • LSo invalid data is rejected before it reaches the calculations
  • LProtection locks formula cells so they cannot be overwritten
  • LTogether, 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.

The marker looks for key phrases. If you said it another way and it is still right, give yourself the mark.

Think this answer or mark scheme is wrong? Report it

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

Mark scheme0 / 4
  • LGoal Seek works backwards from a target output to an input
  • LGoal Seek changes only one input and finds one answer
  • LScenario Manager stores several named sets of input values
  • LScenario 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.

The marker looks for key phrases. If you said it another way and it is still right, give yourself the mark.

Think this answer or mark scheme is wrong? Report it

3A 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 marks
Explain

Mark scheme0 / 5
  • LThe fourth argument is missing, so VLOOKUP makes an approximate match
  • LApproximate matching needs a sorted table and returns the nearest lower value
  • LIt always returns something, so no error appears
  • LFix: add FALSE, or use XLOOKUP or INDEX with MATCH
  • LRetest 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.

The marker looks for key phrases. If you said it another way and it is still right, give yourself the mark.

Think this answer or mark scheme is wrong? Report it

Machine mark 0 / 13