How to Do a Break Even Chart in Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

Break-even analysis determines the point at which total costs of production are equal to total revenues for a product or service. A break even computation can be simple or it can be complex. It all depends on the number and detail of the cost and revenue factors you wish to include. Excel can perform the calculations required to help you determine the sales quantity at which the revenues and costs are near equal and break even.

Advertisement

Step 1: Enter Your Data

Image Credit: Ron Price

Open a New Excel workbook and enter the data needed to perform a break-even analysis: Fixed Costs, Variable Costs and Sales Price. You may have other costs you wish to add, which is fine. Just keep in mind the type of each cost when it is included in a formula.

Advertisement

Video of the Day

Step 2: Project Cost and Revenue

Image Credit: Ron Price

In another area of the worksheet, enter numbers to represent different quantities sold for modeling purposes. For each quantity, compute the total cost of production and the total gross revenue. The formula for the total costs is =(QuantityVariable Cost) + Fixed Cost, or as an example: =A7$B$3+$B$2. The formula for the total revenue is =Quantity * Sale Price, or as an example: =A7*$B$4. Copy these two formulas and paste them in the cost and revenue cells for each quantity in the model or use the Fill handle on the lower right corner of a cell to drag the formula into the other cells.

Advertisement

Step 3: Highlight the Data

Image Credit: Ron Price

In the Economics world, the break-even point occurs when revenues equal costs. To begin creating a chart that shows lines for revenues and costs, highlight the three columns -- the quantity ("Units Sold"), cost ("Total Costs") and revenue ("Total Revenue") data generated in the previous step. To do this, click the mouse on the heading of the quantities (or "Units Sold" in the example) and while holding the left mouse button down, drag down to the revenue ("Total Revenue") of the last entry.

Advertisement

Advertisement

Step 4: Create A Break-even Chart

Image Credit: Ron Price

Select the Insert tab and click on the Scatter Charts icon in the Charts group. From the Scatter Chart gallery, choose one of the line charts: Scatter Chart with Straight Lines or Scatter Chart with Smooth Lines. Choose the "Marker" type charts to mark each of the data points.

Advertisement

Image Credit: Ron Price

The chart that displays shows graphically where revenues and costs are equal, which is the point at which breakeven occurs.

Video of the Day

Advertisement

Advertisement

references

Report an Issue

screenshot of the current page

Screenshot loading...