How To Make A Break Even Chart In Excel
A startup business will utilize a Break Even Analysis to calculate whether or not it would be financially viable to produce and sell a new product or pursue a new venture. This analysis is a common tool used in a solid business plan. The formulas for the break even point are relatively simple, but it can be difficult coming up with the projected sales, selecting the right sale price, and calculating the fixed and variable costs. While these tasks are still the responsibility of the business owner, our Break Even Calculator can help you run and report the analysis.
Advertisement
In addition to the spreadsheet, this page explains the formulas used in a break-even analysis. If you are more worried about your budget than your time, you can use the formulas and explanation below the template to create your own spreadsheet from scratch.
Break Even Analysis Calculator
for Excel
Buy Now
via ClickBank.net
Only $8.95
Required: Excel 2003 or later
60-DAY MONEY-BACK Guarantee
Try it out! If you don't think it was worth the cost, I will refund your purchase.
License: Private Use
(not for distribution or resale)
"No installation, no macros - just a simple spreadsheet" - by
Other Versions
Spreadsheet.com
Description
The Break Even Point is usually either the number of units you have to sell or the dollar amount of sales required to cover your costs. It may also be defined as (1) the point at which an investment will start generating a positive return or (2) the point at which total costs = total revenue. A break-even analysis can also be used to calculate the Payback Period, or the amount of time required to break even.
Our Break-Even Analysis Calculator is a simple spreadsheet that contains 3 separate worksheets to solve for either (1) Break-Even Units, (2) Break-Even Price, or (3) Payback Period. All of these scenarios are just different ways of manipulating the basic breakeven equation, explained in detail below.
In addition to the Break-Even Point, the worksheets also solve for the number of units or the price to reach a target Net Income Before Taxes (NIBT).
Bonus: Break-Even Analysis for Multiple Products
Calculate the break-even units (X) and break-even sales (S) for multiple products by defining a Sales Mix and assuming it to be constant over time. (See the screenshot on the left).
This version of the break even calculator is included as a separate download when you purchase the above spreadsheet.
Solving Break-Even Analysis Problems
The formula used to calculate a breakeven point (BEP) is based on the linear Cost-Volume-Profit (CVP) Model[1] which is a practical tool for simplified calculations and short-term projections. See reference [1] for more information about this model, and especially the discussion about the assumptions. All the different types of break-even analyses are based on the following basic equation:
Break-Even Equation
Total Costs = Total Revenue
TC = TR
TFC + TVC = P × X
TFC + (V × X) = P × X
The variables and definitions used in the break-even equation are listed below.
- P = Selling Price per unit
- V = Variable Cost per unit.
- X = Number of Units Produced and Sold
- TR = Total Revenue = P * X
- TC = Total Costs = TFC + TVC
- TFC = Total Fixed Costs
- TVC = Total Variable Costs = V * X
- P-V = Contribution Margin per unit (CM)
- CMR = Contribution Margin Ratio = (P - V) / P
Payback Period
The Payback Period is the time it will take to break even on your investment. In break-even analyses in which are are solving for the break-even price or number of sales, the payback period is defined ahead of time. Depending on rate of change in your market, this may be a few months or a few years. Or, if you are just starting a business, your bank may want to see evidence that you will start making a profit after 18 months, or some other period.
Sales Price
One of the assumptions of the linear CVP model is that the Sales Price per unit (P) remains constant. So, the total revenue (TR) is just the price (P) multiplied by number of units sold (X). However, prices typically decrease with increasing demand, so be aware that the linear CVP model is a simplification.
Variable Costs
Variable costs include the production, direct labor, materials, and other expenses which depend on the number of units produced and sold. On financial statements, like an income statement, Cost of Goods Sold (COGS) is a variable cost. Some variable costs may be percentage-based (like commissions) while others may be dollar-based (like material costs).
Example: If you are selling software online, the payment processing service might charge $1 plus 7.5% of the sale price. If the sale price was $14.00, then the Variable Cost per unit (V) would be 1+(0.075*P) = $2.05. In the break-even calculator, you can split the cost between the percentage-based and dollar-based categories.
Direct Labor: Let's say that every 100 sales requires 8 hours of technical support over the life of the products. If the labor cost (including payroll taxes) is $20/hr, then the Variable Cost per unit (V) would be V = (8*$20)/100 = $1.60 per unit.
The linear CVP model assumes that the Variable Cost per Unit (V) is constant over the specified Period. You should be aware that this is a simplification. For example, when labor is involved in production, productivity can have a significant effect (see ref [1]).
Fixed Costs
Fixed costs are those which are assumed to be constant during the specified payback period and which do not depend on the number of units produced. Advertising, insurance, real estate taxes, rent, accounting fees, and supplies would all be examples of fixed costs. Fixed costs also include salaries and payroll taxes for non-direct labor such as administrative assistants and managers, or in other words, the payroll not included as variable costs.
In reality, increasing production may also increase the expenses that are listed as "fixed costs" because they increase as the business grows and hires new employees. After you run the break-even analysis, and especially if you use the CVP model to calculate sales required to reach a target profit, you should revisit your cost analysis to ensure that the costs match the level of production and sales required to reach your goals.
Break Even Chart
The spreadsheet includes a break-even chart like the one shown below, which shows the Break-Even Point (BEP) as the intersection between the Total Revenue and Total Cost when plotted with the number of units on the x-axis. The Profit (or Loss) is also shown on the chart as Total Revenue - Total Cost.
Formula to Calculate the Break-Even Point
You can find the basic breakeven point formula all over the place, and the formula that is most often given is for calculating the "Break Even Units", or the number of units that you'll have to sell to cover costs. Actually, there are many ways to define the break even point. You may want to solve for the total dollar sales to break even, what price you'll have to charge to break even. You may also want to calculate how long it will take you to break even, which is officially called the payback period.
Break-Even Units
The following formula is for calculating the number of units (X) you will have to sell over the specified period of time.
X = TFC / ( P - V )
X = TFC / CM
If you want to solve for the number of units required to reach a targeted Net Income Before Taxes (NIBT), then substitute (TFC+NIBT) for TFC in the above equation.
Break-Even Sales
The break-even sales amount (S) is just the total revenue (TR) at the break-even point, which can be calculated as S = X × P. The following formula, derived from TR = X × P is another way to calculate the break-even sales amount.
S = TFC / ( 1 - V / P )
S = TFC / CMR
The value (1 - V / P) is known as the Contribution Margin Ratio (CMR), which is basically just the percentage of revenue earned for each unit sale after subtracting out the variable costs:
CMR = 1 - V / P = (P - V) / P
Break-Even Price
To solve for the price, you can use the Goal Seek tool in Excel to set X to a certain value by changing the price.
The formula for solving for the break-even price requires you to break down the variable costs into dollar-based and percentage-based costs:
- V = Vd + (Vp × P) = Variable Costs per unit
- Vd = Total Dollar-Based costs per unit
- Vp × P = Total Percentage-Based costs per unit
The following formula is used to solve for the sale price (P) required to break-even if you produce and sell X units during the specified payback period.
P = ( 1/(1-Vp) ) × ( Vd + (TFC / X) )
If you want to solve for the price required to reach a targeted net income before taxes (NIBT), then substitute (TFC + NIBT) for TFC in the above equation.
Payback Period
For very simple sales scenarios, the CPV model can be used to solve for the Payback Period, or the number of months required to break even. Like the other formulas, we start with TR = TC. Both the revenue and the costs may depend on time so we have to define a few new terms.
To calculate the payback period, the number of units sold (X) is specified as a number of units per month. The fixed costs are broken down further into Start-up Costs (SC) and Recurring Fixed Costs (RC). Start-Up Costs are the costs required to develop the product, or create the very first product. Recurring Fixed Costs are those which are paid monthly or annually but which are not directly tied to the number of units sold, like web-hosting fees, monthly advertising expenses, insurance premiums, etc.
- t = Payback Period in months
- TFC = SC + (RC × t)
- TVC = V × x × t
- SC = Total Start-up Costs
- RC = Recurring Costs per month
- x = Number of units sold per month = X / t
Payback Period (t) = SC / ( P×x - V×x - RC )
Break-Even Sales (S) = P × x × t
Example: The selling price for an iPhone application is P=$1.99 and I expect to sell x=450 units per month. The development cost of the application is SC=$7,500 and my recurring monthly fees for advertising and web hosting come to RC=$65.00/month. I am charged a commission of Vp=30% to sell the app from iTunes. Result: The break-even spreadsheet calculates the payback period to be 13.35 months, which I'd round up to 14 months (because fractional recurring costs don't make sense in this case).
Important: This calculation should only be used as a rough estimate. It does not take into account the time value of money, risk, interest, financing, opportunity costs, etc. The financial formulas NPV and IRR are usually better for calculating the return on an investment.
Related Content
References and Resources
- [1] Management Accounting: Concepts, Techniques & Controversial Issues by James R. Martin, Chapter 11.
- Break-Even Period for Paying Points on a Mortgage at decisionaide.com - An interesting calculator I wish I had seen a while ago.
- Cost-Volume-Profit Analysis at wikipedia.com - Some nomenclature.
- How to Do a Breakeven Analysis at about.com
Disclaimer: This spreadsheet and the information on this page is for illustrative and educational purposes only. We do not guarantee the results or the applicability to your unique financial situation.
Follow Us On ...
Related Templates
Finance Articles
How To Make A Break Even Chart In Excel
Source: https://www.vertex42.com/ExcelTemplates/breakeven-analysis.html
Posted by: biondohuriturnar.blogspot.com
0 Response to "How To Make A Break Even Chart In Excel"
Post a Comment