Map Costs and Sales in Excel with a Classic Breakeven Chart

When I worked as a CFO, my monthly reports always included a breakeven chart. It was one of the most popular reports I produced.

But during many years of consulting I’ve never seen a breakeven chart created by others. I’m sure that other companies use them, but they don’t seem to be common.

The reason they aren’t common, I think, is that they take some work and thought to create. In the press of daily business there’s seldom time to create them.

So I thought I’d explain how to create a breakeven chart.

A Brief Introduction

As you probably learned in school, businesses have costs that tend to vary with sales (variable costs), and costs that don’t (fixed costs).

In the chart above, the fixed costs of 40 are represented by the horizontal Fixed Costs Line.

The diagonal line that begins at 40 is the Total Costs Line. At any point on this line, it represents the sum of both Fixed Costs and Variable Costs.

The diagonal line that begins at zero is the Breakeven Line, where costs and revenues are equal. When performance—represented by a dot—is below that line it means that costs are less than revenues, and the performance is profitable.

The Breakeven Point is where the Total Costs Line crosses the Breakeven Line. That is, it represents the amount of sales where total costs equal total revenue.

Finally, in the chart above (and below), you’ll see black markers that represent recent monthly performance. The one orange marker shows performance for the most-recent month.

Set Up Your Data

This table contains the data and calculations needed to support the breakeven chart.

The yellow cells contain actual values entered each month from the accounting system. Most of the other cells with numbers or #N/A contain formulas.

Rows 4 and 11 serve two purposes. First, as indicated by the gray shading, they serve as border rows for several range names that I explain below. These rows also serve as the beginning and ending rows for data to be charted.

Cell B4 always contains the value of zero. The other cells in the two gray rows contain formulas, which I’ll explain shortly.

Define the Range Names

To begin, set up the general structure of the table,  entering the values you see in yellow. Then you need to define your range names.

First, define these names, which are anchored in the gray rows:

To do so, first choose Formulas, Defined Names, Name Manager (or choose Ctrl+F3.) In the Name Manager dialog, choose New. Type in the name and the Refers To as shown. (You also can copy the text for each definition from the table in this web page.) Maintain the Workbook scope. Click OK. Then repeat the process for each of the other names. When you’re done, click the Name Manager’s close button.

Notice that I started most of these names with the characters “g.”. I did this for two reasons. First, a bug in Excel 2010 won’t let you assign a range name that begins with “R” or “C” to a SERIES formula. So starting names with “g.”—for “graph”—means I never need to work around this problem.

Second, since I started to use this convention, I’ve found it very useful to have all names that my charts reference have the same prefix. (That’s why I added the period after “g”, by the way. Doing so differentiates a name that normally begins with “g” from the “g” prefix associated with a chart. And Excel treats a period like any other character when the period is included in the name of a range.)

Your next step is to define the following names, which define the X and Y values for the linear regression that calculates the slope and intercept of the Total Cost Line.

These definitions will look very strange if you’ve never defined names to contain formulas. However, Excel has had the ability to define named formulas from the time the program was first introduced.

These are called dynamic range names. They rely on the OFFSET function to adjust automatically when you add rows to or delete rows from the table.

For the X range, the OFFSET function defines the range as the g.Sales range but without the shaded rows. That is, it defines the range of actual sales and excludes the artificial values in the two gray rows, values that are required by our chart. The Y range uses the same logic with the g.TotCosts range. We use a formula to define these ranges because they automatically adapt as you insert new rows to add new months of data.

Third, define the results of the regression equation…

y = mx + b

…where the Excel name M contains the slope and the name B contains the Y intercept.

These two definitions illustrate another capability of Excel names. Although the X and Y names were defined by formula, they did refer to a range in your spreadsheet. Here, however, the A and B names return values, not cell references.

The LINEST function uses the “least squares” method to calculate a straight line that best fits your data and returns an array that describes the line. Here, we use its simplest form, which returns an array with only two values: the slope (M) and the Y intercept (B). Then we use the INDEX function to return the two values for the two results.

Finally, define two miscellaneous range names:

Enter the Formulas

Here are the key formulas in the Breakeven Data worksheet:

C5:  =IF($A5=MAX(Date),$B5,NA())
D5:  =IF($A5=MAX(Date),$F5,NA())

Columns C and D use the MAX function to calculate the most-recent date of our data. These formulas return #N/A values for all sales except for the current month. The chart uses these columns of data to display the orange marker for the current month, shown in the chart above and below.

E5:  =IF($A5<MAX(Date),$G5,NA())

Column E contains the cost data for all rows but the row of data, which contains the current period. These values position the black markers shown in the chart.

G5:  =$B5-$F5
Total Costs are equal to Sales minus Profits.

In the table, notice that Sales is X, the independent variable. And Costs is Y, the dependent variable. That is, as the amount of Sales vary, our Costs thend to vary in response.

H4:  =B
Fixed Costs are calculated by finding the Y intercept of the regression equation, which uses the LINEST formula that we defined as the name B.

H5:  =H4
We repeat the same value for B down this column, which allows the chart to plot it as a straight horizontal line.

I4:  =M*B4+B
I5:  =M*B5+B
Column I contains the results of our regression formula in the form: y = mx + b, where column B contains our X values. In other words, at any value of sales, the total costs calculated by the regression formula are equal to the variable-cost ratio (M) multiplied by the current month’s sales, plus the fixed costs (B).

Copy the formulas in row 5 down the column as needed to the gray row. In columns H and I also copy the formula into the gray area.

I1:  =TEXT(MAX(Date),”mmmm, yyyy”)
This formula relies on the TEXT function to return the most recent date as formatted text to be used as the date of the report.

I12:  =M
This value provides information only. It’s not used in the report.

I13:  =B/(1-M)
We calculate the Breakeven point here so that we can display it as text in the chart figure.

Create the Chart

This figure shows the breakeven chart as it will appear in the second worksheet in your workbook.

It will be easier to create this chart if you enter the SERIES formulas manually, using the formulas shown below.

To begin, save your workbook as BE.xlsx. (You can rename your workbook later, if you want.)

Select the range H4:Hll, as shown in the table above; then create an XY (scatter) chart that displays that column of data. When you press Finish, your chart will look nothing like the figure here.

Copy the formula for Series 1 below, then select the horizontal line in your chart. Select the formula in your formula bar and replace it by pasting the new formula.

=SERIES(,BE.xlsx!g.Sales,BE.xlsx!g.FixedCosts,1)
=SERIES(,BE.xlsx!g.Sales,BE.xlsx!g.RegrCosts,2)
=SERIES(,BE.xlsx!g.Sales,BE.xlsx!g.Sales,3)
=SERIES(,BE.xlsx!g.Sales,BE.xlsx!g.PastCosts,4)
=SERIES(,BE.xlsx!g.CurSales,BE.xlsx!g.CurCosts,5)

Copy the formula for Series 2, then select the chart object, making sure that no SERIES formula is visible in your formula bar. Then paste the Series 2 formula into your formula bar. Repeat this process for the remaining three series.

Series 4 and 5 need to display markers, not lines. So first select Series 4. (To do so, select any line in the chart then press your up or down arrow keys until Series 4 is displayed in your formula bar.)

With Series 4 selected, press Ctrl+1 to launch its Format Data Series dialog. In the Marker Options tab, choose Built In and choose the circular marker and format it as you want. (In the chart above, I filled the marker with black.) Then in the Line Color tab, choose No Line.

Take the same approach with Series 5, but format the Marker Fill to a different color. In the figure above, I filled the marker with orange.

Now you can clean up your chart:

• If it displays a legend, select and delete it.
• If you don’t want gridlines, you can remove them. At the minimum, assign a gray pattern to them so that they fade into the background, as shown in the chart.
• To force the Fixed Cost line to extend to the far right edge of your chart, first select the X axis in the chart. Press Ctrl+1 to launch the Format Axis dialog. In the Scale tab enter at least 200 as the maximum value, if you’re using the sample data. Then make sure to enter the same value in cell B11, as shown in the table above. (Any value above 200 will work for the sample data.)

Finish the Display

To complete the display, first copy the chart and paste it to a new sheet in the same workbook.

As shown in the figure above, enter the title text in cell A1.

Enter the formulas for the cells shown here:
H2:  =TEXT(CurMo,”mmmm, yyyy”)
H3:  =”Breakeven Sales: “&TEXT(Breakeven,”$#,##0″)&” K ”

Right-align these formulas and format the cells as as you want.

Finally, make any other formatting adjustments you think are needed to complete your Breakeven report.