Executive Summary
For my
project I created a gross margin projector. This tool can be used by retailers
that enter into contracts with vendors wherein they agree upon a set gross
margin percentage for a certain time period for a specific collection of
merchandise. This tool was something that would have been very useful at my
previous job working as a buyer at Kohl’s Corporation, and is something that I
plan to take with me to utilize at my future employer if I end up working for a
retailer. I also plan to share it with former colleagues who are still working in
corporate retail.
The tool
requires certain inputs by the users that will then calculate their projected
gross margin for the season. These inputs are (for both current year and
previous year): Regular Sales Units (i.e. non-clearance), Ticket Price (aka Retail
Price), Regular Selling Price (aka Average Unit Retail or Out-the-Door price),
Unit Cost, and Receipt Units.
The agreed
upon Gross Margin Plan is also entered as a benchmark to compare how deficient
(or sufficient) the projected gross margin is based upon the inputs entered.
If the
program (any specified collection of merchandise) is being projected to be less than the agreed
upon gross margin the tool then uses a Goal
Seek function in Excel to suggest strategies for changing inputs to achieve
gross margin plan.
There are
some assumptions about clearance prices that are included in this tool. These
assumptions can be changed by the user, although they are the most widely used
in the industry so adjusting these may not be necessary. The first assumption
is regarding the first markdown of clearance, calculated from the ticket price,
not the regular selling price. This is assumed to be 75% off. The second markdown
is assumed to be 90%. The second assumption made is how many of the remaining
units (the units remaining after the regular priced items have been sold) will
be “sold through” (known as ST% or sell-through percentage in the industry)
during the first and second round of clearance pricing. I set the default
assumptions for these at 75% and 100% respectively.
This tool
can either be used as a pre-season planning tool, an in-season scorecard tool,
or an end of season tool used to help negotiate for gross margin support with
vendors. Ideally it would be used as all three with sales projections updated
with the most current information. In
this way, the retailer would be able to supply the vendor with real-time
information. This would be advantageous because the vendor would be able to
plan for exactly the amount they are deficient to GM plan. It is much easier to
formulate a strategy several weeks before the end of the season than it is when
it is a surprise that must be dealt with immediately – which happened
frequently when I was at Kohl’s. Additionally, the tool includes a search
string function that will search for similar products on Amazon.com and will
return a range of prices being charged for the product. A retailer can use this
feature to ensure they are pricing competitively with similar products being
sold online.
No comments:
Post a Comment