How to Create a Break Even Chart in Excel

Written by l.p. klages
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Create a Break Even Chart in Excel
Graphing in excel is a powerful way to visualise your data (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.

Skill level:
Easy

Other People Are Reading

Instructions

  1. 1

    Type "Fixed Costs" into cell A1.

  2. 2

    Type "Unit Expense" into cell A2.

  3. 3

    Type "Unit Revenue" into cell A3.

  4. 4

    Type "Units" into cell C1.

  5. 5

    Type "Revenue" into cell D1.

  6. 6

    Type "Expense" into cell E1.

  1. 1

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

  2. 2

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

  3. 3

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

  4. 4

    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.

  5. 5

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

  6. 6

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

  7. 7

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

  8. 8

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

  9. 9

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

  10. 10

    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.

  1. 1

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

  2. 2

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

  3. 3

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

  4. 4

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

  5. 5

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

  6. 6

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

  7. 7

    Click "OK" to exit the Edit Series dialogue.

  8. 8

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

  9. 9

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

  10. 10

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

  11. 11

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

  12. 12

    Click "OK" to exit the Edit Series dialogue.

  13. 13

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

Tips and warnings

  • You can update the break even chart in real time by changing the data in the columns.

Don't Miss

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

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.