AUTOMOBILE SALES DATA ANALYTICS

🚗 Project Showcase: Analysing Automobile Demand Trends (Q1 2018 – Q2 2020)

This project demonstrates practical business analysis and data analytics skills through the exploration of automobile demand patterns over nine consecutive quarters, from Q1 2018 to Q2 2020.

🔍 Project Objectives & Approach:

• Define clear business questions and analytical objectives to understand demand fluctuations.

• Use Power BI for interactive visualisations to uncover meaningful trends and market behaviours.

• Leverage advanced analytical tools, including Python and DAX to extract deeper insights from the data.

📦 Final Deliverables: 

✅ Defined Business Rules & Scope of Analysis

✅ Data Cleaning & Preparation

✅ Power BI Dashboard (Visual Storytelling & Insights)

✅ Advanced Analytics using Python & DAX

✅ Actionable Recommendations (Data-driven strategy formulation)

This end-to-end analysis highlights the ability to turn raw data into strategic insights that support informed decision-making in the automotive industry.

Data Set: Auto Sales Dataset

Source: https://www.kaggle.com/datasets/ddosad/auto-sales-data/data

1. Objective

   1. Top sales performance by product and country

   2. Product Line pricing analysis

   3. Sales and product demand forecasting

   4. Customer buying behaviour by country

   5. Factors influencing sales

   
   

2. Data Processing

   

   

   
   

3. Power BI Dashboard

3.1) OBJECTIVE: Overview of Revenue

• Top countries with the best sales performance

• Product line with the best sales performance

FINDING

• USA doubles on sales countries like Spain and France

• NYC and San Rafael contribute in big proportion to the sales in the USA

• Classic cars, trucks and buses are the most sold product line

3.2) OBJECTIVE: Product Line Pricing Analysis

• Identify where selling prices exceed factory prices.

• Highlight high-value markets and products.

• Develop strategies to leverage successful markets.

FINDING	RECOMMENDATION
• Classic Car products sell above factory prices overall. • Singapore can sell all product lines above factory prices. • Italy sells all product lines below factory prices.	• Increase targets for the Classic Car • Investigate sales strategies in Singapore and Italy.

Product Line Pricing Analysis

3.3) OBJECTIVE:  Customer Behaviour

• Identify the cluster based on the quantity ordered and the sum of sales

• Impact of orders on total sales

FINDING

RECOMMENDATION

• There are 3 potential clusters. Cluster 3 (Orange) customers/products are high performers in both volume and revenue. Cluster 2 (Blue) might include premium or high-priced items (high sales, low quantity). Cluster 1(Dark Blue) could represent bulk buyers or discounted categories (lower price, higher quantity). • Orders with sold units over 60 are the ones causing the sales to be over $4,000.  • Small deal sizes are sold in large quantities • Average price per unit is $101.1

• Apply country filtering to identify regional strengths and weaknesses across clusters. • Prioritise high-performing clusters in specific countries for targeted marketing and sales strategies.

Customer Behaviour and Product Segmentation

3.4) OBJECTIVE:  Sales Forecasting

• Forecast next quarter for sales strategy.

• Identify seasonal sales trends in each country and overall.

FINDING	RECOMMENDATION
• Some countries lack consistent sales data from Q1 2018 to Q2 2020. • Fluctuating graphs due to inconsistent data. • Sales generally perform well in Q4.	Use forecasts to set sales volume targets for each country.

Sales Trend Analysis and Forecast for Q3 2020 Using Regression Modelling

3.5) OBJECTIVE:  Sales and Product Projection

• Analyse trends in products and countries by examining revenue and item sales relationships using linear regression.

• Predict the number of items needed to meet sales targets for effective marketing planning.

Sales and Product Projection by Product Line and Country

📦 Sales and Product Projection by Product Line and Country:

This interactive dashboard presents a predictive analysis of the relationship between sales revenue and quantity ordered, using a linear regression model.

A scatter plot visualises historical data, highlighting a clear upward trend between sales and quantity ordered.

• The regression model allows users to input expected sales values and forecast the projected quantity.

• Users can filter and sort by Product Line and Country, enabling a more granular analysis tailored to specific markets or product segments.

🧠 Business Value:

This tool supports data-driven decision-making by:

• Projecting demand for each product line across different regions.

• Identifying high-performing products in key markets.

• Enabling inventory and supply chain teams to align production plans with expected demand.

4. Advanced Analytics using Python & DAX

   Python: Visualising Profitability by Product Line

   This Python visualisation uses Matplotlib to display total profit/loss (diff_price) across product lines. Bars turn red when sales fall below zero, offering a clear visual cue for underperforming categories. A great example of conditional formatting in data storytelling.

   

   CODE:

DAX(Data Analysis Expressions):

To calculate the slope and intercept of a simple linear regression model within Power BI — used to predict sales quantity based on revenue performance.

🧠 Use Case

These custom DAX measures allow Power BI to:

• Plot a trendline without using built-in analytics

• Forecast demand for given sales inputs

• Dynamically calculate expected outcomes using linear regression logic

Combined with slicers (e.g. by Product Line and Country), this dashboard empowers decision-makers to explore regional or category-level performance and forecast accordingly.

To predict quantity from a given expected sales value:

quantity_predicting = [Slope]*sales_expect[sales_expect Value]+[Intercept_B]

✅ Explanation:

• Slope (m) — Shows how much quantity increases per unit of sales

• Intercept_B (b) — The base quantity when sales = 0

• sales_expect — The input or selected sales value (from slicer or parameter)

  
  

DAX for Slope:

DAX for Intercept B:

4. Recommendations

Focus Area	Recommendation
High-Impact Product Lines	Focus on Classic Cars in top-performing countries (e.g., USA, Germany) to maximise revenue.
Sales Force Optimisation	Reallocate or retrain sales reps in underperforming regions (e.g., UK); review commission structures.
Forecast-Driven Targeting	Use regression models to set quarterly sales goals and compare predictions with actual performance.
Dynamic Pricing Strategy	Analyse differences between MSRP and actual price to adjust pricing for improved profitability.
Customer Segmentation	Target volume buyers vs. premium buyers with personalised campaigns, based on sales clusters.
Country-Specific Strategy	Address inconsistent sales trends with regionally tailored marketing and data collection improvement.

✨ Final Thoughts

This project showcases how powerful insights can emerge when data analytics meets business intelligence. By combining tools like Power BI, Python, and DAX, we transformed raw sales data into actionable strategies that can guide marketing, sales forecasting, and operational decision-making. Whether it's optimising product focus, enhancing regional strategies, or refining pricing models, each layer of analysis drives the business toward smarter, data-informed outcomes. This end-to-end journey not only reflects technical proficiency but also highlights the true impact of analytics in navigating complex, real-world business challenges in the automotive sector.