Determination of the break-even point in Microsoft Excel

One of the basic economic and financial calculations of the activity of any enterprise is to determine its break-even point. This indicator indicates at what volume of production the organization's activities will be profitable and it will not suffer losses. Excel provides users with tools that make it much easier to determine this indicator and display the result graphically. Let's find out how to use them when finding the break-even point using a specific example.

The essence of the break-even point is to find the value of the volume of production at which the amount of profit (loss) will be equal to zero. That is, with an increase in output, the enterprise will begin to show profitability, and with a decrease, unprofitability.

When calculating the break-even point, you need to understand that all the costs of the enterprise can conditionally be divided into fixed and variable. The first group does not depend on the volume of production and is unchanged. This can include the amount of salaries for administrative staff, the cost of renting premises, depreciation of fixed assets, etc. But variable costs are directly dependent on the volume of production. This, first of all, should include the cost of purchasing raw materials and energy, so this type of cost is usually indicated per unit of output.

It is with the ratio of fixed and variable costs that the concept of the break-even point is associated. Until a certain volume of production is reached, fixed costs make up a significant amount in the total cost of production, but with an increase in volume, their share falls, which means that the cost per unit of goods produced also falls. At the break-even point level, the costs of production and income from the sale of goods or services are equal. With a further increase in production, the company begins to make a profit. That is why it is so important to determine the production volumes at which the break-even point is reached.

Break even point calculation

We calculate this indicator using the tools of the Excel program, and also build a graph on which we mark the break-even point. For calculations, we will use a table that contains the following initial data on the activities of the enterprise:

fixed costs;
Variable costs per unit of output;
The selling price of a unit of production.

So, let's calculate the data based on the values indicated in the table in the image below.

Creating a Graph

After a table has been created in which the break-even point is calculated, you can create a chart where this pattern will be displayed visually. To do this, we will have to build a diagram with two lines that reflect the costs and revenues of the enterprise. At the intersection of these two lines, there will be a break-even point. Axis X of this chart will be the number of units of goods, and along the axis Y sums of money.

Go to tab "Insert". Click on the icon "Point", which is placed on the ribbon in the toolbox "Diagrams". We have a choice of several types of charts. To solve our problem, the type "Spotting with Smooth Curves and Markers", so we click on this list item. Although, if desired, you can use some other types of charts.

Before us opens an empty area of the chart. It should be filled with data. To do this, right-click on the area. In the activated menu, select the position "Select data...".

The data source selection window is launched. On the left side there is a block "Elements of the Legend (Rows)". Click on the button "Add", which is located in the specified block.

Before us opens a window called "Row Change". In it, we must specify the coordinates of the data placement, on the basis of which one of the graphs will be built. First, let's build a graph that would display the total costs. Therefore, in the field "Row name" enter an entry from the keyboard "Total costs".
In field "X Values" specify the coordinates of the data located in the column "Number of goods". To do this, place the cursor in this field, and then, holding the left mouse button, select the corresponding table column on the sheet. As you can see, after the specified actions, its coordinates will be displayed in the window for changing the series.

In the next field "Y Values" should display column address "Total cost", which contains the data we need. We act according to the above algorithm: we put the cursor in the field and select the cells of the column we need with the left mouse button pressed. The data will be displayed in the field.

After these manipulations have been carried out, click on the button OK located at the bottom of the window.

After that, you automatically return to the data source selection window. You also need to click on the button OK.

As you can see, after this, a graph of the total costs of the enterprise will be displayed on the sheet.

Now we have to build a line of the total income of the enterprise. For these purposes, we right-click on the area of the diagram, on which the line of the organization's total costs is already placed. Select a position from the context menu "Select data...".

The data source selection window is launched again, in which you again need to click on the button "Add".

A small window for changing the row opens. In field "Row name" this time we write "Total Income".
In field "X Values" column coordinates should be entered "Number of goods". We do this in the same way that we considered when constructing the line of total costs.

In field "Y Values", in the same way we indicate the coordinates of the column "Total Income".

After completing these steps, click on the button OK.

Close the data source selection window by clicking the button OK.

After that, the line of total income will be displayed on the sheet plane. It is the point of intersection of the lines of total income and total costs that will be the break-even point.

Thus, we have achieved the goals of creating this chart.

As you can see, finding the break-even point is based on determining the value of the volume of output, at which total costs will be equal to total income. Graphically, this is reflected in the construction of cost and income lines, and in finding the point of their intersection, which will be the break-even point. Carrying out such calculations is basic in organizing and planning the activities of any enterprise.