This project aims to explore how various car features affect pricing and profitability in the automotive industry.
The focus is on identifying key attributes that influence a car's MSRP (Manufacturer's Suggested Retail Price) and understanding the relationship between these attributes, market categories, and consumer preferences.
The analysis is performed using a dataset containing specifications of over 11,000 car models, provided by Cooper Union and sourced from Kaggle.
The analysis was conducted using Microsoft Excel, employing advanced and statistical concepts to derive meaningful insights.
- Raw dataset. (Dataset.xlsx - Car_data)
- Analyzed Excel file. (Data Analysis Project File)
- Logo used.
- Report Presentation. (PPT + PDF)
- Dashboard Image.
- Screenshot of the findings.
The dataset contains 16 variables for 11,159 car models. The variables include:
- Make: Brand of the car
- Model: Specific model of the car
- Year: Year of manufacture
- Engine Fuel Type: Type of fuel used (e.g., gasoline, diesel)
- Engine HP: Horsepower of the engine
- Engine Cylinders: Number of cylinders in the engine
- Transmission Type: Type of transmission (automatic or manual)
- Driven Wheels: Type of driven wheels (front, rear, or all)
- Number of Doors: Number of doors in the car
- Market Category: Market segment the car belongs to (e.g., Luxury, Performance)
- Vehicle Size: Size of the car (Compact, Midsize, Large)
- Vehicle Style: Body style of the car (e.g., Sedan, Coupe)
- Highway MPG: Estimated miles per gallon on the highway
- City MPG: Estimated miles per gallon in the city
- Popularity: Popularity ranking based on online views
- MSRP: Manufacturer’s Suggested Retail Price
The analysis was performed in Excel and follows these steps:
-
Data Cleaning: Missing values were handled, and the dataset was formatted for consistency.
-
Exploratory Data Analysis (EDA): Key statistics and distributions were examined.
-
Statistical Analysis:
- Regression Analysis: Identified car features most strongly influencing the MSRP.
- Correlation Analysis: Examined relationships between variables, such as engine power and fuel efficiency.
- Pivot Tables & Charts: Used to summarize data and visualize trends.
-
Market Segmentation: Analyzed how different market categories impact car popularity and pricing.
-
Dashboard Creation: An interactive Excel dashboard was developed to visualize key findings.
- Popularity vs. Market Category:
The "Flex Fuel" market category is the most popular, with "Crossover" closely following, although the latter has more car models.
- Engine Power and Price:
There is a strong positive correlation between engine horsepower and car price; as horsepower increases, the MSRP also tends to rise.
- Influential Features on Price:
Regression analysis revealed that engine cylinders, engine HP, and fuel type are the most significant factors affecting car price.
- Manufacturer Pricing:
Luxury brands like Bugatti, Ferrari, and Rolls-Royce have the highest average MSRPs, while economy brands like Hyundai and Kia have the lowest.
- Fuel Efficiency:
Cars with fewer engine cylinders generally have better fuel efficiency, as measured by highway MPG.
This project provided valuable insights into how various car features influence pricing and consumer preferences in the automotive industry. The findings can help manufacturers optimize their pricing strategies and product development to enhance profitability while catering to market demands.
-
Excel: For data analysis, visualization, and statistical computations.
-
PowerPoint: For presenting the findings and insights.
-
Loom Video: Video presentation.
-
Loom Video Presentation:-
https://www.loom.com/share/f928689162ee42d89d15f2b08f112ccb -
Project File Copy on Google Sheets:- https://docs.google.com/spreadsheets/d/1mvfT177sCRViMZlNBa-ZJeYPudch8oCk/edit?gid=1846981398#gid=1846981398
-
Mainak Mukherjee
-
Email: subha.mainak@gmail.com
-
Linkedin: www.linkedin.com/in/mainak8
Connect with me on LinkedIn or check out my GitHub for more projects and insights.
- Project Impact:
This project provided significant insights into how car manufacturers can leverage data to enhance their pricing strategies and product development. By analyzing the relationship between various car features and their impact on MSRP, manufacturers can better understand consumer preferences and market dynamics. The findings from this analysis can inform strategies that balance consumer demand with profitability, helping companies stay competitive in a rapidly evolving industry.
- Learning Experience:
Working on this project was an enriching experience that deepened my understanding of data analysis and its application in the automotive industry. Some key takeaways include:
Advanced Excel Techniques: This project reinforced my proficiency in Excel, especially in using pivot tables, regression analysis, and interactive dashboard creation. I also honed my ability to manipulate large datasets, ensuring accuracy and reliability in the analysis.
Statistical Analysis: I gained deeper insights into the statistical relationships between variables, such as the correlation between engine power and pricing, and the importance of certain features in determining car prices.
Data-Driven Decision Making: The project highlighted the importance of data-driven decision-making in a competitive business environment. It underscored how data can reveal hidden patterns and trends that are crucial for strategic planning.
Presentation and Communication Skills: Summarizing complex analyses into clear, actionable insights and presenting them effectively through reports and presentations was a key learning experience. It emphasized the importance of conveying data insights in a way that is accessible to both technical and non-technical stakeholders.
This project has not only solidified my technical skills but also enhanced my ability to apply them in a practical, business-oriented context. I am excited to bring these skills to future projects and continue growing as a data analyst.