Figuring a Break-Even Point

Problem

You want to figure out a break-even point for an investment or business venture.

Solution

Set up a spreadsheet containing projected expenses and revenue over a period of time and compute cumulative expenses and revenue over that period. Compute the net revenue and find the point at which net revenue goes from negative to positive.

Discussion

Consider the data shown in Figure 14-4, which shows hypothetical expenses and revenue for a proposed business venture.

An initial, one-time cost of $97,000 is required in year 0, with operating expenses increasing linearly from $9,600 to $14,400 from year 1 through year 10. Revenue is expected to start flowing in year 1 and to increase annually until year 6, at which point it levels off.

Figure 14-4. Break-even example

The question is how long it will be before you break even. That is, how many years until your total revenue exceeds your total expenses?

Columns G and H compute cumulative expenses and revenue over the period in question. These are computed by summing all previous expenses and revenue for years prior to and including the current year.

The last column in the table computes the difference between cumulative revenue and cumulative expenses. The break-even point is found when this value changes from negative to positive. In this example, break-even occurs between years 5 and 6. A plot of the cumulative expenses and revenue illustrates the break-even point graphically; see Figure 14-5.

The point where these two curves cross is the break-even point. If you plot the net revenue values over time (the last column in the table shown in Figure 14-4), you'll see that the break-even point occurs when the net revenue curve crosses the horizontal axis. You can pinpoint the break-even point by interpolating the net revenue curve. Or you can fit a curve through it and use Solver or Goal Seek to find its root. See Chapter 9 to learn how.

Figure 14-5. Break-even plot