Profit Margin and Price Markup Calculation and Formulas [Excel]

In this post I am going to run through some Business Maths; to those that are heaving a sigh and have a mental block on maths, I feel your pain. As a high school student my issue with maths in a classroom was always how the examples were relevant to the real world, it was all poles and shadows, angles and area of odd shapes. In Business Maths and Business Statistics everything always ties back to a real problem, or the need for an answer that must be quantified or qualified. The problem could be how much production, how much money, how much profit or simply how many sales days to break-even? This automatically gives relevance to the numbers that you are working with because you are working with money, time (days, weeks, months, quarters, years) and the answer either tell you whether you are mistakes which will cost you money or doing it right and making profit.

This post is fairly high-level and expects you to understand basic maths. We’ll explain some of the concepts and cover Selling Price, Mark-up Percentage, Gross Margin Percentage, and Selling Price Margin which are the sales formulas for businesses. We additionally cover some of the terminology like net, gross, and cost of goods which you hear business people talk about. As we go along we’ll make mention of price-points and other marketing terminology which you might find interesting to look up, and some other academically worthwhile things, that you may or may not know.

Regardless of industry, business has a vernacular (net, gross, cost of goods, markup, markdown, margin) that you need to know, to be able to understand conversations with business people.

This gets more complicated where different businesses have their own cultural vernaculars that often don’t translate outside of their businesses (Low hanging fruit, bandwidth, unicast, multicast, swing around, beach holiday). This is all part of business, industries, and company cultures. Picking up the lingo makes understanding business much easier.

You often hear business people talk about net, gross and revenue, cost of goods. In this context gross refers to the total (everything) and net refers to the part of the total that really matters (what you take home, or were left with). The revenue is what you make (income) from sales, minus discounts and rebates via the main operations of the business. Finally the cost of goods sold (COGS) is a combined value that takes into account numerous out-going money streams such as all the costs of purchase, transport, packaging, material, labour, allocated overhead to store (this is oversimplification it also uses LIFO or FIFO). The goods not sold is deferred as cost of inventory until the inventory sells or gets written down in value.

We need to go through a very simplistic explanation on pricing. In retail psychological pricing is a strategy that says consumers assume odd or un-rounded prices are perceived lower than they actually are. An Example of this would be $ 1.99 or $ 1.98 is perceived as $ 1.00 and not $ 2.00. An easy-ish (I suppose) rounding mistake, unless you are talking about $ 49,999 and the rounding mistakes is $1,000. If you suffer from this mental tick, just practice always rounding up. Psychological pricing is one type of strategy that you can follow. Others are price skimming, price discrimination and yield management, price points, bundle pricing, penetration pricing, price lining, value-based pricing, geo and premium pricing all of which are demand-driven strategies.

For my calculation examples I am going to use a psychological selling price of $1.99

My cost price / purchase price / buy price is going to be $ 1.40

Mark-up Percent

If we know our cost, and selling price, and we want to know the mark-up percentage.

\text{Mark-up Percent} = \frac{(\text{Selling Price - Cost Price}) }{ \text{Cost Price} }

\text{Mark-up Percent} = \frac{ (\$ 1.99 - \$ 1.40) }{ \$ 1.40 }

\text{Mark-up Percent} = \frac{\text{ \$0.59} }{ \text{ \$1.40} }

\text{Mark-up Percent} = 42\%

Selling Price

If we don’t have the selling price, but we know the selling price, and have a 42% mark up:

For this step we need to keep in mind that 42% can also be written as 0.42.

\text{Selling Price} = \text{Total Cost} \times \text{(1 + Mark-up Percent) }

\text{Selling Price} = \text{ \$ 1.40 } \times \text{(1 + 0.42) }

\text{Selling Price} = \text{ \$ 1.40 } \times \text{(1.42) }

\text{Rounded Selling Price} = \text{ \$ 1.99 }

Gross Margin Percent

Changing the Mark-up Percentage calculation slightly to the Gross Margin Percent calculation:

\text{Gross Magin Percent} = \frac{(\text{Selling Price - Cost Price}) }{ \text{Selling Price} }

\text{Gross Magin Percent} = \frac{(\text{ \$ 1.99 - \$ 1.40}) }{ \text{\$ 1.99} }

\text{Gross Magin Percent} = \frac{\text{ \$ 0.59 } }{ \text{\$ 1.99} }

\text{Gross Magin Percent} = 30\%

Selling Price Margin

If you have a margin requirement of 30% and need to apply this to your pricing:

\text{Selling Price} = \frac{(\text{ Total Cost }) }{ \text{ 1 - Gross Margin } }

\text{Selling Price} = \frac{(\text{ \$ 1.40 }) }{ \text{ 1 - 0.30 } }

\text{Selling Price} = \frac{(\text{ \$ 1.40 }) }{ \text{ 0.70 } }

\text{Selling Price} = \text{ \$ 1.99 }

We’ve gone through the basic pricing calculations for Markup and Margin and looked at both cost + markup/margin and cost and selling to calculate markup/margin perspectives.

Here is a Excel template for Markup and Profit Margin to see the calculations. Click on the link.

If you want to learn more about Business Maths here is a great book on the subject.