Profit & Loss with a Spreadsheet
Use a spreadsheet to track profit and loss for NSW Year 12 Mathematics Standard 1 (NESA outcome MST-12-S1-01). Each row shows a number of units sold with its cost, revenue and profit, where \(\text{profit} = \text{revenue} - \text{cost}\), so you can see at a glance when a business is losing or making money.
You will complete a profit-and-loss spreadsheet, recognise a negative profit as a loss, pick out the break-even point as the row where profit is zero, and extend the pattern to make predictions — just as a cafe, sports club or market stall plans its prices.
Theory
A spreadsheet lists the cost, revenue and profit of a business for each number of units sold. This NSW Year 12 Mathematics Standard 1 guide shows how profit \(=\) revenue \(-\) cost reveals losses, the break-even row and profits, and how to fill gaps and predict.
A spreadsheet can model a business with one row for each number of units sold. Columns hold the cost, the revenue (income) and the profit, worked out by a formula that is filled down.
Profit \(=\) revenue \(-\) cost. A negative profit is a loss. The break-even row is where profit \(=0\), so revenue \(=\) cost.
Equal steps in units give equal steps in profit, so the pattern can be continued to predict. With rules, revenue \(=\) price \(\times n\) and cost \(=\) fixed \(+\) cost per unit \(\times n\).
On a graph, break-even is where the cost and revenue lines cross. Read the units straight down from that point and the dollars straight across.
The profit in each row:
Rearranged to find a missing entry:
In the spreadsheet the profit formula in cell D2 is =C2-B2, filled down the column.
How to use a profit spreadsheet
- Subtract in each row: profit \(=\) revenue \(-\) cost. A negative answer is a loss.
- Find the break-even row, where profit \(=0\) (revenue equals cost).
- Rearrange to fill a gap: cost \(=\) revenue \(-\) profit, revenue \(=\) cost \(+\) profit.
- Predict by continuing the equal steps, or set profit equal to a target and solve.
| Units | Cost (\(\$\)) | Revenue (\(\$\)) | Profit (\(\$\)) |
|---|---|---|---|
| 20 | 180 | 150 | ? |
| 30 | 220 | 225 | ? |
Profit \(=\) Revenue \(-\) Cost.
| \(20 \text{ units: } 150-180\) | \(=\) | \(-30\) |
| \(30 \text{ units: } 225-220\) | \(=\) | \(5\) |
20 units: a loss of \(\$30\); 30 units: a profit of \(\$5\).
(b) Cost \(\$270\), profit \(-\$15\). Find the revenue.
Rearrange Profit \(=\) Revenue \(-\) Cost.
| \(\text{(a) Cost}\) | \(=\) | \(\text{Revenue}-\text{Profit}\) |
| \(\) | \(=\) | \(210-35=175\) |
| \(\text{(b) Revenue}\) | \(=\) | \(\text{Cost}+\text{Profit}\) |
| \(\) | \(=\) | \(270+(-15)=255\) |
Check: \(210-175=35\ \checkmark\); \(255-270=-15\ \checkmark\). (a) Cost \(\$175\); (b) revenue \(\$255\).
| Units | 10 | 20 | 30 |
|---|---|---|---|
| Profit (\(\$\)) | \(-60\) | \(-20\) | 20 |
Each 10 units adds the same amount.
| \(-20-(-60)\) | \(=\) | \(40\) |
| \(20-(-20)\) | \(=\) | \(40\) |
| \(40 \text{ units: } 20+40\) | \(=\) | \(60\) |
| \(50 \text{ units: } 60+40\) | \(=\) | \(100\) |
Profit goes from \(-20\) to 20 between 20 and 30 units, so it is 0 halfway. Profit \(\$100\) at 50 units; break-even at about 25 units.
Let \(n\) be the number of meals. Set the profit equal to 360.
| \(\text{Profit}\) | \(=\) | \(12n-(200+4n)\) |
| \(12n-200-4n\) | \(=\) | \(360\) |
| \(8n-200\) | \(=\) | \(360\) |
| \(8n\) | \(=\) | \(560\) |
| \(n\) | \(=\) | \(70\) |
Check: revenue \(=12(70)=\$840\), cost \(=200+4(70)=\$480\), profit \(=840-480=\$360\ \checkmark\). It must sell 70 meals (break-even: \(12n=200+4n\Rightarrow 8n=200\Rightarrow n=25\) meals).
Common pitfalls
Frequently asked questions
How do you calculate profit in a spreadsheet?
Put a formula in the profit column that subtracts cost from revenue, such as =C2-B2, and fill it down. Each row then shows revenue minus cost for that number of units.
What does a negative profit mean?
A negative profit is a loss: the cost is more than the revenue. For example, revenue of 70 dollars and cost of 85 dollars gives a profit of negative 15 dollars, a loss of 15 dollars.
How do you find the break-even point in a spreadsheet?
Look for the row where the profit is 0, which is where revenue equals cost. If no row is exactly 0, the break-even is between the last loss row and the first profit row.
How do you find the cost if you know revenue and profit?
Rearrange profit equals revenue minus cost to get cost equals revenue minus profit. For example, revenue 210 dollars and profit 35 dollars gives a cost of 175 dollars.
How many items must be sold to make a target profit?
Write profit as revenue minus cost using the number of items, set it equal to the target and solve. For a price of 12 dollars, cost of 4 dollars each and fixed costs of 200 dollars, a 360 dollar profit needs 70 items.