If you haven’t read the first two posts introducing the concept of Monte Carlo simulations and running through a basic, code-free example, you should do so (Part I, Part II). This post also assumes you have some basic familiarity with VBA or general programming experience.
We’re going to continue with the example from Part II in this series:
“Suppose we work for a small clothing manufacturing company in the midst of a lawsuit. Apparently, our firm allegedly dumped a little bit of excess blue dye into a nearby lake and turned a hundred nearby campers blue. Predictably, government regulators have levied a fine that is due in one years time and the campers have filed a joint civil suit as being dyed blue was not to their liking.
The company failed to reach a settlement after offering each plaintiff $1000 and is going to court in a case that will take a year. It’s possible the company will win, and have to pay out no money. It’s also possible that the firm will have to pay the maximum penalty allowed by law – $3,000 per plaintiff. Obviously there’s quite a bit of variability between $0 (we win) and $300,000 (we lose and pay the maximum penalty to each of the one hundred plaintiffs).
This makes planning for the future difficult – our management doesn’t know how much money they should prepare to pay in a year. If they set aside the maximum and it turns out we win, they may have lost the opportunity to invest that money in projects that could grow the business or increase its margins. If they set aside too little, they may have to scramble to come up with extra cash when the court case is decided – that could mean taking a loan or being forced to sell some current assets.
In this case, our company had been planning a major capital investment and senior management needs to know how likely it is that the company will need to pay out more than $200,000. Any more than that and the firm needs to put its investment plans on hold.”
Using VBA to Insert the Function – Declarations
This first technique is extremely simple and misses out on a lot of the power that VBA brings to Excel – but for basic Monte Carlo simulations it will save you a little time and requires only a novice level of skill with VBA.
The first thing we’re going to do is declare our sub, titled “SimpleMonteCarlo” in purple, and we’re going to follow that up with declarations of some key variables (using “Dim” for “dimension”) in red text:
Sub SimpleMonteCarlo()
Dim Cutoff As Long
Dim Plaintiffs As Integer
Dim JuryAward As Long
Dim simulations As Long
Cutoff = 200000
Plaintiffs = 100
simulations = 100
For simplicity’s sake I’ve only declared our key variables as Long integer and Integer data types. The input assumptions we’ve been working with consisted of no decimals in and of themselves, and for those new to VBA I don’t want to throw too much at you.
The final chunk of code in blue is assigning a value to the variables. As stated before, we want to examine a cutoff of $200,000 for management – so we assign that value to that variable. Plaintiffs number 100, and for simulations I’ve left it at 100 for now. This is an incredibly low number for a Monte Carlo, but faster for testing a model as you’re building it. You can see already a key advantage of using VBA – we can easily alter the number of simulations just by changing that one line of code. No copy/pasting, no dragging of cells or deleting formulas – just change a number.
A Simple Loop
Loops are used in programming to repeat a process or set of instructions a certain number of times. You can specify very complicated conditions on the process and also specify conditions that will cause the loop to stop repeating itself (after a certain number of repetitions perhaps, or maybe after a desired output).
For JuryAward = 1 To simulations
Workbooks(“MC1”).Sheets(“MC”).Cells(JuryAward, 1).Formula = “=NORM.INV(RAND(),1100,400)”
Next JuryAward
Let’s look at the first line of code:
For JuryAward = 1 To simulations
This tells Excel that we’re going to be looping through a set of instructions. “JuryAward = 1 to simulations” tells Excel to start with JuryAward being on its “first” repetition, and to repeat the process for JuryAward until we have finish the number of repetitions we entered as a value for simulations – in this case that means we will do 100 loops. Let’s examine the next line:
Workbooks(“MC1”).Sheets(“MC”).Cells(JuryAward, 1).Formula = “=NORM.INV(RAND(),1100,400)”
We’ll start at the end of the line – this should be familiar.
.Formula = “=NORM.INV(RAND(),1100,400)”
The code is specifying that we will write the Excel formula “=NORM.INV(RAND(),1100,400)” into a series of cells. The series of cells is determined by our loop:
Workbooks(“MC1”).Sheets(“MC”).Cells(JuryAward, 1)
First we’ve told Excel which workbook and which worksheet in that workbook. In the Workbook named “MC1” (this is what I’ve named my workbook, yours may be different) inside a worksheet named “MC” (again, I named my worksheet MC, yours may be different).
Second, we’ve specified which cells to insert our formula into. To better understand, consider the arguments that “Cells” takes:
Cells(Row #, Column #)
Looking back at our code, you can see that “JuryAward” is our row # and we have a 1 in our column # (this means column A). This R1C1 notation (specifying cell locations using numbers only, no letters) is common and useful – consider our loop. Let’s look at our last line of the loop:
Next JuryAward
This line of code simply tells Excel that once it has finished the instructions we gave it (write the formula into a cell), it will move onto the next JuryAward, over and over until it reaches whatever number we input for simulations.
The last line of code closes the sub:
End Sub
Walking Through the Loop
Setting aside the code for a second, lets discuss what actually happens when we run this script.
Excel will begin in Row 1, Column 1 (Cell A1) and will perform the instruction – which is to insert the function we used in Part II of Monte Carlos. After it has done that, it will see the code “Next JuryAward”.
Excel will then perform the loop again, this time however JuryAward will adjust to reflect that this is our second loop. JuryAward will now = 2, and our cell reference inside the loop will change from the first row of the first column to the second row of the first column. Excel will then write our function inside that cell, see the “Next” argument and return to the loop.
You can probably guess what happens now. Excel will perform its third loop, JuryAward and our cell reference both adjust to reflect that, and Excel will write our function into the third row of the first column (this would be cell A3).
Excel will perform the loop until JuryAward reaches 100, its 100th loop.
If we wanted 1,000 simulations we could just set simulations = 1000. Or 1,000,000. Excel would then follow the same process, but write the function to more cells. This is a simple and easy way to get a little more power and flexibility out of our basic MC, but not have to do very much coding at all. It’s not recommended, but you can image how this technique can be adapted slightly. Keep in mind as well that the data type you’ve specified for your variables may constrain your choice of values. We’ll work with more flexible data types as we continue to build MCs.
Our entire code looks like this:
Sub SimpleMonteCarlo()
Dim Cutoff As Long
Dim Plaintiffs As Integer
Dim JuryAward As Long
Dim simulations As Integer
Cutoff = 200000
Plaintiffs = 100
simulations = 100
For JuryAward = 1 To simulations
Workbooks(“MC1”).Sheets(“MC”).Cells(JuryAward, 1).Formula = “=NORM.INV(RAND(),1100,400)”
Next JuryAward
End Sub
Concluding
If you run the code we’ve written (adjusting worksheet and sheet names as needed) you’ll see that we didnt calculate total cost, or calculate the likelihood of the firm having to spend more than $200,000. We won’t be.
You can fairly easily change the formula we’re inserting so that the randomly simulated jury award is multiplied by the number of plaintiffs, and then add $15,000 to it:
Workbooks(“MC1”).Sheets(“MC”).Cells(JuryAward, 1).Formula = “=15,000 + 100 * NORM.INV(RAND(),1100,400)”
If we did that, the name “JuryAward” would be pretty misleading since we’d be calculating the total cost – but you could easily add a new variable “TotalCost”, and insert it instead of “JuryAward” to make things clearer.
From there you could pretty quickly set up bins, a frequency distribution for our visualization and also calculate the probability of a cost greater than $200,000 – exactly like we did in Part II. However, our time is better spent learning how to program the entire Monte Carlo without writing Excel functions into cells, and getting our output via VBA as well. Now that we understand how to declare data types and assign values, as well as run a basic loop – we’ve got the necessary foundations to get waist deep into some more code in Part IV.
=COUNTIF(A1:A100000, “<=”15000)