Car sales of different model from 2019 to 2023
Car sales is something that I seen a lot of people analysing and I don't feel satisfied with the little assignment I did in the IBM. So I decide to dig deeper and analyze more with bigger dataset together with utilising as best as I could the 90 days trial of using Congnos Analytic provided by IBM and Coursera. Hence, this is the project of my own that I found, optimize and analyze more.
01
Data collection
This is actually one of the assignment in the IBM data analyst training program but the dataset is actually very small and already being altered. Hence, I decided to find the actual dataset so that I can utilize, visualize and analyze the data even deeper. Thats where I got the dataset from IBM website itself. It's in zip files hence you need to extract it first using winrar or 7zip then open the "AU_Sales_By_Model.xslsx" file.
Link to raw data: Car sales by model dataset
02
Data cleaning
When I look at the data, there's not much cleaning need to be done in fact I believe that it already being cleaned properly but I need to do my own proper cleaning so that I can optimize it more according to what I wanted. There are two tools that I used during the whole process of cleaning where I use excel for the web and Excel 2017.
-
Uniform all the column so that all the value can be read. I use "CTRL+SHIFT+RIGHT ARROW" and double click on any of the two column so that all data can fit exactly in their own perspective column.
-
The header already being froze so we not going to unfreeze it.
-
Next, I checked on any blank cells, spelling error and duplicate but there are none.
-
I fix the date format system by making it as dd-mm-yyyy format and sort it ascending order as the time stated is the same where its all at 2300 or 11pm.
-
Lastly I format the worksheet into table to do pivot table of it.
03
Data Analysing
The pivot table you can clarify the data based on what you want to observe. Hence I'm making four pivot table out of it where we want to know
-
How many that were being sold by each dealer.
-
How much profit that we make based on each day.
-
How much profit that were made by each individual dealer each year.
-
How much profit that were being made by each dealer based on different model of car.
04
Data Visualization
This is the first visualization by Excel where I'm making a dashboard out of it. I create three slicers out of it where it will let the end user to pick on what they would like to pick out of all the dealer, the model or the year. I took all the visual together with the slicer and add everything in one sheet named as "dashboard". Before that, make sure not to forget to let your slicers has connection with each pivot table by slicer settings.
05
Data Visualization 2
In this case I'll be doing another data visualization using Cognos Analytic. This is actually part of the assignment as well and the data already been queried properly so I don't need to do my own. We'll utilize all the spreadsheet in the zip files and we'll be making a dashboard using all the data given. I made a few adjustment on my own to the dashboard in the assignment where we'll compare the model more deeply compared to what's being stated in the assignment. Since I don't have a proper license for IBM Cognos Analytic, I can't embed it on my portfolio but for those who like to play around with the dashboard can click on the link below