The Break-Even Analysis: A Step-by-Step Guide
Before we begin, I recommend using already-built Excel spreadsheets. I highly recommend Vortex42. You can find all the spreadsheets you need here.
For creating the break-even calculator, please download the following spreadsheets:
12-month Business Budget
Break-even Analysis Calculator
Define Your Costs
Using the 12-month business budget, list every expense that you are aware of that your business will incur. You need to know what all your costs are in order to build a business plan.
Fixed costs are those costs that will not increase as a result of you selling more of your products/services. See the screenshot below of my sample spreadsheet.
Some of these expenses may not apply. Many of these expenses vary depending upon your industry and location.
You may need to call Internet companies, insurance agents, and others to get quotes. You don’t need exact numbers, just reasonable estimates. When you estimate, it is not a bad idea to estimate a tad on the high side.
As you work and rework your financials, you will be able to shop for vendors/solutions that accomplish more for less.
If you are using the 12-month business budget as a guide, visit the second tab on the bottom of the spreadsheet called “Goods.” Here is where we need to discuss variable costs.
These are costs that increase when you sell more products/services and decrease when you sell less.
Typically, variable costs apply when you sell product (rather than a service). These costs are known as Cost of Goods Sold (COGS). For example, if you sell bikes, you had to purchase the bike before you sold it. If you sell more bikes, then you also had to buy more bikes to sell.
In my example here, you have 6 types of products. Scroll down to row 70. For this example, let’s say that Product 1 costs you $100/product.
Price your beginning inventory, estimate the purchase of additional inventory (“goods purchased”), estimate shipping (if applicable), estimate labor costs (if applicable), and use “other” as your catch-all for any other costs associated with the first product.
When estimating variable costs, you will need to begin considering your selling price for the product. Let’s say that for Product 1, we started with 50 products (beginning inventory of $5,000), you bought 100 more products (goods purchased at $10,000), and then you sold 110 products at $175 apiece in January (scroll back up to row 5, sales are $19,250).
It is not important that you establish your price point at this time. For now, you are simply playing with numbers to understand your variable costs.
With shipping charges at $500 ($5/product), your total COGS for Product 1 is $11,500. When you “sold” 110 products at $175/product, row 80 shows a Profit of $7,750 after COGS. You have a remaining inventory of 4o products for a Less Ending Inventory of $4,000 (this will become your beginning inventory for February).
Play with these numbers by considering the cost of your products based on each manufacturer or contractor. If you want higher quality products, your variable costs will be higher, and vice versa.
Do not be shy! If you need help from someone that understands spreadsheets and numbers, work with an accountant, CPA, or bookkeeper.
Build a Break-Even Formula
Once you’ve identified your costs, you can begin to understand how much you need to sell before you make money. You need to become more comfortable using a Break-Even Analysis than any other financial statement.
Add Fixed Costs
Simply copy/paste your fixed costs from your 12-month business budget spreadsheet to rows 10-21 on the break-even analysis calculator. If any fixed costs are not listed on the Break Even spreadsheet, add them up and enter the total into row 21.
The fixed costs listed on row 22 of your “break-even-analysis.xlsx” should match your total fixed costs listed on row 43 of your 12-month business budget.
Add Variable Costs
For variable costs, take an average of the total cost for one product sold.
For example, we said above that Product 1 costs you $105 (including shipping). Let’s say that for Product 2 it costs you $125/product, and for Product 3 it costs your $90/product. The average cost per product is $107.
Enter $107 on row 26.
Let’s say that the selling price for each product is as follows: Product 1, $175; Product 2, $230; Product 3, $120. Your average selling price per product is $175.
Enter $175 on row 5.
According to your costs and selling price, row 6 shows you that you would need to sell 568 products in 1 month to break-even. Your total sales equal $99,400 to break even.
“Breaking even” simply means that your business pays for itself. Your sales have kept up with all your costs.
If the sales numbers seem too high to reasonably achieve, you should look for lowering your costs and/or increasing your prices. In this example, it is likely that your fixed costs are too high, specifically your payroll.
A break-even analysis helps you refine your budget before launching your business.
What is most important in having what you need to build a business plan comes down to what it takes to break even and become profitable.
If you would like to add in a profit goal, simply add your profit goal into row 44 (Targeted Net Income Before Taxes). The break-even will update automatically.