Maven Toys - 2024 Expansion Plans
Introduction
Maven Toys is a fictitious chain of small toy stores; they currently have 50 stores spanning 29 cities throughout Mexico. I was hired as a BI consultant to analyze patterns and trends in their sales and inventory data to create an expansion plan for 2024.
To view the full 2-page interactive Tableau Dashboard click Here
Business Questions
- Where should Maven Toys expand to?
- How many stores should they open?
- Which products will perform best at the new location(s)?
About the Data
Sales & inventory data by Maven Analytics for the fictitious toy stores includes information about products, stores, daily sales transactions, and current inventory levels at each location. It was collected between January 1, 2022, and September 30, 2023. The data contains 4 tables consisting of 16 fields and 829,262 records in CSV format.
I sourced Mexican airport passenger traffic data to determine which airports would be best suited for expansion. Wikipedia's 'List of the busiest airports in Mexico' includes the 50 busiest airports by passenger traffic between 2020-2022. For this analysis, only data from 2021 and 2022 were used.
Tools Used
Excel (Data Cleaning)
SQL BigQuery (Data Exploration)
Tableau (Visualization)
I created a database schema to better understand the relationships between the 4 tables, to join the tables in Tableau.
Exploring the stores and potential expansion locations
Exploring the products and sales using SQL
The complete SQL code can be found Here
Insights
1. Where should Maven Toys expand to?
Maven Toys has 4 location/store types: Airport, Commercial, Downtown, and Residential. However not all cities have all 4 of the location types, leaving room for expansion into new cities and/or location types.
The analysis shows that Maven’s top 2 stores are located at Mexico City and Guadalajara International Airport, accounting for 7% of total sales. And airport locations do almost 2 times as well as other types of locations.
Cancun and Tijuana international airports are top choices for expansion in 2024. They are among the top airports in terms of passenger traffic and currently do not have any Maven Toy stores. They also boast an impressive 36% and 27% yoy growth in passenger traffic respectively.
2. How many stores should they open?
Maven Toys has been opening stores for the past 30+ years, although they have only opened stores during 19 of those years. On average (only considering the 19 years in which stores were opened), 2.6 stores have been opened per year. Opening 2 stores would be in line with this trend.
3. Which products will perform best at the new location(s)?
Since the new stores will be airport stores, we need to determine which products do best at this type of location. Airport location sales data shows that these products (with above-average sales) are the top performers at existing airport stores.
Lego Bricks, the top product, has been skyrocketing in sales. While Colorbuds has been plummeting in the same period.
The new stores can expect to see $273K (Cancun) and $111K (Tijuana) in sales based on an average 0.9% conversion rate of airport passengers to product sales.
Recommendations
Open two Stores: Cancun and Tijuana International Airports are fast-growing and busy airports, based on passenger traffic. These would work well since existing airport stores are top performers. We expect sales at the Cancun and Tijuana stores to be $273K and $111K, respectively.
Display Top Products: Since space is at a premium at airport stores only stock products with above-average sales within each category (based on top performers from existing airport stores):
Toys - Lego Bricks, Action Figure, Animal Figures
Arts & Crafts - Magic Sand, Kids Makeup Kit
Electronics - Colorbuds, Gamer Headphones
Games - Glass Marbles, Rubik's Cube, Deck of Cards
Sports & Outdoors - Nerf gun, Splash Balls, Dart Gun
Increase Sales: Colorbuds have been steadily decreasing in sales over time. This could be due to a variety of factors which would need further investigation. Meanwhile, they should be displayed prominently in stores to see if sales improve.
Increase Profitability: Lego Bricks is the top seller but has among the lowest profit ratios of all the products. Since it's becoming a popular product consider raising the price, avoiding markdowns, and working with suppliers to reduce product costs.
Data Limitations
- The Maven Toys data is not comprehensive as it only covers one full year (2022) and partial data for 2023 (up to September).
- Airport passenger traffic data is only available up to 2022.
- To better analyze current trends, obtaining updated Maven Toys data beyond September 2023 and acquiring 2023 airport passenger traffic data would be necessary.
Data Sources