# 134|PYTHON – Monte Python and Valuation Madness

BYU Student Author: @IWillyerd
Reviewers: @Jae, @Mark
Estimated Time to Solve: 75 Minutes

We provide the solution to this challenge using:

• Python

The challenge solution uses both the Pandas library and “Random” library.

Overview
You are an intern at a small private equity firm. Your boss Siobhan recently brought you a task that you need to finish up by the end of the day. She provided you with the financial statements from a local startup that came through her desk, and wanted to see what you could rustle up for some valuation numbers. But the issue is Siobhan isn’t sure on some of the values as there aren’t a lot of solid comparable companies for this type of business. So, she wants you to create a Monte Carlo Simulation that will take some ranges to get a better sense of what the offer should be. Along with the financial statements she provided you with the following ranges that she felt good about:

• Growth Rate for the first 5 Years from 2 - 6.5%
• Weighted Average Cost of Capital (WACC) from 3 – 9%
• Terminal Growth Rate from 0.5 – 2%

You know this kind of task is something that really comes to the interns often, and to show off a little bit you decide to create a python function that will do it for you so that you can reuse it quickly later.

To calculate an equity estimate you’ll use the following format:

Instructions

1. Read in the tabs the from the given excel workbook and create three dataframes for each tab to store the financial statement information.
Suggestions and Hints
``````#I used the following code to read in each of the sheets
``````
2. Clean the data, ensure there is no whitespace in the account description. Ensure there is an easy way to reference both the year and the account to capture the information for use in the valuation.
Suggestions and Hints
``````#I used the following code to clean up the Balance Sheet and create a readable dataframe
bs_df = bs_df.iloc[2:,1:].reset_index(drop=True)#deletes the first two rows and first column
bs_df = bs_df.rename(columns = {"Balance Sheet":"Account",'Unnamed: 2':'2021','Unnamed:             3':'2022'})#Renames Columns
bs_df['Account'] = bs_df['Account'].str.strip()#Deletes whitespace from account description
bs_df.set_index(['Account'],inplace=True)#Sets the Account Column as Index
``````
3. Create a small function that will calculate the present value of a cash flow, taking the value of the cash flow, the rate applicable, and the number of periods.
Suggestions and Hints
``````#The following code can be used to create a PV function
def present_value (FV,r,n):
PV = FV/((1 + r) ** n)
return PV
#PV = the Present Value you want to return
#FV = the Future Value, in this case the future cash flow
#r = the discount rate, in this case the WACC
#n = the number of periods you want to discount by (1 for CF Y1, 2 for CF Y2 etc.)
``````
4. Create initial variables or lists for all the following:
• Free Cash Flow for 2022 (Calculated as Operating Cash Flow minus Investing Cash Flow)
• Net Debt (Calculated as Long-term debt – Cash)
• EBITDA for 2022
• Range for the initial growth rate (given above)
• Range for the WACC (given above)
• Range for the terminal growth rate (given above)
Suggestions and Hints
``````# I used the following code to reference a specific amount in each of the financial statements
inv_cf = cf_df.loc['Cash Flow from Investing Activities','2022']

#I used the following code to create a range for each of the rates
wacc_range = [0.03,0.09]
``````
5. Create a Monte Carlo function that will randomize a rate in each of the ranges created in #4 and perform the calculations (detailed in the image of the calculations above) to create an estimated valuation of equity in the business. The function should output each of those randomization scenarios into a pandas dataframes. To do this the function should take each of the following variables:
• Number of Iterations (Number of times it should randomize)
• Free Cash Flow for 2022
• Net Debt
• EBITDA (Earnings Before Interest, Taxes, Depreciation, and Amortization)
• Range for the initial growth rate (given above)
• Range for the WACC (given above)
• Range for the terminal growth rate (given above)
Suggestions and Hints
Required Columns

The dataframe output of the Monte Carlo Function should have each of the following columns:

• Randomized initial growth rate from the range.
• Randomized WACC from the range
• Year 1 Free Cash Flow
• Year 2 Free Cash Flow
• Year 3 Free Cash Flow
• Year 4 Free Cash Flow
• Year 5 Free Cash Flow
• The Terminal Value
• Present Value of the Year 1 Cash Flow
• Present Value of the Year 2 Cash Flow
• Present Value of the Year 3 Cash Flow
• Present Value of the Year 4 Cash Flow
• Present Value of the Year 5 Cash Flow
• Present Value of the Terminal Value
• Calculated Enterprise Value
• Calculated Equity Value
• Calculated EBITDA Multiple (Enterprise Value ÷ EBITDA using 2022 EBITDA)
• Range for the terminal growth rate (given above)
``````#I used the following code to randomize each of the rates
growth_low = growth_range[0]
growth_high = growth_range[1]
growth_rate = random.uniform(growth_low,growth_high)
``````
6. Create a second output dataframe that will output the averages of each of the columns of Monte Carlo simulation and output both dataframes into a single excel file, with each dataframe on its own respective sheet.
Suggestions and Hints
``````#I used the following code to write each of the Monte Carl dataframes to a single excel #workbook
writer = pd.ExcelWriter('Monte_Carlo_Valuation.xlsx')
monte_carlo_df.to_excel(writer, sheet_name='Monte Carlo', index=True)
average_monte_carlo.to_excel(writer, sheet_name='Summary', index=True)
writer.save()
``````

Data Files

Solution

Solution Code
``````import pandas as pd
import random

#sets display options for Pandas
pd.options.display.float_format = '{:,.2f}'.format
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
pd.set_option('display.width', 10000)
pd.set_option('display.max_colwidth', None)

#Reads in Financial Statements into Pandas Dataframe

#Cleans Up Financial Statements
bs_df = bs_df.iloc[2:,1:].reset_index(drop=True)#deletes the first two rows and first column
bs_df = bs_df.rename(columns = {"Balance Sheet":"Account",'Unnamed: 2':'2021','Unnamed: 3':'2022'})#Renames Columns
bs_df['Account'] = bs_df['Account'].str.strip()#Deletes whitespace from account description
bs_df.set_index(['Account'],inplace=True)#Sets the Account Column as Index

is_df = is_df.iloc[2:,1:].reset_index(drop=True)#deletes the first two rows and first column
is_df = is_df.rename(columns = {"Income Statement":"Account",'Unnamed: 2':'2021','Unnamed: 3':'2022'})#Renames Columns
is_df['Account'] = is_df['Account'].str.strip()#Deletes whitespace from account description
is_df.set_index(['Account'],inplace=True)#Sets the Account Column as Index

cf_df = cf_df.iloc[2:,1:].reset_index(drop=True)#deletes the first two rows and first column
cf_df = cf_df.rename(columns = {"Cash Flow Statement":"Description",'Unnamed: 2':'2021','Unnamed: 3':'2022'})#Renames Columns
cf_df['Description'] = cf_df['Description'].str.strip()#Deletes whitespace from account description
cf_df.set_index(['Description'],inplace=True)#Sets the Account Column as Index

#Function to Calculate the Present Value
def present_value (FV,r,n):
PV = FV/((1 + r) ** n)
return PV

#Gets Free Cash Flow by taking the Operating Cash Flow - Investing Cashflow
ocf = cf_df.loc['Cash Flow from Operating Activities','2022']
inv_cf = cf_df.loc['Cash Flow from Investing Activities','2022']
fcf = ocf + inv_cf

#Calculates the Net Debt by taking LT debt minus Cash
net_debt = bs_df.loc['Long-Term Debt','2022']- bs_df.loc['Cash and Cash Equivalents','2022']

#Gets EBITDA figure from Balances Sheet Dataframe
EBITDA = is_df.loc['EBITDA','2022']

#Creates the ranges for the Monte Carlo
growth_rate_range = [0.02,0.065]
wacc_range = [0.03,0.09]
terminal_growth_range = [0.005,0.02]

#Creates the Monte Carlo Function
def monte_carlo_simulation(num_iterations,fcf,net_debt,ebitda,growth_range,wacc_range,terminal_range):
#Creates Output dataframe with named columns
monte_carlo_df = pd.DataFrame(columns=['5yr growth','WACC','Y1','Y2','Y3','Y4','Y5','TerminalValue',\
'PV_Y1','PV_Y2','PV_Y3','PV_Y4','PV_Y5','PV_Terminal','EV','Equity','EV_EBITDA_Mult'])

#Sets up the high low range from function lists
growth_low = growth_range[0]
growth_high = growth_range[1]
wacc_low = wacc_range[0]
wacc_high = wacc_range[1]
term_low = terminal_range[0]
term_high = terminal_range[1]

#Loop for the Number of Iterations given
for i in range(num_iterations):

#Creates the variable for growth rate, WACC, and Terminal Growth
growth_rate = random.uniform(growth_low,growth_high)
wacc = random.uniform(wacc_low,wacc_high)
terminal_growth = random.uniform(term_low,term_high)

#Calculates the Free Cash Flow from Year 1-5
y1 = fcf * (1+growth_rate)
y2 = y1 * (1+growth_rate)
y3 = y2 * (1+growth_rate)
y4 = y3 * (1+growth_rate)
y5 = y4 * (1+growth_rate)

#Calculates PV of 5 FCF's
pv_y1 = present_value(y1,wacc,1)
pv_y2 = present_value(y2,wacc,2)
pv_y3 = present_value(y3,wacc,3)
pv_y4 = present_value(y4,wacc,4)
pv_y5 = present_value(y5,wacc,5)

#Calculates Terminal (using Gordon Growth), PV of the Terminal, and the Enterprise Value
terminal = y5/(wacc-terminal_growth)
pv_terminal = present_value(terminal,wacc,5)
enterpise_value = (pv_y1 + pv_y2 + pv_y3 + pv_y4 + pv_y5 + pv_terminal)

#Calculates the Equity Value (EV - Net Debt)
equity = enterpise_value - net_debt

#Calculates EBITDA_Multiple
ebitda_mult = enterpise_value/ebitda

#Inputs the Variables into the Output Dataframe
monte_carlo_df.loc[i,'5yr growth'] = growth_rate
monte_carlo_df.loc[i,'WACC'] = wacc
monte_carlo_df.loc[i,'Y1'] = y1
monte_carlo_df.loc[i,'Y2'] = y2
monte_carlo_df.loc[i,'Y3'] = y3
monte_carlo_df.loc[i,'Y4'] = y4
monte_carlo_df.loc[i,'Y5'] = y5
monte_carlo_df.loc[i,'TerminalValue'] = terminal
monte_carlo_df.loc[i,'PV_Y1'] = pv_y1
monte_carlo_df.loc[i,'PV_Y2'] = pv_y2
monte_carlo_df.loc[i,'PV_Y3'] = pv_y3
monte_carlo_df.loc[i,'PV_Y4'] = pv_y4
monte_carlo_df.loc[i,'PV_Y5'] = pv_y5
monte_carlo_df.loc[i,'PV_Terminal'] = pv_terminal
monte_carlo_df.loc[i,'EV'] = enterpise_value
monte_carlo_df.loc[i,'Equity'] = equity
monte_carlo_df.loc[i, 'EV_EBITDA_Mult'] = ebitda_mult

#Prints the Dataframes and Exports the Data to Excel
print(monte_carlo_df)
average_monte_carlo = monte_carlo_df.mean()
print('\n')
print(average_monte_carlo)

writer = pd.ExcelWriter('Monte_Carlo_Valuation.xlsx')
monte_carlo_df.to_excel(writer, sheet_name='Monte Carlo', index=True)
average_monte_carlo.to_excel(writer, sheet_name='Summary', index=True)
writer.save()

num_it = input("Enter the Number of iterations: ")
num_it = int(num_it)

monte_carlo_simulation(num_it,fcf,net_debt,EBITDA,growth_rate_range,wacc_range,terminal_growth_range)
``````

This is quite the challenge. If it were not for the suggestions, hints, and ChatGPT, I do not think I would have ever gotten the results (Monte Carlo simulations are not a strong suit for me). As a result, my code is very similar with a few preference changes, such as uploading the entire excel workbook only once and forming dataframes from each tab using parse,

inputting iterations in the code instead of a a user-prompted input,

and using .append to put in a full row of data by variable instead of doing each one individually.

This was a fun challenge! I have not had much experience with financial modelling, but am interested in it so this piqued my interest. ChatGPT definitely played a large role, if only for the more tedious and repeated parts of the code writing, haha.

I did some things a little different than the solution:
First I downloaded the whole Excel and made each sheet an element in a list. I created a function to clean the whitespace, and applied to each function, getting my dfs:

``````#1. Read in the tabs

import pandas as pd

# The path to your Excel file

file_path = 'Challenge134_Data.xlsx'

# Reading all sheets into a dictionary of DataFrames

# Creating a list of DataFrames

all_sheets_list = [df for df in all_sheets_dict.values()]

#2. create a function to clean the data
def cleantab(df):
# Deletes the first two rows and first column

df = df.iloc[2:,1:].reset_index(drop=True)#deletes the first two rows and first column
df.columns = ['Account', '2021', '2022'] + df.columns.tolist()[3:]

df['Account'] = df['Account'].str.strip()#Deletes whitespace from account description
df.set_index(['Account'],inplace=True)#Sets the Account Column as Index

return df

#apply function and name all dfs
bs_df = cleantab(all_sheets_list[0])
is_df = cleantab(all_sheets_list[1])
cf_df = cleantab(all_sheets_list[2])
``````

My code was similar again up to the Monte Carlo function itself. Firstly, I made the num_iterations argument optional by setting a default value. Secondly I embedded a function within the function that would ask the user to input the number of iterations they wanted and would loop if they did not input an integer. This way, if the user did not put in the number of iterations, it would ask them and not return an error:

``````def monte_carlo_valuation(fcf, net_debt, ebitda, igr_range, wacc_range, tgr_range, num_iterations = 1000):
def get_integer_input(prompt):
while True:
try:
num_it = int(input(prompt))
return num_it
except ValueError:
print("That's not an integer. Please enter an integer.")

num_iterations = get_integer_input("Enter the Number of iterations: ")
``````

Next, I initalized a list to keep the outputs in, and appended each set of outputs to the list after each iteration. After all iterations were complete, I created a df out of the list of all values. To me, it seemed to work easiest to do it that way:

``````results_list = []

for i in range(0, num_iterations):
``````

The rest of my function was essentially the same, creating an averages df and printing them both to Excel using write, except I added a portion at the end that would return the two dfs if the function was called so the user could continue manipulating the data in Python if they chose. See my full code below. What a fun challenge!

``````#1. Read in the tabs

import pandas as pd

# The path to your Excel file

file_path = 'Challenge134_Data.xlsx'

# Reading all sheets into a dictionary of DataFrames

# Creating a list of DataFrames

all_sheets_list = [df for df in all_sheets_dict.values()]
#2. create a function to clean the data
def cleantab(df):
# Deletes the first two rows and first column

df = df.iloc[2:,1:].reset_index(drop=True)#deletes the first two rows and first column
df.columns = ['Account', '2021', '2022'] + df.columns.tolist()[3:]

df['Account'] = df['Account'].str.strip()#Deletes whitespace from account description
df.set_index(['Account'],inplace=True)#Sets the Account Column as Index

return df

#apply function and name all dfs
bs_df = cleantab(all_sheets_list[0])
is_df = cleantab(all_sheets_list[1])
cf_df = cleantab(all_sheets_list[2])
#3. PV Function
#The following code can be used to create a PV function
def present_value (FV,rate,periods):
PV = FV/((1 + rate) ** periods)
return PV
#4 Create variables and ranges

inv_cf = cf_df.loc['Cash Flow from Investing Activities','2022']

op_cf = cf_df.loc['Cash Flow from Operating Activities','2022']

fcf = op_cf - inv_cf

Lt_debt = bs_df.loc['Long-Term Debt','2022']

Cash = bs_df.loc['Cash and Cash Equivalents','2022']

Net_debt = Lt_debt - Cash

ebitda = is_df.loc['EBITDA','2022']

igr_range = [.02, .065]

wacc_range = [0.03,0.09]

tgr_range = [.005, .02]
#5 Monte Carlo Code
import pandas as pd
import numpy as np

# Monte Carlo Simulation Function
def monte_carlo_valuation(fcf, net_debt, ebitda, igr_range, wacc_range, tgr_range, num_iterations = 1000):
def get_integer_input(prompt):
while True:
try:
num_it = int(input(prompt))
return num_it
except ValueError:
print("That's not an integer. Please enter an integer.")

num_iterations = get_integer_input("Enter the Number of iterations: ")

# Initialize a list to store the results
results_list = []

for i in range(0, num_iterations):
# Randomize the initial growth rate, WACC, and terminal growth rate
igr = np.random.uniform(igr_range[0], igr_range[1])
wacc = np.random.uniform(wacc_range[0], wacc_range[1])
tgr = np.random.uniform(tgr_range[0], tgr_range[1])

# Calculate the cash flows for the first 5 years
pv_cash_flows = []
cash_flows = []
for year in range(1, 6):
cf = fcf * (1 + igr)
cash_flows.append(cf)
pv_cash_flow = present_value(cf,wacc,year)
pv_cash_flows.append(pv_cash_flow)

# Calculate the terminal value and its present value
terminal_value = (pv_cash_flows[-1] / (wacc - tgr))
pv_terminal_value = terminal_value / ((1 + wacc) ** 5)

# Sum up all present values to get the enterprise value
enterprise_value = sum(pv_cash_flows) + pv_terminal_value

# Subtract net debt to get the equity value
equity_value = enterprise_value - net_debt

#Calculates EBITDA_Multiple
ebitda_mult = enterprise_value /ebitda

# Add the results to the results list
results_list.append([
igr, wacc, cash_flows[0], cash_flows[1], cash_flows[2],
cash_flows[3], cash_flows[4], terminal_value,
pv_cash_flows[0], pv_cash_flows[1], pv_cash_flows[2],
pv_cash_flows[3], pv_cash_flows[4], pv_terminal_value,
enterprise_value, equity_value, ebitda_mult
])

# Create a DataFrame from the results list
results_df = pd.DataFrame(results_list, columns=[
'5yr growth', 'WACC', 'Y1', 'Y2', 'Y3', 'Y4', 'Y5', 'TerminalValue',
'PV_Y1', 'PV_Y2', 'PV_Y3', 'PV_Y4', 'PV_Y5', 'PV_Terminal', 'EV', 'Equity', 'EV_EBITDA_Mult'
])

# Calculate the averages for each column and create a new DataFrame for the averages
averages_df = pd.DataFrame(results_df.mean()).transpose()
averages_df.index = ['Average']

with pd.ExcelWriter('Valuation_Results.xlsx') as writer:
results_df.to_excel(writer, sheet_name='Monte Carlo Simulation')
averages_df.to_excel(writer, sheet_name='Averages')

return results_df, averages_df

# Call the Monte Carlo simulation function
valuation_results, averages = monte_carlo_valuation(
fcf=fcf,
net_debt=Net_debt,
ebitda=ebitda,
igr_range=igr_range,
wacc_range=wacc_range,
tgr_range=tgr_range
)

# Show the results
print(valuation_results)
print(averages)
``````