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

## 2 Easy Methods to Calculate Sales Mix with a Formula in Excel

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 Number of Units Sold to Calculate 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. Number of Units Sold, Fixed Cost, and Variable Cost for each product are 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.

• 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

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

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

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

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

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

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

In this method, we will calculate 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.

• 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

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

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

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

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

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

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

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

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

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

• After that, select the Number option.

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

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

• 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

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

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

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

## 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.com, and unlock a great resource for Excel-related content.

## Related Articles

Md. Abu Sina Ibne Albaruni

This is Abu Sina, a fresh Engineering Graduate and currently an Excel and VBA content developer at SOFTEKO. I did my bachelor’s in Mechanical Engineering from BUET. My passion is to build new skills, gather experience and apply them to optimize and balance various trade-offs for a better future. Besides, I am fond of travelling, hanging out with friends, and watching movies.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF