Margin of Safety vs. Break-even point 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.

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 Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite 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

• Blog » Microsoft » Microsoft Excel » Intermediate