Want to get a handle on Pricing, Markup and Margin?
This Excel Tool helps you to:
- Calculate Price and Margin from Cost and Markup
Starting with Cost and Markup (screenshot below).
See how prices and margins vary with different markups across your cost base.
- Calculate Price and Markup from Cost and Margin
Starting with Cost and Margin (screenshot below)
Prices and markups will alter as you experiment with different margins.
- Calculate Markup and Margin from Cost and Price
Enter your Cost and Price (screenshot below)
The Excel Tool calculates your markup and margin, allowing you to compare like-for-like.
Get the Markup & Margin Excel Tool by clicking the button below.
After completing payment at PayPal you will receive an email containing the download link.
Or, you can build your own!
First of all, let’s just remind ourselves of what Markup and Margin mean.
- Markup – The percentage of the cost you add to get the sales price. e.g. if you buy something for £2.00 and add a markup of 100%, you will sell it for £4.00 (See Wikipedia definition of Markup for further detail.)
- Margin – The proportion of the sales price which is profit. e.g. if you buy something for £2.00 and sell it for £4.00, then the margin is 50%, being (£4.00-£2.00)/£4.00 X 100. (See Wikipedia definition of Margin for further detail.)
Markup and Margin
As you can see, markup and margin are not the same.
100% markup is equivalent to 50% margin.
However, ‘markup’ and ‘margin’ are often used interchangeably. This is usually done quite innocently, but can lead to stressful confusion when trying to compare suppliers and products. The Markup & Margin Excel Tool will help you overcome this problem. You can set your pricing by markup or margin, or start with your cost and price and calculate back to your markup or margin. If one supplier is talking to you in markup and the other in margin, enter their data into the Excel Tool to compare like-for-like. Set your pricing policy and set prices accurately, quickly and simply.
Building the Price by Markup Calculator
Determine a cell in your worksheet to be where you enter the margin, e.g. B3
Now build a table with columns to hold the costs that you enter and the prices that the tool will calculate.
In the prices column enter the formula which will take the cost amount and apply the margin increase for selling price. The formula would be:
Cost + (Cost x Margin)
To make it easier to copy the formula down the column it is best to use the absolute cell reference for the margin percentage by enclosing the cell reference in dollar symbols ($). If you decide to copy the formula down the prices column for costs which haven’t been entered yet, it will make your worksheet tidier to use an IF statement which checks for an entry before attempting to calculate the marked up price.
Building the Price by Margin Calculator
As with the Price by Markup Calculator, determine a cell which will hold the markup percentage, e.g. C3
Build the table with columns for costs to be entered and prices that the Excel tool will calculate.
In the prices column enter the formula which gives you marked up price:
Cost / (1-Margin)
Using the absolute cell reference for the Margin will make it possible to copy the formula down several rows in the prices column. An IF statement can help you check that a cost has been entered before attempting to calculate the marked up price.
Building the Markup and Margin Calculator
Start with a table of four columns. Give these the headings Cost, Price, Markup and Margin. Enter your costs and prices into the costs and prices columns. The formula for the markup column is:
(Price – Cost) / Cost
And the formula for the margin column is:
(Price – Cost) / Price
You can copy these formulas down the columns.
It’s always nice to have a nice to look at spreadsheet so take a little time to format your Markup and Margin Excel Tool to make it easier for users to identify which cells they should enter data into and which cells output the results. You could also take advantage of cell locking to stop users inadvertently changing layouts or formulas after all your hard work.
Or, if you’d rather take advantage of the hard work we’ve already put into the Markup & Margin Tool, you can purchase it by simply clicking below.
You will be directed to PayPal to complete payment and then receive the download link by email.