Cellwise Start free

Excel guide · 5 min read

How to calculate initial markup in Excel

Quick answerInitial markup % = (operating expenses + planned profit + reductions) / (planned net sales + reductions), where reductions are markdowns, shrinkage and employee discounts. In Excel, with expenses in B3, profit in B4, reductions in B8 and net sales in B2: =(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.

RowABCDEFGHIJ
1Season plan (at retail)AmountAs % of net salesRateClassUnits boughtUnit costOriginal retail
2Planned net sales480000Net sales1Knit tops120011.529
3Operating expenses168000Expenses0.35Dresses4503179
4Planned profit33600Profit0.07Denim6002459
5Markdowns72000Markdowns0.15Outerwear18058139
6Shrinkage9600Shrinkage0.02Accessories9004.214
7Employee discounts4800Employee discounts0.01
8Total reductions86400Initial markup %0.50847457627
9Initial markup %0.50847457627
10Maintained 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.

=SUM(B5:B7)

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.

=(B3+B4+B8)/(B2+B8)

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.

=(E3+E4+SUM(E5:E7))/(E2+SUM(E5:E7))

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.

=(B3+B4)/B2

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.

=B9/(1-B9)

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.

=I2/(1-B9)

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.

=1-SUMPRODUCT(H2:H6,I2:I6)/SUMPRODUCT(H2:H6,J2:J6)

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

Updated