Written by:Eric Torkia
12/12/2008 2:10 PM 

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 etorkia@crystalballservices.com

TrackbackPrint

Tags:

Categories:

Location: BlogsThe Cutting Edge