Every small business owner must have a solid grasp of their break-even point. This requires they fully understand both their fixed and variable costs and capture their profit contribution – which is simply the product or service’s price minus its variable costs. To make it easier, I’ve decided to include a sample excel sheet in today’s post. This sheet includes a graph depicting various break-even points, different profit contributions, and allows the user to input different product prices and variable costs.
Understanding the Company’s Fixed and Variable Costs
Before getting to the excel sheet, we’ll first review the two variables within a company’s total cost structure, which are its fixed and variable costs. When thinking of fixed costs, think of the company’s fixed monthly payments. These could include salaries, rent, heating, electricity etc. Variable costs pertain to usage and correlate to the number of units manufactured or sold. A great example would be purchasing additional raw material and parts to complete a large volume order.
The calculation is a very straightforward one. However, it’s important to note that companies should never rely upon the analysis to determine their product’s pricing. A product’s price should always be based on other factors including its features and benefits, as well as what the market and customers are willing to pay for the product. The calculation involves using the company’s fixed and variable costs as well as the product or service’s price. The calculation is explained below.
- Break Even Point = BE
- Fixed Costs = F
- Selling Price = $P
- Variable Costs = V
- BE = F/($P-V)
The aforementioned profit contribution portion is the $P-V aspect of the equation. The attached sample break-even excel sheet allows you to input your company’s existing fixed and variable costs as well as your product’s price. There are two portions to the excel sheet. The first allows you to play around with your product’s pricing. The second portion allows you to play around with different variable costs. In this sheet I’ve assumed your small business knows your product’s current price.
While there are ways to determine a product’s price with the analysis, it’s best to determine a product’s price on those previously mentioned variables. To get this started, and to give you an idea of what a company’s costs would look like, I’ve decided to include an example of them below.
- Fixed Costs
- Monthly Salaries = $1800
- Monthly Rent = $500
- Miscellaneous monthly payments = $200
- Total Fixed Costs = $2500
- Variable Costs
- Raw Materials = $100
- Labor = $250
- Total Variable Costs = $300
Determining Break-Even Point with Price
The first portion of the excel sheet allows you to play around with your product’s pricing. In this case I’ve highlighted the blue portion of the top table where it’s written “Sell Price”. This is where you can input up to five different prices for your product.
Don’t input any price variables anywhere other than this blue section, otherwise it will disrupt the excel sheet. The sheet will then carry these variables over to the corresponding profit contribution portions of the table and graph below. To simplify it, here are the five profit portions with the various prices.
- Sell Price Profit Contribution 1: $P-V = $445 - $300 = $145
- Sell Price Profit Contribution 2: $P-V = $485 - $300 = $185
- Sell Price Profit Contribution 3: $P-V = $525 - $300 = $225
- Sell Price Profit Contribution 4: $P-V = $565 - $300 = $265
- Sell Price Profit Contribution 5: $P-V = $605 - $300 = $305
Each additional unit sold means another increase in the corresponding profit contribution. Where the line crosses the company’s fixed costs is where the company has “broke-even”. Anything below this intersection point is considered a loss and anything above is considered a profit.
Determining Break-Even Point with Variable Costs
Another way to improve a company’s break-even is by reducing a company’s fixed and variable costs. In fact, in most cases, companies must first concentrate on reducing their costs before looking at price. As mentioned, it’s never as simple as just raising the product’s price. I’ve assumed that your small business wants to leave its current fixed costs as is and have instead concentrated on allowing you to play around with your variable costs. The second portion of the template allows you to play around with different variable costs.
The highlighted yellow portion of the second table allows you to input various values. Again, once you input your variable costs, the sheet will carry that value forward. To summarize, here are the profit contributions based on the five different variable cost values.
- Variable Cost Profit Contribution 1: $P-V = $445 - $300 = $145
- Variable Cost Profit Contribution 2: $P-V = $445 - $280 = $165
- Variable Cost Profit Contribution 3: $P-V = $445 - $260 = $185
- Variable Cost Profit Contribution 4: $P-V = $445 - $240 = $205
- Variable Cost Profit Contribution 5: $P-V = $445 - $220 = $225
When small businesses look to improve their break-even point, it often involves them looking at ways to raise their product’s price. However, the best approach is to first concentrate on reducing the company’s costs. Once all those avenues are exhausted, only then should a company even entertain thinking of looking at price and most importantly, they should only change price if the market will allow it. This sample excel sheet should be used to identify when your revenue meets and exceeds your expenditures.
You can download this excel sheet here: Download Sample-Break-Even-Excel-Sheet-for-Small-Businesses
One important rule is that fixed costs aren’t fixed indefinitely. There are ways to reduce fixed costs. If you want to read more about break reducing a company’s fixed & variable costs, please refer to the following post.Does Your Company Use Best Practices In Cost Reduction?
The above video explains how to determine hourly rates for a contractor or small business owner.