Markov model is a a stochastic model describing a sequence of possible events in which the probability of each event depends only on the state attained in the previous event. Wikipedia
This is a good introduction video for the Markov chains.
So, let’s consider that you have to consider the following example – you are working in a car insurance company and the rules for the insurance are the following – each year every customer is given one of four insurance classes, based on their “behaviour” on the road from the previous year. The classes are A, B, C and D. The transition probabilities look like these:
As you see, state “D” is a final state and it can be achieved from each of the other 3 states. State “A” can not be achieved from any of the other states. State “B” can be achieved only from State “A”. All the coefficients in the transition probability matrix look like this:
And the dashboard looks like this:
Cool! So how do we calculate the whole party? And what is the “ok” under check? And how we make sure that from state “A” to state “A” we pass exactly 0.6111 cases, when the total is 1000?
There is some preparation, before we calculate everything. With named ranges like tpA2A and initialStateVector. Once we have them, we can simply copy and paste this code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
Public Sub FillMarkovModel() Dim cnt As Long Dim rngToFill As Range Dim roundVar As Long If Left([roundingStatus], 2) = "No" Then roundVar = 16 Set rngToFill = tblMarkov.Range("C8:G8") rngToFill.ClearContents Dim n2nVector(4) As Double Dim a2dVector(20, 4) As Double For cnt = 1 To 20 With rngToFill n2nVector(1) = Round(.Cells(1).Offset(-1) * [tpA2A], roundVar) n2nVector(2) = Round(.Cells(2).Offset(-1) * [tpB2B], roundVar) n2nVector(3) = Round(.Cells(3).Offset(-1) * [tpC2C], roundVar) n2nVector(4) = .Cells(4).Offset(-1) a2dVector(cnt, 2) = Round(.Cells(2).Offset(-1, -1) * [tpA2B], roundVar) a2dVector(cnt, 3) = Round(a2dVector(cnt, 3) + .Cells(3).Offset(-1, -2) * [tpA2C], roundVar) a2dVector(cnt, 3) = Round(a2dVector(cnt, 3) + .Cells(3).Offset(-1, -1) * [tpB2C], roundVar) a2dVector(cnt, 4) = Round(a2dVector(cnt, 4) + .Cells(4).Offset(-1, -3) * [tpA2D], roundVar) a2dVector(cnt, 4) = Round(a2dVector(cnt, 4) + .Cells(4).Offset(-1, -2) * [tpB2D], roundVar) a2dVector(cnt, 4) = Round(a2dVector(cnt, 4) + .Cells(4).Offset(-1, -1) * [tpC2D], roundVar) .Cells(1) = n2nVector(1) .Cells(2) = n2nVector(2) + a2dVector(cnt, 2) .Cells(3) = n2nVector(3) + a2dVector(cnt, 3) .Cells(4) = n2nVector(4) + a2dVector(cnt, 4) .Cells(5).FormulaR1C1 = "=IF(SUM(RC[-4]:RC[-1])=SUM(initialstatevector),""ok"",SUM(RC[-4]:RC[-1]))" FixTheRoundingIfNeeded rngToFill End With Set rngToFill = rngToFill.Offset(1) Next cnt End Sub Public Sub FixTheRoundingIfNeeded(rngToFill As Range) With rngToFill If InStr(1, [roundingStatus], "equal") And .Cells(5) <> "ok" Then While .Cells(5) <> "ok" Dim increaseCell As Range Set increaseCell = rngToFill.Find(WorksheetFunction.Max(rngToFill.Offset(0, -1))) increaseCell.Value2 = increaseCell + WorksheetFunction.Sum([initialstatevector]) - .Cells(5) Wend End If End With End Sub |
As you see, it loops 20 times, once per period, and multiplies the cases, based on the coefficients. It is important to make sure that the A2A, B2B, C2C coefficients are calculated before the calculations are carried out. I am saving them in the n2nVector.
Concerning the “rounding questions”, this Markov Model supports 3 types of rounding:
- Number of cases is rounded to Integer;
- No rounding. Cases are a floating point number;
- Number of cases is rounded to Integer and is equal to the initial sum;
As far as “No rounding. Cases are a floating point number” is rather self-explanatory, I will explain the other two types of rounding. In the screenshot above, the selected option is Number of cases is rounded to Integer and is equal to the initial sum. If you have done some advanced modelling with any tool, you would understand that if you have to divide 100 by 3 and round to an Integer, then you would automatically get 33+33+33, which is 99 and which is a bit away from 100. Thus, I am using an additional function, which would actually check that 33+33+33 is not 100 and would add 1 to make it equal. In general, this is how the result would look like just for Number of cases is rounded to Integer case:
As you see, the sum is not always equal to 1200 (the initial state vector), thus it is a good idea to implement some rounding strategy there.