Discounted Cash Flow Scalable Model
2021, May 31
Discounted Cash Flow (DCF) models are one of the most fundamental tools used in Finance. At a high level, they convert Financial Statements and input assumptions into a dollar valuation amount, which can be used for determining a buy or sell price for a certain asset.
Before I started working in Data Science, I worked in Corporate Finance where we frequently created DCF models in Excel. Excel worked great for this, but I wanted to create a programmable version of the model, so I could easily scale the model across thousands of companies at once.
This model was created for a project in my Intro to Data Science Programming class, in which we needed to build a program that asked for inputs, and dynamically returns an output. In this case, the inputs are the financial statement details and underlying assumptions, and the output is a valuation amount and formatted Financial Statments and analysis.
In the future I am hoping to transform the model to be scalable across thousands of companies by connecting to databases (e.g., SEC Financial Statements database), and using statistical methods to autonomously determine reasonable assumptions. Although this project is faily basic, there is much to build on in the future.
Here’s an example set of inputs to use (does not tie to any real company):
Company: Apple Entity: Total company Format: Millions Year: 2020 Number of historical periods: 3 Number of forecast periods: 4 Tax rate: .4 Equity market value: 30000 Debt market value: 4000 Cost of equity: .08 Cost of debt: .04 Current EBT: 5000 EBT prior year 1: 5000 EBT prior year 2: 5000 EBT prior year 3: 5000 Current D&A: 250 D&A prior year 1: 250 D&A prior year 2: 250 D&A prior year 3: 250 Current capex: 1000 Capex prior year 1: 1000 Capex prior year 2: 1000 Capex prior year 3: 1000 Current change in net working capital: 100 Capex for prior year 1: 50 Capex for prior year 2: 50 EBT growth: .1 D&A growth: .05 Capex growth: .15 Current NWC: 50 NWC prior year 1: 100 NWC prior year 2: 50 NWC prior year 3: 75 Current cash and marketable securities: 5000
Table of Contents:
- Create Prompt for Assumptions Input
- Create Prompt for Sensitivity Analysis
- Calculate Missing Income Statement Items
- Create Calculation for Valuation
- Create Calculations for Sensitivity Analysis
- Create an Execution Class
- Aggregate Results and Create Printable Format
- Execute Programs
1. Create Prompt for Assumptions Input
#this class creates the prompts needed to fill in the DCF model
class Assumptions():
def __init__(self):
'''
This class continuously prompts the user for inputs needed in order to complete the model.
This class stores all the variables to be used later in the model.
'''
self.prompt()
@classmethod
def prompt(cls):
'''
This method continuously prompts the user for inputs needed in order to complete the model.
This method stores all the variables to be used later in the model.
'''
#company name and entity
cls.company_name = input('''\n--Valuation Assistant--\n\nWelcome to the company Valuation Assistant tool. This tool simplifies valuation process by automatically generating models. This tool uses the Discounted Cash Flow Model (DCF) as well as a scenario analysis tool. Please input your assumptions into the upcoming prompts. A visualized model will be output at the end. Please enter only reasonable numbers found on a typical income statement. Error messages will not be available for incalculable numbers because of processing limits.\nEnter company name: ''')
cls.entity_name = input('Enter entity name: ')
#note on the format of the numbers (thousands or millions, etc.)
while True:
try:
cls.num_format = input('Would you like to show this in thousands, millions, or billons (enter either "thousands", "millions", or "billions")?: ').lower()
if cls.num_format not in ['thousands', 'millions', 'billions']:
raise Exception('Invalid entry. Please try again')
break
except Exception as e:
print(e)
while True:
try:
cls.current_year = int(input('Enter year for income statement to end on: '))
if (int(cls.current_year) < 1900) or (int(cls.current_year) >= 2030):
raise Exception('Invalid year entered. Please try again.')
break
except ValueError:
print('Invalid year entered. Please try again.')
except Exception as e:
print(e)
#the model layout changes based on how many historical periods you enter in the prompt below
while True:
try:
cls.num_hst_periods = int(input('How many number of historical periods would you like to show (enter a number between 1 and 3)?: '))
if (int(cls.num_hst_periods) < 1) or (int(cls.num_hst_periods) > 3):
raise Exception('Invalid number entered. Please try again.')
break
except ValueError:
print('Invalid number entered. Please try again.')
except Exception as e:
print(e)
#same with number of forecast periods
while True:
try:
cls.num_fcst_periods = int(input('How many number of forecast periods would you like to show (enter a number between 1 and 4)?: '))
if (int(cls.num_fcst_periods) < 1) or (int(cls.num_fcst_periods) > 4):
raise Exception('Invalid number entered. Please try again.')
break
except ValueError:
print('Invalid number entered. Please try again.')
except Exception as e:
print(e)
while True:
try:
cls.tax_rate = float(input('Enter a tax rate (as a decimal between 0 and 1): '))
if (float(cls.tax_rate) < 0) or (float(cls.tax_rate) > 1):
raise Exception('Invalid number entered. Please try again.')
break
except ValueError:
print('Invalid number entered. Please try again.')
except Exception as e:
print(e)
while True:
try:
cls.equity_market_value = float(input('Enter the equity market value: '))
break
except ValueError:
print('Invalid number entered. Please try again.')
while True:
try:
cls.debt_market_value = float(input('Enter the debt market value of : '))
break
except ValueError:
print('Invalid number entered. Please try again.')
while True:
try:
cls.cost_of_equity = float(input('Enter a cost of equity amount (as a decimal between 0 and 1): '))
if (float(cls.cost_of_equity) < 0) or (float(cls.cost_of_equity) > 1):
raise Exception('Invalid number entered. Please try again.')
break
except ValueError:
print('Invalid number entered. Please try again.')
except Exception as e:
print(e)
while True:
try:
cls.cost_of_debt = float(input('Enter a cost of debt amount (as a decimal between 0 and 1): '))
if (float(cls.cost_of_debt) < 0) or (float(cls.cost_of_debt) > 1):
raise Exception('Invalid number entered. Please try again.')
break
except ValueError:
print('Invalid number entered. Please try again.')
except Exception as e:
print(e)
while True:
try:
cls.ebt = float(input('Enter the current year Earnings Before Tax (EBT) amount: '))
cls.ebt_py_1 = float(input('Enter the Earnings Before Tax (EBT) amount for prior year 1: '))
if float(cls.num_hst_periods) >= 2:
cls.ebt_py_2 = float(input('Enter the Earnings Before Tax (EBT) amount for prior year 2: '))
if float(cls.num_hst_periods) >= 3:
cls.ebt_py_3 = float(input('Enter the Earnings Before Tax (EBT) amount for prior year 3: '))
break
except ValueError:
print('An invalid number was entered. Please enter EBT amounts again.')
while True:
try:
cls.da = float(input('Enter current year D&A amount (as a positive number): '))
if (cls.da < 0):
raise Exception('Amount cannot be negative. Please try again.')
cls.da_py_1 = float(input('Enter the D&A amount for prior year 1: '))
if (cls.da_py_1 < 0):
raise Exception('Amount cannot be negative. Please try again.')
if float(cls.num_hst_periods) >= 2:
cls.da_py_2 = float(input('Enter the D&A amount for prior year 2: '))
if (cls.da_py_2 < 0):
raise Exception('Amount cannot be negative. Please try again.')
if float(cls.num_hst_periods) >= 3:
cls.da_py_3 = float(input('Enter the D&A amount for prior year 3: '))
if (cls.da_py_3 < 0):
raise Exception('Amount cannot be negative. Please try again.')
break
except ValueError:
print('Invalid entry. Please try again.')
except Exception as e:
print(e)
while True:
try:
cls.capex = float(input('Enter current year capital expenditures amount (as a positive number): '))
if (cls.capex < 0):
raise Exception('Amounts cannot be negative. Please try again.')
cls.capex_py_1 = float(input('Enter the capex amount for prior year 1: '))
if (cls.capex_py_1 < 0):
raise Exception('Amounts cannot be negative. Please try again.')
if float(cls.num_hst_periods) >= 2:
cls.capex_py_2 = float(input('Enter the capex amount for prior year 2: '))
if (cls.capex_py_2 < 0):
raise Exception('Amounts cannot be negative. Please try again.')
if float(cls.num_hst_periods) >= 3:
cls.capex_py_3 = float(input('Enter the capex amount for prior year 3: '))
if (cls.capex_py_3 < 0):
raise Exception('Amounts cannot be negative. Please try again.')
break
except ValueError:
print('Invalid entry. Please try again.')
except Exception as e:
print(e)
while True:
try:
cls.nwc = float(input('Enter the current year change in net working capital (as either a positive or negative number): '))
cls.nwc_py_1 = float(input('Enter the change in net working capital for prior year 1: '))
if float(cls.num_hst_periods) >= 2:
cls.nwc_py_2 = float(input('Enter the change in net working capital for prior year 2: '))
if float(cls.num_hst_periods) >= 3:
cls.nwc_py_3 = float(input('Enter the change in net working capital for prior year 3: '))
break
except ValueError:
print('Invalid entry. Please try again.')
while True:
try:
cls.ebt_gr = float(input('Enter your forecasted EBT growth rate (as a decimal): '))
if (cls.ebt_gr > 1) | (cls.ebt_gr < -1):
raise Exception('Please enter a growth rate lower than 100%')
break
except ValueError:
print('Invalid number entered. Please try again.')
except Exception as e:
print(2)
while True:
try:
cls.da_gr = float(input('Enter your forecasted D&A growth rate (as a decimal): '))
if (cls.da_gr > 1) | (cls.da_gr < -1):
raise Exception('Please enter a growth rate lower than 100%')
break
except ValueError:
print('Invalid number entered. Please try again.')
except Exception as e:
print(e)
while True:
try:
cls.capex_gr = float(input('Enter your capex growth rate (as a decimal): '))
if (cls.capex_gr > 1) | (cls.capex_gr < -1):
raise Exception('Please enter a growth rate lower than 100%')
break
except ValueError:
print('Invalid number entered. Please try again.')
except Exception as e:
print(e)
while True:
try:
cls.current_nwc = float(input('Enter current NWC amount: '))
cls.nwc_amt_fp_1 = float(input('Enter your forecasted NWC amount for forecast period 1: '))
if cls.nwc_amt_fp_1 < 0:
raise Exception('Amount must be greater than or equal to zero. Please try again')
if cls.num_fcst_periods >= 2:
cls.nwc_amt_fp_2 = float(input('Enter your forecasted NWC amount for forecast period 2: '))
if cls.nwc_amt_fp_2 < 0:
raise Exception('Amount must be greater than or equal to zero. Please try again')
if cls.num_fcst_periods >= 3:
cls.nwc_amt_fp_3 = float(input('Enter your forecasted NWC amount for forecast period 3: '))
if cls.nwc_amt_fp_3 < 0:
raise Exception('Amount must be greater than or equal to zero. Please try again')
if cls.num_fcst_periods == 4:
cls.nwc_amt_fp_4 = float(input('Enter your forecasted NWC amount for forecast period 4: '))
if cls.nwc_amt_fp_4 < 0:
raise Exception('Amount must be greater than or equal to zero. Please try again')
break
except ValueError:
print('Invalid number entered. Please try again.')
except Exception as e:
print(e)
while True:
try:
cls.cash = float(input('Enter current cash and marketable securities amount: '))
break
except ValueError:
print('Invalid number entered. Please try again.')
2. Create Prompt for Sensitivity Analysis
#this class is used for later in the model when variables need to be changed.
#a class was needed in order to go back and change the values based on a list input at a later point in time.
class SensitivityInput(Assumptions):
def __init__(self):
'''
This class is used for later in the model when variables need to be changed. A class was needed in order to
go back and change the values based on a list input at a later point in time.
'''
pass
@classmethod
def sensitivity_input(cls, input_variable, variable_amount):
'''
This method is used for later in the model when variables need to be changed. A class was needed in order to
go back and change the values based on a list input at a later point in time.
'''
#how variable are exchanged
if input_variable == 'ebt_gr':
Assumptions.ebt_gr = float(variable_amount)
if input_variable == 'capex_gr':
Assumptions.capex_gr = float(variable_amount)
if input_variable == 'da_gr':
Assumptions.da_gr = float(variable_amount)
if input_variable == 'cost_of_debt':
Assumptions.cost_of_debt = float(variable_amount)
if input_variable == 'tax_rate':
Assumptions.tax_rate = float(variable_amount)
3. Calculate Missing Income Statement Items
#this class conducts the calculations needed to complete the DCF model. It leverages the inputs from the Assumptions class.
class IncomeStatementCalculations(SensitivityInput):
def __init__(self):
'''
A DCF model is calculated based off of free cash flow. But only a small amount of assumptions were collected in order to find out free cash flow. This
class calculates the missing pieces needed to calculate free cash flow based on the input assumptions above.
'''
self.calcs()
@classmethod
def calcs(cls):
'''
A DCF model is calculated based off of free cash flow. But only a small amount of assumptions were collected in order to find out free cash flow. This
class calculates the missing pieces needed to calculate free cash flow based on the input assumptions above.
'''
#create forecasted amounts for ebt, da, capex, and nwc (using input growth rates)
cls.ebt_fcst_1 = (1 + Assumptions.ebt_gr) * Assumptions.ebt
cls.da_fcst_1 = (1 + Assumptions.da_gr) * Assumptions.da
cls.capex_fcst_1 = (1 + Assumptions.capex_gr) * Assumptions.capex
cls.nwc_fcst_1 = Assumptions.nwc_amt_fp_1 - Assumptions.current_nwc
if Assumptions.num_fcst_periods >= 2:
cls.ebt_fcst_2 = (1 + Assumptions.ebt_gr) * cls.ebt_fcst_1
cls.da_fcst_2 = (1 + Assumptions.da_gr) * cls.da_fcst_1
cls.capex_fcst_2 = (1 + Assumptions.capex_gr) * cls.capex_fcst_1
cls.nwc_fcst_2 = Assumptions.nwc_amt_fp_2 - Assumptions.nwc_amt_fp_1
if Assumptions.num_fcst_periods >= 3:
cls.ebt_fcst_3 = (1 + Assumptions.ebt_gr) * cls.ebt_fcst_2
cls.da_fcst_3 = (1 + Assumptions.da_gr) * cls.da_fcst_2
cls.capex_fcst_3 = (1 + Assumptions.capex_gr) * cls.capex_fcst_2
cls.nwc_fcst_3 = Assumptions.nwc_amt_fp_3 - Assumptions.nwc_amt_fp_2
if Assumptions.num_fcst_periods >= 4:
cls.ebt_fcst_4 = (1 + Assumptions.ebt_gr) * cls.ebt_fcst_3
cls.da_fcst_4 = (1 + Assumptions.da_gr) * cls.da_fcst_3
cls.capex_fcst_4 = (1 + Assumptions.capex_gr) * cls.capex_fcst_3
cls.nwc_fcst_4 = Assumptions.nwc_amt_fp_4 - Assumptions.nwc_amt_fp_3
#calculate wacc
cls.enterprise_value = Assumptions.debt_market_value + Assumptions.equity_market_value - Assumptions.cash
cls.debt_value_percentage = Assumptions.debt_market_value / cls.enterprise_value
cls.equity_value_percentage = Assumptions.equity_market_value / cls.enterprise_value
cls.wacc = (Assumptions.cost_of_equity * cls.equity_value_percentage) + ((Assumptions.cost_of_debt * cls.debt_value_percentage) * (1 - Assumptions.tax_rate))
#complete missing income statement items (interest, taxes, ebit, unlevered fcf) - both for historical and forecast periods
cls.unlevered_fcf_list = []
cls.interest = (Assumptions.debt_market_value * ((1 + Assumptions.cost_of_debt)**0)) * Assumptions.cost_of_debt
cls.taxes = Assumptions.tax_rate * Assumptions.ebt
cls.ebit = Assumptions.ebt + cls.interest
cls.unlevered_fcf = ((cls.ebit - cls.taxes) + Assumptions.da) - Assumptions.capex - Assumptions.nwc
cls.interest_py_1 = (Assumptions.debt_market_value * ((1 + Assumptions.cost_of_debt)**(-1))) * Assumptions.cost_of_debt
cls.taxes_py_1 = Assumptions.tax_rate * Assumptions.ebt_py_1
cls.ebit_py_1 = Assumptions.ebt_py_1 + cls.interest_py_1
cls.unlevered_fcf_py_1 = ((cls.ebit_py_1 - cls.taxes_py_1) + Assumptions.da_py_1) - Assumptions.capex_py_1 - Assumptions.nwc_py_1
if Assumptions.num_hst_periods >= 2:
cls.interest_py_2 = (Assumptions.debt_market_value * ((1 + Assumptions.cost_of_debt)**(-2))) * Assumptions.cost_of_debt
cls.taxes_py_2 = Assumptions.tax_rate * Assumptions.ebt_py_2
cls.ebit_py_2 = Assumptions.ebt_py_2 + cls.interest_py_2
cls.unlevered_fcf_py_2 = ((cls.ebit_py_2 - cls.taxes_py_2) + Assumptions.da_py_2) - Assumptions.capex_py_2 - Assumptions.nwc_py_2
if Assumptions.num_hst_periods >= 3:
cls.interest_py_3 = (Assumptions.debt_market_value * ((1 + Assumptions.cost_of_debt)**(-3))) * Assumptions.cost_of_debt
cls.taxes_py_3 = Assumptions.tax_rate * Assumptions.ebt_py_3
cls.ebit_py_3 = Assumptions.ebt_py_3 + cls.interest_py_3
cls.unlevered_fcf_py_3 = ((cls.ebit_py_3 - cls.taxes_py_3) + Assumptions.da_py_3) - Assumptions.capex_py_3 - Assumptions.nwc_py_3
cls.taxes_fcst_1 = Assumptions.tax_rate * cls.ebt_fcst_1
cls.interest_fcst_1 = (Assumptions.debt_market_value * ((1 + Assumptions.cost_of_debt)**1)) * Assumptions.cost_of_debt
cls.ebit_fcst_1 = cls.ebt_fcst_1 + cls.interest_fcst_1
cls.unlevered_fcf_fcst_1 = ((cls.ebit_fcst_1 - cls.taxes_fcst_1) + cls.da_fcst_1) - cls.capex_fcst_1 - cls.nwc_fcst_1
cls.unlevered_fcf_list.append(cls.unlevered_fcf_fcst_1)
if Assumptions.num_fcst_periods >= 2:
cls.taxes_fcst_2 = Assumptions.tax_rate * cls.ebt_fcst_2
cls.interest_fcst_2 = (Assumptions.debt_market_value * ((1 + Assumptions.cost_of_debt)**2)) * Assumptions.cost_of_debt
cls.ebit_fcst_2 = cls.ebt_fcst_2 + cls.interest_fcst_2
cls.unlevered_fcf_fcst_2 = ((cls.ebit_fcst_2 - cls.taxes_fcst_2) + cls.da_fcst_2) - cls.capex_fcst_2 - cls.nwc_fcst_2
cls.unlevered_fcf_list.append(cls.unlevered_fcf_fcst_2)
if Assumptions.num_fcst_periods >= 3:
cls.taxes_fcst_3 = Assumptions.tax_rate * cls.ebt_fcst_3
cls.interest_fcst_3 = (Assumptions.debt_market_value * ((1 + Assumptions.cost_of_debt)**3)) * Assumptions.cost_of_debt
cls.ebit_fcst_3 = cls.ebt_fcst_3 + cls.interest_fcst_3
cls.unlevered_fcf_fcst_3 = ((cls.ebit_fcst_3 - cls.taxes_fcst_3) + cls.da_fcst_3) - cls.capex_fcst_3 - cls.nwc_fcst_3
cls.unlevered_fcf_list.append(cls.unlevered_fcf_fcst_3)
if Assumptions.num_fcst_periods >= 4:
cls.taxes_fcst_4 = Assumptions.tax_rate * cls.ebt_fcst_4
cls.interest_fcst_4 = (Assumptions.debt_market_value * ((1 + Assumptions.cost_of_debt)**4)) * Assumptions.cost_of_debt
cls.ebit_fcst_4 = cls.ebt_fcst_4 + cls.interest_fcst_4
cls.unlevered_fcf_fcst_4 = (((cls.ebit_fcst_4 - cls.taxes_fcst_4) + cls.da_fcst_4) - cls.capex_fcst_4 - cls.nwc_fcst_4)
cls.unlevered_fcf_list.append(cls.unlevered_fcf_fcst_4)
#create terminal value calculations
cls.ev_ebitda_multiple = cls.enterprise_value / (Assumptions.ebt + cls.interest + Assumptions.da)
if Assumptions.num_fcst_periods == 1:
cls.perpetual_growth = cls.unlevered_fcf_fcst_1 / cls.wacc
cls.ev_over_ebitda = cls.ebit_fcst_1 * cls.ev_ebitda_multiple
if Assumptions.num_fcst_periods == 2:
cls.perpetual_growth = cls.unlevered_fcf_fcst_2 / cls.wacc
cls.ev_over_ebitda = cls.ebit_fcst_2 * cls.ev_ebitda_multiple
if Assumptions.num_fcst_periods == 3:
cls.perpetual_growth = cls.unlevered_fcf_fcst_3 / cls.wacc
cls.ev_over_ebitda = cls.ebit_fcst_3 * cls.ev_ebitda_multiple
if Assumptions.num_fcst_periods == 4:
cls.perpetual_growth = cls.unlevered_fcf_fcst_4 / cls.wacc
cls.ev_over_ebitda = cls.ebit_fcst_4 * cls.ev_ebitda_multiple
4. Create Calculation for Valuation
#this class conducts the DCF valuation calculation
class ValuationCalculations(IncomeStatementCalculations):
def __init__(self):
'''
This class leverages all the objects from the previous classes to calculate net present value.
'''
self.npv_calc()
@classmethod
def npv_calc(cls):
'''
This method leverages all the objects from the previous classes to calculate net present value.
'''
cls.npv_perpetual_growth = 0
#net present value calculation varies based on how many forecast periods are selected
for x in IncomeStatementCalculations.unlevered_fcf_list:
j = 1
if x == IncomeStatementCalculations.unlevered_fcf_list[len(IncomeStatementCalculations.unlevered_fcf_list)-1]:
cls.npv_perpetual_growth += ((x + IncomeStatementCalculations.perpetual_growth) / ((1 + IncomeStatementCalculations.wacc) ** j))
else:
cls.npv_perpetual_growth += (x / ((1 + IncomeStatementCalculations.wacc) ** j))
j += 1
5. Create Calculations for Sensitivity Analysis
#this class takes an input variable and values to adjust that variable by and recalculates net present value for each of them
class ExecuteSensitivity(ValuationCalculations):
def __init__(self):
'''
This class takes an input variable and values to adjust that variable by and recalculates net present value for each of them.
'''
self.orig_npv()
@classmethod
def orig_npv(cls):
'''
This method stores the current NPV value.
'''
cls.test_variable_1_npv_values = [ValuationCalculations.npv_perpetual_growth]
@classmethod
def execute_sensitivity(cls):
'''
This method takes an input variable and values to adjust that variable by and recalculates net present value for each of them.
'''
#input for which variable you would like to adjust
while True:
try:
cls.test_variables = list(input('Enter one variable you would like to manipulate?: (Enter either "ebt_gr", "capex_gr", "cost_of_debt", "tax_rate", or "da_gr"').split(', '))
if cls.test_variables not in [['ebt_gr'], ['capex_gr'], ['cost_of_debt'], ['tax_rate'], ['da_gr']]:
raise Exception('Please enter only one of the following: "ebt_gr", "capex_gr", "cost_of_debt", "tax_rate", "da_gr".')
break
except Exception as e:
print(e)
#input for the values you would like to adjust the variable above by
while True:
try:
cls.test_variable_1_values = list(map(float, input("Enter the range of values you would like to test for the first variable you listed (enter five separated by a comma and a space): ").split(', ')))
if len(cls.test_variable_1_values) != 5:
raise Exception('Please enter 5 variables separated by a comma and a space. Please try again.')
break
except ValueError:
print('Please only enter numeric variables. Please try again.')
except Exception as e:
print(e)
#store the original assumptions so that you can reset the classes after each of the variables are tested
cls.test_variable_1_npv_values = [ValuationCalculations.npv_perpetual_growth]
cls.original_ebt_gr = Assumptions.ebt_gr
cls.original_capex_gr = Assumptions.capex_gr
cls.original_da_gr = Assumptions.da_gr
cls.original_tax_rate = Assumptions.tax_rate
cls.original_cost_of_debt = Assumptions.cost_of_debt
#recalculate net present value for each of the input values
for x in cls.test_variable_1_values:
SensitivityInput.sensitivity_input(cls.test_variables[0], x)
cls.temp_object=IncomeStatementCalculations()
cls.temp_object=ValuationCalculations()
#store the value to be printed out later. Since the model will be reset to the original assumptions afterwards.
cls.test_variable_1_npv_values.append(cls.temp_object.npv_perpetual_growth)
#reset the model to the original assumption. So that the model prints out the original inputs when completed.
SensitivityInput.sensitivity_input(cls.test_variables[0], cls.original_ebt_gr)
cls.temp_object=IncomeStatementCalculations()
cls.temp_object=ValuationCalculations()
6. Create an Execution Class
#this class executes the Solver function.
#the solver function takes an input target NPV value and an input variable, and finds the minimum value of the
#input variable needed to achieve the input target NPV.
class ExecuteSolver(ExecuteSensitivity):
def __init__(self):
'''
This method executes the Solver function. The solver function takes an input target NPV value and an input variable, and finds the
minimum value of the input variable needed to achieve the input target NPV.
'''
pass
@classmethod
def execute_solver(cls):
'''
This method executes the Solver function. The solver function takes an input target NPV value and an input variable, and finds the
minimum value of the input variable needed to achieve the input target NPV.
'''
#input for target npv value
while True:
try:
cls.target_npv = float(input('Enter target NPV amount: '))
break
except ValueError:
print('Please enter a number. Please try again.')
#input for variable you would to adjust
while True:
try:
cls.solver_variable = input("Enter variable you would like to manipulate (Enter either 'ebt_gr', 'capex_gr', 'cost_of_debt', 'tax_rate', or 'da_gr') (Note: Please only enter reasonable variables. It is not recommended to use a variable that isn't a large portion of FCF (<50%)):")
if cls.solver_variable not in (['ebt_gr', 'capex_gr', 'cost_of_debt', 'tax_rate', 'da_gr']):
raise Exception('Please enter only one of the following: "ebt_gr", "capex_gr", "cost_of_debt", "tax_rate", "da_gr".')
break
except Exception as e:
print(e)
#save original assumptions so you can reset the model after the solver function completes.
cls.original_ebt_gr = Assumptions.ebt_gr
cls.original_capex_gr = Assumptions.capex_gr
cls.original_da_gr = Assumptions.da_gr
cls.original_tax_rate = Assumptions.tax_rate
cls.original_cost_of_debt = Assumptions.cost_of_debt
#initialize a temp model for the solver function to run on
cls.temp_object2=IncomeStatementCalculations()
cls.temp_object2=ValuationCalculations()
#while loop that runs the solver. The loop is split up based on which variable is selected (a cost (negative) or a revenue (positive) variable).
#the loop sets a extreme minimum or maximum value and adjusts upwards or downwards based on whether the target is above or below the original npv value.
cls.counter = 1
while cls.counter == 1:
#in case the target is above the original npv value
if cls.temp_object2.npv_perpetual_growth < cls.target_npv:
#some variables are costs, some are revenue items, so whether the variable is increased or decreased depends on this
if cls.solver_variable in ['ebt_gr', 'da_gr', 'cost_of_debt']:
cls.increase_decrease = 'increase'
cls.j = -1
while cls.temp_object2.npv_perpetual_growth < cls.target_npv:
cls.j += .0001
SensitivityInput.sensitivity_input(cls.solver_variable, cls.j)
cls.temp_object2=IncomeStatementCalculations()
cls.temp_object2=ValuationCalculations()
cls.counter += 1
#for the cost values
else:
cls.increase_decrease = 'decrease'
cls.j = 1
while cls.temp_object2.npv_perpetual_growth < cls.target_npv:
cls.j -= .0001
SensitivityInput.sensitivity_input(cls.solver_variable, cls.j)
cls.temp_object2=IncomeStatementCalculations()
cls.temp_object2=ValuationCalculations()
cls.counter += 1
#in case the target is below the original npv value
elif cls.temp_object2.npv_perpetual_growth > cls.target_npv:
if cls.solver_variable in ['ebt_gr', 'da_gr', 'cost_of_debt']:
cls.increase_decrease = 'decrease'
cls.j = 1
while cls.temp_object2.npv_perpetual_growth > cls.target_npv:
cls.j -= .0001
SensitivityInput.sensitivity_input(cls.solver_variable, cls.j)
cls.temp_object2=IncomeStatementCalculations()
cls.temp_object2=ValuationCalculations()
cls.counter += 1
#for the cost values
else:
cls.increase_decrease = 'increase'
cls.j = -1
while cls.temp_object2.npv_perpetual_growth > cls.target_npv:
cls.j += .0001
SensitivityInput.sensitivity_input(cls.solver_variable, cls.j)
cls.temp_object2=IncomeStatementCalculations()
cls.temp_object2=ValuationCalculations()
cls.counter += 1
#store the answer
cls.ebt_answer = cls.temp_object2.ebt_gr
cls.capex_answer = cls.temp_object2.capex_gr
cls.da_answer = cls.temp_object2.da_gr
cls.tax_rate_answer = cls.temp_object2.tax_rate
cls.cost_of_debt_answer = cls.temp_object2.cost_of_debt
#reset to the model to the original values so it can be printed out
if cls.solver_variable == 'ebt_gr':
SensitivityInput.sensitivity_input('ebt_gr', cls.original_ebt_gr)
cls.temp_object2=IncomeStatementCalculations()
cls.temp_object2=ValuationCalculations()
if cls.solver_variable == 'da_gr':
SensitivityInput.sensitivity_input('da_gr', cls.original_da_gr)
cls.temp_object=IncomeStatementCalculations()
cls.temp_object=ValuationCalculations()
if cls.solver_variable == 'capex_gr':
SensitivityInput.sensitivity_input('capex_gr', cls.original_capex_gr)
cls.temp_object=IncomeStatementCalculations()
cls.temp_object=ValuationCalculations()
if cls.solver_variable == 'tax_rate':
SensitivityInput.sensitivity_input('tax_rate', cls.original_tax_rate)
cls.temp_object=IncomeStatementCalculations()
cls.temp_object=ValuationCalculations()
if cls.solver_variable == 'cost_of_debt':
SensitivityInput.sensitivity_input('cost_of_debt', cls.original_cost_of_debt)
cls.temp_object=IncomeStatementCalculations()
cls.temp_object=ValuationCalculations()
7. Aggregate Results and Create Printable Format
#this class prints out the results from the above into a readable format
class PrintModel(ExecuteSolver):
def __init__(self):
'This class prints out the results from the above into a readable format.'
#initialize for the default print out
self.top_section()
self.create_line_items()
self.middle_section()
self.valuation_section()
self.income_statement = ''
self.income_statement = self.top_section + self.income_statement_body + self.bottom_section
def top_section(self):
'Method for creating the header of the print out'
#the top section prints out important assumptions that were input
self.top_section_header = '\n\n' + '--'*18 + ' DCF Model ' + '--'*18 + '\n'
self.top_section = (self.top_section_header + f'Company Name: {Assumptions.company_name}\nCurrent Year: {Assumptions.current_year}\nNote: all numbers in {Assumptions.num_format}\n'
+ f'\n\n-- ASSUMPTIONS --\nTax Rate: {Assumptions.tax_rate*100:.1f}%\nCost of Debt: {Assumptions.cost_of_debt*100:.2f}%\nCost of Equity: {Assumptions.cost_of_equity*100:.2f}%\n'
+ f'Market value of Debt: {Assumptions.debt_market_value:,.0f}\nMarket value of Equity: {Assumptions.equity_market_value:,.0f}\nWeighted Average Cost of Capital (WACC): {IncomeStatementCalculations.wacc*100:.2f}%\n')
def middle_section(self):
'Method for creating the body of the print out'
self.years = []
#create the header for years. the years header changes based on current year, as well as number of historical and forecast periods
for x in reversed(range(1, Assumptions.num_hst_periods+1)):
self.years.append(str(Assumptions.current_year - x))
self.years.append(str(Assumptions.current_year))
for x in range(1, Assumptions.num_fcst_periods+1):
self.years.append(str(Assumptions.current_year + x))
self.years = '\t'.join(self.years)
#combine all components of the middle section with the exception of the line items
self.income_statement_body = ('\n\n\n-- FINANCIALS --\nYear' + '\t'*3 + f'{self.years}\n\n{self.ebt_line}\n{self.interest_line}\n{self.ebit_line}\n{self.taxes_line}\n'
+ f'{self.da_line}\n{self.capex_line}\n{self.nwc_line}\n' + '\t'*3 + '-'*61 + f'\n{self.unlevered_fcf_line}')
def create_line_items(self):
'Method for creating the individual line items of the print out'
#since a varying number of historical and forecast periods can be input, make the output changed based on those values
self.ebt_hst_line = ('EBT' + ' '*((8*3)-3) + f"{Assumptions.ebt_py_1:,.0f}")
self.interest_hst_line = ('Interest' + ' '*((8*3)-8) + f"{IncomeStatementCalculations.interest_py_1:,.0f}")
self.ebit_hst_line = ('EBIT' + ' '*((8*3)-4) + f"{IncomeStatementCalculations.ebit_py_1:,.0f}")
self.taxes_hst_line = ('Less: Taxes' + ' '*((8*3)-11) + f"{IncomeStatementCalculations.taxes_py_1:,.0f}")
self.da_hst_line = ('Plus: D&A' + ' '*((8*3)-9) + f"{Assumptions.da_py_1:,.0f}")
self.capex_hst_line = ('Less: Capex' + ' '*((8*3)-11) + f"{Assumptions.capex_py_1:,.0f}")
self.nwc_hst_line = ('Less: Changes in NWC' + ' '*((8*3)-20) + f"{Assumptions.nwc_py_1:,.0f}")
self.unlevered_fcf_hst_line = ('Unlevered FCF' + ' '*((8*3)-13) + f"{IncomeStatementCalculations.unlevered_fcf_py_1:,.0f}")
if Assumptions.num_hst_periods == 2:
self.ebt_hst_line = ('EBT' + ' '*((8*3)-3) + f"{Assumptions.ebt_py_2:,.0f}" + ' '*(4-(len(f"{Assumptions.ebt_py_2:,.0f}")-4))
+ f"{Assumptions.ebt_py_1:,.0f}")
self.interest_hst_line = ('Interest' + ' '*((8*3)-8) + f"{IncomeStatementCalculations.interest_py_2:,.0f}" + ' '*(4-(len(f"{IncomeStatementCalculations.interest_py_2:,.0f}")-4))
+ f"{IncomeStatementCalculations.interest_py_1:,.0f}")
self.ebit_hst_line = ('EBIT' + ' '*((8*3)-4) + f"{IncomeStatementCalculations.ebit_py_2:,.0f}" + ' '*(4-(len(f"{IncomeStatementCalculations.ebit_py_2:,.0f}")-4))
+ f"{IncomeStatementCalculations.ebit_py_1:,.0f}")
self.taxes_hst_line = ('Less: Taxes' + ' '*((8*3)-11) + f"{IncomeStatementCalculations.taxes_py_2:,.0f}" + ' '*(4-(len(f"{IncomeStatementCalculations.taxes_py_2:,.0f}")-4))
+ f"{IncomeStatementCalculations.taxes_py_1:,.0f}")
self.da_hst_line = ('Plus: D&A' + ' '*((8*3)-9) + f"{Assumptions.da_py_2:,.0f}" + ' '*(4-(len(f"{Assumptions.da_py_2:,.0f}")-4))
+ f"{Assumptions.da_py_1:,.0f}")
self.capex_hst_line = ('Less: Capex' + ' '*((8*3)-11) + f"{Assumptions.capex_py_2:,.0f}" + ' '*(4-(len(f"{Assumptions.capex_py_2:,.0f}")-4))
+ f"{Assumptions.capex_py_1:,.0f}")
self.nwc_hst_line = ('Less: Changes in NWC' + ' '*((8*3)-20) + f"{Assumptions.nwc_py_2:,.0f}" + ' '*(4-(len(f"{Assumptions.nwc_py_2:,.0f}")-4))
+ f"{Assumptions.nwc_py_1:,.0f}")
self.unlevered_fcf_hst_line = ('Unlevered FCF' + ' '*((8*3)-13) + f"{IncomeStatementCalculations.unlevered_fcf_py_2:,.0f}" + ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf_py_2:,.0f}")-4))
+ f"{IncomeStatementCalculations.unlevered_fcf_py_1:,.0f}")
if Assumptions.num_hst_periods == 3:
self.ebt_hst_line = ('EBT' + ' '*((8*3)-3) + f"{Assumptions.ebt_py_3:,.0f}" + ' '*(4-(len(f"{Assumptions.ebt_py_3:,.0f}")-4))
+ f"{Assumptions.ebt_py_2:,.0f}" + ' '*(4-(len(f"{Assumptions.ebt_py_2:,.0f}")-4)) + f"{Assumptions.ebt_py_1:,.0f}")
self.interest_hst_line = ('Interest' + ' '*((8*3)-8) + f"{IncomeStatementCalculations.interest_py_3:,.0f}" + ' '*(4-(len(f"{IncomeStatementCalculations.interest_py_3:,.0f}")-4))
+ f"{IncomeStatementCalculations.interest_py_2:,.0f}" + ' '*(4-(len(f"{IncomeStatementCalculations.interest_py_2:,.0f}")-4)) + f"{IncomeStatementCalculations.interest_py_1:,.0f}")
self.ebit_hst_line = ('EBIT' + ' '*((8*3)-4) + f"{IncomeStatementCalculations.ebit_py_3:,.0f}" + ' '*(4-(len(f"{IncomeStatementCalculations.ebit_py_3:,.0f}")-4))
+ f"{IncomeStatementCalculations.ebit_py_2:,.0f}" + ' '*(4-(len(f"{IncomeStatementCalculations.ebit_py_2:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit_py_1:,.0f}")
self.taxes_hst_line = ('Less: Taxes' + ' '*((8*3)-11) + f"{IncomeStatementCalculations.taxes_py_3:,.0f}" + ' '*(4-(len(f"{IncomeStatementCalculations.taxes_py_3:,.0f}")-4))
+ f"{IncomeStatementCalculations.taxes_py_2:,.0f}" + ' '*(4-(len(f"{IncomeStatementCalculations.taxes_py_2:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes_py_1:,.0f}")
self.da_hst_line = ('Plus: D&A' + ' '*((8*3)-9) + f"{Assumptions.da_py_3:,.0f}" + ' '*(4-(len(f"{Assumptions.da_py_3:,.0f}")-4))
+ f"{Assumptions.da_py_2:,.0f}" + ' '*(4-(len(f"{Assumptions.da_py_2:,.0f}")-4)) + f"{Assumptions.da_py_1:,.0f}")
self.capex_hst_line = ('Less: Capex' + ' '*((8*3)-11) + f"{Assumptions.capex_py_3:,.0f}" + ' '*(4-(len(f"{Assumptions.capex_py_3:,.0f}")-4))
+ f"{Assumptions.capex_py_2:,.0f}" + ' '*(4-(len(f"{Assumptions.capex_py_2:,.0f}")-4)) + f"{Assumptions.capex_py_1:,.0f}")
self.nwc_hst_line = ('Less: Changes in NWC' + ' '*((8*3)-20) + f"{Assumptions.nwc_py_3:,.0f}" + ' '*(4-(len(f"{Assumptions.nwc_py_3:,.0f}")-4))
+ f"{Assumptions.nwc_py_2:,.0f}" + ' '*(4-(len(f"{Assumptions.nwc_py_2:,.0f}")-4)) + f"{Assumptions.nwc_py_1:,.0f}")
self.unlevered_fcf_hst_line = ('Unlevered FCF' + ' '*((8*3)-13) + f"{IncomeStatementCalculations.unlevered_fcf_py_3:,.0f}" + ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf_py_3:,.0f}")-4))
+ f"{IncomeStatementCalculations.unlevered_fcf_py_2:,.0f}" + ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf_py_2:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf_py_1:,.0f}")
self.ebt_line = (self.ebt_hst_line + ' '*(4-(len(f"{Assumptions.ebt_py_1:,.0f}")-4)) + f"{Assumptions.ebt:,.0f}"
+ ' '*(4-(len(f"{Assumptions.ebt:,.0f}")-4)) + f"{IncomeStatementCalculations.ebt_fcst_1:,.0f}")
self.interest_line = (self.interest_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.interest_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.interest:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.interest:,.0f}")-4)) + f"{IncomeStatementCalculations.interest_fcst_1:,.0f}")
self.ebit_line = (self.ebit_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.ebit_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebit:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit_fcst_1:,.0f}")
self.taxes_line = (self.taxes_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.taxes_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.taxes:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes_fcst_1:,.0f}")
self.da_line = (self.da_hst_line + ' '*(4-(len(f"{Assumptions.da_py_1:,.0f}")-4)) + f"{Assumptions.da:,.0f}"
+ ' '*(4-(len(f"{Assumptions.da:,.0f}")-4)) + f"{IncomeStatementCalculations.da_fcst_1:,.0f}")
self.capex_line = (self.capex_hst_line + ' '*(4-(len(f"{Assumptions.capex_py_1:,.0f}")-4)) + f"{Assumptions.capex:,.0f}"
+ ' '*(4-(len(f"{Assumptions.capex:,.0f}")-4)) + f"{IncomeStatementCalculations.capex_fcst_1:,.0f}")
self.nwc_line = (self.nwc_hst_line + ' '*(4-(len(f"{Assumptions.nwc_py_1:,.0f}")-4)) + f"{Assumptions.nwc:,.0f}"
+ ' '*(4-(len(f"{Assumptions.nwc:,.0f}")-4)) + f"{IncomeStatementCalculations.nwc_fcst_1:,.0f}")
self.unlevered_fcf_line = (self.unlevered_fcf_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf_fcst_1:,.0f}")
if Assumptions.num_fcst_periods == 2:
self.ebt_line = (self.ebt_hst_line + ' '*(4-(len(f"{Assumptions.ebt_py_1:,.0f}")-4)) + f"{Assumptions.ebt:,.0f}"
+ ' '*(4-(len(f"{Assumptions.ebt:,.0f}")-4)) + f"{IncomeStatementCalculations.ebt_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebt_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.ebt_fcst_2:,.0f}")
self.interest_line = (self.interest_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.interest_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.interest:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.interest:,.0f}")-4)) + f"{IncomeStatementCalculations.interest_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.interest_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.interest_fcst_2:,.0f}")
self.ebit_line = (self.ebit_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.ebit_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebit:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebit_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit_fcst_2:,.0f}")
self.taxes_line = (self.taxes_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.taxes_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.taxes:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.taxes_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes_fcst_2:,.0f}")
self.da_line = (self.da_hst_line + ' '*(4-(len(f"{Assumptions.da_py_1:,.0f}")-4)) + f"{Assumptions.da:,.0f}"
+ ' '*(4-(len(f"{Assumptions.da:,.0f}")-4)) + f"{IncomeStatementCalculations.da_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.da_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.da_fcst_2:,.0f}")
self.capex_line = (self.capex_hst_line + ' '*(4-(len(f"{Assumptions.capex_py_1:,.0f}")-4)) + f"{Assumptions.capex:,.0f}"
+ ' '*(4-(len(f"{Assumptions.capex:,.0f}")-4)) + f"{IncomeStatementCalculations.capex_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.capex_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.capex_fcst_2:,.0f}")
self.nwc_line = (self.nwc_hst_line + ' '*(4-(len(f"{Assumptions.nwc_py_1:,.0f}")-4)) + f"{Assumptions.nwc:,.0f}"
+ ' '*(4-(len(f"{Assumptions.nwc:,.0f}")-4)) + f"{IncomeStatementCalculations.nwc_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.nwc_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.nwc_fcst_2:,.0f}")
self.unlevered_fcf_line = (self.unlevered_fcf_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebt_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.ebt_fcst_2:,.0f}")
if Assumptions.num_fcst_periods == 3:
self.ebt_line = (self.ebt_hst_line + ' '*(4-(len(f"{Assumptions.ebt_py_1:,.0f}")-4)) + f"{Assumptions.ebt:,.0f}"
+ ' '*(4-(len(f"{Assumptions.ebt:,.0f}")-4)) + f"{IncomeStatementCalculations.ebt_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebt_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.ebt_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebt_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.ebt_fcst_3:,.0f}")
self.interest_line = (self.interest_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.interest_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.interest:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.interest:,.0f}")-4)) + f"{IncomeStatementCalculations.interest_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.interest_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.interest_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.interest_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.interest_fcst_3:,.0f}")
self.ebit_line = (self.ebit_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.ebit_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebit:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebit_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebit_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit_fcst_3:,.0f}")
self.taxes_line = (self.taxes_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.taxes_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.taxes:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.taxes_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.taxes_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes_fcst_3:,.0f}")
self.da_line = (self.da_hst_line + ' '*(4-(len(f"{Assumptions.da_py_1:,.0f}")-4)) + f"{Assumptions.da:,.0f}"
+ ' '*(4-(len(f"{Assumptions.da:,.0f}")-4)) + f"{IncomeStatementCalculations.da_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.da_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.da_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.da_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.da_fcst_3:,.0f}")
self.capex_line = (self.capex_hst_line + ' '*(4-(len(f"{Assumptions.capex_py_1:,.0f}")-4)) + f"{Assumptions.capex:,.0f}"
+ ' '*(4-(len(f"{Assumptions.capex:,.0f}")-4)) + f"{IncomeStatementCalculations.capex_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.capex_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.capex_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.capex_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.capex_fcst_3:,.0f}")
self.nwc_line = (self.nwc_hst_line + ' '*(4-(len(f"{Assumptions.nwc_py_1:,.0f}")-4)) + f"{Assumptions.nwc:,.0f}"
+ ' '*(4-(len(f"{Assumptions.nwc:,.0f}")-4)) + f"{IncomeStatementCalculations.nwc_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.nwc_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.nwc_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.nwc_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.nwc_fcst_3:,.0f}")
self.unlevered_fcf_line = (self.unlevered_fcf_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf_fcst_3:,.0f}")
if Assumptions.num_fcst_periods == 4:
self.ebt_line = (self.ebt_hst_line + ' '*(4-(len(f"{Assumptions.ebt_py_1:,.0f}")-4)) + f"{Assumptions.ebt:,.0f}"
+ ' '*(4-(len(f"{Assumptions.ebt:,.0f}")-4)) + f"{IncomeStatementCalculations.ebt_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebt_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.ebt_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebt_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.ebt_fcst_3:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebt_fcst_3:,.0f}")-4)) + f"{IncomeStatementCalculations.ebt_fcst_4:,.0f}")
self.interest_line = (self.interest_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.interest_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.interest:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.interest:,.0f}")-4)) + f"{IncomeStatementCalculations.interest_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.interest_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.interest_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.interest_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.interest_fcst_3:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.interest_fcst_3:,.0f}")-4)) + f"{IncomeStatementCalculations.interest_fcst_4:,.0f}")
self.ebit_line = (self.ebit_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.ebit_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebit:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebit_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebit_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit_fcst_3:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.ebit_fcst_3:,.0f}")-4)) + f"{IncomeStatementCalculations.ebit_fcst_4:,.0f}")
self.taxes_line = (self.taxes_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.taxes_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.taxes:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.taxes_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.taxes_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes_fcst_3:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.taxes_fcst_3:,.0f}")-4)) + f"{IncomeStatementCalculations.taxes_fcst_4:,.0f}")
self.da_line = (self.da_hst_line + ' '*(4-(len(f"{Assumptions.da_py_1:,.0f}")-4)) + f"{Assumptions.da:,.0f}"
+ ' '*(4-(len(f"{Assumptions.da:,.0f}")-4)) + f"{IncomeStatementCalculations.da_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.da_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.da_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.da_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.da_fcst_3:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.da_fcst_3:,.0f}")-4)) + f"{IncomeStatementCalculations.da_fcst_4:,.0f}")
self.capex_line = (self.capex_hst_line + ' '*(4-(len(f"{Assumptions.capex_py_1:,.0f}")-4)) + f"{Assumptions.capex:,.0f}"
+ ' '*(4-(len(f"{Assumptions.capex:,.0f}")-4)) + f"{IncomeStatementCalculations.capex_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.capex_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.capex_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.capex_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.capex_fcst_3:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.capex_fcst_3:,.0f}")-4)) + f"{IncomeStatementCalculations.capex_fcst_4:,.0f}")
self.nwc_line = (self.nwc_hst_line + ' '*(4-(len(f"{Assumptions.nwc_py_1:,.0f}")-4)) + f"{Assumptions.nwc:,.0f}"
+ ' '*(4-(len(f"{Assumptions.nwc:,.0f}")-4)) + f"{IncomeStatementCalculations.nwc_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.nwc_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.nwc_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.nwc_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.nwc_fcst_3:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.nwc_fcst_3:,.0f}")-4)) + f"{IncomeStatementCalculations.nwc_fcst_4:,.0f}")
self.unlevered_fcf_line = (self.unlevered_fcf_hst_line + ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf_py_1:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf_fcst_1:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf_fcst_1:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf_fcst_2:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf_fcst_2:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf_fcst_3:,.0f}"
+ ' '*(4-(len(f"{IncomeStatementCalculations.unlevered_fcf_fcst_3:,.0f}")-4)) + f"{IncomeStatementCalculations.unlevered_fcf_fcst_4:,.0f}")
def valuation_section(self):
'Method for creating the Valuation section of the print out'
self.npv_output = ExecuteSensitivity.test_variable_1_npv_values[0]
self.bottom_section = f"\n\n\n-- VALUATION --\nNet Present Value: ${self.npv_output:,.0f}\n\n\n"
def sensitivity_analysis_section(self):
'Method for creating the sensitivity section of the print out'
#print out results
self.sensitivity_section = (f'-- SENSITIVITY ANALYSIS --'
+ f'\n{ExecuteSensitivity.test_variables[0]} \tNPV'
+ f'\n{float(ExecuteSensitivity.test_variable_1_values[0])*100:.1f}%: \t{float(ExecuteSensitivity.test_variable_1_npv_values[1]):,.0f}'
+ f'\n{float(ExecuteSensitivity.test_variable_1_values[1])*100:.1f}%: \t{float(ExecuteSensitivity.test_variable_1_npv_values[2]):,.0f}'
+ f'\n{float(ExecuteSensitivity.test_variable_1_values[2])*100:.1f}%: \t{float(ExecuteSensitivity.test_variable_1_npv_values[3]):,.0f}'
+ f'\n{float(ExecuteSensitivity.test_variable_1_values[3])*100:.1f}%: \t{float(ExecuteSensitivity.test_variable_1_npv_values[4]):,.0f}'
+ f'\n{float(ExecuteSensitivity.test_variable_1_values[4])*100:.1f}%: \t{float(ExecuteSensitivity.test_variable_1_npv_values[5]):,.0f}')
#aggregate results
self.income_statement = self.top_section + self.income_statement_body + self.bottom_section + self.sensitivity_section
def solver_analysis_section(self):
'Method for creating the solver section of the print out'
#the print out changes depending on the which variable is input
if ExecuteSolver.solver_variable == 'ebt_gr':
self.solver_section = (f'\n\n\n-- SOLVER --\nEBT growth rate needs to {ExecuteSolver.increase_decrease} to {float(ExecuteSolver.ebt_answer)*100:.2f}% to achieve an NPV of ${float(ExecuteSolver.target_npv):,.0f}')
if ExecuteSolver.solver_variable == 'capex_gr':
self.solver_section = (f'\n\n\n-- SOLVER --\nCapex growth rate needs to {ExecuteSolver.increase_decrease} to {float(ExecuteSolver.capex_answer)*100:.2f}% to achieve an NPV of ${float(ExecuteSolver.target_npv):,.0f}')
if ExecuteSolver.solver_variable == 'da_gr':
self.solver_section = (f'\n\n\n-- SOLVER --\nD&A growth rate needs to {ExecuteSolver.increase_decrease} to {float(ExecuteSolver.da_answer)*100:.2f}% to achieve an NPV of ${float(ExecuteSolver.target_npv):,.0f}')
if ExecuteSolver.solver_variable == 'tax_rate':
self.solver_section = (f'\n\n\n-- SOLVER --\nTax rate needs to {ExecuteSolver.increase_decrease} to {float(ExecuteSolver.tax_rate_answer)*100:.2f}% to achieve an NPV of ${float(ExecuteSolver.target_npv):,.0f}')
if ExecuteSolver.solver_variable == 'cost_of_debt':
self.solver_section = (f'\n\n\n-- SOLVER --\nCost of debt needs to {ExecuteSolver.increase_decrease} to {float(ExecuteSolver.cost_of_debt_answer)*100}% to achieve a NPV of ${float(ExecuteSolver.target_npv):,.0f}')
#aggregate results
self.income_statement = self.top_section + self.income_statement_body + self.bottom_section + self.sensitivity_section + self.solver_section + '\n\n'
def __repr__(self):
'Print out model'
return self.income_statement
8. Execute programs
A. Run DCF model
#this section executes the assumptions prompts and returns a DCF model
print_mod=Assumptions()
print_mod=SensitivityInput()
print_mod=IncomeStatementCalculations()
print_mod=ValuationCalculations()
print_mod=ExecuteSensitivity()
print_mod=ExecuteSolver()
print_mod=PrintModel()
print(print_mod)
B. Run Sensitivity Analysis
#this section executes the sensitivity analysis prompts
print_mod3=ExecuteSensitivity()
print_mod3.execute_sensitivity()
print_mod3=ExecuteSolver()
print_mod3.execute_solver()
print_mod3=PrintModel()
print_mod3.sensitivity_analysis_section()
print_mod3.solver_analysis_section()
print(print_mod3)