http://www.techrepublic.com/blog/10things/10-steps-to-creating-a-simple-break-even-template-in-excel/3503?tag=nl.e042&s_cid=e042 - By Susan Harkins - November 29, 2012, 1:40 PM PST
Takeaway: Determining the break-even point for your products gives you valuable insights into how business is performing. Here’s how to set up abreak-even profit model.
Knowing the right price to charge for a product or service can make or break your business. Part of that decision process is often a break-even analysis. The break-even point (BEP) is the point where costs equal revenue (sales). At this point, the product has profit, but you’re covering your costs. In other words, anything over the BEP is profit; anything under is loss.You’ll need a few variables to calculate the BEP:
* Price per unit
* Cost per unit
* Fixed cost (a constant that doesn’t change, regardless of the number of units produced)
* Variable costs (costs associated with each unit so it varies with the number of units sold)
When determining the BEP, keep in mind that it isn’t a magic number. It’s a best-guess point that provides insightinto how profit (and loss) changes as your sales go up and down. In this article, we’ll build a BEP profit model in 10 steps. Along the way, we’ll assume a lot about your financial expertise — this is an Excel lesson, not a financial business lesson.
Note: The sample Excel workbook used in this walk-through is available for download.
1: Create tables for recording costs
There are two sets ofcosts, fixed and variable. (There’s also semi-variable, but for our purposes, two is adequate.) We’ll use Excel’s table feature to store this data, making customization a bit easier (you won’t have to update cell references).
Use Figure A as a guide to create both tables in two sheets, named FixedCosts and VariableCosts. You can fine-tune them to fit your organization. Start by entering the labels.Then, create a table by selecting any cell in the data range, clicking the Insert tab, clicking Table in the Tables group, checking the My Table Has Headers option, and clicking OK. In 2003, use lists, the precursor to tables (on the Data menu).
These two tables record fixed and variable costs.
2: Enter the BEP labels and formats
Using Figure B as a guide, enter the labels to createthe BEP analysis sheet named BreakEven. I’ve used a fill color to indicate input cells, but it isn’t necessary for this technique. However, you should apply the following numeric formats:
* Currency: C2, C5, C6, C8, C9
* Numeric with no decimals: C3, C11
You’ll use this sheet to accept user input values for unit costs and units sold and generate the BEP.
3: Create range namesOnce you have the labels in place, you can create range names to use in formulas. To create the first two, select B2:C3, click the Formulas tab, click the Create From Selection option in the Defined Names group, and then click OK. Repeat this process with B5:C6, B8:C9, and B11:C11. (Excel will embed underscore characters between the words in the names.)
4: Enter BEP formulas
Now you’re readyto enter the formulas that will generate the BEP value. Table A identifies each formula and its cell and offers a brief explanation.
These formulas will generate the BEP value.
5: Enter costs
Return to the cost tables and enter the appropriate values for your new product. Feel free to alter these tables as necessary. Figure C shows the values we’ll use to illustrate how this part ofthe template works.
Enter cost values.
The template doesn’t care whether you’re using values that represent monthly costs, annual costs, or some other period costs. Just be sure the values consistently represent the same period. These are monthly figures. When you enter the units sold value (next), you’ll also want to enter a monthly value.
6: Input BEP variables
Once you have...