How to Calculate Sales Mix with Formula in Excel

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


Download Practice Book

You can download this practice book while going through this article.


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.

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

Read More: How to Calculate Total Sales in Excel (With Easy Steps)


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.

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

Read More: How to Calculate Percentage of Sales in Excel (4 Examples)


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

Read More: How to Calculate Projected Sales in Excel (2 Easy Ways)


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

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

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo