DESCRIPTION:
Created and designed the automated Efficient Product Assortment tool utilizing Microsoft Access VBA/SQL.
BUSINESS PURPOSE:
Automatically determine the optimal product offering within key categories at retail customers
- Maintain and grow distribution
- Target underperforming items
- Assess how efficient assortment is versus rest of market
- Evaluate Private Label performance
DIMENSIONS:
- DATABASE SOURCES: Nielsen or IRI
- PRODUCTS: Key Categories/Segments
- TIME PERIODS: Latest 26 Weeks
- GEOGRAPHIES: Retail Trading Areas & Rest of Market (ROM)
- MEASURES: Dollar Sales, Dollar Sales % Chg. Year Ago, Unit Sales, Unit Sales, % Change Year Ago, % ACV Distribution, Dollar Sales Per Million ACV, Cumulative Dollar Share
Overlay relevant data from various sources
- Substitutability: Evaluate items that have a high substitutability index for possible deletion
- Annual category consumption: Factor items with the highest consumer worth
- Exclusivity: Factor items with the highest exclusivity
- Loyalty (Share of Category Requirements): Consider possible deletion of items with low loyalty and low risk to category sales
END USER PROCESS:
- Collaborated with stakeholders such as business leaders, sales managers, and category managers to gather data requirements and align on KPIs.
- Designed the tool to efficiently and quickly navigate the end user to make custom selections and analyze automated reports within minutes.
STEP 1: SELECT TRADING AREA and ENTER data conditions
STEP 2: ADJUST weight factor for the measures (if needed)
STEP 3: RUN/REFRESH data
STEP 4: VIEW/EVALUATE reports
SAMPLE REPORT:
STEP 5: MAKE ADJUSTMENTS to the RETAIN/ADD/DELETE recommendations
STEP 6: Review UPDATED reports
SAMPLE CODE: Integrated and transformed data from various sources by writing SQL queries and coding VBA modules