Car Sales Analysis — Data Analysis Project
Excel Data Analysis
Problem Statement
In a scenario where a car dealership aims to optimize its sales performance and profitability, the management seeks to understand the factors influencing sales and identify areas for improvement. The dealership has been experiencing fluctuations in sales volume across different car models and wants to devise effective strategies to enhance overall sales performance.
Dataset Description
The car dealership provided data in an Excel workbook comprising 19 columns and 156 rows of unclean datasets and inconsistent data. The information included in the dataset includes: Manufacturer, Model, Unit Sales, Price, Year Resale Value, Retention %, Retention Value, Engine Size, Horsepower, HP Level, Vehicle Type, Fuel Efficiency, Power Perf Factor, Wheelbase, Width, Length, Curb Weight, Fuel Capacity, Latest Launch.
Tools Used
I used Microsoft Excel for data cleaning, data analysis, and data visualization, and created a dashboard to give a proper summary of the analysis I performed.
Analysis Objectives
This project seeks to answer four major questions:
1. Sales Performance:
- Which car models are the top sellers in terms of unit sales?
- How do sales vary across different manufacturers and vehicle types?
2. Pricing Strategy:
- What is the relationship between the price of vehicles and their sales volume?
- Are there any pricing trends or insights that can inform pricing strategy adjustments?
- How do pricing dynamics impact overall sales revenue and profitability?
Data Analysis Workflow
The data analysis workflow for this project can be structured into several key steps:
NB: This dataset was provided as part of a course I was doing online. It does not reflect real world numbers and the numbers used here are purely for demonstration purposes.
1. Data Collection and Preparation:
I received the car sales dataset in CSV format. I converted it to an Excel Workbook by saving the file as an Excel Workbook. After opening it, I took a few minutes to scan through and understand the data in each column. I autofitted the columns to see the data correctly.
Before starting the analysis process, I had to make sure my data is analysis-ready. To make the data cleaning process more efficient, I formatted the dataset as a table.
Here are the data preparation techniques I employed to ensure data integrity:
Checked for missing values
To check for any missing values, I used the Go to Special feature. You can access it in the Find and Select tab in the Home Panel, then select Go To Special, check the “Blanks” radio button and click OK.
My dataset did not have blanks as indicated below:
This is excellent news because in some cases, blank cells might mean the data is incomplete. When all cells contain values as expected it helps to maintain data completeness and accuracy.
Checked for Consistent Data Formats
I also checked the data formats to ensure they were the right ones to ensure I get the right results.
The three columns Unit Sales, Price, and Year Resale Value were set to General. I changed the data format for Unit Sales to Number and Price and Year Resale Value to Currency.
I also noted the data type for Engine Size, Horsepower, Fuel Efficiency, Power Perf Factor, Wheelbase, Width, Length, Curb Weight, Fuel Capacity was set to “General.” I changed it to “Number” to allow for numerical analysis to be performed on these columns.
I used the filter feature in the table to check through each column for any inconsistencies. I did not find any. There were also no case inconsistencies.
To analyze sales performance, I needed to calculate revenue. I got the revenue by inserting a new column between Price and Year Resale Value.
Revenue = Units Sold * Price
Analysis
Top-Selling Manufacturers and Models
Identify which manufacturers and car models have the highest unit sales and revenue, allowing the dealership to focus on promoting and stocking these popular models.
Price Analysis
I analyzed the relationship between the price of the vehicles and their sales volume to understand pricing elasticity and determine optimal pricing strategies. The dashboard has a slicer that allows you to go through the different Manufacturers and their respective models.
Resale Value and Retention
I evaluated the resale value and Year Resale Value of different car models to assess their long-term appeal to customers and guide inventory management decisions.
Manufacturers and models with high retention rates (Use the slicer to check for the retention rates of different manufacturers and models):
Car Sales Analysis — Insights Summary
The insights below provide answers to the objectives of this project:
a) The total revenue generated was $ 180,171,766,875 for the sale of 8,253,104 units.
b) Ford was the best performing Manufacturer after selling the most units (2,022,635) and generating the most revenue ($45,776,128,865).
c) The F-Series is the most popular car model. It sold 540,561 units.
d) Based on the data, customers tend to buy cars based on brand and not price.
Here is a dashboard:
The dashboard provides a quick summary of the analysis I performed.
Recommendations
The car dealership can use these findings to optimize its sales performance and profitability.
Project Limitations
The dataset presents a few challenges in the analysis process, which hindered me from performing more concise analysis.
· The dataset does not provide geographical details of their customers.
· The dataset does not have dates and time when the sales were made which prevented me from performing a time-series analysis.
Please feel free to offer corrections and recommendations on a better approach to this amazing workflow.
