It’s the first week of the Side Hustle Cohort 6 Bootcamp. If I'm being honest I felt this slight dread before we received our task for the week, even after we received it I wondered how we were going to go about it and finish before the deadline. Immediately we started though it all seemed easier and the initial dread evaporated.
Our first task was to choose a product and work on a product sales analysis. We were asked to scrape data, clean, analyze and visualize it using Microsoft Excel and Power BI. My team decided to work on the sales data of a chocolate company that sold chocolates in six countries. We also decided it’ll be best if we divided ourselves into 2 sub-teams, one to work with Excel and the other to work with Power BI. I worked with the Power BI team. I have a preference for Power BI over Excel especially when it comes to visualizing data, the numerous visual options and using Power Query to clean and transform your data.
Let’s get down to business….
Chocolate Sales Analysis Using Power BI
The dataset was sourced from Kaggle.com. It contained details of sales made from January 2021 to January 2022 and was analyzed using Power BI Desktop.
We started by looking through the raw dataset and familiarizing ourselves with the different columns which are;
Salesperson (Names of Salespeople)
Geography (Countries where the company sold to)
Product (Type of Chocolate)
Date
Amount
Customer (Number of Customers sold to)
Boxes (Number of boxes sold)
Category (Chocolate category)
Size
Then we proceeded to clean the data with Power Query by…
Removing the first two columns which were empty as well as the 6th and 7th columns
Removing the blank rows from all the columns using the ‘Remove rows’ feature
Removing the columns which were not needed
Using the first row as the header row
Changing the data type of the date column from whole number to Date
Trimming the Geography column to remove blank spaces
At this point, the data was clean and ready to be queried.
Visualizing the Data
We had a couple of insights/questions that needed to be answered
First was the Top 10 Salespeople by the number of boxes of chocolate sold.
A clustered bar chart was used with ‘sum of boxes’ on the x-axis and the Salesperson column on the y-axis. The salesperson column was filtered by the sum of the boxes using the Top-N feature on the filter pane to show the Top ten Salespeople.
Then came the Number of products sold by geography(country)
A clustered bar chart was used for this visualization with the 6 countries on the y-axis and the number of products sold on the x-axis.
The Amount sold by Quarter
A clustered bar chart was used to show the sum of the amount sold by quarter. A filter of ‘Years’ was applied to distinguish the 1st quarter of 2021 from that of 2022, seeing as the dataset had data from 2021 to January 2022.
Top Sales Persons by Amount
A line graph was used to show the top 10 salespersons based on the amount of chocolates sold by them with the Sales Person column on the x-axis and the sum of the amount on the y-axis. The salesperson column was filtered with the Top-N filter by Sum of Amount to display the Top 10 Salespersons that made the highest amount from the sale of chocolate.
Three Card visuals were also inserted into the report, one showing the total amount gotten from sales of chocolate in 2021 and January 2022 and the second showing the total number of boxes sold and the third showing the total number of salespersons.
The total number of boxes sold per month.
A clustered bar was used with the Month column on the y-axis and the Sum of boxes on the x-axis.
Count of Products by Category
A clustered bar chart was used to show the number of products in each category of chocolate (Bars, Bites or Others) with the category column on the y-axis and the number of products on the x-axis. There were 22 distinct products.
Amount by Geography(countries)
A clustered bar chart was used to show the total amounts obtained from the sale of chocolate in 6 countries in descending order with the geography column on the y-axis and the Sum of the amount on the x-axis.
Top Salespersons by Products
A clustered bar chart was used to show the top 10 salespersons based on the number of products they sold with the salesperson column on the y-axis and the count of products on the x-axis.
INSIGHTS
The total amount obtained from sales of chocolate in 2021 and January 2022 was $21,701,722.
The total number of boxes sold was 1,344,574 boxes.
The top 10 salespeople had a range of 55,610 - 62,730 boxes of chocolate sold with Karen McCaffrey having sold the highest number of boxes during the year.
All the countries had over 600 products sold with New Zealand having the highest number of products sold (647) and the USA having the lowest(609).
The highest amount made per quarter in 2021 was in Quarter 1 which was $ 5 million while the lowest was in the 3rd quarter which was $4.2 million, While the total amount in Quarter 1 2022 was $ 3.1 million.
The salesperson with the highest amount was Karlen McCaffrey with a sum of $ 9.6 million.
January 2022 had the highest number of boxes sold with 250,000 boxes while September 2021 had the lowest number of boxes sold which was 70,000 boxes.
The ‘Bites’ category had the most number of products (11 products), the ‘Bars’ category had 7 products while the ‘Other’ category was the lowest having 4 products.
All 6 countries obtained above $3 million from the sale of chocolates.
New Zealand had the highest amount which was $3.78 million while the USA was the lowest having $ 3.45 million.
All top 10 sales persons had sold between 150 to 160 products each.
The salesperson with the highest number of products sold was Brien Boise who sold 160 products.
POWER BI VISUALS
DASHBOARD
