How To Do A Break Even Chart In Excel With Pictures

Select all of the data, including the header row and the blank amounts, by clicking and dragging your mouse over all of the cells. Click the “Format as Table” button. You’ll find this in the Home tab. If you’re using Excel for Mac, click the Tables tab, click the “New” button, and then select “Insert Table with Headers. " Check the “My table has headers” box. This will preserve the labels in the first row as header labels. Click the “Table Name” field in the upper-right corner and name it “VariableCosts. "

Click the “Format as Table” button in the Home tab. Check “My table has headers” to turn row 1 into headers for the table. Click the “Table Name” field and name the table “FixedCosts. "

A1: Sales - This is the label for the Sales section of the spreadsheet. B2: Price Per Unit - This will be the price you charge for each item you sell. B3: Units Sold - This will be the number of units you have sold at the specified price in a set timeframe. A4: Costs - This is the label for the Costs section of the spreadsheet. B5: Variable Costs - These are the costs of your product that you have control over (shipping, commission rates, etc. ) B6: Fixed Costs - These are the costs of your product that you don’t have control over (facility rent, insurance, etc. ) A7: Revenue - This is the amount of money selling your products generates before costs are considered. B8: Unit Margin - This is the amount of money you make per unit after costs are considered. B9: Gross Margin - This is the total amount of money you make for all the units sold after costs. A10: BEP - This is the label for the Break Even Point section of the spreadsheet. B11: Units - This is the number of units you need to sell to match your cost outlay.

Highlight C2, C5, C6, C8, and C9. Click the drop-down menu in the “Number” section of the Home tab and choose “Currency. " Highlight C3 and C11. Click the drop-down menu and select “More number formats. " Select “Number” and then set “Decimal places” to “0. "

Select B2:C3 and then click the “Formulas” tab. Click “Create from selection” and then click “OK. " Select B5:C6 and then click the “Formulas” tab. Click “Create from selection” and then click “OK. " Select B8:C9 and then click the “Formulas” tab. Click “Create from selection” and then click “OK. " Select B11:C11 and then click the “Formulas” tab. Click “Create from selection” and then click “OK. "

=SUM(VariableCosts)*Units_Sold

=SUM(FixedCosts)

=Price_Per_Unit-SUM(VariableCosts)

=Unit_Margin*Units_Sold

=IFERROR(Fixed_Costs/Unit_Margin,0)

Each cost in the VariableCosts table should be per unit sold.