How to Calculate Product Margin in Excel: Formula Examples

Calculating margin for many people is a significant challenge because they think of adding margin as adding a percentage to a given amount.

In fact, margin is not a percentage of "overhead costs" but the percentage that represents profit in the final price of a product or service.

With a given cost and margin percentage, the price is calculated using the following formula:

Price = Cost / (1 - margin percentage)

The margin should be less than 100% because it is impossible to sell something while earning 100% or more, as every business activity involves some costs.

At the same time, the margin can be negative, meaning the company sells its goods or services below the cost and incurs a loss on each operation.

Contrary to how it may seem, this is not a rare phenomenon. In some industries, such as selling printers, it is normal to sell them below cost. Manufacturers cover these losses by selling services and high-margin cartridges (ink). This strategy is used to simplify customer acquisition.

To calculate the margin, knowing the cost and price, we use the following formula:

Margin Percentage = (price - cost) / price

The above formula can also be written as: margin percentage = 1 - cost / price.

However, if the desired element is the cost, we use the same formula as we use to decrease the amount by a percentage.

In other words, we reduce the price by the margin size and get the cost.

Cost = price * (1 - margin)

Weighted Average Margin from Sales

To calculate the average margin, we cannot use the simple average; we must calculate it using a weighted average, where the weights are the sales volumes.

We can do this in three ways:

Using the first method, we multiply each margin percentage by its corresponding sales volume, sum the results, and then divide this sum by the total sales volume. This formula gives us full control over the calculation method and helps understand the basis of the weighted average.

Using the second method, we use the SUMPRODUCT function, which adds up the products of the margin and its corresponding sales volume. The result returned by the function must be divided by the total sales volume. This function is detailed in the article "Basic Functions."

The third method is the simplest for many but requires creating an additional column with the margin for each sale. We get values in column K by multiplying each margin percentage by its corresponding sales volume. To calculate the weighted average margin, simply divide the sum of the margin by the sum of all sales.

Presentation Calculation of Weighted Average Margin

download file Download

I suggest you delete the contents of all green fields in the exercises file for this lesson and enter the formulas yourself without peeking into the above description. If you manage to enter them all correctly, you have mastered this material. If not, you can always start again and again until you get the result.