10 Steps to creating a simple break even point

10 steps to creating a simple break-even template in Excel 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 a break-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 insight into 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 of costs, 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.