134|PYTHON – Monte Python and Valuation Madness

Challenges
, , , ,
1

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:

Challenge134_Image
Challenge134_Image556×519 7.97 KB

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

2

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,

134_1

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

134_2

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

134_3
134_31510×542 65 KB

3

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

all_sheets_dict = pd.read_excel(file_path, sheet_name=None)

# 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

all_sheets_dict = pd.read_excel(file_path, sheet_name=None)

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