How to Calculate the Contribution Margin in Excel – 2 Examples

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.

=SUM(C8:C11)

Select cell C12, insert the given formula and hit Enter


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.

Calculate Contribution Margin for per Unit

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.

Calculate Contribution Margin per Unit

You will get the value of the Contribution Margin per unit.

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

Estimate Contribution Margin for Entire Batch

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.

Estimate Contribution Margin for Entire Batch

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.

Estimate Contribution Margin for Entire Batch

  • To calculate the Contribution Margin per Unit, use the following formula in C15.

=C7-C14

  • Press Enter.

Estimate Contribution Margin for Entire Batch

  • To determine the value of the Total Contribution Margin, enter the following formula in C16.

=C5*C15

  • Press Enter.

Estimate Contribution Margin for Entire Batch

You will get the value of the Total Contribution Margin for an entire batch of products.

Estimate Contribution Margin for Entire Batch

 


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:

How to Calculate Contribution Margin Ratio

Steps:

  • To calculate the value of Total Sales, enter the following formula in C14.

=C5*C7

  • Press Enter.

How to Calculate Contribution Margin Ratio

  • To calculate the Contribution Margin Ratio, use the following formula in C15.

=(C14-C12)/C14

  • Press Enter.

How to Calculate Contribution Margin Ratio

  • Set the cell format to Percentage in Number (Home tab).

You will get the value of the Contribution Margin Ratio.

How to Calculate Contribution Margin Ratio


Download Practice Workbook

Download the practice workbook.


<< Go Back to Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

2 Comments
  1. 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.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Dec 5, 2023 at 6:05 PM

      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo