…or sometimes known as development triangles is a modelling technique, and hence a prediction technique, for process data. It lends itself well to business planning where the focus is placed on deterministic targets and subsequent monitoring of actual versus expected results. That’s exactly what I built these models to achieve previously with great success – or anti-success should I say? More on that later in this series of posts though.
The technique was taught to me by the Managing Director, who happened to be an Actuary, of a Life Assurance company I worked with in the 1990’s. I say taught – when I moved into Risk Management I inherited it as a task that had been handed to my new boss and he passed it on to me. It was probably the first real example of picking apart the workings of a spreadsheet that I ever did.
Take a three stage insurance Sales process whereby a customer, after seeing some advertising, will phone a Call Centre. An Agent will take the call and give the Customer some information about a product and hope to turn the Enquiry into an Appointment for a Sales Representative to visit them. After the Sales visit there may be other Sales visits but hopefully from that the Customer Makes an Application. The Application runs for a period of time and ultimately becomes a Completed sale. This is a fairly standard Insurance Sales process although sometimes the Customer visits a Sales Office for their appointment.
At each step of the process you can record, for a population of First Enquiries, a conversion rate. In old school planning the Average Conversion Rate at each step was a key measure of process performance. What that gives you is a model :
The example in this simple model shows Conversion Rate %’s as 15%, 25% and 80% for each of the steps. As shown this gives us 3 Completed Sales per 100 Enquiries with the calculation being :
100 * 0.15 * 0.25 * 0.8 = 3
Double our Enquiries and, assuming conversion rates stayed the same, we would get 6 Sales.
There are some issues with this simple model :
1) It doesn’t tell us when things happen
2) It doesn’t tell us how things happen across a period of time.
3) It doesn’t tell us the effect of what happens when the experience in our process starts changing.
In order to solve those two problems we need to convert our flat Conversion Rate %’s into development triangles, or as some people know them, Chain Ladders.
Chain Ladders / Development Triangles
What’s a Chain Ladder or a Development Triangle? Well for arguments sake let’s imagine prospective Customers are making telephone Enquiries and these can occur at any time of the working day from 9 till 5 Monday to Friday. We know that however many calls we get we only convert 15% of those into Sales Appointments. Some of those Appointments might happen after a day or so, others later. If you create a frequency table you might get something that looks like this :
Or you could use a frequency tally chart and get this :
This picture shows a ‘*’ for every occurrence of an Appointment and where it occurs in relation to when an Enquiry was made. If you think it looks like a frequency distribution then you’d be right!
In a real world situation, to enable the production of this kind of chart what you would need to do is to record, for every Customer, an Enquiry Date and their Appointment Date if they had one.
The Next Step – Build The Chain Ladder Model
To take our example further we need to start building our model. Microsoft Excel is the perfect tool for doing this and the examples shown here are built in that.
So in the real world our Enquiries would not arrive in a uniform fashion but let’s say we get 100 Enquiries per day. We can construct our model to show, assuming that our Conversion Rate experience doesn’t change, when our Appointments will be and when the Applications will roll in :
As you can see the complexity of our model has ramped up a notch pretty quickly! The colour highlighted cells are those used to sum up the Appointments occurring on Day 10. So you get 1 Appointment from Enquiries occurring on Day 1, zero from Enquiry days 2, 3 and 4, a single appointment from Enquiry days 5 and 6, eight from Enquiry day 7 and so forth.
Looking at this picture, as you may have already foreseen, the number of appointments taking place per day depends on what happened with Enquiries received previously. The model built so far shows that the number of Appointments stabilises at 15 on the 10th day.
Already though our model, while still relatively simple, starts to look useful. We know for example that, disregarding the initial ramp up period, that we need enough Salesmen for 15 Appointments per day given that we receive 100 Enquiries per Day and we convert 15% of those into Appointments that occur according to the pattern shown – on average of course.
We’ve only got a single Process step in our model thus far. The same treatment needs to be applied to the Appointment to Application and Application to Completion development triangles. We’ve got our starting point though – the number of Appointments per day.
That’s it for now though – the next part of this series will show the development of our example model, adding the next two process steps, and will add some more detail to help the reader understand the usefulness of this important modelling technique.