Calculating the Realised Margin

We are often asked how Realised Margin is calculated. A financial margin is the amount left after costs are deducted from sales. If the sales are shown including the VAT then that also must be deducted. However, there is no "Trademaster" way of doing this, only a correct way.

In this document, "*" means "multiply by" and "/" means "divide by".

Gross Margin

This is easy, the answer is just:

  Purchases at Retail - VAT on Purchases at Retail - Purchases at Cost

Trademaster shows purchases at cost, but in any case they are just purchases at retail divided by the markup. The Gross Margin calculated this way is a money value. It would be turned into a percentage by the formula:

  GM% = Gross Margin Value / Purchases at Retail * 100

Realised Margin

Following from the above, our margin is the gross value less any VAT and less the cost. So, for Realised Margin the answer we are looking for is:

  Realised Margin = Sales - VAT on Sales - Cost of Sales

The question is, what is the cost of sales? It is not the cost of purchases (purchases at CPTE) because in a period, sales hardly ever equal purchases, and we must also take account of markdowns and any shrinkage (wastage). The cost of sales is the cost of all the stock that we have disposed of in the period. That stock value is:

  Value of Sales + Markdowns + Shrinkage

where these are all at retail. The cost of these is what is paid for this stock and in a Trademaster budget that is calculated from the markup or gross margin for the season. Thus the cost is:

  (Value of Sales + Markdowns + Shrinkage) / Markup

This means; add together sales, markdowns and shrinkage at retail and divide the total by the markup. You can also get this from the gross margin percent but that is more complicated and the answer would be the same, see the notes at the end.

A Worked Example

The best way is illustrating how this is worked out is through an example. These are the values we shall use, taken from an actual Trademaster budget:

Trademaster shows the Markup as entered by the user, and this is then used to calculate purchases at cost, but it is of course:

  Markup = Purchases SPTI / Purchases CPTE

  Example: 43500 / 16527 = 2.63206

Trademaster will display this as "2.63" but of course it "knows" the exact value. Next, take the total of all transactions out of stock, Sales + Markdowns + Shrinkage

  Example : 36420 + 10850 + 500 = 4777

Divide this by the markup to give cost of sale.

  Example: 47770 / 2.63206 = 18149.28        (1)

Now, calculate the VAT on Sales

  Example 36420 * 17.5 / 117.5 = 5424.25    (2)

So the Realised Margin is sales at retail less the cost (1) less the VAT (2):

  Which is: 36420 - 18149.28 - 5424.25 = 12846.47

Trademaster rounds this to the nearest pound: £12,846, and then calculates the percentage of sales at retail that results:

  12846 / 36420 * 100 = 35.27183

Trademaster will display this as 35.27%.

That may look a little complicated, but it takes longer to describe than it does to do. It will take less than a minute on a calculator. Try it!

Further Notes

Cost of Sales

In a budget, Trademaster uses the markup on purchases to work out the cost of sales. Clearly this is an approximation, but it would be very complicated to do it any other way. In almost all situations it will work well.

In a Variance Report or Best Estimate (Re-Estimate) the actual Markup and Gross Margin by period are known. Trademaster can thus assess the cost of sales more precisely, but the principles of calculation are exactly the same.

Consolidations

For a consolidation, the value (in money) of the Realised Margin is added up from the individual headings in the consolidation, and then the Realised Margin % is calculated. If you use the example method above you may get very small difference due to the rounding applied.

Life without VAT

Some clients operate Trademaster with all values at SPTE, and some operate in areas that have no VAT. The principles are exactly the same, just ignore the VAT element in the examples. For Realised Margin the answer becomes:

  Realised Margin = Sales - Cost of Sales

Note that the sales are exclusive of VAT. Working out the cost of sales is exactly as before:

  (Value of Sales + Markdowns + Shrinkage) / Markup

but here, the Markup is Purchases at Retail, less the VAT, divided by Purchases at Cost. None of the values have VAT on them.

Some retailers present values at retail including VAT but present the Margins ex-VAT. To do this, when you have worked out the money value of the Margin, work out the percentage at SPTE (Selling Price Tax Exclusive):

  Percentage = Margin / SPTE Value / 100

Markup and Gross Margin

We used Markup in the calculations because it makes them easier. You could use Gross Margin%, and that might seem logical because the Gross and Realised Margin percentages are so obviously related. To find a Markup from a Gross Margin percentage we do this:

  GM% = 1 / (1 - Margin / 100 - Vat Rate / (100 + Vat Rate))

Whoops! That looks complicated, but to explain; "Margin / 100" is just the margin % as a decimal fraction, so 48.22% becomes 0.4822. "Vat Rate / (100 + Vat Rate)" is the fraction of a retail value that the VAT represents, for 17.5% that is 0.148936, or 14.8936% of retail. Take both of these away from 1 and the result is the cost fraction. (See "Gross Margin" above. Divide 1 by that fraction to get the Markup. Then use the Markup, which is what we did in the first place. Now you know why!