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 generated by the product equals the costs associated with manufacturing and selling the product. 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 product. Figure 7.12 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 discount.

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 7.12).
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 7.12, that’s cell C4, the Units Sold value. Figure 7.13 shows the completed dialog box.)
5. Click OK.

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 7.14. 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.

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 7.15 shows Goal Seek set up to perform such an analysis. 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 7.16 shows the result: a price of $27.30.

• Anti Aging Makeup Tricks and Tips pt 1 There are all kinds of makeup tricks and tips out there, but choosing the right ones for your own personal...
• Getting started with the envelope budget method This is a guest post from Luke the guy behind Neobudget. NeoBudget is an online budget manager that uses the...
• 4 Weight Loss Tips and Tricks The following weight loss tips and tricks are designed to help you lose weight more effectively and more efficiently than...
• iPad's Hidden Keyboard Functions: Tips & Tricks In this video I demonstrate the hidden keys and functions on the iPad keyboard.......
• The Awesome Foursome - The World's Best Trick Golf Balls User Reviews Send this to a friend The Awesome Foursome - The World's Best Trick Golf Balls Manufacturer: Customer Rating:...