Simulated NPV with Time-Series

Load Environment

Let's start by making sure all the tools we nned are loaded up. You will almost always need to load these packages up anytime you are build a Monte-Carlo model.

using Distributions
using Dates
using Gadfly
using StatsBase
using MCHammer
using DataFrames

Setup Inputs and Outputs

The next critical step is to setup key inputs, arrays and other important model parameters.

#Setup the Date Range for the analysis
dr = collect(Date(2019,1,01):Dates.Year(1):Date(2023,01,01))

#Setup Global Inputs
ForecastYrs = 5
Trials = 10000
Units = [5000, 10000, 17000, 18000, 20000]
InitialInvestment = 250000
Investment = [100000, 0, 0, 25000,0] #fill(0,ForecastYrs)

#Setup Outputs
Sensitivity_Tbl = []

ProjectNPV = []
USP = []
USC =[]
DR = []
OP =[]
Annual_CashFlows =[]

Any[]

Monte-Carlo simulation needs to generate a table of scenarios which are known as ***Trials***. A trial documents, in the form of a row, all of the inputs and calculated outputs for a particular scenario.

Using this results table allows you to runs all sorts of analysis, including sensitivity analysis and assigning probabilities to outcomes. To generate this table, you need to loop your equation/function as many times as you need and vary the inputs using probability distributions.

Another challenge to account for is that our example is a 5yr NPV model which requires building and analyzing the results over multiple periods. To extend the model, we are using MCHammer's GBMM function that allows to project a random walk forecast over how ever many periods you need, which extends automatically the model in Julia.

for i = 1:Trials

    UnitSellPrice = GBMM(80, 0.2, 0.1, ForecastYrs)
    UnitCost = GBMM(40, 0.1, 0.05, ForecastYrs)

    #Each period the discount rate is independent. If you use an additive method instead of multiplicative, you can end up with differences. These may or may not impact the decision. For simulation it is best to use the risk free rate.

        #Multiplicative Method
        DiscountRate = cumprod(rand(Normal(0.02,0.0075),ForecastYrs)+fill(1,ForecastYrs))#accumulate(+,rand(Normal(0.02,0.0075),ForecastYrs))+fill(1,ForecastYrs)

        #Additive Method
        #DiscountRate = accumulate(+,rand(Normal(0.02,0.0075),ForecastYrs))+fill(1,ForecastYrs)

        #a static DR
        #DiscountRate = accumulate(+, fill(0.02,ForecastYrs))+fill(1,ForecastYrs)

    #print(DiscountRate)

    #DCF Elements
    Annual_Sales = UnitSellPrice .* Units
    Annual_COGS = UnitCost .* Units
    OPEX = rand(TriangularDist(.2,0.5,0.35),ForecastYrs) .* Annual_Sales

    #Constant Dollar Cashflow
    #CashFlow_C = (Annual_Sales - Annual_COGS - OPEX - Investment)

    #Discounted CashFLow over multpile periods. This function uses arrays and DOT functions.

    CashFlow = (Annual_Sales - Annual_COGS - OPEX - Investment) ./ DiscountRate

    #Calculated Output
    Trial_NPV = sum(CashFlow)-InitialInvestment

#Convert Arrays to Scalars for sensitivity analysis
    push!(ProjectNPV, Trial_NPV)
    push!(USC, mean(UnitCost))
    push!(USP, mean(UnitSellPrice))
    push!(DR,  mean(DiscountRate))
    push!(OP,  mean(OPEX))
    push!(Annual_CashFlows,  CashFlow)

end

Setting up data for analysis and charting

Setup inputs/outputs(above) and output tables (below) for sensitivity analysis and charting. Since correlation is based on the same math as regression, the only way to calculate sensitivity on an Array > 1 (in this case multiple years) is to condense the array into a scalar value using either mean, sum or any other transform because what ever you pick will generate a similar or identical result.

Sensitivity_Tbl = DataFrame(hcat(ProjectNPV, USC, USP, DR, OP))
names!(Sensitivity_Tbl, [:ProjectNPV, :USC, :USP, :DR, :OP])
NPV_Sensitivity = cormat(Sensitivity_Tbl,1)

5×5 Array{Float64,2}:
  1.0        -0.268443     0.903869   -0.0169668    0.602936
 -0.268443    1.0         -0.0100426  -0.00161571  -0.00494222
  0.903869   -0.0100426    1.0         0.0116294    0.835425
 -0.0169668  -0.00161571   0.0116294   1.0          0.00784858
  0.602936   -0.00494222   0.835425    0.00784858   1.0

Stats

Generate model results and list all the outputs for your charting and analysis functions.

print("Project Mean: ", mean(ProjectNPV),"\n")
print("Project Std.Dev: ", std(ProjectNPV),"\n")
print("Prob. of Neg. NPV: ", GetCertainty(ProjectNPV,0,0),"\n")
print("NPV p10, p50, p90 : ", quantile(collect(Float64, ProjectNPV),[0.1,0.5,0.9]),"\n")
println("")
println("OUTPUTS: Annual_CashFlows, ProjectNPV, Sensitivity_Tbl")
println("date range = dr")

Project Mean: 2.5276551743783243e6
Project Std.Dev: 1.0689942031448558e6
Prob. of Neg. NPV: 0.0016
NPV p10, p50, p90 : [1.2033067273826795e6, 2.463327457125737e6, 3.9152107803786374e6]

OUTPUTS: Annual_CashFlows, ProjectNPV, Sensitivity_Tbl
date range = dr

To generate a complete list of percentiles, use the fractiles().

fractiles(ProjectNPV)

11×2 Array{Any,2}:
 "P0.0"    -590221.0
 "P10.0"         1.20331e6
 "P20.0"         1.60518e6
 "P30.0"         1.93304e6
 "P40.0"         2.19838e6
 "P50.0"         2.46333e6
 "P60.0"         2.72835e6
 "P70.0"         3.03693e6
 "P80.0"         3.38385e6
 "P90.0"         3.91521e6
 "P100.0"        7.52754e6

##Visualizing the outputs

Looking at the Probability Distribution

histogram_chrt(ProjectNPV, "Five Year NPV")

density_chrt(ProjectNPV, "Five Year NPV")

What variables are most influential on my output distribution?

sensitivity_chrt(Sensitivity_Tbl, 1, 3)

What does my cashflow look like over time?

The trend chart is a median centered chart that establishes a 90% confidence interval for each period. Remember dr or the date range is specified at the top.

CashFlow forecast

trend_chrt(Annual_CashFlows, dr)