Often, we come across scenarios where we have to measure a particular KPI at a specific point in time, and also look at how the metric is performing against some other related metric.
Any KPI can be measured against three performance benchmarks:
- Targets
- Time (last month Vs. this month, last year, Vs. this year and so on)
- Peer group (Rank of students in a class being a case in point)
We can also measure a KPI against a related KPI. A simple scenario will be as below:
The Product Manager handling Term Deposits in a Bank wants to see the replacement rate of his Deposit book. The normal metrics one would look at includes:
- New Deposits booked
- Region, Branch
- By amount band, interest rate band, tenure band
At the same time, every month, the existing deposit book also matures. The matured deposits that leave the product balance may or may not be retained inside the bank, which is a separate line to track. However, what matures and goes out of the product book will be a mix of vintage deposits, low interest deposits and so on.
A product manager needs to know not only how many new deposits that have been booked across various dimensions, but also the deposits that have matured across the same dimensions to understand the replacement behaviour. It will indeed be alarming if the data shows that long term deposits are being replaced by short term deposits, or low interest deposits are being replaced by high interest deposits.
Let us attack the ‘visualization’ part to view the replacement rate of the Deposit book in two aspects, New Deposits Booked – Count metric and dimension being Tenure band and Time Dimension – 12 months. This needs to be compared with the matured deposit book of the 12 months across their contractual maturity band and count of deposits. So effectively, in one visualization, we need to show the following things:
- New deposits booked in 12 months
- Deposits across various tenure bands
- Comparing what has matured in the same time period & in the same band – whether the count of deposit is more than what matured in a similar dimension (Time and Tenure)
Let’s try this in Excel.
Input Data
The Maturity data (Count of Term Deposits matured) for the 12 months in 2011 looks like below. Please note that the tenure banding is based on Contractual maturity. For example, in January, 831 deposits matured that were booked in 2006 or before, for a period of greater than 5 years term.
The New deposits booked during the same period looks like below. Again, the maturity band is contractual maturity. In January, 532 deposits were opened for a period of more than 5 years, maturing after January 2016 or later.
Now, the above data in itself might not mean anything. If we compare this with the previous maturity table, we see that the number of long term deposits (> 5 years bucket) has decreased in January, that is: 831 deposits matured and were ‘replaced’ by 532 deposits. Of course, we need to look at the average rate and amount band to know whether it really is a cause of concern. But for simplicity’s sake, we assume the interest rate and amounts are similar, and we conclude that the replacement of long term deposits is ‘negative’ or has decreased in comparison to what has matured. Now we build a delta table, which can be calculated as – New Deposits MINUS the Deposits that matured in the last 12 months. The DELTA data looks like this:
Now, this table tells whether the number of deposits has increased or decreased in comparison to a related metric, the maturity data.
Bubble Chart
Now, to visualize the data, I am going to use Bubble chart in Excel. More about Bubble chart and how to construct one is available on the internet in multiple sites. A few minutes of play should make you at ease with them. Primarily, Bubble charts will give me the ability to view this data in matrix form, with bubbles representing each data point and size of the bubble representing the value.
For creating a bubble chart, first we need to get the data prepared in a different format, which a bubble chart can read.
We are creating a bubble chart with the New Deposit Table as the primary data from where the size of the bubble is determined, and the DELTA table that will drive the colour coding of each bubble.
Bubble Chart – New Deposit Table
The data can be prepared by transforming the matrix table into an array table. This, I achieve by using simple IF conditions and OFFSET function.
The Raw Data (Matrix Data of New Deposits booked) is in the ‘Raw Data’ sheet. (You may download the Excel sheet given at the end of the blog. The transformed data set looks like below:
Now, we insert a ‘Bubble Chart’, the series above looks like below:
Now if we play around, we can control the size of the bubbles so that they don’t overlap. The rows of bubble represent the different buckets of tenure (contractual tenure) and columns represent the months.
Now, visually, these would tell me that bigger the size, larger is the number of deposits booked; trend month wise and tenure bucket wise.
We will now try and represent the DELTA data as colours.
Colouring the Bubbles
Similar to the New Deposit Matrix, we transform the data into an array table. We need to now classify this data based on the value it holds. The simplest thing to do is as follows:
- Look at the maximum and minimum data points – 0 and 100%. 50% of that spread would be our median
- Look at the 33% and 66% data points
- Anything less than 33% should be coded Red, meaning that data point with respect to the population has been either replaced negatively or replaced by a lesser positive value
- Anything greater than 66% will be Green, meaning they have a positive replacement rate or in comparison to the population, a better replacement rate.
- Of course, the above definition can be changed like this, all negative values as RED, Slightly positive values as AMBER and GREEN for larger positive values
We achieve this by simply using a Colour coding flag – as given below:
The Data spread values / conditions are calculated as below:
The tables look like this:
Now, the colour of each bubble in the chart should be driven out of the values in the Colour Data table.
In Excel, it is tedious to select each bubble and keep colouring them based on the value of another table. So, we write a simple Macro which will do this pretty quickly. The macros code looks like this:
I have also placed a button on the excel sheet that would drive this. So, the final output looks like this:
We can make the Chart more readable by using Arbitrary Axis and XY Chart labeller, so that we know what we are looking at.
The interpretation of such a chart (Heat Bubble Chart) is easy – The red ones simply means in those tenure buckets and months, while the new deposits booked is X (represented by the size of the bubble), it is lower than the deposits that matured in the same bucket and tenure, and hence there has been a leakage in that particular bucket.
If we start visualizing this data along with the two other dimensions; Amount Band and Interest Rate Band, the product manager can plan interest rates on specific buckets to ‘increase’ new deposit booking at the right places, and maintain a healthy mix of deposit books.
The Excel working is attached below.
Playing with Data – Part 2. Data Visualization: Heat Maps
Conclusion
The above, i.e., visualizing more than two dimensions of data can be achieved in Microstrategy (the tool we use in Biz$core for data visualization) in a far simpler way. The inbuilt chart is a heat map, and the snapshot of the same is shown below. Heat Maps help in visualizing large amounts of data in a quick and easy to interpret manner, and come in handy in the Banking context when there are lot of dimensions in metrics within one particular product.
