Determination of product price based on Break Even Point Using Microsoft Excel

by Kardi Teknomo

Break even point ( BEP) is the point where the revenue is equal to total cost. At BEP, the company makes neither profit nor loss. This point is important to determine the price of a product such that the company still gains net profit. In this tutorial, we will use MS Excel Goal Seek to find the price at certain BEP. You may download the spreadsheet here. Before that, let me refresh you on how to compute Break Even Point (BEP).

Your company may have fixed cost, such as worker salary, building operation and maintenance, machine and equipments that you must pay every month regardless how much sales that your company can get. Aside from the fixed cost, there is also variable cost, such as materials, energy supply (oil or electricity), and worker wages that depend on the production or sales. Higher sales, you may want to produce more and eventually paid higher variable cost.

Let be variable cost/ unit and is the total sales. We can compute the variable cost as and the total cost . Your company revenue is depend on the total sales and the price of the product , or .

The break event point happens when the total cost is equal to revenue, or which is equivalent to or

(Equation 1)

Thus, the break-even point price is obtained from revenue line at

(Equation 2)

Now let us try numerical example of BEP computation using MS Excel. The spreadsheet example can be downloaded here.

Let us put the value of variable cost/unit, in cell C3, the fixed cost in cell C4 and price of the product/unit in cell C5. The unit of BEP is in cell C7 computed as Equation 1 above, or C7 = C4/(C5-C3). The break-even point is C8 = C7*C5.

For variable cost/unit, , fixed cost and product price/unit , we get break-even at $180 for 60 units sales. The graph relationship of revenue, total cost and fixed cost is shown below. Clearly, the BEP is the intersection point between Revenue line and Total Cost line.

Now suppose you want to know how much is the price should be so that the break-even point is at $200. Of course, you can manipulate it easily from equation 2 to get the product/unit . Nevertheless, you can also use MS Excel Goal seek to compute it for you.

Here is steps by step procedure

1. From the menu of MS Excel, click Tools-Goal Seek … and Goal Seek dialog show up.

2. Set cell C8 ( the BEP price ) to value 200 ( target value ) by changing cell $C$5 ( initial guess of price ) and click OK button.

3. The result is shown in the figure below. To reach BEP of $200, you need to set the price at $2.73/unit

See also: Goal Seek Tutorial, MS Excel Tutorial index, Kardi Teknomo's Tutorial

Send your comments, questions and suggestions

This tutorial is copyrighted.

Preferable reference for this tutorial is

Teknomo, Kardi.Determination of product price based on Break Even Point Using Microsoft Excel. http:\\people.revoledu.com\kardi\ tutorial\Excel\BreakEvenPoint.html