PBI Use Case #4: Sales Dashboard (Part 2 of 4)
In the previous article PBI Use Case #3: Sales Dashboard (Part 1 of 4) we went over the mechanism to see if there is any growth in the business by using historical data. Well, in this article we will review another approach which is Sales vs Targets to measure the business performance.
Business Questions:
- How are we performing in-month vs forecast/target?
- What is the required daily run rate to hit the forecast/target?
Types and definitions:
- Corporate Targets: these targets often times are cascaded on an annual basis at company/region level and sometimes at company/region/business unit level. Normally, the target is in USD or EUR depending on the location of the headquarters of the company. The driver of this target is based on the vision of the CEO, expectations of the Investors, macroeconomic factors, or a mix of all the above.
- Sales Targets: these targets are more granular and depending on the company, they may be updated more frequently (i.e. Quarterly or Monthly basis) and the drivers are economic factors of the region, market share, promotions, campaigns, big bets, etc.
- Demand Plan Targets: these targets are more tactical and very granular and are updated every month. This is a bottom-up approach, and the idea is to gather inputs from Sales, from Marketing, from Factories. Every week, each department provides their inputs and Demand Planners make the necessary changes in the plan. At the end of each month, a major meeting is held to present the final forecast to the upper management. Once is validated, it gets locked and becomes the target for the next month. The drivers here are: Inventory, Allocations, New Product Developments, Production attainment, Production Lead Times, Transit Lead Times. One key component here is the accuracy of what you said 1, 2, 3 months ago — this is what is called: Forecast Accuracy analysis. I wrote an article about it in case you want to learn more about it:
Let us start off with the main takeaway which is the comparison between Actuals and the Target:
Let’s set conditional format on the gauge to communicate the sentiment based on the performance:
But how do we know if we are comparing apples to apples? YTD Sales need to be compared against YTD Target. Previously, we were comparing YTD Sales against the Full Year Target. This does not sound fair.
If we replace the Full Year Target by the YTD Target, this time we get to see our real performance and thanks to the conditional format in the gauge, we get to see that we are closer than we thought to hit the target by comparing apples to apples.
The monthly trend looks like this:
The Executive dashboard looks like this:
We can add more functionalities such as:
- Toggle to switch Values to Quantities
- Dropdown menu to select the different versions of the targets to compare against.
- Dropdown menu for Time Intelligence
- Selector to switch from Calendar year to Fiscal year if applicable.
- Add drilldown / drill through pages to deep dive.
Hope you enjoyed the reading as much as I did writing the story.
Wanna work together? I help people to learn more about Supply Chain Management, Power BI and Analytics: Training, Analytics projects, SME services.
PBI Guy
success@ootconsulting.com
