Maven Analytics Toy Challenge – How we tackled it
by Nati Turtledove 16/05/2021 · Business Intelligence / Power BI
TDDA's Submission
Maven Analytics publish a monthly BI Reporting challenge across various social platforms allowing for BI Developers all over the world to participate and share their skillets. This allows for BI Developers to share their various analytical and creative skills as well.
To get started, one needs to register with Maven Analytics and head over to their Data Playground. Registration in absolutely free and includes access to multiple datasets, inclusive of the monthly challenge.
This months challenge was to analyse sales and stock data belonging to a chain of toy stores and thus determine the following outcomes:
- Which product categories drive the biggest profits? Is this the same across store locations?
- Can you find any seasonal trends or patterns in the sales data?
- Are sales being lost with out-of-stock products at certain locations?
- How much money is tied up in inventory at the toy stores? How long will it last?
After downloading the csv files from the playground, we loaded them into Power BI and applied the following joins:
Our First Step
After modelling a bit of the data, we started displaying the key factors in cards to draw your attention to the summarised details. This included the Total Revenue, Total Profits etc.
Question 1...
Diving deeper into the data, we identified in a stacked column chart that the toy that bought in the most revenue was Lego, however, Colourbuds drove the most profit. This answered our first question.
Question 2...
We then created a basic Line Graph displaying both revenue and profits on a monthly trend. This assisted us in answering the second question, that sales tend to dip in October and November.
Question 3...
Later, we created some new columns and measures using DAX queries to build further on the stock. Applying an average consumption rate per product sales as well as what stock is currently on hand, it was determined that there was potential sales lose on the top performing toys. We identified this by applying a potential stock formula allowing or us to see how many months the current stock would last for. In our case, the top performing products sold more than they could supply, leaving Lego & Colourbuds with only half a month and 1.5 weeks worth of stock.
Question 4...
Lastly, we calculated the cost per item to sum up the value of the combine stores inventory. The total inventory was sitting at a total of $300k. This answered our last question.
A little extra touch
We added some additional visuals to increase the information value on the dashboard. Splitting the stock amounts per category and revenue per store location, filled up the dashboard and showed some more interesting data. We then published the report with some date filters and drop down to filter through the data.
All in all, this dataset, consisting of over 830k records and allowed for a lot of creative designs and was quite some fun working with.
Sources:
https://app.mavenanalytics.io/
