This project is done from the perspective of a Fashion Merchant working as part of the Merchandising team at Lululemon (job simulation). The goal of this project is to assist with the preparation that is necessary to launch and sell shorts that are part of the May 2022 capsule.
The source of the data is Lululemon and Forage.
MS Excel (Pivot Tables, Functions), Assortment Planning, Merchandising, Financial Planning, Market Research
To create the on-order log and assist with inventory planning, I created pivot tables to display the $ value and number of units of Lululemon's shorts capsules by country, channel and product. To do so, I created two additional columns and determined the country of each channel and whether the products were designated for the online store or a brick-and-mortar store using functions like IF, SEARCH and ISNUMBER.
I then created a line sheet for the Spring 2022 shorts capsule at lululemon using a pivot table and summarized the SKU count for all channels by counting the number of unique products by colourway.
I then calculated sales forecasts and the full-price sell-through to assist with real-time decisions to appropriately manage lululemon's inventory.
In total, there are almost 50,000 shorts in Lululemon's inventory that are worth $3.7 million in total. Online sales account for the majority of Lululemon's sales so the majority of the shorts are being allocated for the online stores (67% or $2.4 million).
The US has the largest on-order market with 48% of the shorts and $1.8 million of the inventory being allocated for the US. Canada has the second largest on-order market at 44% of units and $1.6 million being allocated for Canada. The order is pretty evenly split between the US's ecommerce store and brick-and-mortar stores whereas the majority of Canada's order is for their e-commerce store.
Finally, Europe has a relatively small order coming in at 7% of units worth $271k. The majority of Europe's order is for their brick-and-mortar stores (60%).
In total there are 17 products with a unique SKU being sold in Canada.