Including Different Decision Making Scenarios in a Dashboard

by Dorinna Pentchev

Sometimes companies will use dashboards as a way to help them make business decisions. In this case, some level of prediction depending on the various possible outcomes may be useful. For example, a company could be looking for ways to save money and you’ve been asked to make a dashboard on the purchases of the company. You could make charts on certain metrics, such as spending, rejection rates, shipping costs etc. but these will mostly be helpful for seeing how the company is doing at present.

What the client really wants to know is what they can do to keep afloat, and what the potential results will be.

When looking at a purchasing dataset, I realised that the unit price paid on purchases was generally higher than the standard price offered by vendors, and that the company was using different vendors for each product. It was clear that the company could save money in three scenarios:

Method 1: They re-negotiate the unit prices down towards the standard prices.

Method 2: For each product, buy from the cheapest vendor only.

Method 3: For each product, buy from the vendor with the lowest standard price, then re-negotiate towards that standard price.

To calculate these potential savings per purchase, first I found the lowest prices for every product, then I calculated the difference in spending using the different prices. Essentially, these are losses or implicit costs of not undertaking these savings measures earlier, but they also provide an indication of the possible savings that can be made if these measures are undertaken.

Using a parameter and sheet swapping, I added a KPI to the dashboard that shows potential savings, with the ability to switch between scenarios.

These are of course maximum potential savings for each scenario and there are other factors to take into account (for example, shipping costs and quality are likely reasons behind using more than one vendor for each product). However, these numbers can be useful as an initial insight into the money that could be saved.