[COLOR=#3333ff]Option Explicit
Option Base 1
[COLOR=#009900]'**********************************************************************************[/COLOR]
[COLOR=#009900]'* Run Monte Carlo Simulation *[/COLOR]
[COLOR=#009900]'**********************************************************************************[/COLOR]
Sub MonteCarlo()
Dim Iteration As Long, i As Long
Dim Q As Double, P As Double, TR As Double
Dim VC As Double, FC As Double, TC As Double
Dim SdVC As Double, MeanVC As Double, SdP As Double, MeanP As Double
Dim MinQ As Double, MaxQ As Double, AverageTP As Double, SumTP As Double
Dim ProfitX As Double, CountNo As Double
Iteration = Range("C3").Value
FC = Range("C7").Value
MinQ = Range("C13").Value
MaxQ = Range("C14").Value
MeanVC = Range("C15").Value
SdVC = Range("C16").Value
MeanP = Range("C17").Value
SdP = Range("C18").Value
ProfitX = Range("B24").Value
ReDim TP(Iteration) As Double
SumTP = 0
CountNo = 0
For i = 1 To Iteration: Cells(12, 3) = i
VC = Truncate_Normal_VC(MeanVC, SdVC, MeanVC / 2, MeanP)
P = Truncate_Normal_P(MeanP, SdP, 1)
Q = Int((MaxQ - MinQ + 1) * Rnd + MinQ)
TC = FC + VC * Q
TR = P * Q
TP(i) = TR - TC
[COLOR=#009900]'Comment out the following will make the simulation run faster[/COLOR]
Cells(5, 3) = Q
Cells(6, 3) = P
Cells(8, 3) = VC
Cells(9, 3) = TC
Cells(10, 3) = TR
Cells(11, 3) = TP(i)
If TP(i) > ProfitX Then CountNo = CountNo + 1
SumTP = SumTP + TP(i)
Next i
AverageTP = SumTP / Iteration
Cells(25, 7) = AverageTP
Cells(24, 3) = 1 - CountNo / Iteration
Call Sort(Iteration, TP)
Call Hist(Iteration, 40, TP(1), TP(Iteration), TP)
For i = 1 To 20
Cells(i + 3, 6) = 1 - (0.05 * i)
Cells(i + 3, 7) = TP(Int(Iteration / 20 * i))
Next i
Cells(3, 6) = "Close to 100%"
Cells(13, 6) = "Median = 50%"
Cells(23, 6) = "Close to 0%"
Cells(3, 7) = TP(1)
End Sub
[/COLOR]