Analyzing the Impact of Car Features on Price and Profitability 

Click here for Excel Data Set

Over recent decades, the automotive industry has shifted towards prioritizing fuel efficiency, sustainability, and technological innovation. Amidst intensifying competition and evolving consumer preferences, understanding what drives car-buying decisions is crucial. 

In addressing a client's query on optimizing pricing and product development for maximum profitability, our role as Data Analysts is to analyze car features and identify key factors influencing consumer demand. These insights will help car manufacturers and dealers enhance vehicle sales. 

Approach

To kickstart the project, I meticulously reviewed the dataset and outlined the tasks ahead. My initial step involved tidying up the dataset in Excel.

During the data cleaning process, several issues were identified:

• There were no duplication of rows.

• For Null values in the Number of Doors column, I found that there were only two such Car models. So searched for the information online and replaced them with the correct values.

Once the data was cleaned, I used Excel to visualize the data. After reading the data, I conducted some cleaning and replaced certain values. I opted for Excel because it allows me to create attractive dashboards with a variety of charts to clearly visualize trends, patterns, and analysis. The modeling techniques I employed include pivot tables, Power Query, and data visualizations.

Tech-Stack Used

In this project, I employed Microsoft Excel version 2021. Excel emerged as a crucial tool for tasks ranging from data cleaning and analysis to visualization. Utilizing its sorting, filtering, and pivot tables and chart functionalities, I successfully cleaned the dataset and computed the various task requirements.

Microsoft Word was used for creating the project report.

         Resources used:

o   Dataset ‘Analyzing the Impact of Car Features on Price and Profitability’ provided.

Data Analytics Tasks:

●      Task 1.A: Create a pivot table that shows the number of car models in each market category and their corresponding popularity scores.

●      Task 1.B: Create a combo chart that visualizes the relationship between market category and popularity.

Insight Required: How does the popularity of a car model vary across different market categories?

Insight 

We notice that the average popularity of cars, categorized by their Market Category, typically falls between 1200 to 1800, with the exception of Exotic cars, which have the lowest popularity, and Flex Fuel cars, which are the most popular.

● Task 2:  Create a scatter chart that plots engine power on the x-axis and price on the y-axis. Add a trendline to the chart to visualize the relationship between these variables.

Insight Required: What is the relationship between a car's engine power and its price?

Insight

We can see that the relationship is positive, indicated by the positive slope of the trendline. This makes sense because higher Engine HP necessitates a more intricate level of design and engineering, along with more expensive sub-parts. Additionally, cars with higher Engine HP are typically Performance cars.

● Task 4.A: Create a pivot table that shows the average price of cars for each manufacturer.

●Task 4.B: Create a bar chart or a horizontal stacked bar chart that visualizes the relationship between manufacturer and average price.

Insight Required: How does the average price of a car vary across different manufacturers?

Insight

From the tables and graphs provided, it's evident that the Bugatti brand leads in terms of the highest-priced cars, followed by Maybach, Rolls-Royce, Lamborghini, and others. These brands are renowned for their high-performance and luxury vehicles.

● Task 5: Create a scatter plot with the number of cylinders on the x-axis and highway MPG on the y-axis. Then create a trendline on the scatter plot to visually estimate the slope of the relationship and assess its significance.

Insight

It's noticeable that the relationship between highway MPG and Engine Cylinders displays a negative slope. Additionally, the correlation coefficient is negative, with a value of -0.610338. This is expected because as the number of Engine Cylinders increases, the fuel consumption also increases, leading to a decrease in highway MPG.

DASHBOARD :

Result: 

This project has underscored the significance of Data Analytics in analyzing car features, offering invaluable insights crucial for making data-driven decisions. It has provided insights into various aspects such as the impact of features on car prices and the relationship between Engine Cylinders and fuel efficiency. Additionally, I gained hands-on experience in data preprocessing tasks like Data Cleaning and Feature Engineering, which can be effectively communicated to stakeholders as needed.