How to Create a Break Even Chart in Excel

graph image by Attila Toro from Fotolia.com

A break even chart is a simple way to visually determine how many units of a product you'll need to sell in order not to lose any money, given the price of each unit to customers, the cost of each unit to you, and the fixed costs of production.

Excel not only allows you to create tables of data based on the starting inputs, but also can create the break even chart itself.

Type "Fixed Costs" into cell A1.

• A break even chart is a simple way to visually determine how many units of a product you'll need to sell in order not to lose any money, given the price of each unit to customers, the cost of each unit to you, and the fixed costs of production.
• Type "Fixed Costs" into cell A1.

Type "Unit Expense" into cell A2.

Type "Unit Revenue" into cell A3.

Type "Units" into cell C1.

Type "Revenue" into cell D1.

Type "Expense" into cell E1.

Input your fixed costs of a production into cell B1. For example, if rent for the factory is £16 per month, then type "25."

Input the cost of each unit you produce into cell B2. For example, if each widget costs you £3 to manufacture, type "5."

• Type "Expense" into cell E1.
• Input the cost of each unit you produce into cell B2.

Input the revenue you make for selling each unit into cell B3. For example, if each widget sells for £6, type "10."

Input the number of units sold in column C, under the words "Units." For example, you might write "1" in cell C2, "2" in cell C3, and so on until you write "10" in cell C11.

Input the following formula into cell D2: \=\$B\$3*C2

Copy the formula from the previous step by left clicking on D2 and pressing "Ctrl" and "C" at the same time.

Highlight cells D3 through D11 by clicking D3 and dragging down to D11.

Paste the formula by pressing "Ctrl" and "V" at the same time.

Input the following formula into cell E2: \=\$B\$1+\$B\$2*C2

Copy the formula from the previous step into the cells in column E by following the same steps as copying and pasting the previous formula.

Click "Insert" > "Scatter" > "Scatter with smooth lines and markers" to insert a blank chart into your spreadsheet.

Right click on the chart and click "Select Data" to display the Select Data Source dialogue.

Click "Add" under Legend Entries to display the Edit Series dialogue.

Type "Revenue" into the "Series Name" text box.

Click the "Series X Values" text box, then highlight cells D2 through D11, the entire Revenue column.

Click the "Series Y Values" text box, then highlight cells C2 through C11, the entire Units column.

• Copy the formula from the previous step into the cells in column E by following the same steps as copying and pasting the previous formula.
• Click the "Series Y Values" text box, then highlight cells C2 through C11, the entire Units column.
• Copy the formula from the previous step into the cells in column E by following the same steps as copying and pasting the previous formula.
• Click the "Series Y Values" text box, then highlight cells C2 through C11, the entire Units column.

Click "OK" to exit the Edit Series dialogue.

• Click "OK" to exit the Edit Series dialogue.

Click "Add" under Legend Entries to display the Edit Series dialogue.

Type "Expense" into the "Series Name" text box.

Click the "Series X Values" text box, then highlight cells E2 through E11, the entire Expense column.

Click the "Series Y Values" text box, then highlight cells C2 through C11, the entire Units column.

• Copy the formula from the previous step into the cells in column E by following the same steps as copying and pasting the previous formula.
• Click the "Series Y Values" text box, then highlight cells C2 through C11, the entire Units column.
• Copy the formula from the previous step into the cells in column E by following the same steps as copying and pasting the previous formula.
• Click the "Series Y Values" text box, then highlight cells C2 through C11, the entire Units column.

Click "OK" to exit the Edit Series dialogue.

• Click "OK" to exit the Edit Series dialogue.

Click "OK" to exit the Select Data Source dialogue.