PBI Use Case #3: Sales Dashboard (Part 1 of 4)
One common use case in all companies is the classic Daily Sales Report. Super basic, nothing fancy but the idea is to get to know the latest and greatest from a revenue perspective.
Common questions:
- What is the Total MTD, YTD Revenue?
- What is my MTD, YTD Revenue by brand?
- What is my MTD, YTD Revenue by customer?
These questions seem to be simple to answer but there are some challenges that I will address in this article. Assuming that we are able to answer the above questions, the next question mark is to know the performance or if the business is growing. There are different techniques to achieve this, but in this article, we are going to use the revenue (or Gross Sales) and compare it against itself (historical revenue). So, for this we are going to use Power BI to mechanize the metrics we want to monitor:
- L4W: Last 4 weeks of sales
- L12W: Last 12 weeks of sales
- L24W: Last 24 weeks of sales
- L52W: Last 52 weeks of sales
- L4W YAGO: Last 4 weeks of sales a year ago
- L12W YAGO: Last 12 weeks of sales a year ago
- L24W YAGO: Last 24 weeks of sales a year ago
- L52W YAGO: Last 52 weeks of sales a year ago
- L4W Chg %: Change (or Growth) between L4W and L4W YAGO
- L12W Chg %: Change (or Growth) between L12W and L12W YAGO
- L24W Chg %: Change (or Growth) between L24W and L24W YAGO
- L52W Chg %: Change (or Growth) between L52W and L52W YAGO
As you can see, the metrics are very straightforward. You can also compare the current sales against the sales 2YAGO to get a pulse of your performance using a wider range.
However, the analysis gets tricky when you analyze the changes over time by brand, by customer, by other categories, etc.
Summary by Brand:
Summary by Customer:
Putting it all together:
Let’s take it up a notch (to be addressed in a separate article):
- Quantity: it would be fantastic to see the abovementioned views by Quantity (Cases, Eaches, Kgs, Lbs) and even better, a selector to pick whatever measure we want.
- Net Sales
- Gross Profit Value
- Cost of Goods Sold (a.k.a CoGS)
- NS/qty: Net Sales Value divided by Qty
- GP/qty: Gross Profit Value divided by Qty
- CoGS/qty: self-explanatory
- Closing Inventory: this is a bit complex, but it gets you a wealth of information. Having the closing inventory for each month gives you the opportunity to know how your products are being consumed and also adjustments to your Forecast.
- WoC: Weeks of Coverage tells you how long the current inventory you have on hand will last based on current sales and Closing Inventory
- Currency: what if your dashboard is consumed by people located in different geographies and they want to see the number in their local currency? for this, you need to incorporate an Exchange rate table based on the various currencies.
Who is the Audience? I think is easier to explain who does not use it. The visibility that this information has is very high. This is why it becomes very critical to keep it up to date. Something critical here is to have a Data Quality Framework in place to communicate proactively when something breaks and also to pinpoint issues.
Hope this article gives you some clarity about this essential use case, how to take it to the next level or simply get some inspiration to improve yours.
Wanna work together? I help people to learn more about Supply Chain Management, Power BI and Analytics. Let us create better tools and processes together! Get in touch!
PBI Guy
success@ootconsulting.com
