≡ Menu

Break Even Analysis in Excel

The break even point is whereby a business is in between profit and loss. It is the point at which the revenue accumulated from sales is equal to the expenses that were incurred in the process of production. It is the point where the cost curves and the revenue curves intersect. With Microsoft Office excel, you can use a spreadsheet to find the break even point for any production activity. The spreadsheet is preferred since it is easy to produce, has a high degree of accuracy and it can make a very good template for making a business presentation.

How to Calculate Break Even Analysis in Excel

Begin by opening a new workbook, making one of the worksheets in the workbook the expense sheet and another the break-even data sheet. In the expense sheet, create columns for expense description, cost type, fixed costs and other variable costs. Fill in each of the expenses giving brief descriptions of the corresponding costs. Be keen to separate the fixed costs and the variable costs. Next, make a rough estimation of the products to be sold per fixed cost. For every variable cost, fill in the amount that it will cost to make a single unit of the products. Once you are done, draw up the totals of the expenses.

1. Open the break even data worksheet. Make columns for the sales volume, a multiplier, unit-selling price, the number of units sold, fixed expenses, variable cost, the sum of fixed and variable costs and finally the revenue.

2. Fill in the expected number of sales in the first column, usually from the second row down to the last. In the second column, start with zero in ascending value of 0.1 until you reach 1.5.

3. In the third column, fill in the unit sales price.

4. In the second row of the third and the fourth columns, enter alternate selling prices for the products.

5. Fill in a formula in the second row sixth column, which should give you a product of the first, and the second columns. This column will enable you to get the range of sales that you can use to calculate the revenues and costs.

6. On the second row seventh column enter a formula to give you the total fixed costs calculated on the expenses tab.

7. Change the cell reference to absolute by pressing F4 and press ENTER.

8. Enter a formula in the next cell on the right to give you the total variable cost by the values in the sixth column. Here, you will change the reference to an absolute reference.

9. Calculate the totals for the ninth column by adding value in the seventh column to those in the eighth column.

10. Enter a formula in the tenth cell to get the income based from the data that you have from the third column. Have the cell be equal to the third column of the sixth column.

11. Enter formulas in the eleventh and twelfth columns to utilize the alternative price data. Set the eleventh column to be equal to be the product of fourth and sixth columns. The twelfth column should be equal to the fifth column multiplied by the sixth.

You will now be ready to complete the break even chart, depending on the version of Excel that you are using. The most common way is to select all columns from the fourth to the eighth and click “Insert”, go to charts then choose a line chart.

How Break Even Analysis Helps in Decision Making

When operating a business, you have to know at which point you will be breaking even, so that you can plan on how to boost sales or where you will need to cut down on costs. It is used in the measure profit and losses at varying sales and production levels. Therefore, before a company embarks on a certain decision, such as whether to increase the production capacity, they will have an informed decision.

It is also helps in tracing the effect of changes in the prices. This is used by the company in evaluating how much they will need to adjust the prices in case of any change in factors such as the price and availability of commercial goods in order to retain a stable profit margin or even to expand this margin accordingly.

It enables a company to predict the type of effect that change in the business structure will bring about, so that during the entire transition process, the managers of the company will know how to adjust to certain expected changes and how to ward off the unexpected changes. The data obtained can also be used to display the relationship between fixed cost and variable cost.

Advantages and Disadvantages of Using Break Even Analysis for a Business

The advantages of this analysis are very much similar to its uses.

Some of the disadvantages are:

It is based on the assumption that the sale prices will remain the same at all levels of output. There are certain discounts that the business might get, for instance when purchasing in bulk. These, combined with other economies of scale and dis-economies of scale.

It brings the assumption that production capacity will remain the same as sales. This could not be more wrong since during the production process, there are bound to be some defective products. In addition, during the transit to the trade area, some of the products might get lost or be damaged, and these will not have been catered for in the break even analysis.

The break-even charts are usually very time-consuming to prepare. Mostly, it will require that there be assistance of trained professionals if at all the information to be derived is to be of any use to the company. In the long run, it ends up consuming a lot of money to pay such employees. A slight error in the input of data might bring about very wrong figures.

It can only apply to a single range of products, each with its specific fixed and variable cost. However, in some production activities, there will be a mixed production, and the factors of production can be shared, hence one cannot ascertain the pure cost for each product.

{ 0 comments… add one }

Leave a Comment