 # 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.

## 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.

## 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.

## 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.

## 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.

## 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.