A break even chart is an essential financial analysis tool that helps businesses visualize their fixed and variable costs, and identify the point at which sales revenue equals total expenses. This point is known as the break-even point, and it is vital for understanding your business’s financial health and making informed decisions. In this article, we will show you how to create a break even chart in Excel step by step.

Step 1: Gather your data

To create a break even chart, you need to have information on your fixed costs, variable costs per unit, and selling price per unit. Make sure you have accurate and up-to-date data before proceeding.

Step 2: Set up your Excel worksheet

Open a new Excel workbook and set up your worksheet by creating the following column headers in row 1:

A1: Units Sold

B1: Fixed Costs

C1: Variable Costs

D1: Total Costs

E1: Revenue

F1: Profit/Loss

Step 3: Enter your fixed costs

Enter your fixed costs in cell B2. These are the ongoing expenses that don’t change based on production or sales volume (e.g., rent, salaries, insurance). You can enter a single amount or calculate a total from different expense components.

Step 4: Enter variable costs per unit and selling price per unit

Input your variable costs per unit (costs that change depending on the number of products produced) into cell C2. Then, enter the selling price per unit into cell E2.

Step 5: Create formulas for calculating expenses, revenue, and profit/loss

In cell D2, input this formula to calculate total costs:

=B2+C2*A2

In cell E2, input this formula to calculate revenue:

=A2*E2

For profit/loss calculation in cell F2, use this formula:

=E2-D2

Step 6: Fill in the Units Sold column

In cell A2, start with zero units sold. Then, enter increasing values in the column (e.g., 50, 100, 150) to represent different levels of sales. Repeat this process until you have a sufficient data range.

Step 7: Apply formulas to all rows

Click on cells D2, E2, and F2 to apply the formulas created in Step 5 down the column for each unit sold.

Step 8: Create a chart

Select all the data in columns A, D, and E, then go to the “Insert” tab and click on the “Line” chart icon. Choose the first line chart option – “Line Chart.”

Step 9: Identify your break-even point

The point where the total cost and revenue lines intersect is your break-even point. It represents the number of units you need to sell to cover all your expenses. You can find this point visually on the chart or by looking for the row where Profit/Loss is zero or close to it.

And that’s how you create a break even chart in Excel! It’s a valuable tool that can help guide business decisions and improve overall financial stability. Remember to keep your data accurate and up to date for the best results.