What is Margin of Safety?
Margin of safety is a concept in accounting and finance that measures the difference between the current level of sales and the break-even point. The break-even point is the level of sales that covers all the fixed and variable costs of a business. The margin of safety indicates how much sales can drop before the business starts to lose money. It also reflects the risk and profitability of a business.
The margin of safety can be expressed in different ways, such as:
- Margin of safety in units: the number of units sold above the break-even point
- Margin of safety in dollars: the amount of sales revenue above the break-even point
- Margin of safety in percentage: the ratio of margin of safety to current sales level
The margin of safety formula is:
The margin of safety percentage formula is:
How to Calculate Margin of Safety in Excel?
To calculate the margin of safety in Excel, we need to know the following information:
- Current sales level: the actual or projected sales of the business
- Break-even point: the level of sales that covers all the fixed and variable costs of the business
- Fixed costs: the costs that do not change with the level of sales, such as rent, salaries, depreciation, etc.
- Variable costs: the costs that change with the level of sales, such as raw materials, labor, commissions, etc.
- Contribution margin: the difference between sales revenue and variable costs per unit
- Contribution margin ratio: the ratio of contribution margin to sales revenue per unit
The break-even point can be calculated using two methods:
- Break-even point in units: the number of units that must be sold to cover all the fixed and variable costs
- Break-even point in dollars: the amount of sales revenue that must be generated to cover all the fixed and variable costs
The break-even point in units formula is:
The break-even point in dollars formula is:
The contribution margin per unit formula is:
The contribution margin ratio formula is:
To calculate the margin of safety in Excel, we can use the following steps:
- Enter the data for current sales level, fixed costs, variable costs, and sales price per unit in separate cells.
- Calculate the contribution margin per unit by subtracting the variable cost per unit from the sales price per unit.
- Calculate the contribution margin ratio by dividing the contribution margin per unit by the sales price per unit.
- Calculate the break-even point in units by dividing the fixed costs by the contribution margin per unit.
- Calculate the break-even point in dollars by dividing the fixed costs by the contribution margin ratio.
- Calculate the margin of safety in units by subtracting the break-even point in units from the current sales level in units.
- Calculate the margin of safety in dollars by subtracting the break-even point in dollars from the current sales level in dollars.
- Calculate the margin of safety in percentage by dividing the margin of safety in dollars by the current sales level in dollars and multiplying by 100%.
Example of Margin of Safety in Excel
Let’s assume that a company has the following data:
- Current sales level: 10,000 units
- Fixed costs: $50,000
- Variable costs: $3 per unit
- Sales price per unit: $10
We can use the following Excel formulas to calculate the margin of safety:
- Contribution margin per unit:
=D2-E2(where D2 is the sales price per unit and E2 is the variable cost per unit) - Contribution margin ratio:
=F2/D2(where F2 is the contribution margin per unit) - Break-even point in units:
=C2/F2(where C2 is the fixed costs and F2 is the contribution margin per unit) - Break-even point in dollars:
=C2/G2(where C2 is the fixed costs and G2 is the contribution margin ratio) - Margin of safety in units:
=B2-H2(where B2 is the current sales level in units and H2 is the break-even point in units) - Margin of safety in dollars:
=B2*D2-I2(where B2 is the current sales level in units, D2 is the sales price per unit, and I2 is the break-even point in dollars) - Margin of safety in percentage:
=J2/(B2*D2)*100(where J2 is the margin of safety in dollars, B2 is the current sales level in units, and D2 is the sales price per unit)
The Excel table below shows the results:
| Current sales level (units) | Fixed costs ($) | Variable costs ($/unit) | Sales price ($/unit) | Contribution margin ($/unit) | Contribution margin ratio | Break-even point (units) | Break-even point ($) | Margin of safety (units) | Margin of safety ($) | Margin of safety (%) |
|---|---|---|---|---|---|---|---|---|---|---|
| 10,000 | 50,000 | 3 | 10 | 7 | 0.7 | 7,143 | 71,429 | 2,857 | 28,571 | 40 |
The margin of safety in units is 2,857, which means that the company can sell 2,857 units less than the current sales level and still break even. The margin of safety in dollars is $28,571, which means that the company can generate $28,571 less than the current sales level and still cover all the costs. The margin of safety in percentage is 40%, which means that the current sales level is 40% above the break-even point.
