Md. Abu Sina Ibne Albaruni
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.
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.
- Secondly, enter the following formula into the cell:
=C$5/SUM($C$5:$F$5)
- 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 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.
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.
- Secondly, enter the following formula into the cell:
=C$5/SUM($C$5:$F$5)
- 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.
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.
- 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)
- 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.
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
- How to Calculate Sales Tax in Excel (4 Suitable Ways)
- Calculate Sales Growth over 3 Years in Excel (2 Methods)
- How to Calculate Annual Sales in Excel (4 Useful Methods)
- Calculate Average Sales per Month in Excel (2 Easy Methods)
- How to Forecast Sales Using Regression Analysis in Excel (3 Methods)
- Forecast Sales in Excel (5 Easy Ways)
- How to Calculate Sales Growth Percentage in Excel
