Margin of Safety vs. Break-even point in Excel
Posted on: 06/25/2021
Break-even point (BEP) is the level of sales where the total cost (fixed cost and variable cost) equals total revenues. Break-even is where Net Income is zero. There is no profit and no loss.
The Margin of Safety (MoS) is a difference between actual/budgeted sales and the level of break-even sales. In this detailed video, I will calculate both the Break-even and Margin of Safety. I'll calculate break-even and Margin of Safety for the number of units and dollars, and I'll calculate the Margin of Safety as a percentage.
Break-even is also known as Cost-Volume-Profit analysis.
At the bottom of this article are two other videos I created on Cost-Volume-Profit analysis. The first video is for one product and charts the fixed cost, variable cost, break-even and shows net loss and net income in the graph. The second video shows the break-even based on a product mix or multiple products.
Break-even vs. Margin of Safety in Excel
YouTube Video
Fixed Cost vs. Variable Cost
Fixed costs do not change. Fixed cost includes:
-
Rent
-
Insurance
-
Security
-
Supervisors' salary
-
Property taxes
-
Depreciation
-
Some utilities
Variable cost change in proportion with how much we produce. The more we produce, the higher the variable cost. Variable cost includes:
-
Direct labor
-
Direct materials
Contribution Margin per unit
The Contribution Margin per unit is the selling price of an item minus the variable cost of the item. For example, if the sale price is $500 and the variable costs are $300, the contribution margin is $200 per unit.
Break-even point
At the break-even point, you aren't making any money or losing any money. Your Net Income or Profit is zero. To calculate the break-even point in units, divide your fixed cost by the contribution margin per unit.
In the example below, your fixed costs are $7,000, and our Contribution Margin per unit is $8. We have to cover the $7,000 of fixed cost every period, and we make $8 per unit. We take the $7,000 and divide it by $8 to get 875 units.
Break-even in units
Three ways to look at the Break-even
-
The level of activity at which a business makes neither a profit nor loss.
-
The point where total sales revenue equals total expenses.
-
The point where fixed expenses equlas total contribution margin.
Net income is $0.00
Margin of Safety in Dollars
Important note: You can't figure out the margin of safety until you calculate the break-even.
The margin of safety in dollars is the budgeted sales minus break-even in dollars.
-
Budgeted units 1,000
-
Sale price per unit $40
-
Budgeted sales $40,000 (1000 x 40)
-
Break-even in dollars $35,000 (875 break-even in units x 40)
-
Budgeted Sales - Break-even in dollars $5,000 (40,000 - 35,000)
In the example above, you had to calculate the breakeven in dollars before calculating the margin of safety.
Break-even in units
Break-even in dollars
Margin of Safety as a percentage
The margin of safety as a percentage is the margin of safety in dollars divided by the budgeted sales.
$5000/$40,000 = 12.50%
The higher the percentage, the better. This is the percentage your budgeted sales can decrease before you have a net loss. Our sales could decrease by 12.5%. Over 12.5% and we have a net loss.
Margin of safety as a percentage
Break-even analysis video: One product and a chart
The video below shows one product and one chart. Many accounting and business students use this video for cost/managerial accounting classes. The chart shows
-
Fixed cost
-
Variable cost
-
Break-even point
-
Net loss
-
Net income
Break-even analysis video: Multiple products - product mix
After creating the video with one product, an Executive MBA student wanted to know how to calculate the break-even with multiple products. I used a product or sales mix to figure out the break-even for numerous products. It is the video below.
Chris Menard
Chris Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers.
Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.
Categories