Data analysis Facilitator

THE INSIGHT FROM THE DASHBOARD

This report presents the results of analyzing the sales data of a clothing company from 2020 to 2021. The main findings are:

• The total sales were $899 million, the total profit was $332 million, and the total quantity sold was 2.48 million.
• The average profit per unit sold was $134.
• The West region had the highest sales, followed by the North East, the South East, the South, and the Midwest.
• The most popular sales method was in-store, followed by outlet and online.
• The top 10 states by sales were New York, California, Florida, Texas, Illinois, Pennsylvania, Ohio, Georgia, Michigan, and New Jersey.
• The sales increased across the years and the quarters, with the peak in the third quarter of each year.
• The price dropped in the fourth quarter of 2019 and 2020, possibly due to seasonal discounts or promotions.
• The top retailers were Walmart, Foot Locker, Kohi, Sports Direct, West Gear, and Amazon.
• The most profitable product category was Men’s Apparel, while the least profitable was Women’s Apparel.

Recommendations

Based on the analysis, some possible recommendations are:

• Increase marketing and distribution efforts in the Midwest region, which had the lowest sales.
• Explore ways to improve online sales, which had the lowest share of sales methods.
• Invest more in Women’s Apparel products, which had the lowest profit margin.
• Maintain or increase the price level in the fourth quarter, unless there is a clear benefit from lowering it.
• Reward or partner with the top retailers, especially Walmart and Foot Locker, which had a significant share of sales.

Conclusion

The report has provided an overview of the sales performance of the clothing company from 2020 to 2021. The report has identified some strengths and weaknesses of the company’s sales strategy and has suggested some recommendations for improvement. The report hopes to help the company make informed decisions and achieve its sales goals.

Data Preparation

Before conducting the analysis, the following steps were taken to prepare the data:

• Removed a few duplicate records that could affect the accuracy of the results.
• Used Power Query to create some new columns based on the existing data, such as region, sales method, product category, etc.
• Applied some Excel formulas to perform some conditional calculations in the new columns, such as profit margin, price change, etc.
• Created PivotTable and from there created the charts.

These steps helped to transform the raw data into a more structured and meaningful format that enabled the visualization and analysis of the sales performance.

Skills: data visualization, statistical analysis, predictive analytics.

For more visit: https://github.com/Star-cj/excel_businss_analysis.git