Monte Carlo simulation is a very rich methodology whose applications are not limited to business topics. We can also apply it, for example, to a very personal question: who should I marry?
The candidates arrive one after another, and we have to decide on each one without knowing who is still to come. Below we show how to structure such a selection decision, build it as a model in Excel and compare different strategies with a Monte Carlo simulation, first for 10 and then for 30 candidates. It turns out that the best strategy depends on the goal we pursue.
What is the selection problem?
For a personal question like choosing a partner to be examined with a simulation, we first have to structure it. Then it becomes quite natural to use this method. Structuring means nothing other than making assumptions that we take as the basis for the topic. For partner choice, we assume the following basic structure:
- Each candidate has a different quality for me. I can recognise a candidate’s quality, and no two candidates are equally good. So there are no clones, every candidate is unique.
- The candidates arrive in random order. I do not know in which order the qualities will come. I have to examine and assess each candidate individually, one after another, and then make my decision.
- I make exactly one decision per candidate: I accept or reject them. The decision cannot be undone. If I accept a candidate, I am married. The process then ends, and I do not even look at any further candidates. If I reject a candidate, I move on to the next one. But I cannot bring back the rejected candidate, they are lost once and for all.
In which steps do we proceed?
Against this background, we proceed in four steps. In the first step we set our capacity, that is, how many candidates we can look at in the first place. That is not an infinite number. Typically there is a time window in which we want to make the decision, and in that time our resources are only enough for a certain number of candidates.
In the second step we set ourselves a goal. The goal can be to get the best candidate in the sample. But it can also be to get the best expected quality. Then we might also be satisfied with a candidate who is not optimal but very good. A third goal would be a short waiting time: we want to reach our goal quickly and not have to use up our capacity completely. These three goals are very different, and it may well not be the same strategy that is best for all three.
In the third step we set up a rule for how we want to decide. It can be, for example: “Test first, then choose.” Testing first means that we do not make the decision immediately. For a certain time we gather experience and look at what the candidates are like. In a second phase we then decide on one of the best candidates.
In case of doubt, however, this rule leads to no decision. If we have waited too long, it may not work out at all. Then we need a tiebreak, that is, a rule for exactly this case. The tiebreak is not set automatically, we have to choose it just like the decision rule. It can be, for example, that in case of doubt we take the last candidate we can see at all. That is classic last-minute panic. The alternative would be to forgo a decision altogether and say: there was no suitable candidate, I will stay single. Both are possible.
In the fourth step we compare the results of the decision rules in the simulation. We look at the figures for our goals and check which decision rule comes closest to our goals.
How do you model partner choice in Excel?
In short: Risk Kit is an add-in for Microsoft Excel that lets you build and analyse Monte Carlo simulations directly in the spreadsheet. What we show here applies in general, though: comparing decision strategies with a Monte Carlo simulation works on the same principle in any simulation tool.
The model in Excel is divided into three areas. The first area is the world that the Monte Carlo simulation represents, here the random order of the candidates we meet. The second area is our reaction to this situation, that is, the decisions we make in view of the order. The third area is the evaluation under the individual goals: maximising the expected quality, maximising the probability of getting the best, or having the shortest possible waiting time.
Figure 1 shows the model. On the left, columns A to C hold “Die Welt” (the world), shaded light yellow, and next to them columns D to H hold “Die Reaktion” (the reaction), shaded light green. To the right, columns I to O hold “Die Auswertungen” (the evaluations) for the three goals. Each of rows 5 to 14 stands for one candidate and at the same time for one strategy, as we will see shortly. Before the simulation, the evaluations do not yet have values.
Figure 1: The model in Excel with the areas “Die Welt” (the world), “Die Reaktion” (the reaction) and “Die Auswertungen” (the evaluations). At the top is the formula of the score in cell B5. The evaluations are only defined after the simulation (German interface).
The decisive point is simulating the order, because it is the actual problem. Column A holds an index. It counts 1, 2, 3 and so on up to the maximum of our capacity. Here we look at ten candidates at most, we have no resources for more.
For the quality of the candidates we need the numbers from 1 to 10 in random order. 1 is the worst quality, 10 the best, and no number may repeat. This can be done with a trick. We first draw a score with a continuous probability distribution. A continuous distribution has the property that, with probability 1, each value occurs only once in a sample. The probability of drawing a value twice is 0. So in ten draws we always get ten different values.
For simplicity we use the standard normal distribution, that is, the normal distribution with expectation 0 and standard deviation 1. At the top, in the formula bar of Figure 1, is the formula of cell B5 with the Risk Kit function Normal_variate:
=+Normal_variate(0;1)
The parameters do not matter at all in this case. We could also have chosen other parameters or even any other continuous distribution, and exactly the same would have come out. As Figure 1 shows, all scores are nicely different from each other. All we still need is their rank in this small sample.
The rank is provided by the Excel function RANG.GLEICH, the German name of RANK.EQ. Cell C5 contains:
=+RANG.GLEICH(B5;$B$5:$B$14;1)
The function returns the rank of the score in B5 among all ten scores in B5 to B14. The 1 at the end means that ranks are counted in ascending order. So the smallest score gets quality 1, the largest quality 10. Figure 2 shows the formula while it is being entered, with the ten scores outlined in red.
Figure 2: The quality is the rank of the score among all ten scores. The reference B5 to B14 is outlined in red. While the formula is being entered, cell C5 shows part of it (German interface).
In our sample, quality 1 belongs to the score −1.36 in row 12, the smallest value in the sample. The 10 belongs to the score 1.13 in row 7, the largest value. All others lie in between accordingly. Through the interplay of a Risk Kit function for the continuous distribution and an Excel function for the rank, we thus simulate the random order of the candidates.
This completes an essential step, and incidentally the only one that is random at all. Everything else is just our deterministic reaction, that is, our fixed, no longer random reaction to the random world we are confronted with.
Which strategies do we compare?
Now we have to make decisions. The decision rules we evaluate here separate a test phase from a decision phase. In the test phase we look at a certain number of candidates and reject all of them as a matter of principle. In the decision phase we take the first candidate who is better than the best candidate of the test phase.
In the first step, then, we set the length of the test phase. It is in column D, and each row is a separate decision rule. We can do without testing altogether. Then we take the first candidate who comes along and marry without any prior experience. That is a test phase of length 0. The second rule is to look at one candidate and then marry the first one who is better. The third is to look at two candidates and then marry the first one who is better than the best of these two. And so on, up to a test phase of 9.
With a test phase of 9, we have looked at nine candidates and would then have to marry one who is better than all nine. For that we must be lucky: the very best has to come right at the end, so that we can still make an active decision at all. Otherwise we have already seen them and come away empty-handed. Then the tiebreak applies.
As the tiebreak we assume here that we then marry the last candidate. With a test phase of 9, this puts us in the paradoxical situation that it does not matter what we decide: we always take the last one. The alternative would be to forgo a decision altogether in this case. Then this rule would only lead to marriage if the best happens to come tenth, and in all other cases one would stay single. This, too, is very easy to simulate.
Who was the best in the test?
Next we determine in column E which candidate was the best in the test. If we have not tested at all, there is no best in the test either. It then has quality 0. If we have tested two candidates, it is the maximum of the qualities of the first two, and so on. For the test phase of length 1, cell E6 contains:
=+MAX($C$5:C5)
The range always starts with the first candidate in row 5 and extends to the last candidate tested. In our sample in Figure 1, the best in the test is the 7 for a test phase of 1, and also the 7 for a test phase of 2. From a test phase of 3 onwards the 10 is already included, so we have already seen the best. This will shortly lead to a small problem when we want to get the first better candidate after the test.
When does the first better candidate come?
In column F we count the steps to the first better candidate after the test. For this we use the Excel function VERGLEICH, the German name of MATCH. Cell F6 contains:
=+VERGLEICH(WAHR;C6:$C$14>E6;0)
VERGLEICH looks among the candidates of the decision phase for the first one whose quality is greater than that of the best in the test, and returns their position. WAHR is the German TRUE. Without a test phase, in row 5, the comparison value is 0. Every candidate is better than 0, so we take the first one straight away.
Figure 3 shows the formula for the test phase of length 1 while it is being entered in cell F6. Outlined in blue are the qualities C6 to C14, that is, the candidates of the decision phase. Outlined in red is E6, the best in the test with quality 7. The first candidate who is better than 7 is the 10. It is in second position, which is why F6 in Figure 1 shows two steps.
Figure 3: VERGLEICH looks in the decision phase C6 to C14 (blue) for the first candidate who is better than the best in the test in E6 (red) (German interface).
With a test phase of length 2, the best in the test is again the 7, and the first candidate after it is the 10 straight away. So here it is one step. After that we come to the problem zone, so to speak, the area in which the 10 was already in the test phase. There is no better candidate after the test, and VERGLEICH finds no solution. This is why, from row 8 onwards, Figure 3 shows the error message #NV, the German #N/A, short for “nicht verfügbar” (not available). In this case the tiebreak applies.
What is the result of a strategy?
We determine the quality of the first better candidate after the test in column G with the Excel function INDEX. Cell G5 contains:
=+INDEX(C5:$C$14;F5)
INDEX selects from the candidates of the decision phase the one at the position that VERGLEICH has found. In row 5 it is the first one, the 7. In row 6 it is the second candidate of the decision phase, the 10. If there was no position, the error message #NV is returned here too.
With that we are essentially done. The result in column H is the quality of the first better candidate from the decision phase. If there was an error there, the Excel function WENNFEHLER, the German name of IFERROR, returns the value from C14 instead, that is, the last candidate in the row. That is our tiebreak. Figure 4 shows the formula for the test phase of length 4 in cell H9:
=+WENNFEHLER(G9;$C$14)+@OutputName(A8)
Figure 4: The result of each strategy in column H. Where there is no better candidate after the test, the tiebreak applies and returns the quality 8 of the last candidate from cell C14 (German interface).
With a test phase of 0, in this sample we marry the first candidate, with quality 7. With a test phase of 1 or 2 we get the 10. From a test phase of 3 onwards the tiebreak applies, and we get the last candidate, with quality 8. The addition OutputName at the end of the formula only gives the cell a name, here the 4 from cell A8, and does not change its value.
We now have the nice situation that we can evaluate ten different decision rules at the same time, each in one row. On this basis we carry out the entire evaluation, because we calculate statistics from the result of each row.
How do you measure how good a strategy is?
Goal 1: the best expected quality
For the first goal we want to maximise the expected value, that is, the mean quality over all simulation runs. To do this we apply the Risk Kit function Mean to the result. Cell I14, for example, contains:
=+@Mean(H14)
The function Mean requires the cell passed to it to be an output cell. That is the case here: we marked the result cells as outputs with the button “Ausgabe” (output) on the Risk Kit toolbar, which is why they are coloured orange. Figure 5 shows that before the simulation the cells display the message “Erst nach der Simulation definiert.” (only defined after the simulation), of which the narrow column shows only the beginning. After all, Risk Kit cannot see into the future and know from the outset which distribution the result has. This is why the expected value can only be calculated after the simulation.
Figure 5: The expected value of the result with the function Mean. Before the simulation, the cells show the beginning of the message “Erst nach der Simulation definiert.” (only defined after the simulation), and the column Rang (rank) shows the error message #WERT!, the German #VALUE! (German interface).
Exactly the same applies to the rank. In column J we look at the rank of a rule’s expected value among the ten expected values. Cell J5 contains:
=+RANG.GLEICH(I5;$I$5:$I$14)
It is the same function as for the quality, except that we have left out the last parameter. Then the highest result gets the 1 and the lowest the 10. As with German school grades, the best result is therefore in first place and the worst in tenth place. As long as the expected values are missing, the column shows the error message #WERT!.
Goal 2: getting the best
For the second goal, actually getting the best, we introduce a new result cell in column K. If the result really was 10, that is, the best, it counts a 1, otherwise a 0. Cell K5 contains the following formula, in which WENN is the German name of IF:
=+WENN(H5=10;1;0)
The expected value of this random variable is nothing other than the probability of getting the best. That is because it is the share of simulation runs in which the best came out. It is in column L, and in column M we again get the rank of each strategy.
Goal 3: a short waiting time
The last goal is to optimise the waiting time. Cell N5 contains:
=+WENNFEHLER(D5+F5;10)
If we were dealing with an error, the waiting time is 10, because then we always take the last one. Otherwise it is the length of the test phase plus the steps in the decision phase. That is the total time until we have actually managed to marry. With a test phase of 1, in Figure 1 for example, it is one candidate in the test and two steps after it, so 3. From this result cell we again calculate the expected value in column O, the mean waiting time.
Which strategy is best with ten candidates?
The model is now complete, and we can run the simulation. In the configuration we set 100,000 simulation runs. That is relatively many. But it is worth it in order to still distinguish strategies that lie very close together and to keep the Monte Carlo error, that is, the random inaccuracy of the simulation, as small as possible.
Figure 6 shows the result. Columns I to O now contain the expected values, the probabilities, the mean waiting times and the ranks. The colours range from yellow for the best to red for the worst values. Columns A to H only show a single sample.
Figure 6: The result of the simulation with 100,000 runs for ten candidates. Each row is a strategy with the test phase from column D (German interface).
Which test phase gives the best expected quality?
Let us first look at the goal of maximising the expected value. Here we reach the goal relatively quickly. The optimal strategy is to put a short test phase of length 2 in front and then move straight into the decision phase. It achieves an expected quality of 8.1. Very close behind comes the test phase of length 3, which is only very slightly worse and achieves an average quality of 8.0.
The strategy that does without a test phase altogether and marries immediately is also interesting. With rank 9 and an expected quality of 5.5 it does very badly. That is exactly the same value as for the strategy of testing for an extremely long time and then practically having to take the last one. Both strategies essentially forgo a decision: one takes the first under all circumstances, the other the last. In both cases this is a random candidate, and on average the qualities 1 to 10 have the value 5.5. It therefore makes sense that their expected values are the same. Which of the two ends up in last place and which in second to last depends somewhat on chance. Essentially both are the worst.
So it turns out that a certain amount of experience pays off. To maximise the expected value, however, you do not need an extreme amount of experience. Experience very quickly reaches the limit of its usefulness. Anyone who then wants to gather ever more experience tends to end up worse off rather than better. That is because they reject too many candidates, possibly including good ones.
How often do you get the best?
For the goal of getting the best, things are interestingly somewhat different. Here a test phase of 3 has the best value, followed by a test phase of length 4. With probabilities of 39.9% and 39.7%, the two are very close together. Here too, chance can still decide, so that the two swap places.
So you stay in the game a little longer in the test phase in order to really get the best. You do not want to be satisfied too early. But here too it is important not to wait too long. Otherwise the probability rises sharply that you have already seen the best in the test phase and lose them beyond reach.
Just as with the first goal, the strategy of not testing at all and marrying immediately is very bad. Here it is in rank 10. Testing for a very long time and thereby giving up all room for manoeuvre is just as bad, in rank 9. Both take a random candidate, the first or the last, and that candidate is the best in only about one run in ten. In Figure 6 it is 9.9% and 10.0%.
Between goal 1 and goal 2 the pattern is therefore the same. In both cases a certain amount of experience is good, too little is bad, and too much no longer helps. To find the best, however, you need somewhat more experience than to maximise the expected value.
How long does the search take?
The third goal, a short waiting time, is of course the domain of the strategy of marrying immediately. It always needs just a single step, so its mean waiting time of 1.0 is not random at all. This puts it undisputed in first place.
After that, the mean waiting time rises very quickly. With a test phase of length 1 it is already 3.8, practically four attempts, and with a test phase of length 2 it is 5.7, roughly six attempts. Then it goes to 7.0, and finally we need 8 to 9 or even 10 attempts on average. So the waiting time very quickly approaches very long values.
If we want to be done quickly, we may therefore actually have to accept compromises on the quality of the result. Speed does not necessarily go together with the other goals. The only compromise here is maximising the expected value. But even that already takes roughly six attempts out of ten, so we have seen roughly 60% of the candidates.
What changes with 30 candidates?
Now we can ask what happens if we expand our capacity. So far, as is usual in the old school, we have painstakingly got to know each candidate, evaluated them and then moved on to the next. That takes a great deal of time. If we add an app instead, we might be able to make contacts very quickly and evaluate many more candidates. What then happens to our strategies?
That is the second analysis. Its worksheet is not called “Old School” but “Tinder”. It is set up in exactly the same way, except that we now have a capacity of 30 candidates instead of 10. Swiping left or right does not count as an examination, only an actual date does. With an app that suggests possible partners, however, you get to a date much faster.
Figure 7 shows the result of this simulation. Rows 5 to 34 contain the strategies with a test phase from 0 to 29.
Figure 7: The result of the simulation with 30 candidates. A test phase of 4 gives the best expected quality, a test phase of 11 the highest probability of getting the best. Because of its number format, cell L5 shows the probability rounded to 0 (German interface).
With the larger capacity, the strategies move further apart, they now differ more clearly. If we want to maximise the expected value, here too we reach the goal very quickly. We only need a test phase of length 4, which achieves an expected quality of 25.9. With ten candidates it was a test phase of length 2. When capacity triples, the test phase has therefore only doubled. It has grown much less than proportionally.
Here too, the strategies of waiting very long or marrying very quickly are again both very bad, each with an expected quality of 15.5. A long waiting time very quickly leads to a worse expected quality of the partner. Some experience is good, but then we also have to come to a decision.
The probability of getting the best is highest with a test phase of length 11, at 38.1%. Second and third place, with the test phases 10 and 12, follow very closely with 37.9% each. A test phase of 11 is about 37% of the whole capacity. It would also turn out that this is broadly stable: with 10 candidates you have to look at about 3 to 4, with 30 candidates at 10, 11 or 12. That is the order of magnitude.
To reach this optimum we need considerably longer. The test phase is more than twice, almost three times as long as when optimising the expected value. And here too, the strategies of taking the first or only the last are exactly equally bad. They are in ranks 29 and 30.
Finally, the mean waiting time shows the same finding as with the small capacity. Marrying immediately is still in first place, after which the mean waiting time rises rapidly. At the optimum for the expected quality of the spouse, we are already at 12.5 candidates on average. At the optimum for the probability of getting the best, we have even looked at 22.3 candidates on average.
Where else do such decisions arise?
What is interesting about this model is that we can essentially apply it to all decision questions in which something unique is to be achieved. So there are no clones, and we cannot go back. We can look for a spouse.
But we can also buy a house, for example. The same house does not exist twice, there is no clone. There are certainly similar properties of similar quality, but not exactly the same one. Here too we have to decide whether we want it. Otherwise someone else buys it, and we cannot go back.
A second example is mergers and acquisitions. We want to buy a company that is offered to us. If we decline, it is gone, and someone else buys it. We may not be able to repeat this deal on the same terms. Or we are at an art auction at Christie’s and are offered works of art. We buy them, or we decline, and then someone else buys them.
Questions of this kind, in which we have to decide on the basis of the information that has been revealed to us up to a certain point, are very widespread. In principle, they can all be evaluated with methods like these.
How can the strategies be refined?
We could also refine the strategies by which we decide. For example, we could reject a certain number of candidates at the start as a matter of principle, just as before. Then we look at the next group. If one of them is better than the best from the test phase, we take them immediately. After that we might add the second best: if a candidate comes who is better than the second best we have ever seen, we take them. Later perhaps even the third best.
This way we guard against the danger that we have already seen the best, or perhaps even the second best, in the test phase or in the time after it in which we had not yet made a move. So we factor in a declining quality of the remaining sample.
This can be evaluated with exactly the same methods as we have shown here. The only problem is that the number of all possible strategies then suddenly becomes very large. The universe of all strategies is much larger than with our approach, and anyone who wants to evaluate them all has a great deal of calculating to do. This is why the simple approach is better suited as an illustration.
You can play with this approach: you see a stream of data bit by bit and want to react well to it. The model is a platform on which you can compare the reactions you have thought of, that is, the patterns by which you want to proceed, under different goals. You also see that different goals can lead to quite different optimal strategies.