# 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 Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with over 800 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 12 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at chrismenardtraining.com/linked or on YouTube at chrismenardtraining.com/youtube