Ms Office Tune Up

When you’re analyzing costs and sales for a new product, perhaps the most basic analysis you can perform is to calculate the break-even point. This is the point at which revenue gen­erated by the product equals the costs associated with manufacturing and selling the prod­uct. You usually approach a break-even analysis in one (or both) of two ways:

• If you already know the price you want to charge for the product, then you calculate the number of units you must sell to break even.
• If you have a target for the number of units you want to sell, then you calculate the price you must charge per unit to break even.

In both cases, you set up a worksheet model that calculates the profits made by the prod­uct. Figure 1 shows an example of a profit model. Total Revenue (cell C6) is calculated by multiplying the units by the price per unit, and then taking off the average custom dis­count. Total Costs (C10) is calculated by multiplying the cost per unit by the number of units sold and adding on the fixed costs. Total Profit is just the difference between Total Revenue and Total Costs.

For the initial break-even analysis, let’s assume a fixed price of $29.95 for the product. How many units must we sell to get the Total Profit value to 0? The easiest way to figure this out is to use Excel’s Goal Seek feature, which uses iterative methods to arrive at the result you’re looking for (assuming a solution exists). Here’s how it works:

1. Choose Data, What-If Analysis, Goal Seek. Excel displays the Goal Seek dialog box.
2. In the Set Cell range box, type or select the address of the cell that contains the profit formula (cell C12 in Figure 1).
3. In the To Value text box, type 0.
4. In the By Changing Cell range box, type or select the address of the cell that contains the value you want to adjust to seek the break-even point. (In Figure 1, that’s cell C4, the Units Sold value. Figure 2 shows the completed dialog box.)
5. Click OK.

Figure 1 A worksheet model that calculates the profit generated by a product.

Goal Seek now attempts to find a solution, and you see the Goal Seek Status dialog box as it works. If it finds a solution, you see a dialog box similar to the one shown in Figure 3. As you can see, in this example, Goal Seek determined that we must sell 140,449 units to break even. Click OK to keep the solution or click Cancel to discard it.

Figure 2 The Goal Seek dialog box ready to calculate the break-even point.

Figure 3 The break-even solution generated by Goal Seek.

The other break-even model is to predict the number of units sold and then vary the unit price to reach a profit of 0. Figure 4 shows Goal Seek set up to perform such an analy­sis. Notice that in this case, we’ve assumed unit sales of 200,000, and the changing cell is now C3, the price per unit. Figure 5 shows the result: a price of $27.30.

Figure 4 The Goal Seek dialog box where the changing cell is now C3, the price per unit.

Figure 5 The break-even solution for the price per unit.

Technorati Tags: break even point

Popularity: 2% [?]

If you liked this post, would you please buy me a twelve-ounce lattes for only $2