Home » Budget Spreadsheet » Break Even Analysis Spreadsheet

Break Even Analysis Spreadsheet

Break Even Analysis Spreadsheet – How many units do I have to produce to get a specific profit? From how many sales my company is profitable? Am I able to produce the number of units that generate profits and not losses? … questions like these are extremely frequent in the productive and financial environment in general. The most likely thing that happens to a new entrepreneur when trying to sell their business idea is that they ask the following question:
What is your Point of Balance?
The importance of knowing your balance point is what you permit with great ease. Answer the questions posed above, ie, let me know how many units I must produce to generate a desired utility, from how many sales my organization is profitable and many other unknowns of great value in the economic environment.

WHAT IS THE EQUILIBRIUM POINT?

The Point of Equilibrium is that point of activity in which total revenues are exactly equivalent to the total costs associated with the sale or creation of a product. That is, it is that point of activity in which there is no utility, no loss.
VARIABLES ASSOCIATED WITH THE EQUILIBRIUM POINT
According to its definition, the break-even analysis studies the relationship between fixed costs and expenses, variable costs and expenses, sales volume and operating profits. By effort it is imperative to know precisely the nature and behavior of the costs associated with the productive and / or financial process, according to the case of the sea.

For the analysis of the break-even point, it is common to classify the costs and expenses in the groups: fixed and variable; Although a fixed cost and a fixed expense do not equal the same, and even when a variable cost and a variable cost are not equal.

Fixed costs and expenses

Costs of a fixed nature are those that do not vary with the volume of production and are recoverable within the operation. For example the cost of leasing a premises; Whose value is US $ 1,500 per month will not vary according to the volume of production of said place.
Period Lease Cost Production Level Cost per unit

1 US $ 1500 monthly 4800 monthly unds US $ 0.3125
2 US $ 1500 monthly 4200 monthly unds US $ 0.3571

That is, costs of fixed nature are fixed by quantity and variables per unit. On the other hand, fixed operational expenses are those that are required to place (sell) the products or services in the hands of the final consumer and that have an indirect relation with the production of the good or service that is offered, they have the same behavior as the Fixed costs, only affecting a different operation, ie a sales process rather than a production process.
Variable costs and expenses

Variable costs are understood as costs that, like fixed costs, are incorporated in the final product, but which, unlike fixed costs, are variable in quantity if they depend directly on the volume of production. For example raw materials, labor and indirect manufacturing costs.
Period Cost of Raw Materials Production Level Cost per unit

1 US $ 120000 monthly 4800 monthly unds US $ 25
2 US $ 105000 monthly 4200 monthly unds US $ 25

That is, costs of a variable nature are variable by quantity and fixed per unit. On the other hand the variable expenses like the commissions of sales depend exclusively on the commercialization and sale. If there are sales will be paid commissions, otherwise there will be this item in the expense structure.
HOW IS THE EQUILIBRIUM POINT DETERMINED?
The determination of the Point of Balance requires the application of a series of relatively simple formulas, which vary according to necessity, since this (equilibrium point) can be determined for both units and monetary values.

The formulas used in determining the break-even point in units are as follows:

The unit variable cost (C.V.U) is obtained by dividing total variable costs by the number of units produced; However, it is very common to determine the unit variable cost more easily than the total variable costs (for reasons of unit decomposition in costs).

The formula used in determining the equilibrium point in monetary values ​​is the following:

One of the most interesting tools presented by the breakeven point is undoubtedly its graphic analysis, given that from this can facilitate the apprehension of various concepts associated with the profitability of a productive process.

The graph associated with the equilibrium point analysis is as follows:

EXAMPLE OF HOW TO FIND AND ANALYZE THE EQUILIBRIUM POINT

Mrs. Katy wants to start her business of marketing reducing belts, for this she presupposes her operating costs as follows, leasing the local $ 1’200,000 month, hiring a person to assist her in secretarial and sales functions $ 1’150,000 Month, the payment of services will be $ 150,000 month. Agreed with a producer and manufacturer of reducing belts and the cost will be $ 90,000 for each belt. Katy plans to sell each belt to $ 100,000.

According to the market that Katy knows, the possible sales per year will be 100 reduction bands monthly.

A) Calculate the monthly P.E.Q in units and in monetary values
B) How much would Katy win if she sells 100 units per month?
STEP 1: CLASSIFYING COSTS
This step consists of classifying costs and expenses into two groups: variable costs and fixed costs.

Fixed costs:
Value Concept
Lease $ 1,200,000
Wages $ 1’150,000
Services $ 150,000
Fixed costs Total $ 2,500,000
Variable costs:
Unit Variable Cost (Labor + Raw Material): $ 100,000
In this step, if you have total variable costs, find the unit variable costs, however in the current example we have the unit variable cost as input data.
STEP 2: APPLY EQUILIBRIUM POINT FORMULA
Applying the break-even formula for units:

Applying the equilibrium point formula for monetary values:

STEP 3: CHECK THE YEAR BY MEANS OF THE STATEMENT OF RESULTS
In this step the accounting operation of calculating the operating profit according to the amount indicated at the equilibrium point is developed, if this utility corresponds to zero (0), this means that the exercise is satisfactory. In addition, the income statement allows us to determine the profit that would be obtained with any number of units sold.

To check P.E.Q:
Value Concept
Sales (P.V * P.E.Q) $ 25’000,000
– Total Variable Cost (C.V.U * P.E.Q) $ 22,500,000
= Total Contribution Margin (M.C.T) $ 2’500.000
– Total Fixed Costs $ 2,500,000
= Operating Income (M.C.T – C.F) $ 0
Conclusions: the equilibrium point is 250 units, ie, it is necessary to sell 250 reduction bands monthly so that the income is equal to the costs; Therefore, from the sale of 251 reducing belts, it would only be starting to generate profits, while the sale of 249 reduction belts or a smaller number would mean losses.
To check the utility by selling 400 units per month (by requirement of our example):
Value Concept
Sales (P.V * Q) $ 40,000,000
– Total Variable Cost (C.V.U * Q) $ 36’000,000
= Total Contribution Margin (M.C.T) $ 4’000.000
– Total Fixed Costs $ 2,500,000
= Operating Income (M.C.T – C.F) $ 1,500,000
STEP 4: GRAPHIC
The resulting graph of the example is as follows:

In it we can appreciate the little margin of utility that presents this commercial process in the current conditions; As an action plan it would be possible to rethink the value of the sale price or to find different alternatives of production that allow to reduce the unit variable cost presented by the product.
You can download a format to calculate the break-even point in Downloads and Multimedia.

You can also calculate your break-even point in the following format (fill only the blue cells):

Other Collections of Break Even Analysis Spreadsheet

free break even analysis spreadsheet templatebreak even analysis template ukbreak even analysis template for service businessbreak even analysis spreadsheet in excelbreak even analysis chart templatesample break even analysis spreadsheetbreak even analysis template excel freesocial security break even analysis spreadsheet