Using a spreadsheet to support modelling: entering formulae with relative and absolute cell references, filling down a recurrence, using built-in functions, and using goal seek to find an input for a target output.

What this dot point is asking

The SQA expects you to use a spreadsheet as a modelling tool. You enter formulae with cell references, distinguish relative from absolute references, fill a recurrence down a column, use built-in functions, and use goal seek to find an input that produces a target output. The question paper requires software output to be printed and submitted, so these skills are examined directly.

Formulae and cell references

Every spreadsheet calculation is a formula beginning with =. Instead of typing numbers, you refer to the cells that hold them, so the result updates automatically if the inputs change. For example =B2+C2 adds two cells and =B2*(1+0.03) grows a value by $3\%$.

The power of a spreadsheet comes from filling a formula down or across, which copies the calculation while adjusting the references. How a reference adjusts depends on its type.

You use a relative reference for a value that should track each row, such as the previous balance, and an absolute reference for a shared constant, such as an interest rate or a tax band held in one cell.

Built-in functions

Spreadsheets provide functions that calculate over a range of cells. The argument in brackets is usually a range written with a colon, such as B2:B11.

• =SUM(B2:B11) adds the values, useful for totals such as total income.
• =AVERAGE(B2:B11) gives the mean.
• =STDEV(B2:B11) gives the sample standard deviation, used in statistics work.
• =MAX(B2:B11) and =MIN(B2:B11) give the largest and smallest values.

Functions save manual arithmetic and reduce slips, and the SQA expects you to choose and apply the right one. In statistics tasks a function such as PEARSON returns a correlation coefficient directly from two ranges.

Goal seek

Goal seek answers the reverse question: instead of asking what output a given input produces, it finds the input that produces a chosen output. You tell it which cell to set, the target value, and which input cell to change; the spreadsheet then adjusts the input until the output hits the target.

This solves equations without rearranging them. To find break-even, set the profit cell to $0$ by changing the number-of-items cell. To find the deposit needed to reach a savings goal, set the final-balance cell to the goal by changing the deposit cell.

Presenting spreadsheet output

The question paper requires printed output, so format it clearly: label columns, show the formula bar where asked, and round displayed values sensibly while keeping full precision in the calculation. Clear formatting is part of communicating the model.

Try this

Q1. Cell B2 holds a price and cell B3 should show the price after a $20\%$ discount. Write the formula for B3. [1 mark]

• Cue. =B2*0.8 (or =B2*(1-0.2)), giving $80\%$ of the price.

Q2. A tax rate $0.21$ is held in cell G1. Write a formula in C2 for the tax on an amount in B2 that can be filled down the column. [2 marks]

• Cue. =B2*$G$1, with B2 relative and $G$1 absolute so every row uses the same rate.

Q3. Profit is modelled as =12*B1-900 in cell B2, where B1 is the number sold. Describe the goal seek settings to find break-even. [2 marks]

• Cue. Set cell B2 to value $0$ by changing cell B1; this returns $B1 = 75$.