The Contribution Margin
The Contribution Margin shows the difference between the total sales and the total direct variable costs.
The mathematical expression of the Contribution Margin for per unit product is:
The general expression to determine the Contribution Margin for an entire batch is:
There is another formula to calculate the value of the Contribution Margin:
Consider the dataset below. It showcases the total number of sold products and different types of variable costs.
- Use the SUM function in C12 to sum the variable costs.
- Press Enter.
=SUM(C8:C11)
Example 1 – Calculate the Contribution Margin per Unit
Steps:
- Name B14 as Total Variable Cost per Unit.
- In C14, enter the following formula:
=C12/C5
- Press Enter.
You will get the amount of the total variable cost per unit.
- Name B15 as Contribution Margin per Unit.
- Enter the following formula in C15.
=C7-C14
- Press Enter.
You will get the value of the Contribution Margin per unit.
Example 2 – Calculate the Contribution Margin for an Entire Batch
Steps:
- Name B14 as Total Sales.
- In C14, use the following formula:
=C5*C7
- Press Enter.
You will get the value of total sales.
- Name B15 as Total Contribution Margin and enter the following formula in C15.
=C14-C12
- Press Enter.
You will get the value of the Contribution Margin for an entire batch of products.
Alternative Method to Calculate the Total Contribution Margin
Steps:
- To calculate the Total Variable Cost per Unit, enter the following formula in C14,
=C12/C5
- Press Enter.
- To calculate the Contribution Margin per Unit, use the following formula in C15.
=C7-C14
- Press Enter.
- To determine the value of the Total Contribution Margin, enter the following formula in C16.
=C5*C15
- Press Enter.
You will get the value of the Total Contribution Margin for an entire batch of products.
How to Calculate the Contribution Margin Ratio
The Contribution Margin Ratio
The Contribution Margin Ratio is the ratio of the difference between total sales and total variable costs and the value of total sales. The mathematical expression of the Contribution Margin Ratio is:
Steps:
- To calculate the value of Total Sales, enter the following formula in C14.
=C5*C7
- Press Enter.
- To calculate the Contribution Margin Ratio, use the following formula in C15.
=(C14-C12)/C14
- Press Enter.
- Set the cell format to Percentage in Number (Home tab).
You will get the value of the Contribution Margin Ratio.
Download Practice Workbook
Download the practice workbook.
<< Go Back to Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hello,
You made an error in the calculation by including the 1 unit cost of $30 in the sum. Only cell rows of 8-11 equals the total variable cost for 500 units sold. The $30 per unit is only a factor in the Total Sales figures used to calculate the final ratio.
Hello KURT
Thank you for reading our article and providing helpful input. We sincerely appreciate your time and effort in reviewing the content and pointing out the calculation error.
We have already taken corrective action and have updated the article accordingly. Thank you one more. Best wishes!
Regards
Lutfor Rahman Shimanto
ExcelDemy