Let’s walk through how to calculate profit margin and markup in Excel, and then apply this to setting the selling price based on cost and either margin or markup, and target costing based on selling price and margin or markup.

⏱️TIMESTAMPS⏱️
00:00 How to calculate margin and markup
01:07 Margin and markup scenarios
02:36 How to calculate the selling price from cost and margin %
04:28 Cost-plus pricing: How to calculate the selling price from cost and markup %
05:16 Target costing and allowable cost
06:19 Target costing based on markup %

In each of the tabs of this margin and markup file in Excel, the input variables are shown in yellow, and the other numbers are calculated from those input variables. Let’s start with inputting a selling price, also known as the retail price, of $100 per unit. Cost, or wholesale price, is $80. The gross profit in dollars equals the selling price minus the cost, cell B6 minus cell B7, $20. Now margin is the profit as percentage of the selling price, cell B8 divided by cell B6, 20%. Markup is the profit as a percentage of cost, cell B8 divided by cell B7, 25%. Let’s input various cost levels, to see how these affect margin and markup.
If the cost is $60, then margin is 40% (40 out of 100) and markup 67% (40 over 60).
If the cost is $50, then margin is 50% (50 out of 100) and markup 100% (50 over 50). The selling price is double the amount of the cost, something that is quite common in for example the fashion and clothing industry.
If the cost is $20, then the margin goes all the way up to 80% (80 out of 100) and the markup to 400% (80 over 20). The selling price is 5 times the cost.
It is mathematically impossible for the margin to be at 100%, unless your cost is zero. Markup can be well over 100%, as you can see in the example. Once the margin is above 50%, the markup is above 100%. And yes, both margin and markup can also be negative. But that’s not economically viable for very long.

How to calculate the selling price from cost, and margin %? In this tab, the cost row and the margin % row contain input variables. Let’s put a cost of $80 and margin of 20%.

The sum of the cost as percentage of selling price, and the #margin as percentage of selling price, always equals 100% in total. So if cost goes up by a certain number of percentage points, then margin goes down by the same number of percentage points. And if cost goes down, then margin goes up.
At 20% margin, a cost of $80 equals 80% of the selling price. $80 divided by 0.8 equals $100.

How to calculate the selling price from cost, and markup % (also known as "cost-plus pricing")? In this tab, the cost row and the markup % row contain input variables, and our output variable is selling price. As the term “cost-plus pricing” suggests, we take the cost as the basis (this is now 100%), add the markup, and then get to the selling price. The Excel formula for selling price is B7 times open parentheses 1 + B10 close parentheses. At a cost of $80, and a #markup of 25%, the selling price is 125% of $80, which is $100.

We can also work in the opposite direction: target costing. If the marketing team came up with a selling price of $100 based on their market research, and business management is looking for 20% margin on the products, then what is the maximum allowable cost that manufacturing and sourcing have to work with? Target cost equals B6 (selling price) multiplied by open parentheses 1 minus B9 (1 minus the profit margin) close parentheses. The same logic applies that we used in the tab where we calculated selling price from cost $ and margin %. The sum of the cost as percentage of selling price, and the margin as percentage of selling price, always equals 100%. $100 selling price, with a margin of 20%, gives a target cost of $80. $100 selling price, with a margin of 40%, gives a target cost of $60.

Target costing also works with a selling price and a markup %. As selling price equals cost times 1 + markup, we can derive that cost equals selling price divided by 1 + markup. Cell B7 (Cost) equals B6 (Selling Price) divided by open parentheses 1 plus B10 (1 plus Markup %) close parentheses. $100 selling price with a markup on cost of 25% gives a target cost of $80. $100 selling price with a markup on cost of 67% gives a target cost of $60.

Philip de Vroe (The Finance Storyteller) aims to make accounting, finance and investing enjoyable and easier to understand. Learn the business and accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better investing decisions. Philip delivers #financetraining in various formats: YouTube videos, livestreams, classroom sessions, and webinars. Connect with me through Linked In!

Want to get access to bonus content, and/or express your gratitude by buying me a cup of tea? Join my channel as a member through [ Ссылка ]

свернуть