How do i make a Break even analysis?

Emil_K wrote:

Thank you so much for your reply. I was wondering if it is possible to get the breakeven point without having to generate the graph. Either in a single cell or a data table?

A lot of the value of breakeven "analysis" comes from looking at the relationships in a graph form. Just solving for one number as an answer doesn't give much insight. However, you don't need a graph to get the result. Notice that you can look in column F of the 'Calculation Table' for the 0 to get your answer from column A. (Numbers doesn't have the equivalent of an Excel 'data table' but this table is pretty close.)

If you want just a single cell answer, though, you can do this (Wayne's algebra in less abstract form):

The formula for revenue R is:

R = P x Units

where P is unit price and Units is the number of units

The formula for total costs TC is:

TC = VC x Units + FC

where VC is variable costs and FC is fixed costs.

Setting R = TC (since you are looking for the breakeven point) you get

P x Units = VC x Units + FC

And manipulating that, you get

P x Units - VC x Units = FC

Or,

Units x (P - VC) = FC

Units = FC / (P - VC)

Applying this to the example, you would derive A2 in the 'Breakeven Units' table with:

=Inputs::B1÷(Inputs::B4−Inputs::B2)

SG

A breakeven analysis is often constructed something like this:

The formulas in the Calculation Table:

A2, filled down: =(ROW()−2)*Inputs::$B$3

B2, filled down: =A*Inputs::$B$4

C2, filled down: =Inputs::$B$1

D2, filled down: =A*Inputs::$B$2

E2, filled down: =C+D

F2, filled down: =B−E

The chart is a 2D Line Chart with data references as shown in the screenshot.

In this example the breakeven is at 400 units where the 'Revenue' line intersects the 'Total Cost' line and 0 appears in the 'Profit' column of the 'Calculation Table'.

SG

YES.

it is the intersection of two lines with different slopes and offsets

so... if you have one line with slope m1 and offset b1

and another line with slope m2 and offset b2

they intersect when the y values are the same using the same x value

y1 = m1*x + b1

y2 = m2*x + b2

y1(x) = y2(x)

m1*x + b1 = m2*x + b2

rearranging to get x on one side you get:

m1*x = m2*x + b2 - b1

x = (m2*x + b2 - b1) / m1