For a matter of simplicity, we go back to step 4.1, the base case.

We calculated the NPV and IRR with revenues and costs, EBITDA and cash flows.

One fundamental assumption was the number of units we expected to sell to customers. It had an impact on revenues and variable costs, not on fixed costs whose definition is ‘don’t depend on sales’.

The NPV was positive, but, obviously, if you slightly reduce the volume, say from 15,000 units to 14,500 units, it’s still positive, but lower (same for IRR). It’s crucial to know under which volume the NPV turns negative, first, and then to have a discussion with Sales & Marketing asking the question: ‘are you sure we’ll generate volumes beyond that critical volume?’. It’s named ‘sensitivity analysis’ and we’ll work on it during step 5. But, so far, we want to calculate the volume which brings the NPV down to 0: it’s named breakeven.

How to do it? Let’s first do it ‘manually’, then we’ll move to the spreadsheet.

Remember the financials of the project: each unit sold generates a revenue, which is the unit selling price, but also generates a cost, which is the variable cost per unit. The difference between these two figures is named ‘unit contribution margin’ or ‘contribution margin per unit’. For example, if you sell for 100 a unit whose variable cost is 60 per unit, the unit contribution margin per unit is 100 – 60 = 40. Assume that your fixed costs are 4,000, you need to generate 100 times the unit contribution margin of 40 to exactly pay you fixed costs. Conclusion: with 100 units sold, revenues exactly match with (fixed + variable) costs, profit is 0 and breakeven is 100 units.

This is easy for one year (we’ll do it again later in the exercise), but more difficult for a project which lasts a few years.

Then, we open the spreadsheet and figure out how to make the calculation.

Work on the spreadsheet.

As is, the spreadsheet looks like the solution of the base case. Blue cells are your inputs.

First, as suggested above, reduce volume (cell D22) from 15,000 down to 14,500.

You observe that the NPV is down and still positive.

The NPV is down by about 100, but is still above 900. So, you conclude that you must reduce again by around 9 times the same volume reduction (15,000 – 14,500 = 500), so an additional 4,500 to be close to breakeven.

You enter 10,000 in D22 and get a negative NPV (well, it’s not ‘linear’…). So, you try again with a higher volume, etc. to get the exact figure.

That’s correct, but painful.

An easier way is to use the ‘goal seek’ function available in the spreadsheet. It’s a ‘tool’ which is named ‘goal seek’.

The Set cell is the formula to which you want to give a target, here the NPV cell: B51.

The Value cell is the target, here 0. Last, the Changing cell is the parameter which will be modified by the spreadsheet so that you get the expected Value in the target Set cell.