How to Create a Break Even Graph in Excel

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.
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.
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.
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. 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. 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. 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. 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. In cell D6, type 'Profit'. Profit is Revenue-Cost, so enter the formula '=C7-B7' in cell D7.
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. Highlight the area from A6 to D30 by holding down the left mouse key and mousing over the area.
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. 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.