Home >  Excel Solutions > 

An Excel 2003 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.

(Follow this link for the Excel 2007 version.)

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.

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 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 therefore profitable.

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 most-recent month.

Set Up Your Data

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

Columns B and D contain actual values entered each month from the accounting system. Column A contains the date value for each row of data. The other columns contain calculations.

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. Cell B11 contains a value equal to the maximum value for sales in your chart. 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:

Second, define the following names, which define the X and Y values for the linear regression that calculates the Total Cost Line.

By the way, 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.

To define these names, define them as you would any other name. Choose Insert Name Define; enter the name in the Names In Workbook textbox; and then enter the formula in the Refers To textbox.

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.

By the way, 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 ranges.

Finally, define miscellaneous range names:

Enter the Formulas

Here are the key formulas in the Breakeven Data worksheet:

E5:  =$B5-$D5
Total Costs are equal to Sales minus Profits. Copy this formula down the column as needed.

F4:  =A
Fixed Costs are the Y intercept, which we defined as the name A.

F5:  =F4
Copy this formula down the column as needed.

G4:  =A+B*B4
Column G 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.

G1:  =TEXT(MAX(Date),"mmmm, yyyy")
This formula returns the most recent date as the date of the report.

G12:  =B
This value provides information only. It's not used in the report.

G13:  =A/(1-B)
We calculate the Breakeven point here so that we can display it with the chart.

C5:  =IF($A5=MAX(Date),$B5,NA())
Column C is intended to contain #N/A values for all sales but for the current month. This formula produces that result.

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.xls. (If you want, you can rename your workbook later.)

Select the range F4:Fll, 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 SERIES function.

Set up the third SERIES formula similarly. As before, change the markers to a line.

=SERIES(,BE.xls!Sales,BE.xls!FixedCosts,1)
=SERIES(,BE.xls!Sales,BE.xls!RegrCosts,2)
=SERIES(,BE.xls!Sales,BE.xls!Sales,3)
=SERIES(,BE.xls!Sales,BE.xls!TotCosts,4)
=SERIES(,BE.xls!CurSales,BE.xls!TotCosts,5)

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 white square with a black Foreground and a white Background. This creates the white marker with a black line around it.

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 needed.)
  
• 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.