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

In the second part of this tip, we create the chart, ie an XY graph.

The columns that I want displayed in the XY graph are, the “Quantity Sold” (1), “Total” (2), “Fixed costs” (3) and “Total cost” (4).
In this example, for clarity, I have given a light blue background to this data.

The advantage of a xy-graph is that, when the data from the x-axis in the leftmost column is fixed, which is true in this case, this is the “Quantity” column, you can add as many as you wish to the y-axis.
This can be done even though they are not properly placed next to each other.

The first thing we do is, select the columns that we want to include in the chart.
Select the titles of the columns to use these as labels for our chart.
To select a column, click and drag it (them).
Keep the Ctrl key on your keyboard pressed when they are not adjacent.
If you have you selected the columns you want, you now have this.

Then select the “Insert” (1) tab, click the “Charts” (2) button, select “Scatter” (3) and then select “Scatter with smooth lines” (4).

This puts our XY chart in the worksheet:

First make the legend a bit clearer for this graph.
The horizontal axis gives us the numbers 0 – 20 – 30 and so on, but this does not say a lot.
For this, we first add an axis title.
In order to do this, select the “Layout” (1) tab, click “Axis titles” (2) in the “Ribbon”, select “Title of primary horizontal axis” (3) and click “Title below Axis” (4).

This places a text box below the horizontal axis (1).
Now you can type the title for this axis, or you can use the formula bar, type the equals sign (=) and select cell A4 (2) in the worksheet.
This will automatically complete the formula (3) and the text in cell A4 is shown as axis title (1).

Changing your text in the column A4, will automatically change the axis title of the graph.
This is very handy.

Next click the legend on the right side of the graph, to move it to the top of graph.
To do this, I select the legend (1) and press the Ctrl +1 keys on the keyboard.
This opens the “Format Legend” dialog box where you select the position as “Top” (2).

The graph looks a little cleaner now, I think.

In the following section we add the Break Even point.

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