This project was done based on the 2019 sales data of a company. The purpose of this project is to take the opportunity to showcase my Excel skills as well as some data analysis. Some analysis included looking at the performance of each product line, and sales representatives for the company.
I've included some graphs created using Microsoft Excel to help with my analysis:
I first check to see if there are any duplicate values. Using Excel's conditional formatting, I was able to identify duplicate values by highlighting the cells.
Using Excel's SUMIF function, I summed up all the values for each productline, months, and quarter. From here we can easily produce visualizations to compare sales performance between productlines, months, and quarters for this company.
Using Excel's VLOOKUP function, I can quickly pull up data for specific order numbers if I need them for deeper analysis later on.
I wanted to graph the unit sales and revenue numbers on the same graph, however, the scales for the two variables were extremely different (ie. revenue ranges from $0 to $120,000 while the unit sales ranges only from 0 to 1,200.) This problem was mitigated by adding a secondary axis. By looking at this graph, we can see the unit sales and revenue numbers align very well with strong correlations. We can also see that sales are stronger at the end of the year from October to December.
This graph describes the popularity of each product line. Classic cars makes up for majority of the sales at 35% so the company could choose to further increase or retain this number.
I created a geomap locating the countries with the highest number of orders and it seems like the US is a major customer segment for this company.
Using Excel's data validation function, I was able to create a dropdown menu, selecting the sales representative and have the sales numbers for the respective sales agent change automatically as I click on different sales representatives within the dropdown list. Not only does the numbers change, the graph will automatically adjust according to the input from the dropdown menu. For this graph, we see that sales representative Jan Klaeboe generated more sales in September than in October. This is interesting because we found ealier that October to December are the busier months of the year.
Using Excel's sparkline and columns function, we can see the seasonality of units sold for each product line. The end of the year seems to be the peak season, in terms of car units sold, for all productlines.
This graph shows the number of models (order number) the company sells in each country for each productlines. This company offers 25 different models.
Using databars, under the conditional formatting function, we can easily see the minimum and maximum values for a dataset. In this case, we can see that Classic Cars is the most popular productline with 1401 orders and Trains is the least popular.
The product lines Classic Cars and Vintage Cars makes up for over 50% of the total sales the dealership generated for the year 2019. There is evidence of seasonality with quantity sold and total sales the greatest in October, November, and December.