Home > Excel
An Excel 2007 Tutorial:
Map Costs and Sales in Excel
With This Breakeven Chart
Here's how to set up a chart that illustrates
how your costs vary
with your monthly sales. To do so, you'll need to create Excel
names that probably are like none you've seen before.
by Charley Kyd
Revised December, 2008
(Follow this link for the Excel 97-2003
(See Control Your Cost Lag Loop To Help Survive the Recession for more information about breakeven charts.)
When I worked as a CFO, my monthly reports always included a
breakeven chart. It was one of the most popular reports that I produced.
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
buy 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, and costs that don't. These are called variable and
fixed costs, respectively.
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, total costs equal fixed costs plus variable costs.
The diagonal line that begins at zero is the Total Revenue Line.
Performance below that line means that sales exceed total costs, and are
The Breakeven Point is where the Total Revenue Line crosses the Total
Costs Line. That is, it's where total costs equal total revenue.
Finally, in the chart above, you'll see markers that represent recent
monthly performance. The one white marker shows performance for the
Set Up Your Data
table contains the data and calculations needed to support the breakeven
The yellow cells contain actual values entered each month from the
accounting system. The other cells with numbers contain formulas.
Rows 4 and 11 serve two functions. 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 of
rows for data to be charted.
Cell B4 always contains the value of zero. The other cells in
these two rows contain formulas, which I'll explain shortly.
Define the Range Names
After you set up the structure of this display, 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 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.
Second, define the following names, which define the X and Y values
for the linear regression that calculates 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.
For the X range, the OFFSET function defines the range as the Sales
range but without the shaded rows. That is, it defines the range of
actual sales and excludes the artificial values required by our chart.
The Y range uses the same logic with the 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, where A is the
Y intercept and B is the slope.
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.
Finally, define miscellaneous range names:
Enter the Formulas
Here are the key formulas in the Breakeven Data worksheet:
This cell finds the largest value from cell B4 to the cell immediately
above cell B11. But if you enter a simple reference to cell B10, you are
laying a trap for yourself.
You step into the trap if you insert a row immediately above row 11.
This is because the formula -- which now has shifted to cell B12 --
still refers to cell B10. Therefore, the formula will ignore any data
you enter into the new cell B11.
However, the INDIRECT statement in the formula above is hardcoded to
always refer to the cell immediately above the cell where it's used.
Total Costs are equal to Sales minus Profits. Copy this formula down the
column as needed.
Fixed Costs are the Y intercept, which we defined as the name A.
Copy this formula down the column as needed.
Column H contains our regression formula. At any value of sales, the
total costs calculated by the regression formula are equal to the fixed
costs (A) plus variable costs (B) multiplied by the current month's
sales. Copy this formula down the column as needed.
H1: =TEXT(MAX(Date),"mmmm, yyyy")
This formula returns the most recent date as the date of the report.
This value provides information only. It's not used in the report.
We calculate the Breakeven point here so that we can display it with the
Columns C and D contain #N/A values for all sales but for the current
month. The chart uses these columns of data to display a uniquely
formatted marker for the current month.
Create the Chart
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. (If you want, you can rename
your workbook later.)
Select the range G4:Gll, as shown in the table above; then create an XY (scatter) chart that displays that column of data. Use the the
default settings, expect do not set up a Legend for this chart. When you
press Finish, your chart will look nothing like the figure here.
Select the chart object and choose Chart, Source Data. In the Series
tab enter =BE.xls!Sales in the X Values edit box and =BE.xls!FixedCosts
in the Y Values edit box. Then click Finish.
Select this data series. Choose Format, Selected Data Series. In the
Patterns tab, choose Automatic for the Line setting and None for the
Marker setting. (Your line might seem to disappear behind a gridline,
but don't worry about it.)
In this web page, select the text for the second SERIES function
below, and then copy it. Select the Chart Area of the chart and then
paste the SERIES function formula into your formula bar. (Your formula
bar must be empty before you can paste a new SERIES formula to your
chart.) After you press
Enter, you should see a diagonal series of markers in your chart. Select
this series and change the markers to a line, as you did with the first
Set up the third SERIES formula similarly. As before, change the
markers to a line.
Enter the fourth SERIES formula. But this time, change the markers to
black squares as shown in the figure above.
Finally, enter the fifth SERIES formula. But change the marker to a
different color square with a different Foreground and Background. I
chose the orange colors that you see above.
Now you can clean up your chart:
- If it displays a legend, select it and delete it.
- If the Plot Area is shaded, select the Plot Area and choose
Format, Selected Plot Area, and then remove the shading.
- If you don't want gridlines, you can remove them.
- To enter the labels for the X and Y axes, select the Chart Area,
and then choose Chart, Chart Options. In the Titles tab enter the
values for the X and Y axes titles.
- To force the Fixed Cost line to extend to the far right edge of
your chart, first select the X axis in the chart. Choose Format,
Selected Axis. In the Scale tab enter 200 as the maximum value, if
you're using the sample data. (Remember to increase this value as
- To force the Total Revenue Line to be extend from corner to
corner, adjust the scale of the Y axis similarly. (Again, remember
to increase this value as needed.)
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:
E1: =TEXT(CurMo,"mmmm yyyy").
E2: ="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.
(A reminder: you can
buy the completed breakeven workbook and enter
your own data immediately.)