Calculation of markup and margin using Excel

Excel is a great business tool that can be used for a variety of purposes. Including pricing. Let’s say we have a certain norm, how much to wind up at one price, and there is also the cost of each product. If there is one item, then this can be done manually. But what to do when you need to calculate the margin of a large number of products at once?

And in general, is there a difference between markup and margin? Let’s understand these key concepts and also determine how to calculate them.

Margin and Markup Calculation in Excel: Formulas

So, we have two indicators, each of which is known to us. The first is the price. This is the final cost of the product, how much it needs to be sold for or it is currently being sold. Margin is a very important parameter that makes it possible to determine many other characteristics of a project or business, such as:

1. Profitability of the project or business as a whole.
2. How variable the profitability of this organization is.
3. Which customers bring in the most money.
4. Which projects are the most resource-intensive and whether these investments pay off.
5. Where does gross income go?
6. How does the salary of employees and the marginality of projects correlate? 
7. How much money each service brings. 

Depending on these results, managerial decisions are made. For example, the need to reduce costs, increase the cost of goods or services, or abandon projects that are resource-demanding but do not bring the target profit is realized. 

What needs to be done in order to determine the markup and margin?

Margin Formula

Let’s make a sign.

In our case, we are interested in a cell labeled “Margin”. That is, D2. It should indicate such a formula (shown in the screenshot).

Thus, our margin began to be 33,3%. In simple terms, a third.

Weighted average sales margin

If we need to determine the average margin, it is not enough to use the previous formula, since the data will be inaccurate. It is much better to use a weighted average that takes into account the weighting factor. In our case, these are sales volumes.

There are three ways to determine the weighted average margin.

1. Multiplying each margin, obtained from the formula in the previous paragraph, by the appropriate weighting factor, which is the volume of sales. In this case, we can fully control the computational process and understand what is the basis for calculating the weighted average.
   

   

2. Take advantage of the function SUMPRODUCT, which does the same, only automatically. In detail, it works as follows: it multiplies the margin by the weighting factor, and sums up the resulting values. But in this case, the result returned by the formula is not enough, since it must also be divided by the total number of goods sold or the total weight.
   

   

3. The easiest method for the average person, but it takes up space on the sheet. It is necessary to create one more column in which the formula for obtaining margin from a specific transaction is written. We need the percentage margin multiplied by the volume of sales in a particular country. After that, you need to divide the result by the total amount of sales.
   

   

It is highly recommended that you practice your calculations before using these formulas in your work. This will help you save a lot of time. 

Formula for calculating markup

We return to the table that we originally created. Our margin is in cell B2. Therefore, we introduce the following formula there.

As a result, the formula outputs a markup of 50%. If we calculate this figure manually, we will get the same share of the number. 

What is the difference between margin and markup (example)

Very often people confuse the concepts of margin and markup, since both are based on profit and expenses. However, the difference between these terms is enormous. They are simply calculated differently, as can be seen from the formulas that were given above.

With the help of a mark-up, enterprises can make a profit from the goods produced. If it were not, then trade would become unprofitable. And the margin is the final result, which turned out after the extra charge was made. To make it more clear, let’s give formulas that describe each of the concepts related to the topic we are considering. 

1. Price – the cost price together with the markup. 
2. Mark-up is a measure of price markup. To get it, you need to know the final cost and cost and subtract the second component from the first. 
3. Margin is the percentage of profit that an enterprise or private seller receives from one transaction. The maximum margin value is not equal to 100%. The fact is that the cost is always included in the price, which is a certain, even if minimal, percentage. 

The margin can be equal to the markup, provided that the calculation is carried out in absolute terms (that is, in numbers). If you determine the percentage, then the margin will always be greater than the margin. 

Here is a table that demonstrates this difference with an example.

The margin may exceed 100%, since it is calculated as a share of the cost. That is, if a person adds to the cost an amount 20 times more than he actually produced, then the markup will be 20 thousand percent. In this case, the margin cannot be more than 99,9999(…).

In this case, the margin will be equal to the markup, if measured in the amount of money that remains after the markup, excluding the cost.

In this case, the margin changes in proportion to the markup. The larger it is, the higher the margin. Therefore, if we know the markup, then we can determine the margin and vice versa. If a person is faced with the task of obtaining a specific net profit, then it is necessary to determine what markup is needed. And it can be done. In simple terms, to get a margin, you need to know the amount of sales and markup, and to get a markup, you need to know the amount of sales and margin. 

Separately, there is such a thing as marginality. It is actually a measure of how profitable a business is. The higher the margin, the more profitable it is to engage in a particular business. 

How is this concept to be considered in practice? In simple terms, margin is the percentage of profit per check issued to a customer or customer. Suppose we are advertising a project. To do this, we needed to pay 10 thousand to attract one client + operating costs for the provision of the service. In this case, the margin amounted to 5 thousand rubles. Percentage-wise, in this example, the margin would be 33 percent. 

Between the concepts of marginality and profitability, you can safely put an equal sign, since these terms are absolutely identical in their meaning. 

Marginality is not only a category of business, but also of marketing. In this area, this term refers to the profitability of a particular project being promoted.

What is a good margin level? If this figure is above 10 percent, then this is considered normal. If it is higher than 20-25 percent, then this indicates that the business is very profitable. It is important to understand that the margin at the start of a business will be lower, because it does not immediately generate income. Therefore, you need to regularly compare this indicator for different periods (which, however, is also important for a long-running business). Some companies generally reach a margin of 51 percent after a while, which is simply amazing.

By the way, according to surveys conducted by the marketing platform HubSpot, 15% do not know at all what the margin of their business is. If you belong to this number, then now you know how to do it in Excel.

There is also such a parameter, which is called the gross margin. This is the amount of funds that remain free after deducting costs that are not directly related to a specific project. It’s also a marketing term. That is, the gross margin does not include expenses such as rent, salaries, rent, purchase of materials, stationery, equipment, and so on. The optimal value of the gross margin is considered to be 50-60%.

To calculate this indicator, it is necessary to subtract from the income those funds that were spent directly on providing a specific activity. This can be done absolutely without any Excel functions, you just need to have the necessary indicators. 

There are several other types of margin:

1. operating margin. This is a kind of margin that determines the amount of income that is left after all the costs that relate to the main activity have been deducted from it. To calculate it, you need to know the operating profit or loss, as well as the total income. The formula is as follows = A1 / B1, where cell A1 can contain both net profit and loss. Accordingly, B1 contains the total income (or sales volume). This figure is always expressed as a percentage. This indicator can be used to assess the profitability of not only the entire enterprise, but also its department, as well as a specific project. If the operating margin is negative, this is a sad call. By the way, using the conditional formatting function, you can set the display of the negative operating margin in the cell in red. In fact, this parameter is identical to net income, with the only exception that we need to determine its share in total income in order to calculate the operating margin. 
2. PBIT margin. This is profitability, which is calculated even before the final accounting of income.
3. PBT. This is “dirty profit”, that is, the amount that was earned before taxes. 

To calculate margin in practice, you need to follow this simple algorithm:

1. Cost per person.
2. Overhead costs – wages, rent, insurance and others.
3. Overhead per hour. To do this, you need to multiply all the hours that were paid by the number of employees.
4. Determination of gross and net margin per client.

All this can be done in Excel. But this is more advanced level. The main thing is to understand the principle of how to do it, and everything else is a matter of technology. 

Calculation of the margin as a percentage if the markup is known

To make it clearer, let’s take a real case. Suppose we have an organization that reached the following indicators for the reporting period:

1. Sales amounted to 1000.
2. The markup was 60 percent.
3. Thus, the cost was 60%.

Based on these data, you can determine the margin. The mathematical operations are as follows:

1. 1000-625 375 =. 
2. 375 / 1000 = 37,5%.

By analogy, the margin is calculated with a known markup and in all other situations.

Calculation of the markup as a percentage if the margin is known

Now let’s imagine another case. Our reporting metrics are as follows:

1. The sales volume remained the same – 1000.
2. The margin was 37,5 percent.
3. Accordingly, on the basis of these figures, we obtain the cost price according to the formula (1000s) / 1000 u37,5d XNUMX%.

Next, using standard mathematical operations, we obtain the unknown from the equation. It is 625. Now that we know the cost, we can determine the markup. This is done in two simple steps that we learned in school:

1. The cost of goods is deducted from the total volume of sales performed by the organization. We get a markup, which is 375 (since we subtracted 1000 from 625).
2. Now we need to convert it to percentage form. To do this, we divide the markup on the cost of production and get a result of 60%.

Thus, it is not difficult to calculate the margin and markup in Excel. Although these values ​​may be identical in absolute terms, in relative terms they have a huge difference, since the markup is calculated from the cost price, and the margin is calculated from the total amount of the goods. 

In any case, both of these indicators are extremely important for running any business, creating a social project and conducting other activities in which you have to calculate these indicators and focus on them.