Analytics Articles

Sequential Simulations using Crystal Ball VBA

Eric Torkia, MASc

Share:

Print

Rate article:

4.0
Rate this article:
4.0

If your objective is to feed one simulation with the results of another, VBA is the way to go. Working on a client assignment, we took the results from a first simulation to configure the parameters of the second. Of course VBA is not the only way, their is also the manual approach.

If all your simulations are in the same workbook, one of the first things that you need to manage is how you will isolate your numbers. For this reason I personally like working with straight values rather than formulas. In crystal ball you can auto-extract most parameters (e.g. mean, median, kurtosis, percentiles, etc.)  from a forecast right into your worksheet.

Alternatively, you can use the CB.GetForeData, CB.GetForePercent, CB.GetForeStat formulas to get the information you need from the first simulation and use copy/paste special - values. In either case we are working with values.

What would happen if we used the formulas only? When we would run the second simulation, the parameters would change as the simulation would run, thus potentially skewing the results.

Ok, so what are the best practices to get this up and running?

  1. Define range names for the cells that contain the number of Trials for both Sim 1 and Sim 2
  2. Define range names for all the assumptions and forecasts data that you want to move around - both source and target ranges
  3. Run your first simulation and copy the values to the range/worksheet containing the second simulation. Keep in mind that this step is what freezes your second simulations inputs. This you can code with VBA (as I did below) or you can copy and paste the values only before running your second simlation
  4. The code below allows to set the trials in the worksheet itself without having to change the run preferences each time you want to run a simulation
  5. Do not forget to set the visibility of the forecasts you want and those you do not as they will appear when you launch the simulation
  6. Use form objects such as buttons to launch the code

Sub RunSimulation()
' Obtain inputs for Simulation 2 by running the simulation 1
'Set Trials for 1st sim
    Dim Trial
    Trials = Range("Trials_Sim1").Value
    MsgBox "Number of Trials for Simulation 1: " & Trials

 

'Setup Crystal Ball by resetting the simulation, setting Extreme Speed in the Run Prefs, run trials f while suppressing charts
    CB.ResetND
    CB.RunPrefsND cbRunMode, cbRunExtremeSpeed
    CB.Simulation Trials, , True, False, False, "Running Probabilistic Reserve Analysis", True

'Obtain final forecast by running the second simulation
    CB.ResetND
    copy_parameters
    Trials = Range("Trials_Sim2").Value
    MsgBox "Number of Trials for Simulation 2 Analysis: " & Trials
    CB.RunPrefsND cbRunMode, cbRunExtremeSpeed
    CB.Simulation Trials, , False, False, False, "Running Sim 2 Analysis", True

End Sub

Sub copy_parameters()
'This routine is to copy the probability parameters for the second simulation

Range("Sim1_Result_1").Copy
Application.Goto Reference:="Sim2_Input_1"
Range("Sim2_Input_1").Select
ActiveSheet.Paste
Range("Sim1_Result_2").Copy
Application.Goto Reference:="Sim1_Result_2"
Range("Sim2_Input_2").Select
ActiveSheet.Paste

 End sub

Hopefully, with this script template you will be able to get a sequential set of sim,ulations going in no time.... if you have any questions on how to apply this in your projects, please don't hesitate to drop me a line at [email protected]

Comments

Collapse Expand Comments (0)
You don't have permission to post comments.

Introduction to Tolerance Analysis (Part 1 / 13)

Aug 10 2010
36
0

Tolerance Analysis is the set of activities, the up-front design planning and coordination between many parties (suppliers & customers), that ensure manufactured physical parts fit together the way they are meant to. Knowing that dimensional variation is the enemy, design engineers need to perform Tolerance Analysis before any drill bit is brought to raw metal, before any pellets are dropped in the hopper to mold the first part. Or, as the old carpenter's adage goes: Measure twice, cut once. 'Cause once all the parts are made, it would be unpleasant to find they don't go together. Not a good thing.

Remembering to pick right learning curve

Jun 17 2010
34
0

This post presents 2 popular learning curve methods for estimating how a person or organization benefits from repeat learning.

This technique is key for the project risk analyst.

Algorithms and the New Millennium

Dr David Berlinski (2000) makes the historical observation that two great ideas have most influenced the technological progress of the Western world:

The first is the calculus, the second the algorithm. The calculus and the rich body of mathematical analysis to which it gave rise made modern science possible; but it has been the algorithm that has made possible the modern world. (Berlinski, p. xv)

Dr Berlinski concludes that:

The great era of mathematical physics is now over. The three-hundred-year effort to represent the material world in mathematical terms has exhausted itself. The understanding that it was to provide is infinitely closer than it was when Isaac Newton wrote in the late seventeenth century, but it is still infinitely far away…. The algorithm has come to occupy a central place in our imagination. It is the second great scientific idea of the West. There is no third. (Berlinski, pp. xv-xvi)

Source: Berlinski, D (2000). The Advent of the Algorithm: The 300-Year Journey from an Idea to the Computer. San Diego, CA: Harcourt.

Related Posts: Enter the Algorithm

Decision Warranties

According to Prof Ronald A Howard (1992):

Three of the warranties that I would like to have in any decision situation are that:
  1. The decision approach I am using has all the terms and concepts used so clearly defined that I know both what I am talking about and what I am saying about it;
  2. I can readily interpret the results of the approach to see clearly the implications of choosing any alternative, including of course, the best one; and
  3. The procedure used to arrive at the recommendations does not violate the rules of logic (common sense).

Plain and simple... Source: Howard, R A (1992), Heathens, Heretics, and Cults, Interfaces, 22(6), 15-27.

Collaborative modeling using predictive analytics

When building models we are often confronted with assumptions that evolve over time. In most cases it is important to capture these changes to keep our model relevant. Over the last decade, Business Intelligence solutions have created a culture of self-service IS information.  Given this democratization and decentralized access to data has created many opportunities and pitfalls for business analysts and decision-makers. We are going to outline some opportunities and pitfalls relating to shared modeling and a few strategies to get started.

This post presents the opportunities and challenges stemming from moving towards a distributed modeling paradigm in the organization. Also presented is a high-level integrated predictive/collaborative planning process.

Risk versus Uncertainty

Prof Frank H Knight (1921) proposed that "risk" is randomness with knowable probabilities, and "uncertainty" is randomness with unknowable probabilities. However, risk and uncertainty both share features with randomness. The illustration below explains the relationship of the concepts better than words...

Source: Knight, F H (2002/1921), Risk, Uncertainty and Profit, Washington, DC: BeardBooks.

 

RESEARCH ARTICLES | RISK + CRYSTAL BALL + ANALYTICS