How to Calculate Sales Mix with Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

Today we will discuss how to calculate sales mix with a formula in Excel. Sales mix represents the share or relative percentage of different products and services a company offers. The sales mix of a product may vary depending on its profitability, production capacity, demand, availability of raw materials, etc. We can easily calculate the sales mix with a formula in Excel.

sales mix formula excel


How to Calculate Sales Mix with Formula in Excel: 2 Easy Methods

In this article, we will demonstrate with examples how we can calculate sales mix with a formula in Excel. Basically, we can calculate the sales mix in two different ways. We can calculate the sales mix based on the number of units of the product that has been sold. We can also calculate the sales mix from the generated revenue of the product.


Method 1: Use the Number of Units Sold to Calculate the Sales Mix with a Formula in Excel

In this method, we will show you how to calculate sales mix with a formula based on the number of units sold for different products. This method is simple and easy to understand. Here in the worksheet, the cost analysis of various products of a company is given. The company sells 4 products: Smartphone, Laptop, Computer, Television. The number of Units Sold, Fixed Cost, and Variable Cost for each product is also given. We can calculate sales mix with a formula based on the number of units of the products. To do so, simply follow the steps below:

Steps:

  • To begin, select the cell where you want to put the value of the sales mix of a product. We want to start with the sales mix of smartphones. We are selecting cell C8.

Selecting a Particular Cell to Calculate Sales Mix with a Formula in Excel

  • Secondly, enter the following formula into the cell:
=C$5/SUM($C$5:$F$5)
This formula will determine the share of smartphones in the total number of units sold of all products

Entering a Formula into the Cell

  • Then, press Enter and you will see the sales mix of smartphones in cell C8.

Using Fill Handle to Autofill Data

  • Subsequently, you will see the sales mix of all the products from C8:F8 based on their number of units sold.

Calculating Sales Mix with a Formula in Excel

  • Moreover, you may want to see the sales mix in percentage. To do so, select cells C8:F8 and go to the Home tab.

Selecting the Home Tab

  • Then, click on the percentage sign (%) in the Number group.

Clicking on the Percentage Sign

  • Finally, you will see the percentage of sales mix of different products in cells C8:F8 based on their number of units sold.

Using Number of Units Sold to Calculate Sales Mix with a Formula in Excel


Method 2: Use Sales Revenue to Calculate Sales Mix with a Formula in Excel

In this method, we will calculate the sales mix from sales revenue. This method is very similar to Method 1. In the worksheet, the Sales Revenue is given for each product instead of the Number of Units Sold. The rest of the dataset is the same as Method 1. Follow the steps below to calculate the Sales mix.

Steps:

  • First, select the cell where you want to put the value of the sales mix of a product. We want to start with the sales mix of the Smartphone. We are selecting cell C8.

Selecting a Particular Cell to Calculate Sales Mix with a Formula in Excel

  • Secondly, enter the following formula into the cell:
=C$5/SUM($C$5:$F$5)
This formula will determine the share of smartphones in the total amount of sales revenue of all products

Entering a Formula into the Cell

  • Then, press Enter and you will see the sales mix of smartphones in cell C8.

Calculating Sales Mix with a Formula in Excel

  • After that, use Fill Handle to Autofill data from cells D8:F8.

Using Fill Handle to Autofill Data

  • Subsequently, you will see the sales mix of all the products from C8:F8 based on their sales revenue.

Calculating Sales Mix with a Formula in Multiple Cells

  • Moreover, you may want to see the sales mix in percentage. To do so, select cells C8:F8 and go to the Home tab.

Selecting the Home Tab

  • Next, click on the percentage sign (%) in the Number group.

Clicking on the Percentage Sign

  • Finally, you will see the percentage of the sales mix of different products in cells C8:F8 based on their sales revenue.

Using Sales Revenue to Calculate Sales Mix with a Formula in Excel


Use Sales Mix to Calculate Sales Mix Variance in Excel

The sales mix variance represents the difference between what the company planned to sell and what the company actually sold. The sales mix variance may be positive or negative. A positive sales mix variance reflects that the product earned more revenue than expected. A negative sales mix variance indicates that the product earned less revenue than predicted. Here in the worksheet, the sales mix analysis of multiple products of a company is given. The Planned Contribution Margin Per Unit field represents the difference between the sales price and related costs for the product. The Planned Sales Mix field shows the projected sales mix of the company for different products. Now, we will calculate the sales mix variance from this worksheet. Just follow the steps below.

Steps:

  • To begin, select cell C8 and follow Method 1 step by step to calculate the actual sales mix of all the products in cells C8:F8.

Using Number of Units Sold to Calculate Sales Mix with a Formula in Excel

  • Then, for ease of calculation, we will convert the percentage values into decimals. Select cell C7:F8 and go to the Home tab.

Selecting the Home Tab

  • Next, click on the menu bar of the Number group.

Selecting the Menu Bar from the Number Group

  • After that, select the Number option.

Clicking on the Number Option

  • Then, you will see the values of cells C7:F8 in decimals.

Sales Mix in Excel

  • Now, select a cell where you want to put the value of the sales mix variance of a product. We want to find the sales mix variance of smartphones. We are selecting cell C9.

Selecting a Particular Cell to Calculate Sales Mix Variance with a Formula in Excel

  • After that, enter the following formula:
=C$5*C$6*(C$8-C$7)
This formula will calculate the sales mix variance of smartphones by multiplying the number of units sold, the planned contribution margin per unit, and the difference between the actual sales mix and to planned sales mix

Entering a Formula into the Cell

  • Subsequently, press Enter and you will see the sales mix variance of smartphones in cell C9.

Calculating Sales Mix Variance

  • Next, use Fill Handle to Autofill data in cells D9:F9.

Using Fill Handle to Autofill Data

  • Finally, you will see the sales mix variance for all the products in cells C9:F9.

Using Sales Mix in Excel to Calculate Sales Mix Variance


Download Practice Book


Conclusion

In this article, we have talked in detail about how to calculate sales mix with a formula in Excel. This article will allow users to use Excel more efficiently and effectively. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, Exceldemy, and unlock a great resource for Excel-related content.


<< Go Back to Sales | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abu Sina Ibne Albaruni
Md. Abu Sina Ibne Albaruni

Md. Abu Sina Ibne Albaruni holds a BSc in Mechanical Engineering from Bangladesh University of Engineering and Technology. He is a dedicated employee of the ExcelDemy project. He has written quite a large number of articles for ExcelDemy. Besides, he has expertise in VBA. He efficiently automates Excel issues using VBA macros and actively engages in the ExcelDemy forum, offering valuable solutions for user interface challenges. His areas of interest in work and study span MATLAB, Machine Learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo