Mission #01
Context
Attention!
For data analysis purposes, we are in December 2013.
Our first mission at Heavy Power Nutrition is to help the Demand Generation team, which is under Pedro’s management, to create the B2B sales reports more easily.
It is essential that, from the reports generated, Pedro and his team can get insights on how to generate more demand for the sales team.
Pedro would like his analysts to stop doing so much manual work and start helping more with those analyses. This way they could use their time for tasks that would bring more strategic benefits to the company.
We got from him a sales spreadsheet from January/2013 to November/2013 that was extracted from the company’s system. Every end of the month the team needs to extract a new updated file and manually create some extra calculations, such as Net Sales, Cost of Sales, Gross Margin, etc. These calculations are marked in blue in the last columns of the table.
Every month it’s the same nightmare: analysts exporting a new base and repeating the same calculations.
Goal 1
The first goal is to import the spreadsheet into Power BI and create an analysis of:
Gross Sales
Net Sales
Discount %
Gross Margin %
The manager wants to have easy and fast access to these values, without needing the team to update the calculations at each new extraction of the system.
Show him that through Power BI it is possible to automate these calculations and that next month it would only be necessary to replace the new file in the same folder, making the process semi-automatic.
Therefore, the analysts would no longer need to create the columns in blue at the end of the table and would have more time for other demands.
Goal 2
Each region of Brazil (South, Southeast, Center-West and North/Northeast) has a sales supervisor. Pedro told us that there was a drop in the gross margin percentage on the months of May/2013 and June/2013, and that it was just after the change of the supervisor on the Southeast region.
The normal is to have a margin around 40% in general, never below 30%. Due to the delay in creating the reports, this decline in the margin took a long time to be identified and fixed in the Southeast.
It is clear that analysts cannot perform the role of analyst due to lack of time, as they are always putting out fires creating reports manually. This is an excellent opportunity for you to help the client in a strategic way and stand out as a BI professional.
So, investigate what may have happened in these two months and how the new Southeast Supervisor recovered the margin from July/2013 onward. Show to Pedro that with Power BI this analysis becomes totally dynamic and much easier to be done.
Overview
These are your tasks for Mission #01:
- Show on a card the gross sales.
- Show on a card the discount percentage.
- Show on a card the net sales.
- Show on a card the gross margin percentage.
- Show in a graph the net sales by category and subcategory of the product.
- Perform all the necessary analyses to understand what led to the drop in the margin percentage during May and June.
- Post on social media that you are developing Mission #01 of the Power BI eXperience Course and use the hashtag #PBIeXperience
The Mission will only be considered concluded if you complete the 07 items in the list above.
Attention!
All values are already in dollars, and so must be presented to the client. The system makes the automatic conversion from the local currency to dollars with the daily exchange rate.
One of the roles of a good data analyst is to know how to investigate the data and make assumptions to verify the facts. Do this to understand what may have happened in May and June.
HPN’s main products are proteins, and the products are sold in boxes with 12 units. The unit price defined in the sales table refers to the box.
The unit cost defined in the sales table involves all the costs for the product and the costs resulting from importing them.
Gross Margin is calculated considering Net Sales and Total Costs.