Excel-VBA script for running simple Monte-Carlo simulations in Excel (no add-ins required).
Before buying Oracle Crystal Ball it might be a good idea to try doing the same simulation using vanilla Excel tools.
Important assumption - all variables have a normal distribution.
Download File - Simple-Monte-Carlo.xlsm
The code:
Option Explicit
Sub GetDistribution()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim Values(6) As Double
Dim RandNPV As Double
Dim NumSim, row, col, i As Long
NumSim = ThisWorkbook.Sheets(1).Cells(15, 3).Value
'Run Simulations
For i = 1 To NumSim
'Read values
For col = 1 To 6
Values(col) = ThisWorkbook.Sheets("Sheet1").Cells(8, col + 2).Value
Next col
RandNPV = ThisWorkbook.Sheets("Sheet1").Cells(11, 3).Value
'Record Results
For col = 1 To 6
ThisWorkbook.Sheets("Sheet2").Cells(i + 1, col + 1).Value = Values(col)
Next col
ThisWorkbook.Sheets("Sheet2").Cells(i + 1, 1).Value = RandNPV
Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub