These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Wednesday, April 11, 2012

Profitability Calculator

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 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

Blog Archive