MODEL BUILDER 2.6: The N-sided Die Example

To help piece together all of the basic elements, we are going to run a more sophisticated simulation. This simulation is done using both the Excel sheet and VBA code. While simulations in finance can be done on the sheet, it is important to understand code-based solutions because as we move on to more advanced concepts through this book there are sections that will simply be far too inefficient to be done on the sheet. If you are struggling with any of the code, refer to the completed version of the Model Builder available on the website.

1. Open a new workbook, save and name it MB2.6_User.xls.
2. Name the first sheet “UserSimulate” and enter the following labels in the corresponding cells:

   B3: “Number of Sides”
   B4: “Iterations”
   B5: “Sample Size”
   E3: “Simulated Probability”
   E4: “Error of Probability”
   E5: “Actual Probability”

3. Enter the following initial values in the corresponding cells:

   C3: 6
   C4: 1000
   C5: 50

4. Since we will be using VBA, we should name the inputs and certain output cells on the UserSimulate sheet. Name the following cells with the corresponding names:

   C3: nSides
   C4: iterations
   C5: numOfRolls
   F3: expectedProb
   F4: errOfProb

5. Figure 2.10 shows how the sheet should look thus far. Open the Visual Basic Editor and insert a module. Name the module “SimFunctions”.

   FIGURE 2.10 Building out the inputs with proper naming is important for the VBA code later.

   

6. In the SimFunctions module we will create a few subroutines and functions. We will start with the first main subroutine and as we come to additional functions and subroutines that are required we will note them and come back to them later in this Model Builder. The first subroutine we will create is called GenRndNumDist. Initialize a subroutine with this name.
7. Next declare the following variables:

   Dim iNSides As Integer
   Dim dProb As Double, adSimProb() As Double
   Dim lIterations As Long, lNumOfRolls As Long, lI As Long, lJ As Long,
   alResult() As Long
   Dim scratch As Range

   Notice that we have created variables and arrays (denoted by the () after the name).

8. The next piece of code is for error handling, in case a user forgets to put in valid assumptions. This is a particularly important habit to get into for simulation models since we don't want to set off a simulation for thousands of iterations with invalid inputs. Enter the following code for the error handling:

   If Not (CheckInputs) Then
       Exit Sub
   End If

9. Turn off screen updating to save time. In some cases we may leave this on to show the results of a simulation via a chart, but for the most part we should always turn it off for any subroutine:

   Application.ScreenUpdating = False

10. Like error handling, we should get in the habit of clearing any Excel workspace that is filled by VBA. This prevents latent information from previous simulations from being incorporated incorrectly into a new analysis:

    Range(Range(“BinStart”), Range(“BinStart”).Offset(150, 3)).Clear

11. We should now assign user-entered values from the Excel sheet:

    iNSides = Range(“nSides”).Value
    lIterations = Range(“iterations”).Value
    lNumOfRolls = Range(“numOfRolls”).Value

12. Once we have values drawn in from the Excel sheet, we can resize our arrays so they fit the input data for the simulation:

    ReDim alResult(1 To lIterations)
    ReDim adSimProb(1 To lIterations)

13. Specific to this simulation is the fact that we know what the probability of an n-sided die roll should be. It is simply 1 divided by the number of sides:

    dProb = 1 / iNSides

14. Code is extremely conducive to simulation, particularly with functionality such as looping. The completion of a loop in itself is an iteration. However, in this example we actually have more than one loop in each iteration due to the possibility of rolling the die multiple times each iteration. For this example we will have two loops, one nested within another. The outer loop is the iteration loop, while each completion of the inner loop is a simulated roll of the die. In general (and there can be exceptions), the outermost loop of a simulation will be an iteration:

    For lI = 1 To lIterations
      For lJ = 1 To lNumOfRolls

15. Within each loop we generate a uniform pseudorandom number and compare it to the probability that is expected. If the number is less than or equal to the probability, then we log a value of 1, if not, nothing is logged. This is repeated for each roll of the die.

    If (Rnd <= dProb) Then
        alResult(lI) = alResult(lI) + 1
    End If
     Next lJ

16. For each iteration we need to figure out the simulated probability. Since this is dependent on the sample size or the number of rolls of the die, we must enter the following code:

    adSimProb(lI) = alResult(lI) / lNumOfRolls
    Next lI

17. Once the loops are complete and our arrays are filled, we need to export the results to the Excel sheet:

    Range(“expectedProb”).Value = MyMean(adSimProb)
    Range(“errOfProb”).Value = Sqr(MyVar(adSimProb))

18. Two additional subroutines help us visualize the information:

    Call CopySimResultsToScratch(adSimProb, lIterations)
    Call SetupHistogramBins

    These two subroutines export the iteration results to a scratch sheet so we can create a histogram. The second subroutine listed (SetupHistogramBins) creates the bins for the histogram. This can be done on the sheet or in code depending on the functionality that the user desires.

    FIGURE 2.11 The Excel sheet is populated with results from the CopySimResultsToScratch subroutine.

    

19. Finally, we wrap up the code by turning screen updating back on and selecting the active sheet that we want to view after the simulation runs.

    Range(“nSides”).Select
    Application.ScreenUpdating = True
    End Sub

20. The main subroutine in steps 1 to 19 requires two additional subroutines that were called in step 18. The first one of these subroutines, CopySimResultsToScratch, is an interesting one to learn from because it shows a method of exporting simulation data based on the number of iterations (which can be different from simulation to simulation). Figure 2.11 depicts the output range. Since this is a supplementary and relatively simplistic subroutine, the entire uncommented code is shown here, rather than going through it in sections:

    Private Sub CopySimResultsToScratch(adSimProb() As Double,
    lIterations As Long)
      Dim scratch As Range
      Dim lI As Long

    Worksheets(“scratch”).Range(Range(“start”), Range(“start”)
      .End(xlDown)).Clear
    
      Set scratch = Worksheets(“scratch”).Range(Range(“start”),
      Range(“start”).Offset(lIterations-1))
    
      scratch = Application.WorksheetFunction.Transpose(adSimProb)
    End Sub

    The interesting part to this subroutine is that it accepts the number of iterations and the probability that we are trying to return for each iteration as a parameter. The subroutine then clears the export area, sets a range based on the number of iterations and writes out the probability array to that range.

21. An additional subroutine that is used, which is an optional code-based solution to creating histogram bins, is called SetupHistogramBins. Like CopySimResultsToScratch, this subroutine is simple and is provided in its entirety here:

    Private Sub SetupHistogramBins()
      Dim i As Integer
      Dim binRng As Range
    
      Worksheets(“scratch”).Range(“$B$1:$B$101”).ClearContents
      Set binRng = Worksheets(“scratch”).Range(“bins”)
    
      For i = 0 To 100
        binRng.Offset(i).Value = i * 0.01
      Next i
    End Sub

    The key to this subroutine is that it makes 100 bins at increments of .01 in the For Next loop. The .01 hardcoded figure could be replaced by a variable that is assigned from the Excel sheet inputs section if a user wanted flexibility in changing this.

22. Finally there are three custom functions; two used for calculation purposes and the third for the error handling. The first one in order of the provided Excel workbook is the error-handling function called CheckInputs (the commenting has been left in to explain each section of code):

    Private Function CheckInputs() As Boolean
    
     ' Check to make sure that the number of dice sides is greater
     than 0.
     If Not (Range(“nSides”).Value > 0) Then
         MsgBox Prompt:=“Please enter a nonzero positive value for N
         Sides”
         CheckInputs = False
         Exit Function
     End If

    ' Check to make sure that the number of iterations sides is greater
     than 0.
     If Not (Range(“iterations”).Value > 0) Then
         MsgBox Prompt:=“Please enter a nonzero positive value for the
         number iterations”
         CheckInputs = False
         Exit Function
     End If
    
     ' Check to make sure that the number of rolls is greater than 0.
     If Not (Range(“numOfRolls”).Value > 0) Then
         MsgBox Prompt:=“Please enter a nonzero positive value for
         number
         of rolls per iteration”
         CheckInputs = False
         Exit Function
     End If
    
        CheckInputs = True
    End Function

    The first calculation function is MyMean:

    Function MyMean(ByRef avInput() As Double) As Double
    
        Dim dSum As Double
        Dim lCnt As Long
        Dim lJ As Long
    
        dSum = 0
        lCnt = 0
    
        For lJ = LBound(avInput) To UBound(avInput)
            If IsNumeric(avInput(lJ)) Then
               dSum = dSum + avInput(lJ)
               lCnt = lCnt + 1
            End If
        Next lJ
    
        If lCnt = 0 Then
            Exit Function
        End If
    
        MyMean = dSum / lCnt
    End Function

    This function is essentially an average function that takes the simulation probabilities from the main subroutine and averages them out by summing them and dividing by the count. The interesting part of this function is the use of LBound and UBound to define the number of loops for the For Next loop. This technique is useful because when we are running simulations we are going to want to be efficient in our looping.

    The final function is MyVar, which is essentially the same as MyMean, but it calculates the variance:

    Function MyVar(ByRef avInput() As Double) As Double
    
        Dim dMean As Double
        Dim lJ As Long
        Dim lCnt As Long
        dMean = MyMean(avInput)
    
        MyVar = 0
        lCnt = 0
    
        For lJ = LBound(avInput) To UBound(avInput)
            If IsNumeric(avInput(lJ)) Then
                MyVar = MyVar + (avInput(lJ)-dMean) ^ 2
                lCnt = lCnt + 1
            End If
        Next lJ
    
        If lCnt = 0 Then
            Exit Function
        End If
    
        MyVar = MyVar / lCnt
    End Function

    The main difference between MyVar and MyMean function is the calculation that is done in the For Next Loop. In MyVar the variance is being captured, while in MyMean the mean is recorded.

23. As a final step you may want to create a button on the Excel sheet and assign the GenRndNumDist subroutine to it so you can quickly run the simulation from the Excel sheet.

    While this Model Builder is simple, it is a good example of the basic inputs and outputs seen in many simulations. This Model Builder also exemplifies a few thoughts we should have in efficiency:

    • For Excel-based systems it is easy to store the inputs on the sheet, draw them all into VBA, process the results in VBA, and then export them back to the sheet for easy viewing. Figure 2.12 exemplifies proper setup. It is incredibly inefficient to do everything on the sheet or to use VBA to loop through sheet-based calculations.
    • Since the number of iterations can frequently change, it is useful to work with dynamic arrays that are cleared and resized based on the number of iterations.
    • User-defined functions process faster than calling functions from the WorksheetFunction library. Learning how to create user-defined functions is also important because in more advanced simulations there are often complex calculations that have not been created in predefined functions.

    The other important take away from this Model Builder is an initial understanding of the statistical foundations of simulations. Take some time to run the subroutine by changing the inputs and looking at the resulting histogram. The first change you should try is lowering the number of iterations. Change it from 1000 to 50 and run the simulation. Looking at the histogram chart you will see the distribution is very choppy. With lower iterations there is higher variance in the results and what should be a smooth normal distribution is all over the place. Using data parsed by confidence intervals from such a result could be misleading.

    FIGURE 2.12 An efficient simulation draws all inputs into code for calculation and exports results back to the sheet for easy interpretation and analysis.

    

    Alternatively, reset the iterations to 1000 and this time lower the sample size to an extreme level such as 5. Notice now that the distribution looks off because of a small sample, but also look at the error (range errOfProb on sheet). The difference in the error is much higher with a smaller sample size. This similarly will lead to misleading results.