# 134|PYTHON – Monte Python and Valuation Madness

**URL:** <https://www.techhub.training/t/134-python-monte-python-and-valuation-madness/758>\
**Category:** Challenges\
**Tags:** intermediate, python, pandas, valuation, monte-carlo\
**Created:** [June 14, 2023, 2:26am UTC](https://www.techhub.training/t/134-python-monte-python-and-valuation-madness/758 "2023-06-14T02:26:59Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![IWillyerd](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/iwillyerd/32/81_2.png) [@IWillyerd](https://www.techhub.training/u/IWillyerd)\
**Post date:** [June 14, 2023, 2:26am UTC](https://www.techhub.training/t/134-python-monte-python-and-valuation-madness/758/1 "2023-06-14T02:26:59Z")

</div>

_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](https://www.techhub.training/t/information-about-software-used-on-techhub-training/138).

**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](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/1X/b5258359044a6471f8988aa0c5938f88fd453fcb.png)

**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**
>
> ```auto
> #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**
>
> ```auto
> #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**
>
> ```auto
> #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**
>
> ```auto
> # 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)
> 
> ```auto
> #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**
>
> ```auto
> #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**

- [Challenge134\_Data.xlsx](https://byu.box.com/shared/static/65akbkpehoomipwa2kh9l0sl3woku5u4)

**Solution**

> **Solution Code**
>
> ```auto
> 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](https://byu.box.com/shared/static/gkrm00wjkzau3zo1ab7rh1kaks1oyowl)  
_Solution Video:_ [Challenge 134|PYTHON – Monte Python and Valuation Madness](https://youtu.be/DJhL--IZv6E)

---

<div class="post-metadata">

**Author:** ![Kyle\_Nilsen](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/kyle_nilsen/32/5506_2.png) [@Kyle\_Nilsen](https://www.techhub.training/u/Kyle_Nilsen)\
**Post date:** [January 14, 2024, 12:30am UTC](https://www.techhub.training/t/134-python-monte-python-and-valuation-madness/758/2 "2024-01-14T00:30:27Z")

</div>

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](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/f/f919c168cce5e1b43d9a2d35c20dce4bfe3265f5.jpeg)

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

![134_2](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/8/8e2265991fcbc682ca72659197c72af871791913.jpeg)

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

 ![134_3](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/c/c01c33a536018a821e088cf92cf255c82cafea0d.jpeg)

---

<div class="post-metadata">

**Author:** ![Trent\_Barlow](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/trent_barlow/32/5520_2.png) [@Trent\_Barlow](https://www.techhub.training/u/Trent_Barlow)\
**Post date:** [January 14, 2024, 3:15am UTC](https://www.techhub.training/t/134-python-monte-python-and-valuation-madness/758/3 "2024-01-14T03:15:32Z")

</div>

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)

```