Proposal and development of an Excel analysis for buyers and planners by category, brand, and competitor to improve inventory allocation and to provide insights to leadership on decision making in advertising, marketing, business strategy and operations areas.
Done by the use of macros with data extracted from Microstrategy and historical competition flyer data.
Situation
Sears was in need to optimize inventory allocation to save costs, specifically in the Refrigeration Category in Major Appliances, in order to have less warehouse space being used for on hand inventory.
Task
The task was to determine the top selling categories (such as top mount, French door) by brand, store and region. Also, to analyze customer demographics within a radius of 20 miles from stores in order to give a more comprehensive view of specific consumer behaviour.
Action
A price-point analysis in Excel was executed for competitiveness using historic sales, promotional history from flyer databases, and demographics. The analysis would point out pricing competitiveness, turnover and best sellers by category, brand and region, suggesting better inventory allocation and price adjustment for better decision making by the buyers.
Results
As a result, inventory was allocated more efficiently, thus using less warehouse space, more competitive pricing and better turnover.
Overall, the matrix helped buyers to make better buying decisions for better inventory handling and more competitive pricing.
Example: Quebec customers prefer French Door refrigerators across the entire province, while most of eastern Ontario close to Quebec, prefer Top Mount.