Axon Classic Cars BI Solution

Problem Statement:

A small company, Axon, a retailer selling classic cars, faces issues in managing and analyzing its sales data. The sales team is struggling to make sense of the data and they do not have a centralized system to manage and analyze the data. The management cannot get accurate and up-to-date sales reports, which is affecting the decision-making process.

Purpose of the Project:

The purpose of this project is to fix this issue by using tools (like Microsoft PowerBI and SQL) to create a system that manages and studies their sales data better. This capstone project aims to design and implement a BI solution using PowerBI and SQL to help the company manage and analyse its sales data easily and make effective decisions.

Project Objectives:

• Efficiency Improvement: Enhance the efficiency of sales data management by streamlining data integration and cleansing processes.
• Insightful Reporting: Provide clear, user-friendly reports and dashboards that offer valuable insights, aiding both the sales team and management in understanding and leveraging sales data effectively.
• Data-Driven Decisions: Empower decision-making by utilizing advanced analytics to extract hidden patterns and trends, enabling Axon to make informed, proactive decisions.
• Enhanced Performance: Improve overall company performance by leveraging accurate and timely sales data, optimizing resource allocation, and staying aligned with market trends.
• User Adoption: Ensure the BI solution is user-friendly and easily accessible, promoting adoption among the sales team and management for sustained use and success.

Steps followed to complete the project objectives:

1. Data collection and integration: The MySQL database is the primary source of data. The MySQL is connected with Power BI for further analysis of the data set. The integration of MySQL and Power BI also ensures that the datasets in Power BI get updated whenever there is a change in the database in MySQL.

2. Data Cleaning and Transformation: In the process of Data Cleaning and Transformation within Power BI, a systematic approach was employed to enhance the quality and consistency of the dataset across multiple tables. Duplicate values are eliminated, missing values are handled, and data consistency is ensured across all the tables

3. Data Modeling: Data modeling plays a pivotal role in structuring and organizing the dataset for effective analysis and visualization within Power BI. The table relationships were examined, and missing relationships were created in the data model.

4. Data Analysis using DAX: Data Analysis Expressions (DAX) within PowerBI for in-depth analysis of sales data. DAX measures are used to derive insights, trends, and performance indicators for better decision-making by Axon management.

5. Dashboard and Report Creation in Power BI: Interactive and intuitive dashboards were created for the sales and management team. The comprehensive reports will provide real-time insights into sales performance.

6. Deployment in Power BI Service: Deploy the BI solution encompassing dashboards, reports, and analytical insights. Ensure user-friendly interfaces for easy adoption by the sales team and management.

Pages in the dashboard:

• Executive Dashboard: The Executive Dashboard offers a comprehensive overview of key performance indicators, including total revenue, profit, order metrics, and cancel rates.
• Orders Map: The Orders Map enriches the understanding of global order distribution, allowing users to filter views continent-wise.
• Product Details: The Product Details dashboard helps stakeholders with essential information about product quantity, cost, and pricing.
• Customer Details: The Customer Details dashboard focuses on customer-centric insights, from overall customer count to individual customer performance.
• Employee & Office: The Employee & Office dashboard gives insights about workforce and office-related metrics, from employee count by job title to top performers based on sales made.
• Payments: The Payments dashboard ensures a thorough examination of financial transactions, showcasing payment amounts over time and detailed customer payment summaries.

Key Insights:

1. Monthly Order and Revenue Insights:

   • Maximum orders are placed in the month of November for two consecutive years.
   • November also stands out as the month generating the highest revenue, indicating a positive correlation between order volume and revenue. The revenue trendline is upward, suggesting sustained growth.

2. Order Cancel Rate:

   • The order cancel rate is observed to be at a low 1.8%, reflecting effective order management and a low incidence of cancellations.

3. Category-wise Order Distribution:

   • Classic car category receives the maximum number of orders, showcasing its popularity among customers.
   • The Trains category records the least number of orders, highlighting potential areas for product promotion or re-evaluation.

4. Most Ordered and Cancelled Products:

   • The most ordered product is identified as the "1992 Ferrari 360 Spider red."
   • The most cancelled product is the "1940 Ford Delivery Sedan," indicating the requirement of closer examination of factors contributing to cancellations.

5. Customer Demographics:

   • The highest number of customers is from the USA.

6. Top Ordering and Revenue-Generating Channels:

   • "Euro + Shopping channel" emerges as a significant contributor, placing the maximum number of orders and generating the highest revenue.
   • The company can provide incentives or discounts for such customers to build loyalty and maximize returns.

7. Sales Representative Assignment:

   • Out of the total customer base, 98 customers have a sales representative assigned, while 28 customers do not.
   • Strategically assigning sales representatives to more customers may enhance customer engagement and satisfaction.

8. Employee and Office Insights:

   • The company employs a total of 23 individuals, with a maximum presence in the San Francisco office.
   • Paris office records the maximum orders thus acting as a significant revenue generator.

9. Top Performing Employee and Incentive Opportunities:

   • Gerard Hernandez stands out as the top-performing employee, making a total of 43 sales.
   • Recognizing and incentivizing top performers like Gerard can motivate the team and improve overall sales performance.

10. Payment Trends:

    • The maximum amount of payments is received in November and December.
    • Understanding payment trends helps in optimizing financial strategies and managing cash flow effectively.