Break Even Analysis (table + chart + break-even point) – Part 1

In this tip, I will show you how to create a Break Even Analysis in Excel.

We use a Break Even Analysis to determine the minimum turnover, to achieve, for example, an investment that produces returns. For this purpose, an investment is divided into fixed and variable costs or, in other words, sales and revenue-independent-dependent costs.
Examples of fixed costs include the rent of a building and depreciation.
Examples of variable costs include the purchase price of the product and wages.

I will show you how to create a table for this analysis, along with a chart and display the Break Even point in the chart.

Let me start by creating the table.
We begin by calculating the total number of goods sold and the total and variable costs.
Since I’m copying this formula down to the right, I make the first cell reference, semi-absolute, whose column is absolute and row is relative.
So I type $ A5. When copying the column A will always remain the same because it is absolute and row 5 will be adjusted since it is relative.

We multiply this by the unit price in cell B2.
Here, it is just the opposite, i.e, the column is relative and row is absolute.
So I type B $ 2. When copying to the right, the column will have to be adapted to the variable costs column, i.e, column C but, the row remains the same, i.e, row 2.

Then we copy this formula, first down, then right.
Double-click the fill handle of the cell and this formula will be copied to the underlying cells (1).
Click and drag the fill handle to the bottom of the cell you have just copied and, the formula will be copied to the right column (2).

As you can see, in the image below, all formulas in both columns are neatly adjusted to the cells from where they their information.
But this is all just basic information.
The only thing we have done here is describing a quick way to copy formulas over several rows and columns.

Feel free to copy the formula using whichever way you prefer.
Please note that only the necessary cells have the proper formula.

What do we have now?
We have the column with the total sales made so far(a) and a column with the variable cost (b).
The next thing we’re going to calculate the is the “Marginal Cost” (1).
What is “Marginal Cost”?
The “Marginal Cost” is the Total Cost (a) minus the variable cost (b).
Or, in other words, the “Marginal Cost” shows us how much we have to pay towards our fixed costs.

This is easy, because that’s cell B5-C5.
Also, we copy this formula down by double-clicking the fill handle.

The next column is the “Fixed costs” column.
Whether you’ve sold something or not, the fixed costs remain the same.
The fixed costs are in cell D2 so, we type the following formula in cell E5, = D2 and make this cell reference absolute, i.e, = $ D $ 2 (1). We are going to copy this to the underlying cells (2).

The next column is the “Total cost” column.
These are the sum of the variable costs and fixed costs, or, to express this in a formula, = C5 + E5.
We copy this formula also to the underlying cells.

And, in last column we have the “Net profit” column.
The net profit is the Total (sales)- the total cost, or, to express this in a formula = B5-F5.
We copy this formula also to the underlying cells.

In the following section we will create the chart.

Part 1 – Part 2 – Part 3 – Part 4

Copyright © 2013 – Swotster Ltd.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats