How to Create a Break Even Graph in Excel

Written by stephanie ellen
• Share
• Tweet
• Share
• Email

A break even point represents the number of units you must sell to begin making a profit, given your fixed costs, cost per unit, and revenue per unit. For example, you might want to know the break even point for selling Mylar balloons. If you know the break even point, you'll know how many balloons you have to sell to make a profit. In order to graph a break even point using Excel 2007, you'll need to know your fixed costs (building, equipment maintenance etc.) and variable costs (electricity, wages, and other fluctuating costs). On a graph, the break even point is shown by the intersection between revenue and total cost.

Skill level:
Challenging

• Excel 2007

Instructions

1. 1

In cell A1, type "Fixed Cost," and in B1 enter the dollar amount of your fixed costs. For example, the supplier of mylar balloons requires that you pay £65 membership fee to be a buyer, and you are charged that amount no matter how many balloons you buy. In that case you would type "100" into B1.

2. 2

In cell A2, type "Cost per Unit," and in B2 enter the dollar amount of the cost per unit. For example, each balloon cost 60p. You would enter "1" into B2.

3. 3

In cell A3, type "Revenue per Unit," and in B3 enter the dollars amount of the revenue per unit. If you plan to sell your balloons at the county fair, and you know you can charge £3 per balloon, then enter "6" into B3.

4. 4

In cell A5, type "Units." In cell A6, enter the number 1. Under the number one (in cell A7) enter the number 2, and continuing entering numbers until you reach 25.

5. 5

In cell B6, type "Cost." In B7 type "=A7*\$B\$2+\$A\$2" without any quotes. This formula means "Multiply the number of units by the cost per unit, then add the fixed cost."

6. 6

Copy B7, and paste it into every cell in the Cost column. In our example, the first cell should read "101," and each cell should grow in value by 1, until the final value is "125."

7. 7

In cell C6, type "Revenue."In C7 type "=A7*\$C\$2" without any quotes. This formula means "Multiply the numbers of units by the revenue per unit."

8. 8

Copy C7, and paste it into every cell in the Revenue Column. In our example, the first cell should read "6," and each cell should grow in value by 6, until the value is "150."

9. 9

In cell D6, type "Profit". Profit is Revenue-Cost, so enter the formula "=C7-B7" in cell D7.

10. 10

Copy that cell, and paste it into every cell in the Profit column. In our example, the first cell should read "-95" or "(95)" (meaning negative 95). The final column should read "25."

11. 11

Highlight the area from A6 to D30 by holding down the left mouse key and mousing over the area.

12. 12

Click the Insert tab on the ribbon at the top of the Excel interface. Inside the "Charts" area on the Insert tab, you'll see a "Line" button.

13. 13

Click that button then choose "Stacked Line" from the sub menu. This will bring up a line chart. The break even point is the point on the chart where the profit graph crosses the cost graph.

Tips and warnings

• If you don't want the cost of each unit on your graph, highlight the line graph by clicking on it and press the delete key.

Don't Miss

References

• All types
• Articles
• Slideshows
• Videos
Sort:
• Most relevant
• Most popular
• Most recent