A REPORT ON CAR SALES DATA ANALYSIS AND
VISUALISATION
Project Submitted to the
MANIPAL ACADEMY OF HIGHER EDUCATION –DUBAI
CAMPUS
In Partial Fulfillment of the Requirement for the Degree of Bachelor of Commerce
SEM VI - BUSINESS ANALYTICS
SANA KHALID (200202049)
RINSHA FIROZ (200202055)
HAROON AHMED (200202034)
SHAHNA JABIN (200202042)
ABDULLA UMAR (200202027)
SUBMITTED TO -
Ms. SHUBHI RATHI
SCHOOL OF BUSINESS
ACADEMY OF HIGHER EDUCATION-DUBAI CAMPUSACADEMIC
CITY, DUBAI - U.A.E.
May 2023
TABLE OF CONTENTS
SL. No. Topic Page No
1. Introduction 3
2. About the Data 3
3. Data Collection & Cleaning 4
4. Pivot Table, Visuals & Dashboard 5-7
Creation
5. Analysis of the Visuals 5-7
6. Suggestions for the Company 8
7. Limitations of the Study 9
8. Conclusion 10
9. Reference 11
INTRODUCTION
Objective: This project aims at studying large set of car sales data from a car
dealershipto draw analysis about the sales, commission and profit earned so that the
company can work to improve these areas in order to maximize revenue in the end.
About the organization: The company we selected is a car dealership. They have
different prices for different car models and car brand. This study will help this
company to classify their revenue and commission earned for various car brand and
car model and the customer’s preference of car.
Source of data: This data has been collected from secondary source, which is internet
from the site https://www.kaggle.com/. Data collected was in raw form and later data
was cleaned using different means in order to make data more organized.
ABOUT THE DATA
The data collected consists of 9 columns and 2500000 rows. The data contains three
variables sales price, commission earned and number of customers. These data are
considered variable because they change with each change of unit. Number of
customers is a discrete variable because it take whole values. Sales price and
commission are continues variables as they can take decimal values as well.
Problems definition: The main problem faced by them are they cannot track the
revenue by salesperson individually and the commission earned by the salesperson
individually. In addition, they cannot identify the commission earned by Car
Company separately. They could not keep a record of the car models purchased by
customers. At the end of the study the company will be able to take major decisions
as to which car model has more demand and need to be sold more and hence do the
marketing accordingly.
DATA CLEANING
The data collected was in raw form and needed modification to convert into an
organized format.
Processes of data cleaning:
The data file was downloaded from google.
The file was saved and named Car Sales Data.
The data was reviewed for any blank, empty raw, or columns by clicking ‘Find
& Select’ in the Editing Group of Home Tab. In the ‘Go-to-Special’ option,
select blanks and click “OK” to run the function. In addition, the data was
free of blank space.
Some columns were deleted from the data as they were not required
The Date column was converted into Short- Date format.
Some of the column headings where changed car make to car model, sales
price to sales and commission earned to commission.
The sales and commission were converted into currency format.
In dashboard background, color was added to make it more visually
appealing.
The column widths were adjusted so that all data are clearly visible.
Added Filter to data.
The headers were filled with a different color so that the titles are
highlighted.
Since all the data was available in a single file, data integration was
not required.
PIVOT TABLE VISUALS AND ANALYSIS
Revenue by Salesperson
100000
90000
80000
70000
60000
50000
40000
30000 Total
20000
10000
0
Aaron Carol Gene Jon Manuel Zoe
Acevedo Young Carroll Harper Graham Sellers
MD
Analysis:
The above chart shows the revenue generated by salespersons.
Mr. Aaron Acevedo created the highest car sales of $92601.
Mrs. Carol Young MD and Mrs. Zoe Sellers had created the least sales during
that year of $24747 and $20906 respectively.
Commission by Car Company
128000000
127500000
127000000
126500000
126000000
125500000 Total
125000000
124500000
124000000
123500000
ALTIMA CIVIC COROLLA F-150 SILVERADO
Analysis:
The above chart shows the commission earned by each car model and
company.
Honda brand made the highest car sale in year 2022 and 2023.
In which Silverado model made the highest sale.
In addition, Corolla made the least sales in the years.
Commission by Salesperson
Zoe Young
Veronica Wong
Sharon Frank
Total
James Mooney
Carmen Collins
Aaron Acosta
0 1000 2000 3000 4000 5000 6000 7000 8000
Analysis:
The above chart shows the commissions earned by salespersons
Mr. Carmen Collins earned the highest commission of $7065.64
And Mr. Aaron Acosta earned the lowest commission of $3771.29
Revenue from Car Models
540000000
538000000
536000000
534000000
532000000
530000000 Total
528000000
526000000
524000000
522000000
Altima Civic Corolla F-150 Silverado
Analysis:
The above chart represents the revenue generated by each car model during
the period October 2022.
Civic model made the least revenue of $528,572,903.
In addition, F-150 made the highest revenue of $ 538175508.
Car Models Purchased by Customers
Altima
Corolla
F-150
Silverado
Analysis:
The above chart represent the number of cars purchased by a customer
Mr. Andrew Patterson purchased two Altima, 2 Corolla, 5 F-150 and 3
Silverado, which brings to 12 cars.
DASHBOARD ON CAR SALES
CAR SALES DASHBOARD
Revenue generated by salesperson Commission Earned by Car Brands Commission earned by Salesperson
Commission by Car Brand
Revenue by Salesperson Commission by Salesperson
128000000
100000 127500000 Zoe Young
127000000
80000
126500000 Veronica Wong
BEST SALESPERSON 60000 126000000
Sharon Frank
40000 125500000 Total
AARON ACEVEDO Total
125000000 Total
20000 James Mooney
124500000
0 124000000 Carmen Collins
Aaron Carol Gene Jon Harper Manuel Zoe Sellers
Acevedo Young MD Carroll Graham 123500000
ALTIMA CIVIC COROLLA F-150 SILVERADO Aaron Acosta
0 2000 4000 6000 8000
Sale Price
10000
Revenue Generated by Each Car Model Types of Car Model Purchased by Customers
10001
10002
MOST SOLD CAR MODEL
10003
HONDA - F150 Revenue from Car Models Car Models Purchased by Customers
540000000
10004
538000000 Car Model 10005
536000000 10006
Altima
534000000
10007
532000000 Civic
Altima
530000000 Total Corolla Corolla Date
528000000 F-150 F-150 All Periods MONTHS
526000000 Silverado 2023
Silverado
524000000 JUN JUL AUG SEP OCT NOV DEC
522000000
Altima Civic Corolla F-150 Silverado
MOST SOLD CAR BRAND
HONDA 2022 has a higher car sales revenue compared to 2023
SUGGESTIONS
The suggestions drawn from the study conducted are mentioned below:
F 150 is the most sold car in the market about 41% of the total sale and has
generated the highest revenue hence the car dealership must focus more on
dealing in F 150 as the demand is expected to increase in the future
Civic is the car model that produces the least revenue therefore the dealership
shall slowly replace some other models which can improve the revenue or focus
on implementing strategies to increase the sale of the car.
The commission earned by Car Company is most earned by Silverado, which is
in second position in the revenue, and most purchased taking some more efforts
can accelerate the growth of the revenue and market share.
The dealership can implement offers like discount so that they are able to
achieve more buyers and hence increase revenue.
Comparing the revenue from car models F 150 produces more revenue and least
revenue is produced by civic, Silverado and Corolla produces the second highest
revenue Altima hold third position. Hence, new strategies must be in order to
increase the revenue by making new customers by more products.
In Commission earned by sales person, Aaron Adams has earned the highest
commission, which is 45000 giving more appreciation through incentives
bonuses can help to increase the commission in future or at least maintain the
same.
The least commission earned is 1350 by Aaron Alaxander MD, giving more
training to help to make more number of sales can motivate to earn more
commission also giving promises of incentives upon hitting high commission
can also motivate them.
LIMITATIONS OF THE STUDY
The limitations faced by the study are listed below:
The data has more than sufficient entries. As using such a big scale of data there
might be errors due to the volume of data.
Data of 2022 and 2023 are only available. If older data were available, it would
help us to have a better understanding of the data.
The accuracy of the data is questionable, as it was not obtained primarily from
the company website instead other online website.
Quantitative measures such as mean, mode, median, mode etc. have not been
included as part of this study, which may give a better idea of the distribution of
data.
CONCLUSION
This study has achieved its objective of categorizing revenue by salesperson,
commission earned by Car Company and salesperson, Different car models and
revenue generated, Car models purchased by the company. Based on this, the
dealership can take better decisions on where to maximize revenue and what new
strategies to implement.
The visuals used in this report helps users to analyze the data at one glance and helps
to save time by not going through the large set of data.
It has been concluded that F 150 is the most selling and revenue earned car model.
Therefore, the dealership must look forward to continue the sale of certain cars such
as civic as they are least sold. The company must focus on pertaining to the customer
requirements while selling the cars by customizing the facilities they need such as full
option car etc.
REFERENCES
https://www.kaggle.com/datasets/suraj520/car-sales-data
https://www.honda.ae/en/cars/new-cars/civic/
https://www.toyota.ae/en/
https://www.caranddriver.com/ford/f-150
https://www.ablebits.com/office-addins-blog/select-highlight-blank-
cellsexcel/#:~:text=On%20the%20Home%20tab%2C%20in,empty%
20cells%20in%20the%20range.
https://www.excel-easy.com/data-analysis/pivot-tables.html
https://support.microsoft.com/en-us/office/use-slicers-to-filter-data- 249f966b-a9d5-
4b0f- b31a12651785d29d#:~:text=Click%20anywhere%20in%20the%20ta
ble%20or%20PivotTable%20for%20which%20you,every%20field%
20that%20you%20selected.
