Excel guide · 5 min read
How to calculate initial markup in Excel
=(B3+B4+B8)/(B2+B8). A plan with 480,000 of net sales, 168,000 of expenses, 33,600 of profit and 86,400 of reductions needs an initial markup of 50.8% on retail.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Season plan (at retail) | Amount | As % of net sales | Rate | Class | Units bought | Unit cost | Original retail | ||
| 2 | Planned net sales | 480000 | Net sales | 1 | Knit tops | 1200 | 11.5 | 29 | ||
| 3 | Operating expenses | 168000 | Expenses | 0.35 | Dresses | 450 | 31 | 79 | ||
| 4 | Planned profit | 33600 | Profit | 0.07 | Denim | 600 | 24 | 59 | ||
| 5 | Markdowns | 72000 | Markdowns | 0.15 | Outerwear | 180 | 58 | 139 | ||
| 6 | Shrinkage | 9600 | Shrinkage | 0.02 | Accessories | 900 | 4.2 | 14 | ||
| 7 | Employee discounts | 4800 | Employee discounts | 0.01 | ||||||
| 8 | Total reductions | 86400 | Initial markup % | 0.50847457627 | ||||||
| 9 | Initial markup % | 0.50847457627 | ||||||||
| 10 | Maintained markup % | 0.42 |
Download this sheet as an Excel practice file (.xlsx)
What initial markup is, and why it's higher than your margin
Initial markup (IMU) is the markup you put on goods when they first arrive, as a percentage of the original retail price. It's the starting point of the season. By the end, some of that markup has gone: items sold on markdown, stock stolen or damaged, staff discounts. What's left is the maintained markup.
So the question a buyer asks before the season is backwards: if we want to end up covering our expenses and making our planned profit after all those reductions, how high does the markup have to start? That's what the formula answers.
Retail plans quote markup on retail (profit / selling price), not on cost. That's the opposite of the cost-based markup in calculating markup in Excel, so label every percentage in the sheet with which one it is.
Lay out the plan in input cells
Put every figure in its own labelled cell, all at retail value for the season: planned net sales in B2, operating expenses in B3, planned profit in B4, then the three reductions: markdowns in B5, shrinkage in B6 and employee discounts in B7. B8 adds the reductions with =SUM(B5:B7), 86,400 here.
These are planning inputs, and each one is a business decision or an estimate from last year's actuals. Keep them out of the formula so anyone can change a number and watch the required markup move.
Result: 86400
86,400 of planned reductions: 72,000 of markdowns, 9,600 of shrinkage and 4,800 of employee discounts.
The initial markup formula
The top line is everything the markup has to pay for: expenses, profit and the reductions. The bottom line is the original retail value of the goods, which is net sales plus the reductions, because every markdown and every stolen item was at full price when it arrived.
Here that's 288,000 / 566,400, or 50.8%. Format B9 as a percentage with one decimal place. A common mistake is to divide by net sales alone: 288,000 / 480,000 is 60%, which overstates the markup you need because it ignores that reductions also add to the retail you price up.
Result: 0.50847457627 shows as 50.8% with Percent format
0.508, which shows as 50.8%: the markup on retail the season's goods need when they arrive.
Can I plan with percentages instead of amounts?
Yes, and many planners do. Express every input as a percentage of net sales, with net sales as 100%. In E2:E7 that's 100%, 35% expenses, 7% profit, 15% markdowns, 2% shrinkage and 1% discounts. The same formula gives the same answer, because the percentages are the amounts divided by the same net sales.
Result: 0.50847457627 shows as 50.8% with Percent format
0.508 again, 50.8%: 60 / 118 when every input is a percentage of net sales.
What about cash discounts and alteration costs?
Some retail plans add workroom or alteration costs to the top line and subtract cash discounts earned from suppliers. If your plan uses them, give each its own input cell and add or subtract it in the numerator. Leave them out if you don't track them; the core formula still holds.
Initial vs maintained markup
Maintained markup is what's left after the reductions: expenses plus profit over net sales, 201,600 / 480,000 = 42% here. It's the markup you actually keep on what you sold.
The two tie together. Initial markup in money is 50.8% of the original retail, 566,400, which is 288,000. Take away the 86,400 of reductions and 201,600 is left, the maintained markup in money. If markdowns run higher than planned, the maintained markup drops and so does profit, which is why the reductions estimate deserves as much care as the expenses.
Result: 0.42 shows as 42% with Percent format
0.42, shown as 42%: the maintained markup on net sales after all reductions.
Convert markup on retail to markup on cost
Suppliers and your cost-based price lists may talk in markup on cost. To convert, divide the retail markup by 1 minus itself: 0.508 / 0.492 = 1.034, so a 50.8% markup on retail is a 103.4% markup on cost. Going the other way, retail markup = cost markup / (1 + cost markup).
To price one item at the planned IMU, divide its cost by 1 minus the markup on retail. A top that costs 11.50 needs a retail price of about 23.40, before you round it to a price point.
Result: 1.0344827586 shows as 103.4% with Percent format
1.034, shown as 103.4%: the same initial markup expressed on cost.
What retail price does an item need at the planned markup?
Cost / (1 - IMU %). Don't multiply the cost by 1 plus the retail markup: 11.50 x 1.508 is 17.34, a price that gives only a 33.7% markup on retail.
Result: 23.396551724 shows as 23.40 with Number format
23.40 for a knit top costing 11.50 at a 50.8% initial markup on retail.
Check the markup you actually achieved on purchases
Once orders are placed, compare the plan with what you bought. Total the cost and the original retail of every purchase with SUMPRODUCT, then take 1 - cost / retail. Here the five classes cost 56,370 and carry 143,370 of original retail: a 60.7% initial markup, comfortably above the 50.8% the plan needs.
Don't average the markups of the five classes. Accessories carry a 70% markup on small amounts and outerwear 58.3% on much bigger ones; a simple average of the class percentages gives every class the same weight and lands at 61.7%, not the 60.7% the money actually supports.
Result: 0.60682151078 shows as 60.7% with Percent format
0.607, shown as 60.7%: the initial markup achieved on 143,370 of purchases at original retail.
Common mistakes
Dividing by net sales instead of net sales plus reductions
The goods were priced at full retail before the markdowns and shrinkage happened. Add the reductions to the bottom line too.
Mixing markup on cost and markup on retail
Retail plans use markup on retail. Convert with markup / (1 - markup) and label every percentage cell.
Leaving shrinkage and staff discounts out
They reduce the markup you keep just like markdowns do. Give each its own input cell, even if the number is small.
Averaging class markups
Weight by money: 1 - total cost / total original retail, with SUMPRODUCT over units and prices.
FAQ
What is the initial markup formula?
Initial markup % = (expenses + profit + reductions) / (net sales + reductions), where reductions are markdowns, shrinkage and employee discounts. All figures are at retail for the same period.
What is the difference between initial markup and maintained markup?
Initial markup is set when goods arrive, on their original retail. Maintained markup is what remains on net sales after markdowns, shrinkage and discounts. In the example they're 50.8% and 42%.
How do I convert initial markup on retail to markup on cost?
Divide the retail markup by 1 minus itself. 50.8% on retail is 0.508 / 0.492 = 103.4% on cost.
What initial markup should I plan for?
That depends on your own expenses, profit target and markdown history, which is why the formula uses input cells. Use last season's actual reductions as a starting point and change them as the plan firms up.
Functions used
Related guides
- How to calculate open-to-buy in Excel
- How to calculate markdown percentage in Excel
- How to calculate markup in Excel
- How to build a retail sales plan by month in Excel
- How to build an accrued expenses schedule in Excel
- Excel AutoSum selecting the wrong range
Updated