Home > Excel Solutions > 

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.

(Follow this link for the Excel 97-2003 version.)

(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.

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.

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:

B11: =MAX(B4:INDIRECT("R[-1]C",FALSE))
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.

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

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

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

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

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

H13:  =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())
D5:  =IF($A5=MAX(Date),$F5,NA())
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

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. (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 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!CurCosts,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 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 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.

(A reminder: you can buy the completed breakeven workbook and enter your own data immediately.)