Margin of Safety vs. Break-even point in Excel

Posted on:  06/25/2021
Margin of Safety vs. Break-even point in Excel
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

Break-even vs. Margin of Safety in Excel

Other Excel articles

YouTube Video

Other Excel articles

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.

Other Excel articles

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

Break-even in units

Three ways to look at the Break-even

  1. The level of activity at which a business makes neither a profit nor loss.
  2. The point where total sales revenue equals total expenses.
  3. The point where fixed expenses equlas total contribution margin.
Cost-volume-profit analysis

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 units

Break-even in dollars

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

Margin of safety as a percentage

Other Excel articles

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 Senior Training Specialist at SurePoint Technologies. Chris is certified in Excel, Word, PowerPoint, and Outlook. Menard has a YouTube channel with other 600 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 7 million viewers have very appreciated. Because of Chris's certification and expertise with Microsoft, Chris is a proud member of Microsoft's Creator Team. Being a member of Microsoft's Creator Teams means many of his videos are available on Microsoft 365 YouTube channel and Microsoft support websites.

Categories