Jan 2022
Here is the Dashboard:
https://public.tableau.com/app/profile/luis1200/viz/classicmodels_16425270715550/Story1#1
(for a better view, visualize this dashboard on full screen)
Tools used:
Prepare: SQL.
Process: SQL.
Analyze: SQL and Tableau.
Visualize and share: Tableau.
Context: ClassicModels is a company that sales scale models of different vehicles. It has operations in 27 countries but for the years 2003, 2004 and 2005 the company drove its business from 5 countries; These countries are: USA, France, UK, Australia and Japan.
For the same 3 years, the company has sold over 9Million Dollars distributed on these 5 countries. Not all business is conducted in cash, since the company has some important collectables that might need to be looked into.
The problem: The Director of sales and marketing has requested a Dashboard to help him expose the different sales details in order to keep a better track of each sales representative, how they are conducting business in the different countries and to keep track of the collectables for each country. Furthermore, the dashboard has to keep track of inventories and its suppliers as well.
Data: 1 Microsoft SQL Server Query file around 56KB:
https://www.mysqltutorial.org/wp-content/uploads/2018/03/mysqlsampledatabase.zip
Prepare:
I ran the .sql file that came in the previous link to get the database on which I was going to work.
Process:
At this point, I wanted to review as much information as possible in the quickest way, for that I created a series of querries.
1.- I wanted to be able to check the seasonality of sales by month, I also wanted to check sales details by country, year, sales by customer, sales by sales-employee, sales by order-status and finally sales by shipped-status.
With this end in mind, I extracted from the table "orders" the orderNumber, status, orderDate. Then, I created a column named shipped_status in which I would determine if the order was "Delayed", "Not Delayed" or "Not Shipped" using the shippedDate and requiredDate. I extracted the customerName from the "customer" table, employeeNumber and concatenated the employee firstName and employee lastName from the "employee" table, calculated the order sales which is the sum of (quantityOrdered * priceEach) from the "orderdetails" table and finally the country where the sale was made from the "offices" table.
Here is the PDF with the query: https://drive.google.com/file/d/1UcWtS9hDycyr2XI8m26SLz4AeKCnT2tj/view?usp=sharing
Here is the CSV, product of the previous query: https://drive.google.com/file/d/1dXKW30xTQmJqWeyYdayaqXBGdFQFTtlV/view?usp=sharing
2.- I wanted to be able to see sales receivables by sales employee, receivables by country, receivables by customer and a comparison between sales and payed sales by customer.
For this I extracted customerNumber from the payments table and then join it by customerNumber to a table created by myself. This table has: customerNumber from the orders table, customerName from the customer table, and the sum of (quantityOrdered * priceEach) from the orderDetails table for the sales amount, All this information grouped by the customerNumber.
Here is the PDF with the query: https://drive.google.com/file/d/14R7OYK1KY6p-G0eh6zm622besuGZgDR3/view?usp=sharing
Here is the CSV, product of the previous query: https://drive.google.com/file/d/1OTdGCnsi9HfdsexJMmcGQuzpVOXBxvyg/view?usp=sharing
3.- I wanted to be able to check Inventories by productLine, Inventories by product filtered by year, and suppliers by productLine filtered by year.
For this I extracted orderNumber, productCode, quantityOrdered and priceEach from the orderdetails table; productVendor, quantityInStock, buyPrice and productLine from the products table; and finally the orderDate from the orders table.
Here is the PDF with the query: https://drive.google.com/file/d/1ELNqLuErkPA3U_hi7Iu4Lqs3YArXKxeS/view?usp=sharing
Here is the CSV, product of the previous query: https://drive.google.com/file/d/1gybhyxEKQZFGjk6rj-UnwBKCsGkalIMM/view?usp=sharing
Analyze:
I end up with a dashboard to be used on sales meetings to review not only the performance of sales and collectables by country but by sales employee or customer and keep a tight glance at inventories.
The meeting should go as follows:
1.- A quick review of the sales seasonality to prepare the sales force for the up coming months and ask them for preparation plans.
2.- A review on top performers not only by client but by sales employee. The idea here is to come up with specific actions, per client, from the sales employees to pump up sales.
3.- Check current orders by status and shipped status to foresee any potential errors in shipping.
4.- Check for product sales by product, product line and year to foresee growth on any abandoned product line the company might have and that could be exploited.
5.- Check the gap between sales and receivables to narrow this gap, if possible; not only by country but by client. This way we will get specific actions by sales employee.
6.- Keep a tight view on inventories to have a healthier rotation of inventories and use the excess of capital to innovate in sales efforts to pump up sales with plans formulated between the sales employees and marketing team.
Here is the Tableau dashboard: https://public.tableau.com/app/profile/luis1200/viz/classicmodels_16425270715550/Story1
(for a better view, visualize this dashboard on full screen)
Findings and conclusions:
Sales are steady from January to September, they start to rise in October, peaking in November.
From 2003 to 2005:
The US was the highest country driving business.
Gerard Hernandez was the sales employee with more sales.
Euro+shopping channel was the highest client driving business.
We got more than 92% on Shipped orders.
We got more than 94% on shipped orders without delay.
The 1992 Ferrari 360 spider red is the most sold model, pumping up the numbers of the Classic car Line.
France has the largest amount of receivables followed by the US. Japan has 0 receivables which means they use low to no credit on their purchases.
The Classic car Line has the largest inventory, this makes sense since the company might want to keep a larger inventory of their most sold item. But, it can be improved by a more lean inventory management system.
Since "Gearbox Collectibles" and "Classic Metal Creations" are the providers on which the company buys most of its models, there might be some deals based on volume of purchases to lower costs.