*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.

Need a program? Click here.

**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**

- 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 bs_df = pd.read_excel('Challenge134_Data.xlsx',sheet_name="Balance Sheet")`

- 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`

- 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.)`

- 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]`

- 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)`

- 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
bs_df = pd.read_excel('Challenge134_Data.xlsx',sheet_name="Balance Sheet")
is_df =pd.read_excel('Challenge134_Data.xlsx',sheet_name="Income Statement")
cf_df =pd.read_excel('Challenge134_Data.xlsx',sheet_name="Cash Flow Statement")
#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)
```

Challenge134_Solution.txt

*Solution Video:* Challenge 134|PYTHON – Monte Python and Valuation Madness