Analyzing the Impact of Car Features on Price and Profitability (excel project)

Discover the correlation between. Explore the relationship between features such as technology, performance, engine, and fuel efficiency, and their influence on pricing strategies and overall profitability. Unlock the potential of data-driven analysis and enhance your understanding of the automotive market with this insightful Excel project because we will Analyze the Impact of Car Features on Price and Profitability (trainity Excel project).

Analyzing the Impact of Car Features on Price and Profitability(excel project)

Project description

The automotive industry has been rapidly evolving over the past few decades, with a growing focus on fuel efficiency, environmental sustainability, and technological innovation. With increasing competition among manufacturers and a changing consumer landscape, it has become more important than ever to understand the factors that drive consumer demand for cars.

This problem could be approached by analyzing the relationship between a car's features, market category, and pricing, and identifying which features and categories are most popular among consumers and most profitable for the manufacturer. By using data analysis techniques such as regression analysis and market segmentation, the manufacturer could develop a pricing strategy that balances consumer demand with profitability, and identify which product features to focus on in future product development efforts. This could help the manufacturer improve its competitiveness in the market and increase its profitability over time.

Project Problem:

the problem of the project "Analyzing the Impact of Car Features on Price and Profitability " is to Investigate the relationship between a car's features and its popularity: By examining the popularity variable in the dataset, a data analyst could identify which features are most popular among consumers and how they affect a car's popularity. This could help manufacturers make informed decisions about product development and marketing.

Predicting the price of a car based on its features and market category: By using the various features and market category variables in the dataset, a data analyst could develop a model to predict the price of a car. This could help manufacturers and consumers understand how different features affect the price of a car and make informed decisions about pricing and purchasing.

Overall, this dataset could be a valuable resource for data analysts interested in exploring various aspects of the automotive industry and could provide insights that could inform decisions related to product development, marketing, and pricing.

I have used the given data set of cars. First of all, I understand the data and then clean up the data. For data cleanup, I removed blank cell rows and duplicated rows.

Data Set: https://drive.google.com/file/d/1HgHNOQx_wKnvApgBQ5DuKS1KlxmkDWPc/view

Approach:

For analytics purposes, I have used MS-excel to perform various functions like pivot table, graph, chart regression, and all.

Because it is easy to use and can make a good impressive dashboard.

Tech-Stack Used:

Ms excel and ms word

Tasks: Analysis

Before diving into the analysis of the given dataset, it is important to perform thorough data cleaning to ensure accurate and reliable results. You need to build an interactive dashboard in Excel from the tasks given below:

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

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.

My analysis file:

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

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.

Result:If engine power increases price will also increase so we can both have a positive relationship between them.

Insight Required: Which car features are most important in determining a car's price?

Task 3: Use regression analysis to identify the variables that have the strongest relationship with a car's price. Then create a bar chart that shows the coefficient values for each variable to visualize their relative importance.

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

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 the manufacturer and the average price.

Result: Bugatti has the highest average price.

My analysis file:

Insight Required: What is the relationship between fuel efficiency and the number of cylinders in a car's engine?

Task 5.A: 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.
Task 5.B: Calculate the correlation coefficient between the number of cylinders and highway MPG to quantify the strength and direction of the relationship.

Result: As we can see here if the no of cylinders increases highway mpg will decreases so we can say that both have a negative relationship between them.

My analysis file:

Building the Dashboard:

Now for the Next portion of the Project, you need to create the Interactive Dashboard.

Use filters and slicers to make the chart interactive. The client has requested these questions given below:

Task 1: How does the distribution of car prices vary by brand and body style?

Hints: Stacked column chart to show the distribution of car prices by brand and body style. Use filters and slicers to make the chart interactive. Calculate the total MSRP for each brand and body style using SUMIF or Pivot Tables.

Result: Chevrolet has the highest price distribution

Task 2: Which car brands have the highest and lowest average MSRPs, and how does this vary by body style?

Hints: Clustered column chart to compare the average MSRPs across different car brands and body styles. Calculate the average MSRP for each brand and body style using AVERAGEIF or Pivot Tables.

Result: Bugatti has the highest MSRP and Plymouth has the lowest Average MSRP

Task 3: How do the different features such as transmission type affect the MSRP, and how does this vary by body style?

Hints: Scatter plot chart to visualize the relationship between MSRP and transmission type, with different symbols for each body style. Calculate the average MSRP for each combination of transmission type and body style using AVERAGEIFS or Pivot Tables.

Result: Automatic_manual is the most expensive category and the most popular also.

Task 4: How does the fuel efficiency of cars vary across different body styles and model years?

Hints: Line chart to show the trend of fuel efficiency (MPG) over time for each body style. Calculate the average MPG for each combination of body style and model year using AVERAGEIFS or Pivot Tables.

Result: Over the year fuel efficiency is increasing at a slow speed.

Task 5: How do the car's horsepower, MPG, and price vary across different Brands?

Hints: Bubble chart to visualize the relationship between horsepower, MPG, and price across different car brands. Assign different colors to each brand and label the bubbles with the car model name. Calculate the average horsepower, MPG, and MSRP for each car brand using AVERAGEIFS or Pivot Tables.

Result: If engine hp increases highway mpg will decrease and the price will also increase.