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

In the third part of this tip, we are going add a Break Even point on the chart.
A Break Even point has only one x value and y value, so we must first determine these in the second table of our worksheet.
We determine the break even point and then we add this point to the existing graph that spans multiple x values and y values that are available.

First, we determine the x value of the Break Even point.

To do this, we type the formula = ROUND (D2 / (B2-C2), 0) in cell B16.
Let me explain this.
The ROUND function is used here, for the reason that we do not want the decimal value. That’s all.
Then we have D2 / (B2-C2). Which is the fixed cost (D2) divided by the difference of the unit price with the variable cost (B2-C2).
The 0 (zero) at the end of the formula, determines the number of decimal places. I do not want them, so zero is fine.

When we click the Enter key on the keyboard, we see that everything is right (1).
When 10 units were sold, the net profit is 0, so we have a Break Even point.

Then we go into the determination of Y value of the Break Even point.
This is the point where the “Total cost” is equal to the “Total profit”.

For this purpose we type following formula:

=B16* B2

Or in human terms, the Break Even number, which is found in cell B16, times the unit price, which is found in cell B2.

When we press the Enter key on the keyboard, we see that everything is right (2).

And finally, we label this Break Even point.
We can then use it later, in our graph.
We can also label text in a formula.
The text is placed between double quotes.

= “BE number =” & B16

Please let me explain.

We start the formula with the equal sign (=).
We then open the quotation marks (“). Text in a formula must be between double quotes.
We type the text that we wish to use in the legend of the graph and put this text within quotes (“).
I chose “BE number =”. BE is the acronym for Break Even.
Of course, you can type the text you wish, here.
Only note, that it is not too long to be used as the label in the graph.

Then we type the ‘&‘ Sign and the cell reference (B16).
To convert text to merge it with the formula, we use the ampersand sign.

Press the Enter key on your keyboard and the label will be filled in(3).

Ok, now we have everything we need to add a Break Even point to our existing XY graph.
First select the chart.
Then select the “Design” (1) tab in the “Ribbon” and click the “Select Data” (2) button.
In the dialog box that appears, click the “Add” button (3).

We continue in the last section.

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