Analytical Charts
Map Costs and Sales in Excel with
a Classic Breakeven Chart
Here's how to set up a breakeven chart in Excel, one that illustrates how your costs vary with your monthly sales.
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.
(Note: This chart will take some work to create. To save
yourself that time and effort, you can
get the completed workbook and enter
your own data immediately.)
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
mostrecent 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:
Date 
=Regr!$A$4:$A$11 
g.Sales 
=Regr!$B$4:$B$11 
g.CurSales 
=Regr!$C$4:$C$11 
g.CurCosts 
=Regr!$D$4:$D$11 
g.PastCosts 
=Regr!$E$4:$E$11 
g.TotCosts 
=Regr!$G$4:$G$11 
g.FixedCosts 
=Regr!$H$4:$H$11 
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.
X 
=OFFSET(g.Sales,1,0,ROWS(g.Sales)2,1) 
Y 
=OFFSET(g.TotCosts,1,0,ROWS(g.TotCosts)2,1) 
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.
M 
=INDEX(LINEST(Y,X),1) 
B 
=INDEX(LINEST(Y,X),2) 
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:
CurMo 
=Regr!$I$1 
Breakeven 
=Regr!$I$13 
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 mostrecent 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 variablecost 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/(1M)
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 "
Rightalign 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.
(A reminder: You can
get the completed breakeven workbook and enter
your own data immediately.)
