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. To graph a break-even point using Excel 2007, you'll need to know your fixed costs (building, equipment maintenance, and so forth) 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.
Step 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 $100 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.
Video of the Day
Step 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 $1. You would enter "1" into B2.
Step 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 $6 per balloon, then enter "6" into B3.
Step 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.
Step 5
In cell B5, type "Cost." In B6 type "=A6*$B$2+$B$1" without any quotes. This formula means "Multiply the number of units by the cost per unit, then add the fixed cost."
Step 6
Copy B6 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."
Step 7
In cell C5, type "Revenue." In C6 type "=A6*$B$3" (without any quotes). This formula means "Multiply the numbers of units by the revenue per unit."
Step 8
Copy C6 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."
Step 9
In cell D5, type "Profit". Profit is Revenue-Cost, so enter the formula "=C6-B6" in cell D6.
Step 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."
Step 11
Highlight the area from A5 to D30 by holding down the left mouse key and mousing over the area.
Step 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.
Step 13
Click that button then choose "Stacked Line" from the sub menu to insert the chart. The break-even point is the point on the chart where the profit line crosses the cost line.
Video of the Day