Skip to content
Search
Generic filters
Exact matches only

Automating the DCF Valuation. Using a Monte Carlo Simulation to… | by Andrew Cole | Jul, 2020

Using a Monte Carlo Simulation to forecast Financials

Andrew Cole

The DCF (Discounted Cash Flow) Valuation model is perhaps the single most important financial tool that financial professionals can have. This model is great in theory and practice, but traditionally performed in excel, it can be quite tedious and cumbersome in function at times. Fortunately, using Python, we can automate many of these valuation steps and then also take it a step further and create a Monte Carlo Simulation function which will be used to visualize distributions of potential DCF outcomes.

Photo by Carlos Muza on Unsplash
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline

The first thing we need is a projection of our annual sales. We will create a DataFrame for six years into the future, with a starting sales figure which would theoretically be carried over from the previous financial statements.

years = ['2020A', '2021B', '2022P', '2023P', '2024P', '2025P']
sales = pd.Series(index=years)
sales['2018A'] = 31.0
sales

We now have structured our sales growth for the next five years (Note: “A” = Actual; “B” = Budget; “P” = Projected). We will now assign our projected annual sales growth to use for our future projections. Eventually, we can replace these estimates with probability distributions to get a more realistic projection.

growth_rate = 0.1for year in range(1, 6):
sales[year] = sales[year - 1] * (1 + growth_rate)

sales

Similarly, we now need to assign the values or formulas for all key variables within the DCF Model. These variables will be kept to standard financial definitions. (Note: Tax Payment will require an additional lambda function which ensures if profit before tax were to be negative, we wouldn’t have a positive tax payment).

ebitda_margin = 0.14
depr_percent = 0.032
ebitda = sales * ebitda_margin
depreciation = sales * depr_percent
ebit = ebitda - depreciation
nwc_percent = 0.24
nwc = sales * nwc_percent
change_in_nwc = nwc.shift(1) - nwc
capex_percent = depr_percent
capex = -(sales * capex_percent)
tax_rate = 0.25
tax_payment = -ebit * tax_rate
tax_payment = tax_payment.apply(lambda x: min(x, 0))
free_cash_flow = ebit + depreciation + tax_payment + capex + change_in_nwc

free_cash_flow

We now have our projected free cash flow (in millions). Now that we have our FCF, we can begin our actual DCF valuation model. We will calculate terminal value and discount all of the cash flows back to the present.

cost_of_capital = 0.12
terminal_growth = 0.02
terminal_value = ((free_cash_flow[-1] * (1 + terminal_growth)) /
(cost_of_capital - terminal_growth))
discount_factors = [(1 / (1 + cost_of_capital)) ** i for i in range (1,6)]dcf_value = (sum(free_cash_flow[1:] * discount_factors) +
terminal_value * discount_factors[-1])

Let’s wrap these variables to a Pandas DataFrame for easier viewing:

output = pd.DataFrame([sales, ebit, free_cash_flow],
index=['Sales', 'EBIT', 'Free Cash Flow']).round(1)
output

Because we used hard-coded values for the DCF Model variables, we don’t have a very robust model. Fortunately, we can improve upon this by introducing a Monte Carlo simulation which will use a normal distribution to randomly generate and sample a very large amount variable inputs and then plot them into a distribution visualization. We use numpy to generate these random samplings and then iterate through 10,000 times.

iterations = 10000def run_mcs():

# Create probability distributions
sales_growth_dist = np.random.normal(loc=0.1, scale=0.01, size=iterations)
ebitda_margin_dist = np.random.normal(loc=0.14, scale=0.02, size=iterations)
nwc_percent_dist = np.random.normal(loc=0.24, scale=0.01, size=iterations)

# Calculate DCF value for each set of random inputs
output_distribution = []
for i in range(iterations):
for year in range(1, 6):
sales[year] = sales[year - 1] * (1 + sales_growth_dist[0])
ebitda = sales * ebitda_margin_dist[i]
depreciation = (sales * depr_percent)
ebit = ebitda - depreciation
nwc = sales * nwc_percent_dist[i]
change_in_nwc = nwc.shift(1) - nwc
capex = -(sales * capex_percent)
tax_payment = -ebit * tax_rate
tax_payment = tax_payment.apply(lambda x: min(x, 0))
free_cash_flow = ebit + depreciation + tax_payment + capex + change_in_nwc

# DCF valuation
terminal_value = (free_cash_flow[-1] * 1.02) / (cost_of_capital - 0.02)
free_cash_flow[-1] += terminal_value
discount_factors = [(1 / (1 + cost_of_capital)) ** i for i in range (1,6)]
dcf_value = sum(free_cash_flow[1:] * discount_factors )
output_distribution.append(dcf_value)

return output_distribution

When we plot this simulation:

This output figure shows us that when iterated through 10,000 potential combinations of model input variables, our discounted cash flow value is most frequent around 23–25 million.

This is a very basic introduction to the powers which Python gives in automating financial operations, but the sheer computing power is clearly displayed by the Monte Carlo simulation which gave us the final figure we began looking for. Keep an eye out for future posts which will go in further detail on how to use Python for automation of everyday financial tasks.