The objective of this project is to analyze the Adidas sales database for the year 2020 and 2021 and identify key insights to help improve sales performance and optimize business strategies. By analyzing the sales data, we aim to understand factors influencing sales, identify trends, and uncover opportunities for growth. The analysis will be conducted using Excel to provide an interactive and insightful dashboard.
The Adidas sales database contains the following columns:
• Retailer: Represents the business or individual that sells Adidas products directly to consumers.
• Retailer ID: A unique identifier assigned to each retailer in the dataset.
• Invoice Date: The date when a particular invoice or sales transaction took place.
• Region: Refers to a specific geographical area or district where the sales activity or retail operations occur.
• State: Represents a specific administrative division or territory within a country.
• City: Refers to an urban area or municipality where the sales activity or retail operations are conducted.
• Gender Type: Categorization of individuals based on their gender, such as male or female.
• Product Category: Represents the classification or grouping of Adidas products.
• Price per Unit: The cost or price associated with a single unit of a product.
• Units Sold: The quantity or number of units of a particular product sold during a specific sales transaction.
• Total Sales: The overall revenue generated from the sales transactions.
• Operating Profit: The profit earned by the retailer from its normal business operations.
• Operating Margin: A financial metric that indicates the profitability and efficiency of a retailer's operations.
• Sales Method: The approach or channel used by the retailer to sell its products or services.
The analysis was conducted using Excel, following a series of steps outlined below:
-
Data Cleaning:
- The initial dataset was assessed for consistency, reliability, completeness, duplicates, and correct spelling.
- Verification checks were performed to ensure the dataset's integrity and cleanliness.
-
Data Transformation:
- The dataset was transformed to enhance its suitability for analysis.
- The data was structured in a table format for better organization and analysis.
- Date details such as Weekday, Month, Quarter, and Year were extracted into separate columns using Excel functions like TEXT and YEAR.
- Calculations were applied to derive additional insights.
-
Data Analysis:
- Excel's pivot table functionality was utilized to perform the analysis.
- Pivot table calculations and aggregations were employed to derive meaningful insights from the data.
-
Data Visualization:
- The analysis results were visualized using various Excel charts, including pivot table charts.
- Slicers were implemented to provide interactivity and enhance the dashboard's usability.
Overall, the Excel analysis and visualization process facilitated a thorough examination of the data, enabling the extraction of valuable insights and presenting them in an interactive and visually appealing manner.
Calculate and visualize the overall sales, profit, average price per unit, and total units sold.
The overall sales for 2020 and 2021 combined is $899,902,125.
The overall profit generated within this time frame is $332,134,761.45.
The total units sold amount to 2,478,861 units.
The average price per unit sold is $0.18.
Analyze the total profit generated by each retailer and identify the top-performing retailers.
The top performing retailers are West Gear with an overall profit of $85,667,873.18 and Foot Locker with an overall profit of $80,722,124.81
Track the trend of sales over time at different levels such as year, quarter, month, and day.
There was an massive increase in sales from 2020 to 2021. Sales increased by 294% in 2021, this could be that the sales in 2020 was negatively impacted by the covid 19 pandemic. 2020 recorded a total sales of $182,080,675 while 2021 recorded a total sales of $717,821,450. There was notable upward trajectory in sales from Q1 which peaks in Q3 and then starts declining. This means more sales happen in the summer. Q3 recorded a total sales of $265,308,354. July is the top performing Month with a total of $95,480,694 sales, while the top performing day of the week is Fridays with a total of $146,683,099 sales. This means most customers make more purchases which in preparation for the weekend.
Examine the distribution of sales across different product categories and identify the top-selling categories.
The top performing product category is Street Footwear the with a total sales of $336,829,057, this accounts for 37% of the overall sales.
Analyze the total number of units sold by product category and gender type to understand customer preferences.
Male customers perfer the Street Footwear product category and bought a total of 593,320 units while the female customers prefer the Apparel Product category and bought a total of 433,827 units
Determine the most effective sales method in generating sales and compare the performance of different sales channels.
In-store sales method generated the highest sales of $356,643,750, indicating customers' preference for physically seeing the items.
Online sales method generated the lowest sales of $247,672,882.
Explore how sales data varies by region, state, and city to identify potential areas for improvement.
The top performing Region is West with a total sales of $269,943,182, while the least performing Region Midwest is with a total sales of $135,800,459
The top performing State is New York with a total sales of $64,229,039, while the least performing State is Nebraska with a total sales of
$5,929,038
The top performing City is Charleston with a total sales of $39,974,797, while the least performing City is Omaha with a total sales of $5,929,038.
States and Cities with less than 8 million overall sales needs improvement.
Identify the top 5 performing cities based on profit and gain insights into their sales strategies.
The top 5 performing Cities and their top performing sales methods are; Charleston - 15,607,190 (Online)
New York - 13,899,973 (Outlet)
Miami - 12,168,619 (In-store)
Portland - 10,760,799 (In-store)
San Francisco - 10,256,250 (Outlet)
Create a detailed page focusing on product categories and enable drill-through filtering to allow users to view specific information about selected categories.
The dashboard for the product catgories is on the 5th tab of the overall dashboard.
Click here to download the full interactive Excel dashboard and analysis.
In conclusion, the analysis of the Adidas sales has provided valuable insights into the performance of the business. Here are the key findings:
-
Significant Sales Increase: There was a massive increase in sales from 2020 to 2021, with sales soaring by 294% in 2021. This substantial growth can be attributed to the recovery from the negative impact of the COVID-19 pandemic on sales in 2020. In 2020, the total sales amounted to $182,080,675, while in 2021, the total sales reached $717,821,450.
-
Top Performing Retailers: West Gear and Foot Locker emerged as the top-performing retailers, generating profits of $85,667,873.18 and $80,722,124.81, respectively. Strengthening partnerships with these retailers can lead to further growth and success.
-
Seasonal Sales Pattern: The analysis indicates a notable upward trajectory in sales throughout the year, with sales peaking in Q3 and then gradually declining. This pattern suggests that more sales occur during the summer season. Specifically, Q3 recorded the highest sales, reaching a total of $265,308,354. Understanding this seasonal trend can help in planning marketing campaigns and inventory management.
-
Product Category Analysis: Street Footwear emerged as the top-selling product category, contributing $336,829,057 in sales, accounting for 37% of the overall sales. Understanding customer preferences, particularly in terms of gender, is crucial for targeting marketing efforts effectively.
-
Sales Method Effectiveness: In-store sales proved to be the most effective method, generating sales worth $356,643,750. On the other hand, online sales performed comparatively lower, with total sales of $247,672,882. It is important to enhance the online sales platform and implement targeted digital marketing strategies to boost online sales.
-
Regional and City Analysis: The West region outperformed other regions, with total sales of $269,943,182. Among cities, Charleston emerged as the top performer, generating sales worth $39,974,797. States and cities with sales below $8 million require focused improvement efforts to tap into their potential.
-
Capitalize on the growth trend by investing in marketing, product innovation, and customer engagement strategies.
-
Strengthen partnerships with top-performing retailers to optimize product placement and explore co-marketing opportunities.
-
Implement strategies to optimize operations in the summer, considering the significance in sales. This may include increased staffing, special promotions, or tailored product offerings.
-
Enhance the online sales platform, improve user experience, and invest in targeted digital marketing campaigns to boost online sales.
-
Consider expanding operations in high-performing regions and target cities with growth potential. Also focus on leveraging the success of high-performing Regions,States and Cities, to identify and replicate strategies that drove exceptional sales growth and implement these strategies on low performing locations.
-
Gather and analyze customer feedback and preferences to better understand their needs, tailor marketing messages, and optimize product offerings.
-
Continuously monitor sales performance, track key performance indicators, and gather customer insights to optimize marketing messages and product offerings.
By implementing these recommendations, businesses can capitalize on their strengths, address areas of improvement, and drive sustainable sales growth, ultimately achieving long-term success in the market.