Skip to content

Instantly share code, notes, and snippets.

@wkw37

wkw37/Valuation.ipynb

Created June 2, 2019 20:03
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save wkw37/1565c3ed6952102ec1c97aa6773ffa6f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save wkw37/1565c3ed6952102ec1c97aa6773ffa6f to your computer and use it in GitHub Desktop.
Download ZIP
Valuation models using multiples
Display the source blob
Display the rendered blob
Raw
Valuation.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Valuation\n",
"This notebook explores how we can use Python commands to help do the most mundane work in evaluating the financial performance of a company. Background of these theories can be found at <a href=\"https://www.wallstreetmojo.com/types-of-financial-models/\">https://www.wallstreetmojo.com/types-of-financial-models/</a>.<br>\n",
"In short, we look at: <br>\n",
"<ol>\n",
" <li> Discounted Cash Flow Model</li>\n",
" <li>Leveraged Buyout Model</li>\n",
" <li>Comparable Company Analysis</li>\n",
" <li>Mergers and Acquisitions Model</li>\n",
"</ol>"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"# Import modules\n",
"import numpy as np\n",
"import pandas as pd\n",
"import matplotlib.pyplot as plt"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## 1. Discounted Cash Flow Model\n",
"$DCF = \\frac{CF_1}{(1+r)^1}+\\frac{CF_2}{(1+r)^2}+\\frac{CF_3}{(1+r)^3}+...+\\frac{CF_n}{(1+r)^n}$<br>\n",
"where $CF$ is cash flow and $r$ is the discount rate or 'weighted average cost of capital (WACC)'."
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"1037.1898208420064"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Assume you have this cash flow \n",
"cash_flow = np.array([100,200,300,600,-100])\n",
"# and a discount rate of 3%\n",
"rate = 0.03\n",
"\n",
"# To get the net present value:\n",
"np.npv(rate=rate,values=cash_flow)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Unlevered free cash flows\n",
"Source: https://www.wallstreetprep.com/knowledge/dcf-model-training-6-steps-building-dcf-model-excel/ <br>\n",
"\n",
"UFCF = $EBIT$x(1-$r$)+D&A+NWC-Capital expenditures <br>\n",
"<ol>\n",
" <li>$EBIT$: Earnings before interest and taxes</li>\n",
" <li>$r$: Effective tax rate (historical)</li>\n",
" <li>D&A: Depreciation and amortization</li>\n",
" <li>NWC: Annual changes in net working capital</li>\n",
" <li>Capital expenditures: Cash investments required to sustain forecast growth of business</li>\n",
"</ol>\n",
"\n",
"Given 2017 has the following financials:"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2017</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Capital expenditures</th>\n",
" <td>-9836.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>D&amp;A</th>\n",
" <td>7124.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EBITDA</th>\n",
" <td>74467.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>NWC</th>\n",
" <td>-1032.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Tax rate</th>\n",
" <td>0.26</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 2017\n",
"Capital expenditures -9836.00\n",
"D&A 7124.00\n",
"EBITDA 74467.00\n",
"NWC -1032.00\n",
"Tax rate 0.26"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dcf_data = {2017:{'EBITDA':74467,'Tax rate': 0.26, 'D&A': 7124, 'NWC': -1032, 'Capital expenditures': -9836}}\n",
"pd.DataFrame(dcf_data)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"With the following projections:"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2017</th>\n",
" <th>2018</th>\n",
" <th>2019</th>\n",
" <th>2020</th>\n",
" <th>2021</th>\n",
" <th>2022</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Capital expenditures</th>\n",
" <td>-9836.00</td>\n",
" <td>-10278.62</td>\n",
" <td>-10741.1579</td>\n",
" <td>-11224.510005</td>\n",
" <td>-11729.612956</td>\n",
" <td>-12257.445539</td>\n",
" </tr>\n",
" <tr>\n",
" <th>D&amp;A</th>\n",
" <td>7124.00</td>\n",
" <td>7480.20</td>\n",
" <td>7854.2100</td>\n",
" <td>8246.920500</td>\n",
" <td>8659.266525</td>\n",
" <td>9092.229851</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EBITDA</th>\n",
" <td>74467.00</td>\n",
" <td>78190.35</td>\n",
" <td>82099.8675</td>\n",
" <td>86204.860875</td>\n",
" <td>90515.103919</td>\n",
" <td>95040.859115</td>\n",
" </tr>\n",
" <tr>\n",
" <th>NWC</th>\n",
" <td>-1032.00</td>\n",
" <td>-928.80</td>\n",
" <td>-835.9200</td>\n",
" <td>-752.328000</td>\n",
" <td>-677.095200</td>\n",
" <td>-609.385680</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Tax rate</th>\n",
" <td>0.26</td>\n",
" <td>0.26</td>\n",
" <td>0.2600</td>\n",
" <td>0.260000</td>\n",
" <td>0.260000</td>\n",
" <td>0.260000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 2017 2018 2019 2020 \\\n",
"Capital expenditures -9836.00 -10278.62 -10741.1579 -11224.510005 \n",
"D&A 7124.00 7480.20 7854.2100 8246.920500 \n",
"EBITDA 74467.00 78190.35 82099.8675 86204.860875 \n",
"NWC -1032.00 -928.80 -835.9200 -752.328000 \n",
"Tax rate 0.26 0.26 0.2600 0.260000 \n",
"\n",
" 2021 2022 \n",
"Capital expenditures -11729.612956 -12257.445539 \n",
"D&A 8659.266525 9092.229851 \n",
"EBITDA 90515.103919 95040.859115 \n",
"NWC -677.095200 -609.385680 \n",
"Tax rate 0.260000 0.260000 "
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"for year in range(2018,2023):\n",
" prev_data = dcf_data[year-1]\n",
" dcf_data[year] = {'EBITDA':prev_data['EBITDA']*1.05,\n",
" 'Tax rate': prev_data['Tax rate'],\n",
" 'D&A': prev_data['D&A']*1.05,\n",
" 'NWC': prev_data['NWC']*0.9,\n",
" 'Capital expenditures': prev_data['Capital expenditures']*1.045}\n",
"dcf_df = pd.DataFrame(dcf_data).T\n",
"dcf_df.T"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To calculate UFCF:"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2017</th>\n",
" <th>2018</th>\n",
" <th>2019</th>\n",
" <th>2020</th>\n",
" <th>2021</th>\n",
" <th>2022</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Capital expenditures</th>\n",
" <td>-9836.00</td>\n",
" <td>-10278.620</td>\n",
" <td>-10741.15790</td>\n",
" <td>-11224.510005</td>\n",
" <td>-11729.612956</td>\n",
" <td>-12257.445539</td>\n",
" </tr>\n",
" <tr>\n",
" <th>D&amp;A</th>\n",
" <td>7124.00</td>\n",
" <td>7480.200</td>\n",
" <td>7854.21000</td>\n",
" <td>8246.920500</td>\n",
" <td>8659.266525</td>\n",
" <td>9092.229851</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EBITDA</th>\n",
" <td>74467.00</td>\n",
" <td>78190.350</td>\n",
" <td>82099.86750</td>\n",
" <td>86204.860875</td>\n",
" <td>90515.103919</td>\n",
" <td>95040.859115</td>\n",
" </tr>\n",
" <tr>\n",
" <th>NWC</th>\n",
" <td>-1032.00</td>\n",
" <td>-928.800</td>\n",
" <td>-835.92000</td>\n",
" <td>-752.328000</td>\n",
" <td>-677.095200</td>\n",
" <td>-609.385680</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Tax rate</th>\n",
" <td>0.26</td>\n",
" <td>0.260</td>\n",
" <td>0.26000</td>\n",
" <td>0.260000</td>\n",
" <td>0.260000</td>\n",
" <td>0.260000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EBIT</th>\n",
" <td>67343.00</td>\n",
" <td>70710.150</td>\n",
" <td>74245.65750</td>\n",
" <td>77957.940375</td>\n",
" <td>81855.837394</td>\n",
" <td>85948.629263</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EBIT(1-t)</th>\n",
" <td>49833.82</td>\n",
" <td>52325.511</td>\n",
" <td>54941.78655</td>\n",
" <td>57688.875878</td>\n",
" <td>60573.319671</td>\n",
" <td>63601.985655</td>\n",
" </tr>\n",
" <tr>\n",
" <th>UFCF</th>\n",
" <td>46089.82</td>\n",
" <td>48598.291</td>\n",
" <td>51218.91865</td>\n",
" <td>53958.958372</td>\n",
" <td>56825.878041</td>\n",
" <td>59827.384287</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 2017 2018 2019 2020 \\\n",
"Capital expenditures -9836.00 -10278.620 -10741.15790 -11224.510005 \n",
"D&A 7124.00 7480.200 7854.21000 8246.920500 \n",
"EBITDA 74467.00 78190.350 82099.86750 86204.860875 \n",
"NWC -1032.00 -928.800 -835.92000 -752.328000 \n",
"Tax rate 0.26 0.260 0.26000 0.260000 \n",
"EBIT 67343.00 70710.150 74245.65750 77957.940375 \n",
"EBIT(1-t) 49833.82 52325.511 54941.78655 57688.875878 \n",
"UFCF 46089.82 48598.291 51218.91865 53958.958372 \n",
"\n",
" 2021 2022 \n",
"Capital expenditures -11729.612956 -12257.445539 \n",
"D&A 8659.266525 9092.229851 \n",
"EBITDA 90515.103919 95040.859115 \n",
"NWC -677.095200 -609.385680 \n",
"Tax rate 0.260000 0.260000 \n",
"EBIT 81855.837394 85948.629263 \n",
"EBIT(1-t) 60573.319671 63601.985655 \n",
"UFCF 56825.878041 59827.384287 "
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dcf_df['EBIT'] = dcf_df.apply(lambda x: x['EBITDA']-x['D&A'],axis=1)\n",
"dcf_df['EBIT(1-t)'] = dcf_df.apply(lambda x: x['EBIT']*(1-x['Tax rate']),axis=1)\n",
"dcf_df['UFCF'] = dcf_df.apply(lambda x: sum(x[['EBIT(1-t)','D&A','NWC','Capital expenditures']]),axis=1)\n",
"dcf_df.T"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"('Sum of present values', 203011.05136788834)\n"
]
}
],
"source": [
"discount_rate = 0.1\n",
"dcf_df['PV of UFCF'] = dcf_df.apply(lambda x: x['UFCF']/(1+discount_rate)**(x.name-2017),axis=1)\n",
"PV = dcf_df.loc[range(2018,2023)]['PV of UFCF'].sum()\n",
"print(\"Sum of present values\",PV)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Terminal Value\n",
"<ol>\n",
" <li>Growth in perpetuity</li>\n",
" <li>EBITDA multiple</li>\n",
"</ol>"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"('Terminal Value', 1037007.9943155855)\n",
"('PV of Terminal Value', 643900.375853354)\n",
"('Enterprise Value', 846911.4272212423)\n"
]
}
],
"source": [
"# 1. Growth in perpetuity\n",
"# State your long term growth rate:\n",
"growth_rate = 0.04\n",
"gterminal_value = dcf_df.loc[2022]['UFCF']*(1+growth_rate)/(discount_rate-growth_rate)\n",
"gpv_terminal_value = gterminal_value/(1+discount_rate)**(2022-2017)\n",
"print(\"Terminal Value\", gterminal_value)\n",
"print(\"PV of Terminal Value\", gpv_terminal_value)\n",
"print(\"Enterprise Value\", PV+gpv_terminal_value)"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"('Terminal Value', 855367.7320321876)\n",
"('PV of Terminal Value', 531116.0638755347)\n",
"('Enterprise Value', 734127.115243423)\n"
]
}
],
"source": [
"# 2. EBITDA multiple\n",
"# EBITDA multiple value\n",
"ebitda_multiple = 9\n",
"eterminal_value = dcf_df.loc[2022]['EBITDA']*ebitda_multiple\n",
"epv_terminal_value = eterminal_value/(1+discount_rate)**(2022-2017)\n",
"print(\"Terminal Value\", eterminal_value)\n",
"print(\"PV of Terminal Value\", epv_terminal_value)\n",
"print(\"Enterprise Value\", PV+epv_terminal_value)"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>2017</th>\n",
" <th>2018</th>\n",
" <th>2019</th>\n",
" <th>2020</th>\n",
" <th>2021</th>\n",
" <th>2022</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Capital expenditures</th>\n",
" <td>-9836.00</td>\n",
" <td>-10278.620000</td>\n",
" <td>-10741.157900</td>\n",
" <td>-11224.510005</td>\n",
" <td>-11729.612956</td>\n",
" <td>-12257.445539</td>\n",
" </tr>\n",
" <tr>\n",
" <th>D&amp;A</th>\n",
" <td>7124.00</td>\n",
" <td>7480.200000</td>\n",
" <td>7854.210000</td>\n",
" <td>8246.920500</td>\n",
" <td>8659.266525</td>\n",
" <td>9092.229851</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EBITDA</th>\n",
" <td>74467.00</td>\n",
" <td>78190.350000</td>\n",
" <td>82099.867500</td>\n",
" <td>86204.860875</td>\n",
" <td>90515.103919</td>\n",
" <td>95040.859115</td>\n",
" </tr>\n",
" <tr>\n",
" <th>NWC</th>\n",
" <td>-1032.00</td>\n",
" <td>-928.800000</td>\n",
" <td>-835.920000</td>\n",
" <td>-752.328000</td>\n",
" <td>-677.095200</td>\n",
" <td>-609.385680</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Tax rate</th>\n",
" <td>0.26</td>\n",
" <td>0.260000</td>\n",
" <td>0.260000</td>\n",
" <td>0.260000</td>\n",
" <td>0.260000</td>\n",
" <td>0.260000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EBIT</th>\n",
" <td>67343.00</td>\n",
" <td>70710.150000</td>\n",
" <td>74245.657500</td>\n",
" <td>77957.940375</td>\n",
" <td>81855.837394</td>\n",
" <td>85948.629263</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EBIT(1-t)</th>\n",
" <td>49833.82</td>\n",
" <td>52325.511000</td>\n",
" <td>54941.786550</td>\n",
" <td>57688.875878</td>\n",
" <td>60573.319671</td>\n",
" <td>63601.985655</td>\n",
" </tr>\n",
" <tr>\n",
" <th>UFCF</th>\n",
" <td>46089.82</td>\n",
" <td>48598.291000</td>\n",
" <td>51218.918650</td>\n",
" <td>53958.958372</td>\n",
" <td>56825.878041</td>\n",
" <td>59827.384287</td>\n",
" </tr>\n",
" <tr>\n",
" <th>PV of UFCF</th>\n",
" <td>46089.82</td>\n",
" <td>44180.264545</td>\n",
" <td>42329.684835</td>\n",
" <td>40540.164066</td>\n",
" <td>38812.839315</td>\n",
" <td>37148.098607</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 2017 2018 2019 2020 \\\n",
"Capital expenditures -9836.00 -10278.620000 -10741.157900 -11224.510005 \n",
"D&A 7124.00 7480.200000 7854.210000 8246.920500 \n",
"EBITDA 74467.00 78190.350000 82099.867500 86204.860875 \n",
"NWC -1032.00 -928.800000 -835.920000 -752.328000 \n",
"Tax rate 0.26 0.260000 0.260000 0.260000 \n",
"EBIT 67343.00 70710.150000 74245.657500 77957.940375 \n",
"EBIT(1-t) 49833.82 52325.511000 54941.786550 57688.875878 \n",
"UFCF 46089.82 48598.291000 51218.918650 53958.958372 \n",
"PV of UFCF 46089.82 44180.264545 42329.684835 40540.164066 \n",
"\n",
" 2021 2022 \n",
"Capital expenditures -11729.612956 -12257.445539 \n",
"D&A 8659.266525 9092.229851 \n",
"EBITDA 90515.103919 95040.859115 \n",
"NWC -677.095200 -609.385680 \n",
"Tax rate 0.260000 0.260000 \n",
"EBIT 81855.837394 85948.629263 \n",
"EBIT(1-t) 60573.319671 63601.985655 \n",
"UFCF 56825.878041 59827.384287 \n",
"PV of UFCF 38812.839315 37148.098607 "
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"dcf_df.T"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Equity value\n",
"Net debt = Debt - assets and securities<br>\n",
"Dilutive shares outstanding = Basic shares outstanding + effect of dilutive securities<br>\n",
"Equity value = (PV of UFCF+PV of Terminal Value-Net debt)/Dilutive shares outstanding"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 2. Leveraged Buyout Model\n",
"Source: http://people.stern.nyu.edu/igiddy/spreadsheets/forestsforever.xls"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [],
"source": [
"# input\n",
"Input = {'Revenues':3600000,\n",
" 'Operating expenses': -2200000,\n",
" 'Growth rate':0.09,\n",
" 'Stable growth rate': 0.03,\n",
" 'Capital expenditures': 125000,\n",
" 'Depreciation': -100000,\n",
" 'Working capital change': 0.04,\n",
" 'Debt': 4500000,\n",
" 'Equity': 2000000,\n",
" 'Principal repayment': 300000,\n",
" 'Risk free rate': 0.045,\n",
" 'Market risk premium': 0.055,\n",
" 'New debt issued':0,\n",
" 'Tax rate': 0.35,\n",
" 'Target debt/equity ratio':1.30,\n",
" 'Rating':'B',\n",
" 'Credit spread':0.065,\n",
" 'New debt': 0,\n",
" 'Preferred dividends': 0,\n",
" # Accumulate from previous year\n",
" '_WACC': 1,\n",
" '_Cost of equity': 1\n",
" \n",
" }\n",
"Input['Change in working capital'] = Input['Revenues']*Input['Working capital change']\n",
"initial_beta = 3.7\n",
"lbo = pd.DataFrame({})\n",
"#lbo = pd.DataFrame({'Year 1':Input})"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Year 1</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Capital expenditures</th>\n",
" <td>125000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Change in working capital</th>\n",
" <td>144000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Credit spread</th>\n",
" <td>0.065</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Debt</th>\n",
" <td>4500000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Depreciation</th>\n",
" <td>-100000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Equity</th>\n",
" <td>2000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Growth rate</th>\n",
" <td>0.09</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Market risk premium</th>\n",
" <td>0.055</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New debt</th>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New debt issued</th>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Operating expenses</th>\n",
" <td>-2200000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Preferred dividends</th>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Principal repayment</th>\n",
" <td>300000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Rating</th>\n",
" <td>B</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Revenues</th>\n",
" <td>3600000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Risk free rate</th>\n",
" <td>0.045</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Stable growth rate</th>\n",
" <td>0.03</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Target debt/equity ratio</th>\n",
" <td>1.3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Tax rate</th>\n",
" <td>0.35</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Working capital change</th>\n",
" <td>0.04</td>\n",
" </tr>\n",
" <tr>\n",
" <th>_Cost of equity</th>\n",
" <td>1.2485</td>\n",
" </tr>\n",
" <tr>\n",
" <th>_WACC</th>\n",
" <td>1.14846</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EBIT</th>\n",
" <td>1300000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Interest rate</th>\n",
" <td>0.11</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Interest</th>\n",
" <td>-495000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Tax</th>\n",
" <td>-281750</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Net income</th>\n",
" <td>523250</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EBITDA</th>\n",
" <td>1400000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>FCFF</th>\n",
" <td>676000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Interest*(1-T)</th>\n",
" <td>321750</td>\n",
" </tr>\n",
" <tr>\n",
" <th>FCFE</th>\n",
" <td>54250</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Outstanding debt</th>\n",
" <td>4200000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Outstanding equity</th>\n",
" <td>2.52325e+06</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Total capitalization</th>\n",
" <td>6.72325e+06</td>\n",
" </tr>\n",
" <tr>\n",
" <th>D/E ratio</th>\n",
" <td>1.66452</td>\n",
" </tr>\n",
" <tr>\n",
" <th>BetaUL</th>\n",
" <td>1.77719</td>\n",
" </tr>\n",
" <tr>\n",
" <th>BetaL</th>\n",
" <td>3.7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Cost of equity</th>\n",
" <td>0.2485</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Cost of debt</th>\n",
" <td>0.0715</td>\n",
" </tr>\n",
" <tr>\n",
" <th>WACC</th>\n",
" <td>0.148457</td>\n",
" </tr>\n",
" <tr>\n",
" <th>PV FCFF</th>\n",
" <td>588616</td>\n",
" </tr>\n",
" <tr>\n",
" <th>PV FCFE</th>\n",
" <td>43452.1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Year 1\n",
"Capital expenditures 125000\n",
"Change in working capital 144000\n",
"Credit spread 0.065\n",
"Debt 4500000\n",
"Depreciation -100000\n",
"Equity 2000000\n",
"Growth rate 0.09\n",
"Market risk premium 0.055\n",
"New debt 0\n",
"New debt issued 0\n",
"Operating expenses -2200000\n",
"Preferred dividends 0\n",
"Principal repayment 300000\n",
"Rating B\n",
"Revenues 3600000\n",
"Risk free rate 0.045\n",
"Stable growth rate 0.03\n",
"Target debt/equity ratio 1.3\n",
"Tax rate 0.35\n",
"Working capital change 0.04\n",
"_Cost of equity 1.2485\n",
"_WACC 1.14846\n",
"EBIT 1300000\n",
"Interest rate 0.11\n",
"Interest -495000\n",
"Tax -281750\n",
"Net income 523250\n",
"EBITDA 1400000\n",
"FCFF 676000\n",
"Interest*(1-T) 321750\n",
"FCFE 54250\n",
"Outstanding debt 4200000\n",
"Outstanding equity 2.52325e+06\n",
"Total capitalization 6.72325e+06\n",
"D/E ratio 1.66452\n",
"BetaUL 1.77719\n",
"BetaL 3.7\n",
"Cost of equity 0.2485\n",
"Cost of debt 0.0715\n",
"WACC 0.148457\n",
"PV FCFF 588616\n",
"PV FCFE 43452.1"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"def update_financials(curr_year,keep_betaul=True):\n",
" \"\"\"\n",
" @param curr_year: pandas.Series\n",
" @return updated pandas.Sers\n",
" \"\"\"\n",
" curr_year = curr_year.copy()\n",
" curr_year['EBIT'] = curr_year[['Revenues','Operating expenses','Depreciation']].sum()\n",
" curr_year['Interest rate'] = curr_year[['Risk free rate','Credit spread']].sum()\n",
" curr_year['Interest'] = -curr_year['Debt']*curr_year['Interest rate']\n",
" curr_year['Tax'] = -curr_year['Tax rate']*curr_year[['EBIT','Interest']].sum()\n",
" curr_year['Net income'] = curr_year[['EBIT','Interest','Tax']].sum()\n",
" curr_year['EBITDA'] = curr_year['EBIT']-curr_year['Depreciation']\n",
" curr_year['FCFF'] = curr_year['EBIT']*(1-curr_year['Tax rate'])-curr_year['Depreciation']-curr_year['Capital expenditures']-curr_year['Change in working capital']\n",
" curr_year['Interest*(1-T)'] = curr_year['Debt'] * curr_year['Interest rate']*(1-curr_year['Tax rate'])\n",
" curr_year['FCFE'] = curr_year['FCFF'] - curr_year['Interest*(1-T)'] - curr_year['Principal repayment'] + curr_year['New debt'] - curr_year['Preferred dividends']\n",
" curr_year['Outstanding debt'] = curr_year['Debt']-curr_year['Principal repayment']+curr_year['New debt']\n",
" curr_year['Outstanding equity'] = curr_year['Equity']+curr_year['Net income']\n",
" curr_year['Total capitalization'] = curr_year['Outstanding debt']+curr_year['Outstanding equity']\n",
" curr_year['D/E ratio'] = curr_year['Outstanding debt']/curr_year['Outstanding equity']\n",
" ul_multiplier = 1+(1-curr_year['Tax rate'])*curr_year['D/E ratio']\n",
" if not keep_betaul: curr_year['BetaUL'] = initial_beta/ul_multiplier\n",
" curr_year['BetaL'] = ul_multiplier*curr_year['BetaUL']\n",
" curr_year['Cost of equity'] = curr_year['Risk free rate']+curr_year['BetaL']*curr_year['Market risk premium']\n",
" curr_year['Cost of debt'] = (curr_year['Risk free rate'] + curr_year['Credit spread'])*(1-curr_year['Tax rate'])\n",
" curr_year['WACC'] = (curr_year['Cost of equity']+curr_year['Cost of debt']*curr_year['Target debt/equity ratio'])/(1+curr_year['Target debt/equity ratio'])\n",
" # Accumulate from previous year\n",
" curr_year['_WACC']*=(1+curr_year['WACC'])\n",
" curr_year['_Cost of equity']*=(1+curr_year['Cost of equity'])\n",
" curr_year['PV FCFF'] = curr_year['FCFF']/curr_year['_WACC']\n",
" curr_year['PV FCFE'] = curr_year['FCFE']/curr_year['_Cost of equity']\n",
" return curr_year\n",
"lbo = pd.DataFrame({})\n",
"# Calculate BetaUL for the first year, keep for the years to come\n",
"lbo['Year 1'] = update_financials(pd.Series(Input),False)\n",
"lbo"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Free cash flow to Firm: 3222757.27575\n",
"Free cash flow to Equity: 821095.190998\n",
"Weighted average cost of capital: 0.114887910767\n",
"Terminal value of equity: 36491303.7345\n",
"Expect selling price after 6 years: 8662830.46545\n"
]
}
],
"source": [
"# Free Cash Flow\n",
"years_to_project = 6\n",
"\n",
"# Changes on year\n",
"for i in range(2,years_to_project+1):\n",
" curr_lbo = lbo['Year {0}'.format(i-1)].copy()\n",
" curr_lbo.name = 'Year {0}'.format(i)\n",
" if i == years_to_project:\n",
" curr_lbo['Principal repayment'] = 0\n",
" curr_lbo[['Revenues','Operating expenses',\\\n",
" 'Depreciation','Capital expenditures',\\\n",
" 'Change in working capital'\n",
" ]]*=(1+curr_lbo['Stable growth rate'])\n",
" else:\n",
" curr_lbo[['Revenues','Operating expenses',\\\n",
" 'Depreciation','Capital expenditures',\\\n",
" 'Change in working capital'\n",
" ]]*=(1+curr_lbo['Growth rate'])\n",
" curr_lbo['Debt'] = curr_lbo['Outstanding debt']\n",
" curr_lbo['Equity'] = curr_lbo['Outstanding equity']\n",
" curr_lbo = update_financials(curr_lbo)\n",
" lbo = pd.concat([lbo,curr_lbo],axis=1)\n",
"lbo\n",
"\n",
"# Sum of changes\n",
"terminal_year = 'Year {0}'.format(years_to_project)\n",
"terminal_FCFF = lbo[terminal_year]['FCFF']/(lbo[terminal_year]['WACC']-lbo[terminal_year]['Growth rate'])\n",
"terminal_FCFE = terminal_FCFF - lbo[terminal_year]['Debt']\n",
"sum_PVFCFF = lbo.loc['PV FCFF'].sum()\n",
"sum_PVFCFE = lbo.loc['PV FCFE'].sum()\n",
"\n",
"print('Free cash flow to Firm: {0}'.format(sum_PVFCFF))\n",
"print('Free cash flow to Equity: {0}'.format(sum_PVFCFE))\n",
"print('Weighted average cost of capital: {0}'.format(lbo[terminal_year]['WACC']))\n",
"print('Terminal value of equity: {0}'.format(terminal_FCFE))\n",
"print('Expect selling price after {0} years: {1}'.format(years_to_project, lbo[terminal_year][['Debt','Equity']].sum()))\n"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Year 1</th>\n",
" <th>Year 2</th>\n",
" <th>Year 3</th>\n",
" <th>Year 4</th>\n",
" <th>Year 5</th>\n",
" <th>Year 6</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Capital expenditures</th>\n",
" <td>125000</td>\n",
" <td>136250</td>\n",
" <td>148512</td>\n",
" <td>161879</td>\n",
" <td>176448</td>\n",
" <td>181741</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Change in working capital</th>\n",
" <td>144000</td>\n",
" <td>156960</td>\n",
" <td>171086</td>\n",
" <td>186484</td>\n",
" <td>203268</td>\n",
" <td>209366</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Credit spread</th>\n",
" <td>0.065</td>\n",
" <td>0.065</td>\n",
" <td>0.065</td>\n",
" <td>0.065</td>\n",
" <td>0.065</td>\n",
" <td>0.065</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Debt</th>\n",
" <td>4500000</td>\n",
" <td>4200000</td>\n",
" <td>3900000</td>\n",
" <td>3600000</td>\n",
" <td>3300000</td>\n",
" <td>3000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Depreciation</th>\n",
" <td>-100000</td>\n",
" <td>-109000</td>\n",
" <td>-118810</td>\n",
" <td>-129503</td>\n",
" <td>-141158</td>\n",
" <td>-145393</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Equity</th>\n",
" <td>2000000</td>\n",
" <td>2.52325e+06</td>\n",
" <td>3.144e+06</td>\n",
" <td>3.86909e+06</td>\n",
" <td>4.70599e+06</td>\n",
" <td>5.66283e+06</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Growth rate</th>\n",
" <td>0.09</td>\n",
" <td>0.09</td>\n",
" <td>0.09</td>\n",
" <td>0.09</td>\n",
" <td>0.09</td>\n",
" <td>0.09</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Market risk premium</th>\n",
" <td>0.055</td>\n",
" <td>0.055</td>\n",
" <td>0.055</td>\n",
" <td>0.055</td>\n",
" <td>0.055</td>\n",
" <td>0.055</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New debt</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>New debt issued</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Operating expenses</th>\n",
" <td>-2200000</td>\n",
" <td>-2.398e+06</td>\n",
" <td>-2.61382e+06</td>\n",
" <td>-2.84906e+06</td>\n",
" <td>-3.10548e+06</td>\n",
" <td>-3.19864e+06</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Preferred dividends</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Principal repayment</th>\n",
" <td>300000</td>\n",
" <td>300000</td>\n",
" <td>300000</td>\n",
" <td>300000</td>\n",
" <td>300000</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Rating</th>\n",
" <td>B</td>\n",
" <td>B</td>\n",
" <td>B</td>\n",
" <td>B</td>\n",
" <td>B</td>\n",
" <td>B</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Revenues</th>\n",
" <td>3600000</td>\n",
" <td>3.924e+06</td>\n",
" <td>4.27716e+06</td>\n",
" <td>4.6621e+06</td>\n",
" <td>5.08169e+06</td>\n",
" <td>5.23414e+06</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Risk free rate</th>\n",
" <td>0.045</td>\n",
" <td>0.045</td>\n",
" <td>0.045</td>\n",
" <td>0.045</td>\n",
" <td>0.045</td>\n",
" <td>0.045</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Stable growth rate</th>\n",
" <td>0.03</td>\n",
" <td>0.03</td>\n",
" <td>0.03</td>\n",
" <td>0.03</td>\n",
" <td>0.03</td>\n",
" <td>0.03</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Target debt/equity ratio</th>\n",
" <td>1.3</td>\n",
" <td>1.3</td>\n",
" <td>1.3</td>\n",
" <td>1.3</td>\n",
" <td>1.3</td>\n",
" <td>1.3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Tax rate</th>\n",
" <td>0.35</td>\n",
" <td>0.35</td>\n",
" <td>0.35</td>\n",
" <td>0.35</td>\n",
" <td>0.35</td>\n",
" <td>0.35</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Working capital change</th>\n",
" <td>0.04</td>\n",
" <td>0.04</td>\n",
" <td>0.04</td>\n",
" <td>0.04</td>\n",
" <td>0.04</td>\n",
" <td>0.04</td>\n",
" </tr>\n",
" <tr>\n",
" <th>_Cost of equity</th>\n",
" <td>1.2485</td>\n",
" <td>1.52511</td>\n",
" <td>1.83298</td>\n",
" <td>2.17629</td>\n",
" <td>2.5602</td>\n",
" <td>2.99874</td>\n",
" </tr>\n",
" <tr>\n",
" <th>_WACC</th>\n",
" <td>1.14846</td>\n",
" <td>1.3055</td>\n",
" <td>1.47284</td>\n",
" <td>1.6523</td>\n",
" <td>1.8458</td>\n",
" <td>2.05786</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EBIT</th>\n",
" <td>1300000</td>\n",
" <td>1.417e+06</td>\n",
" <td>1.54453e+06</td>\n",
" <td>1.68354e+06</td>\n",
" <td>1.83506e+06</td>\n",
" <td>1.89011e+06</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Interest rate</th>\n",
" <td>0.11</td>\n",
" <td>0.11</td>\n",
" <td>0.11</td>\n",
" <td>0.11</td>\n",
" <td>0.11</td>\n",
" <td>0.11</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Interest</th>\n",
" <td>-495000</td>\n",
" <td>-462000</td>\n",
" <td>-429000</td>\n",
" <td>-396000</td>\n",
" <td>-363000</td>\n",
" <td>-330000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Tax</th>\n",
" <td>-281750</td>\n",
" <td>-334250</td>\n",
" <td>-390436</td>\n",
" <td>-450638</td>\n",
" <td>-515220</td>\n",
" <td>-546038</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Net income</th>\n",
" <td>523250</td>\n",
" <td>620750</td>\n",
" <td>725095</td>\n",
" <td>836900</td>\n",
" <td>956836</td>\n",
" <td>1.01407e+06</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EBITDA</th>\n",
" <td>1400000</td>\n",
" <td>1.526e+06</td>\n",
" <td>1.66334e+06</td>\n",
" <td>1.81304e+06</td>\n",
" <td>1.97621e+06</td>\n",
" <td>2.0355e+06</td>\n",
" </tr>\n",
" <tr>\n",
" <th>FCFF</th>\n",
" <td>676000</td>\n",
" <td>736840</td>\n",
" <td>803156</td>\n",
" <td>875440</td>\n",
" <td>954229</td>\n",
" <td>982856</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Interest*(1-T)</th>\n",
" <td>321750</td>\n",
" <td>300300</td>\n",
" <td>278850</td>\n",
" <td>257400</td>\n",
" <td>235950</td>\n",
" <td>214500</td>\n",
" </tr>\n",
" <tr>\n",
" <th>FCFE</th>\n",
" <td>54250</td>\n",
" <td>136540</td>\n",
" <td>224306</td>\n",
" <td>318040</td>\n",
" <td>418279</td>\n",
" <td>768356</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Outstanding debt</th>\n",
" <td>4200000</td>\n",
" <td>3900000</td>\n",
" <td>3600000</td>\n",
" <td>3300000</td>\n",
" <td>3000000</td>\n",
" <td>3000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Outstanding equity</th>\n",
" <td>2.52325e+06</td>\n",
" <td>3.144e+06</td>\n",
" <td>3.86909e+06</td>\n",
" <td>4.70599e+06</td>\n",
" <td>5.66283e+06</td>\n",
" <td>6.6769e+06</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Total capitalization</th>\n",
" <td>6.72325e+06</td>\n",
" <td>7.044e+06</td>\n",
" <td>7.46909e+06</td>\n",
" <td>8.00599e+06</td>\n",
" <td>8.66283e+06</td>\n",
" <td>9.6769e+06</td>\n",
" </tr>\n",
" <tr>\n",
" <th>D/E ratio</th>\n",
" <td>1.66452</td>\n",
" <td>1.24046</td>\n",
" <td>0.93045</td>\n",
" <td>0.701233</td>\n",
" <td>0.52977</td>\n",
" <td>0.44931</td>\n",
" </tr>\n",
" <tr>\n",
" <th>BetaUL</th>\n",
" <td>1.77719</td>\n",
" <td>1.77719</td>\n",
" <td>1.77719</td>\n",
" <td>1.77719</td>\n",
" <td>1.77719</td>\n",
" <td>1.77719</td>\n",
" </tr>\n",
" <tr>\n",
" <th>BetaL</th>\n",
" <td>3.7</td>\n",
" <td>3.21013</td>\n",
" <td>2.85202</td>\n",
" <td>2.58724</td>\n",
" <td>2.38917</td>\n",
" <td>2.29622</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Cost of equity</th>\n",
" <td>0.2485</td>\n",
" <td>0.221557</td>\n",
" <td>0.201861</td>\n",
" <td>0.187298</td>\n",
" <td>0.176404</td>\n",
" <td>0.171292</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Cost of debt</th>\n",
" <td>0.0715</td>\n",
" <td>0.0715</td>\n",
" <td>0.0715</td>\n",
" <td>0.0715</td>\n",
" <td>0.0715</td>\n",
" <td>0.0715</td>\n",
" </tr>\n",
" <tr>\n",
" <th>WACC</th>\n",
" <td>0.148457</td>\n",
" <td>0.136742</td>\n",
" <td>0.128179</td>\n",
" <td>0.121847</td>\n",
" <td>0.117111</td>\n",
" <td>0.114888</td>\n",
" </tr>\n",
" <tr>\n",
" <th>PV FCFF</th>\n",
" <td>588616</td>\n",
" <td>564412</td>\n",
" <td>545312</td>\n",
" <td>529832</td>\n",
" <td>516974</td>\n",
" <td>477611</td>\n",
" </tr>\n",
" <tr>\n",
" <th>PV FCFE</th>\n",
" <td>43452.1</td>\n",
" <td>89527.7</td>\n",
" <td>122372</td>\n",
" <td>146139</td>\n",
" <td>163378</td>\n",
" <td>256227</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Year 1 Year 2 Year 3 Year 4 \\\n",
"Capital expenditures 125000 136250 148512 161879 \n",
"Change in working capital 144000 156960 171086 186484 \n",
"Credit spread 0.065 0.065 0.065 0.065 \n",
"Debt 4500000 4200000 3900000 3600000 \n",
"Depreciation -100000 -109000 -118810 -129503 \n",
"Equity 2000000 2.52325e+06 3.144e+06 3.86909e+06 \n",
"Growth rate 0.09 0.09 0.09 0.09 \n",
"Market risk premium 0.055 0.055 0.055 0.055 \n",
"New debt 0 0 0 0 \n",
"New debt issued 0 0 0 0 \n",
"Operating expenses -2200000 -2.398e+06 -2.61382e+06 -2.84906e+06 \n",
"Preferred dividends 0 0 0 0 \n",
"Principal repayment 300000 300000 300000 300000 \n",
"Rating B B B B \n",
"Revenues 3600000 3.924e+06 4.27716e+06 4.6621e+06 \n",
"Risk free rate 0.045 0.045 0.045 0.045 \n",
"Stable growth rate 0.03 0.03 0.03 0.03 \n",
"Target debt/equity ratio 1.3 1.3 1.3 1.3 \n",
"Tax rate 0.35 0.35 0.35 0.35 \n",
"Working capital change 0.04 0.04 0.04 0.04 \n",
"_Cost of equity 1.2485 1.52511 1.83298 2.17629 \n",
"_WACC 1.14846 1.3055 1.47284 1.6523 \n",
"EBIT 1300000 1.417e+06 1.54453e+06 1.68354e+06 \n",
"Interest rate 0.11 0.11 0.11 0.11 \n",
"Interest -495000 -462000 -429000 -396000 \n",
"Tax -281750 -334250 -390436 -450638 \n",
"Net income 523250 620750 725095 836900 \n",
"EBITDA 1400000 1.526e+06 1.66334e+06 1.81304e+06 \n",
"FCFF 676000 736840 803156 875440 \n",
"Interest*(1-T) 321750 300300 278850 257400 \n",
"FCFE 54250 136540 224306 318040 \n",
"Outstanding debt 4200000 3900000 3600000 3300000 \n",
"Outstanding equity 2.52325e+06 3.144e+06 3.86909e+06 4.70599e+06 \n",
"Total capitalization 6.72325e+06 7.044e+06 7.46909e+06 8.00599e+06 \n",
"D/E ratio 1.66452 1.24046 0.93045 0.701233 \n",
"BetaUL 1.77719 1.77719 1.77719 1.77719 \n",
"BetaL 3.7 3.21013 2.85202 2.58724 \n",
"Cost of equity 0.2485 0.221557 0.201861 0.187298 \n",
"Cost of debt 0.0715 0.0715 0.0715 0.0715 \n",
"WACC 0.148457 0.136742 0.128179 0.121847 \n",
"PV FCFF 588616 564412 545312 529832 \n",
"PV FCFE 43452.1 89527.7 122372 146139 \n",
"\n",
" Year 5 Year 6 \n",
"Capital expenditures 176448 181741 \n",
"Change in working capital 203268 209366 \n",
"Credit spread 0.065 0.065 \n",
"Debt 3300000 3000000 \n",
"Depreciation -141158 -145393 \n",
"Equity 4.70599e+06 5.66283e+06 \n",
"Growth rate 0.09 0.09 \n",
"Market risk premium 0.055 0.055 \n",
"New debt 0 0 \n",
"New debt issued 0 0 \n",
"Operating expenses -3.10548e+06 -3.19864e+06 \n",
"Preferred dividends 0 0 \n",
"Principal repayment 300000 0 \n",
"Rating B B \n",
"Revenues 5.08169e+06 5.23414e+06 \n",
"Risk free rate 0.045 0.045 \n",
"Stable growth rate 0.03 0.03 \n",
"Target debt/equity ratio 1.3 1.3 \n",
"Tax rate 0.35 0.35 \n",
"Working capital change 0.04 0.04 \n",
"_Cost of equity 2.5602 2.99874 \n",
"_WACC 1.8458 2.05786 \n",
"EBIT 1.83506e+06 1.89011e+06 \n",
"Interest rate 0.11 0.11 \n",
"Interest -363000 -330000 \n",
"Tax -515220 -546038 \n",
"Net income 956836 1.01407e+06 \n",
"EBITDA 1.97621e+06 2.0355e+06 \n",
"FCFF 954229 982856 \n",
"Interest*(1-T) 235950 214500 \n",
"FCFE 418279 768356 \n",
"Outstanding debt 3000000 3000000 \n",
"Outstanding equity 5.66283e+06 6.6769e+06 \n",
"Total capitalization 8.66283e+06 9.6769e+06 \n",
"D/E ratio 0.52977 0.44931 \n",
"BetaUL 1.77719 1.77719 \n",
"BetaL 2.38917 2.29622 \n",
"Cost of equity 0.176404 0.171292 \n",
"Cost of debt 0.0715 0.0715 \n",
"WACC 0.117111 0.114888 \n",
"PV FCFF 516974 477611 \n",
"PV FCFE 163378 256227 "
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"lbo"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/plain": [
"Text(0.5,1,'LBO Leverage Analysis')"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
},
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAW4AAAEeCAYAAACqgP9OAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4wLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvFvnyVgAAIABJREFUeJzt3Xl4VdXZ9/HvTQiGISBocKII1IEpECEBFQoRFVAqldfHCipgLVBsrUNbKo+PA15WX+pjq1ValDpSp6iIWqUKVlFwKJOpqFCwgsqraARlUkKA+/3j7KQhJOQEzsnJSn6f6zpXz7DO3vfa1N9ZWXsyd0dERMLRKNUFiIhIzSi4RUQCo+AWEQmMgltEJDAKbhGRwCi4RUQCo+AWqaPMbIqZPXSAy7jLzK5NVE1SNyi46zkzW2tmp1Xyfr6Z7TazrdHj/5nZDRXamJlNMrPVZvatmX1sZlPN7KB9rG++mY1LRl/qsqjfX+1r26SCu0909xtTXYckloK7YfvU3Vu4ewugP/BjMzu73Od3ABOAMUAmcAYwCHi81iuNk5k1TsE6OwDfAxwYXtvrl4ZHwS0AuPsa4A2gK4CZHQv8FLjA3d90953u/h5wDjDUzAbVdB1mdqKZvWFmX5vZP80sP3p/pJktqdD2SjN7Nnp+kJndGo34P4/+/G8afZZvZuvM7CozWw/cb2atzew5MyuKRsHPmVm7csvuaGavmdkWM3vJzP5Yfkqiqjr3YQzwFvAAMLZCPx6Ilv98tL5/mNl3y33+BzP7xMw2m9lSM/teFdvueTP7eYX33jGzs6O/jG4zsy/MbFP0fvdy6/9N9PzQaFt8bWYbzWyBmSkDAqR/NAHKgrofsQACOBVY5+6Lyrdz90+iNqfXcPlHAc8DvwHaAL8CZplZFvAscHxUQ6nzgUei578FjgNygGOAo4DryrU9PFrm0cT+QmgE3B+9bg98C0wr1/4RYBFwCDAFGB1nnVUZAzwcPYaY2WEVPh8F3AC0Bj4Abir32eKoX22iup4ws4xK1vEgcGG5OntG22EOMBgYQGwbHQycB2yoZBm/BNYBWcBhwNXE/kqQwCi4G7Yjo9HXZmAV8A9gYfTZocBnVXzvs+jzmrgQmOPuc9x9t7vPA5YAZ7r7N8AzxAKu9EekM/CsmRkwHrjS3Te6+xbgZmBkuWXvBq5392J3/9bdN7j7LHf/Jmp/EzAwWnZ7IA+4zt13uPtCYj8c1dZZWafMrD+xH4jH3X0p8G9iPzrlPeXui9x9J7Fwzyn9wN0fiurd6e6/Aw4Cjq9kVc8Ax5b7cRsNFLj7DqCE2FRWZ8DcfYW7V/ZvVwIcARzt7iXuvsB1saIgKbgbtk/d/WB3b0lspPYtsZEdwJfE/iOvzBHR5zVxNHBu9EPxtZl9TWxevXQdjxAFN7HgezoK9CygGbC03PdeiN4vVeTu20tfmFkzM7vbzD6KfpReAw42szTgSGBjtOxSn9SgzorGAnPdvXR7PEKF6RJgfbnn3wAtytX6SzNbEU1xfA20opIfRXcvJrZv4cJoemMU8Jfos5eJ/UXxR+BzM5thZi0rqfV/iY3455rZh2Y2uYo+SR2n4BYA3H0TsdA5K3rrZeA7ZtanfDsz+w5wIvD3Gq7iE+Av0Q9F6aO5u0+NPp8LHGpmOcRCqXSa5EtiPyjdyn2vVbRDtaz8Cuv6JbFRa9/oR2lAafnE/lpoY2bNyrX/Tg3qLL8tmgI/BAaa2fpojv1KoGc0lbFP0Xz2VdEyWrv7wcCmqM7KPAhcQGwa6xt3f7NsA7jf4e69gW7EpkwmVfyyu29x91+6eydi/86/MLNTq6tT6h4Fd8OQbmYZ5R57HXlhZi2ITT+8B+Duq4C7gIejnXVpZtYNmAW85O4v7WN9jSusLx14CDjLzIZEy8qIdiy2i9a3E3iS2KiwDTAven838GfgNjNrG9V6lJkN2cf6M4mF/ddm1ga4vvQDd/+I2NTHFDNrYmYn8Z8fK6qrs4KzgV3EdujmRI8uwAJi897VyQR2AkXRNrsOqGykXFr7m8SmhX5HNNoGMLM8M+sbbedtwPaorj2Y2ffN7Jho+mlz1GavdlL3KbgbhjnEgqz0MSV6/0iLjuMGPiIWmBeU+96lwD3EwmwrsSmK+cSOLNmX6RXWd3+0U/MHxHaIFREb2U5iz/8PPgKcBjwRBXmpq4j9if9WNPXxEpXPA5e6HWhKbLT+VlR3eRcAJxHbgfcboAAohrKdr9XVWWps1LeP3X196YPYtMUFlf1AVvAi8Ddi+xc+Iha4n+zzGzATyCb2b1KqJbEft6+i5WwAbq3ku8cS23ZbgTeBP7n7/GrWJ3WQad+ENHRmVgCsdPfrq22cYmY2Bpjg7v1TXYukjkbc0uBEUwvfNbNGZjaU2Aj76VTXVZ1oXv6nwIxU1yKppeCWhuhwYlM+W4mdHXqJu7+d0oqqEc3pFwGf858dt9JAaapERCQwGnGLiARGwS0iEpikXEnt0EMP9Q4dOiRj0SIi9dLSpUu/dPd9XROnTFKCu0OHDixZsqT6hiIiAoCZfRRvW02ViIgERsEtIhIYBbeISGBq7TZPJSUlrFu3ju3bt1ffWBIiIyODdu3akZ6enupSRCSBai24161bR2ZmJh06dCB2cTJJJndnw4YNrFu3jo4dO6a6HBFJoFqbKtm+fTuHHHKIQruWmBmHHHKI/sIRqYdqdY5boV27tL1F6iftnBQRCUytzXFX1GHy8wld3tqpw6ptk5aWRnZ2NiUlJTRu3JixY8dyxRVX0KhR1b9f8+fP59Zbb+W5557b67Obb76Zq6++utLvuTunnnoqTz/9NC1bxm5qsnbtWubPn89FF10UX6fKOe2003jiiSdo3bp1jb8rIomR/WB2tW2Wj12e9Doa1Ii7adOmFBYW8t577zFv3jzmzJnDDTfcsN/Lu/nmm6v8bM6cOfTs2bMstKdPn86QIUO49tpryc/PZ/369VV+tzKjR4/mT3/6037XKiL1R1zBbWYHm9mTZrYyuiP1SckuLNnatm3LjBkzmDZtGu7Orl27mDRpEnl5efTo0YO77767rO3mzZsZMWIEXbt2ZeLEiezevZvJkyfz7bffkpOTwwUXXLDX8h9++GF+8IMfALBlyxauv/56Zs6cyY033sgDDzxA8+bNAbjooou45JJLOOWUU+jUqROvvvoqF198MV26dNljZD58+HAeffTR5G4UEQlCvFMlfwBecPf/MrMmQLPqvhCCTp06sXv3br744gueeeYZWrVqxeLFiykuLqZfv34MHjwYgEWLFvH+++9z9NFHM3ToUJ566immTp3KtGnTKCwsrHTZr7/+eln4N2rUiB07drB582Ygdi2X8r766itefvllnn32Wc466yxef/117rnnHvLy8igsLCQnJ4fWrVtTXFzMhg0bOOSQQ5K3UUSkzqt2xG1mLYEBwL0A7r7D3b9OdmG1pfRGEnPnzmXmzJnk5OTQt29fNmzYwOrVqwHo06cPnTp1Ii0tjVGjRrFw4cJql7tx40YyMzMBaN68OTNnzuTqq6/m2muv5Ve/+hXffPNNWduzzjoLMyM7O5vDDjuM7OxsGjVqRLdu3Vi7dm1Zu7Zt2/Lpp58msPciEqJ4pko6Ebtl0v1m9raZ3WNmzZNcV6348MMPSUtLo23btrg7d955J4WFhRQWFrJmzZqyEXfFw+riOcyucePG7N69u+z18OHDeeKJJ/j1r39NUVERv/vd78o+O+igg4DYyLz0eenrnTv/c7Pz7du307Rp0/3rrIjUG/EEd2OgFzDd3U8AtgGTKzYyswlmtsTMlhQVFSW4zMQrKipi4sSJXHrppZgZQ4YMYfr06ZSUlACwatUqtm3bBsSmStasWcPu3bspKCigf//YDbbT09PL2ld0/PHH8+GHHwKwdetWPvoodsXGzMxMunTpwpYtW2pUr7uzfv36vaZZRKThiWeOex2wzt3/Eb1+kkqC291nEN19Ojc3t9obWcZz+F6ile5MLD0ccPTo0fziF78AYNy4caxdu5ZevXrh7mRlZfH007Ebf5900klMnjyZ5cuXM2DAAEaMGAHAhAkT6NGjB7169eLhhx/eY13Dhg1j/vz5HHPMMZSUlPCTn/yEL7/8kg0bNtC+fXseeaRm93tdunQpJ554Io0bp+wIThGpI+K6WbCZLQDGufu/zGwK0NzdJ1XVPjc31yveSGHFihV06dLlAMsNx2effcaYMWOYN29e2XsHchz35ZdfzvDhwzn11FNr9L2Gtt1FkimZx3Gb2VJ3z42nbbzDt58DD0dHlHwI/Gi/KmtAjjjiCMaPH8/mzZvLjuU++OCDycnJ2a/lde/evcahLSL1U1zB7e6FQFy/BPIfP/zhD/d4fSDBPX78+ESUJCL1QIM6c1JEpD5QcIuIBEbBLSISmAYV3GlpaeTk5JQ9pk6dut/LOvnkk4HYkSI1PbQP4O2332bcuHF7vDdlypT9qmX58uX7daSKiIQpdQcFT2mV4OVtqrZJ6dUBE+GNN94A/hPc559/fo2+f/PNN3PNNdcAsRN0Lr74Yl555RWeeuopzjjjDH7729/Gvazs7GzWrVvHxx9/TPv27WtUh4iEp0GNuKvywgsv0LlzZ/r3789ll13G97//fSA2Ar711lvL2nXv3r3s2iEtWrQAYPLkySxYsICcnBxuu+02vve97+3x49CvXz/eeeedPda3ZcsW3nnnHXr27AnAzJkzadq0KRMnTqSwsJDRo0eXtW3RogVXXXUVvXv35rTTTmPRokXk5+fTqVMnnn322bJ2Z511Fo899lhiN4yI1EkNKrhLz5wsfRQUFLB9+3bGjx/PX//6VxYsWFDj62RPnTq1LKyvvPJKxo0bxwMPPADETpsvLi6mR48ee3xnyZIldO/evex1kyZN2LhxIzt27KBRo0Z7fLZt2zby8/NZunQpmZmZXHPNNcybN4/Zs2dz3XXXlbXLzc1lwYIF+7FVRASIzQJU96gjGlRwl06VlD7OO+88Vq5cSceOHTn22GMxMy688MIDWse5557Lc889R0lJCffdd1+lc8+fffYZWVlZZa/HjBlDly5dePDBBzn55JN58sknyz5r0qQJQ4cOBWJTIgMHDiQ9PZ3s7GxdOVCkgdKFL6j6an8Vr/AXzx3TmzVrxumnn84zzzzD448/TsVT/yH2A1J+WU2aNOGWW26hWbNmnHfeeQwZMoTc3Fw6dOhAenp6WX3lrx6oKweKNFwNasRdmc6dO7NmzRr+/e9/A+xxl5kOHTqwbNkyAJYtW8aaNWv2+n5mZuZeV/obN24cl112GXl5ebRp02av73Tp0oUPPvig7PXq1avZsWMHAMceeyytWrXa43rd8Vi1atUeUywiUn81qOCuOMc9efJkMjIymDFjBsOGDaN///4cffTRZe3POeccNm7cSE5ODtOnT+e4447ba5k9evSgcePG9OzZk9tuuw2A3r1707JlS370o8ov6dK5c2c2bdpUFvgrV64kPz+f+++/nxNOOIFhw4bRtWvXGvXtlVdeYdiw2r/ioojUvriuDlhTIV8dcF93dY/Xp59+Sn5+PitXrqzyDvK33XYbmZmZexzLPWXKlP06lru4uJiBAweycOHCvS77Gsp2F0m5OHY+Znes/nDb2rg6YIMacdeGmTNn0rdvX2666aYqQxvgkksu2eNuNwD5+fn7tc6PP/6YqVOn6lrdIg2ERtz1nLa71KZ4rlcN+z8qTSqNuEVEJFkU3CIigVFwi4gERsEtIhKYBhXc69evZ+TIkXz3u9+la9eunHnmmaxatSrVZYmI1EjKjh+Ld+9zvKrbk+vujBgxgrFjx5ZdRa+wsJDPP/+80hNrDsSuXbtIS0tL6DJFREo1mBH3K6+8Qnp6OhMnTix7Lycnh/79+zNp0iS6d+9OdnY2BQUFAJx33nnMmTOnrO1FF13ErFmz2LVrF5MmTSIvL48ePXpw9913A7ETd0455RTOP/98srNjP0pnn302vXv3plu3bsyYMaNsWffeey/HHXcc+fn5jB8/nksvvRSAoqIizjnnHPLy8sjLy+P1119P+nYRkfA0mDM23n33XXr37r3X+0899RSFhYX885//5MsvvyQvL48BAwYwcuRICgoKOPPMM9mxYwd///vfmT59Ovfeey+tWrVi8eLFFBcX069fPwYPHgzAokWLePfdd+nYsSMA9913H23atOHbb78lLy+Pc845h+LiYm688UaWLVtGZmYmgwYNKrsu9+WXX86VV15J//79+fjjjxkyZAgrVqyovY0kIkFoMMFdlYULFzJq1CjS0tI47LDDGDhwIIsXL+aMM87gsssuo7i4mBdeeIEBAwbQtGlT5s6dyzvvvFN26dVNmzaxevVqmjRpQp8+fcpCG+COO+5g9uzZAHzyySesXr2a9evXM3DgwLKLT5177rll8+wvvfQS77//ftn3N2/ezJYtW8jMzKytzSEiAWgwwd2tW7c9rnNdqqozRzMyMsjPz+fFF1+koKCAUaNGlbW/8847GTJkyB7t58+fT/Pmzfd4/dJLL/Hmm2/SrFkz8vPz2b59e5XrA9i9ezdvvvmmLs8qIvvUYOa4Bw0aRHFxMX/+85/L3lu8eDGtW7emoKCAXbt2UVRUxGuvvUafPn0AGDlyJPfffz8LFiwoC+ohQ4Ywffp0SkpKgNjlVLdt27bX+jZt2kTr1q1p1qwZK1eu5K233gKgT58+vPrqq3z11Vfs3LmTWbNmlX1n8ODBTJs2rex1ou6PKSL1S1wjbjNbC2wBdgE74z2fvi4xM2bPns0VV1zB1KlTycjIoEOHDtx+++1s3bqVnj17YmbccsstHH744UAsSMeMGcPw4cNp0qQJELvW9tq1a+nVqxfuTlZWFk8//fRe6xs6dCh33XUXPXr04Pjjj+fEE08E4KijjuLqq6+mb9++HHnkkXTt2pVWrWLXSLjjjjv42c9+Ro8ePdi5cycDBgzgrrvuqqUtJCKhiOsiU1Fw57r7l/EsVBeZ2retW7fSokULdu7cyYgRI7j44osZMWJEUtal7S61SReZ0kWm6q0pU6aQk5ND9+7d6dixI2effXaqSxKRgMS7c9KBuWbmwN3uPqNiAzObAEwAaN+++l+lhuzWW29NdQkiErB4R9z93L0XcAbwMzMbULGBu89w91x3zy1/B3MREUmsuILb3T+N/vcLYDbQZ39WloybNkjVtL1F6qdqg9vMmptZZulzYDDwbk1XlJGRwYYNGxQmtcTd2bBhAxkZGakuRUQSLJ457sOA2WZW2v4Rd3+hpitq164d69ato6ioqKZflf2UkZFBu3btUl2GiCRYtcHt7h8CPQ90Renp6XucDi4iIvtHhwOKiARGwS0iEhgFt4hIYBTcIiKBaTCXdRWRAxTHtTyI41oecuA04hYRCYyCW0QkMApuEZHAaI5bpJbFc83qOnm9aqkzNOIWEQmMgltEJDB1aqqkw+Tnq22zduqwWqhERKTu0ohbRCQwCm4RkcAouEVEAqPgFhEJjIJbRCQwCm4RkcAouEVEAqPgFhEJjIJbRCQwderMyfosnrNCQWeGikj1NOIWEQmMgltEJDBxB7eZpZnZ22b2XDILEhGRfavJiPtyYEWyChERkfjEFdxm1g4YBtyT3HJERKQ68Y64bwd+DexOYi0iIhKHaoPbzL4PfOHuS6tpN8HMlpjZkqKiooQVKCIie4pnxN0PGG5ma4HHgEFm9lDFRu4+w91z3T03KysrwWWKiEipaoPb3f/b3du5ewdgJPCyu1+Y9MpERKRSOnNSEkL3CxWpPTUKbnefD8xPSiUikewHs6tts3zs8lqopIamtIqvXcf2ya1D6j2dOSkiEhgFt4hIYBTcIiKBUXCLiARGwS0iEhgFt4hIYBTcIiKBUXCLiARGZ06KxCGuM0MzaqEQETTiFhEJjoJbRCQwCm4RkcAouEVEAqPgFhEJjIJbRCQwCm4RkcAouEVEAqPgFhEJjM6cDFCwt/aSOktnhoZFI24RkcAouEVEAqPgFhEJjIJbRCQw2jkptWdKq/jadWyf3DpEAqcRt4hIYKoNbjPLMLNFZvZPM3vPzG6ojcJERKRy8UyVFAOD3H2rmaUDC83sb+7+VpJrExGRSlQb3O7uwNboZXr08GQWJSIiVYtr56SZpQFLgWOAP7r7PyppMwGYANC+vXYuiUjdUd/ODI1r56S773L3HKAd0MfMulfSZoa757p7blZWVqLrFBGRSI2OKnH3r4H5wNCkVCMiItWK56iSLDM7OHreFDgNWJnswkREpHLxzHEfATwYzXM3Ah539+eSW5aIiFQlnqNK3gFOqIVaREQkDvXylHddr1pE6jOd8i4iEhgFt4hIYBTcIiKBUXCLiARGwS0iEhgFt4hIYBTcIiKBUXCLiARGwS0iEph6eeZk0OK5oa5upivSoGnELSISGAW3iEhgFNwiIoFRcIuIBEbBLSISGAW3iEhgFNwiIoFRcIuIBEbBLSISGAW3iEhgFNwiIoFRcIuIBEbBLSISmGqD28y+Y2avmNkKM3vPzC6vjcJERKRy8VzWdSfwS3dfZmaZwFIzm+fu7ye5NhERqUS1I253/8zdl0XPtwArgKOSXZiIiFSuRnPcZtYBOAH4RzKKERGR6sV9BxwzawHMAq5w982VfD4BmADQvn0S79CiO8SISAMX14jbzNKJhfbD7v5UZW3cfYa757p7blZWViJrFBGRcuI5qsSAe4EV7v775JckIiL7Es+Iux8wGhhkZoXR48wk1yUiIlWodo7b3RcCVgu1iIhIHHTmpIhIYBTcIiKBUXCLiARGwS0iEhgFt4hIYBTcIiKBUXCLiARGwS0iEhgFt4hIYBTcIiKBUXCLiARGwS0iEhgFt4hIYBTcIiKBUXCLiARGwS0iEhgFt4hIYBTcIiKBUXCLiARGwS0iEhgFt4hIYBTcIiKBUXCLiARGwS0iEhgFt4hIYKoNbjO7z8y+MLN3a6MgERHZt3hG3A8AQ5Nch4iIxKna4Hb314CNtVCLiIjEIWFz3GY2wcyWmNmSoqKiRC1WREQqSFhwu/sMd89199ysrKxELVZERCrQUSUiIoFRcIuIBCaewwEfBd4EjjezdWb24+SXJSIiVWlcXQN3H1UbhYiISHw0VSIiEhgFt4hIYBTcIiKBUXCLiARGwS0iEhgFt4hIYBTcIiKBUXCLiARGwS0iEhgFt4hIYBTcIiKBUXCLiARGwS0iEhgFt4hIYBTcIiKBUXCLiARGwS0iEhgFt4hIYBTcIiKBUXCLiARGwS0iEhgFt4hIYBTcIiKBUXCLiARGwS0iEpi4gtvMhprZv8zsAzObnOyiRESkatUGt5mlAX8EzgC6AqPMrGuyCxMRkcrFM+LuA3zg7h+6+w7gMeAHyS1LRESqYu6+7wZm/wUMdfdx0evRQF93v7RCuwnAhOjl8cC/El9upQ4FvqyldaWC+hc29S9ctd23o909K56GjeNoY5W8t1fau/sMYEY8K00kM1vi7rm1vd7aov6FTf0LV13uWzxTJeuA75R73Q74NDnliIhIdeIJ7sXAsWbW0cyaACOBZ5NbloiIVKXaqRJ332lmlwIvAmnAfe7+XtIri1+tT8/UMvUvbOpfuOps36rdOSkiInWLzpwUEQmMgltEJDAKbpEkMbOeqa5Bas7MWphZTzNrmepaqlIvgtvMClNdw4Eys6PM7CEze8XMfm1mjct9NiuVtSWCmR1nZn81s2eiI5TuMbONZvaGmR2f6voOlJn1qPDoCTxvZtlm1iPV9R0oMxtb7vmRZvaimW0ws9fM7NhU1nagzOwBMzs0en46sAL4A/Cumf2flBZXhWB2TprZ8Ko+Av7s7m1rs55EM7MXgb8CbwE/BroDw939KzN7291PSGmBB8jMXgVuB1oANwLXAI8Cw4GfuvvpKSzvgJnZbmKHzu4o93YusARwdx+QksISxMyWuXuv6PljwGvEjroYAfzE3U9LZX0HwsyWu3t29Px1YLS7f2hmbYG57p6T2gr3Fs+Zk3XFLKCASs7aBJrWci3J0Nbdp0XPl0QjnNeiH6wwfl33raW7zwYws+vd/aHo/dlmdm0K60qU84FLgJvcfS6Ama1x9++ltqyk6OzuI6PnT5jZ1Smt5sA1MrNMd99C7L+1NQDu/oWZpae2tMqFFNzLgf9b2THkZvZJCupJtIPM7CB3LwZw9wfN7HNgHtAstaUlRFq553+o8FmT2iwkGdz9MTN7HrjZzC4GfkX9+MEt1c7Mfk/sL9xDzSzd3Uuiz0LKkcr8BnjZzO4EFgKPmdkzwCBi//3VOSFt8F8AW6v47NzaLCRJ7gdOAuaXvuHuL5jZSOB/U1VUAt1tZi3cfau731n6ppkdQ7k+hywasf3czHKBh4lNC9UX/13u+btAJrDRzA4H/paakhLD3R81s2XELpJ3HLFcPAWY7e7Pp7S4KgQzxy0SEjMz4GB3/yrVtUj9o+AWEQlMvTgcUESkIQkquM0szczOSXUdyaL+hU39C1dofQsquN19F3BFqutIFvUvbOpfuELrW3Bz3GZ2DbGjSwqAbaXvu/vmlBWVQOpf2NS/cIXUtxCDu7Jjtt3d29d6MUmg/oVN/QtXSH0LLrhFRBq6kE7AKWNmnYGuQEbpe+7+SOoqSiz1L2zqX7hC6VtwI+5oHmow0JnY7dSGAAvdvU5exaum1L+wqX/hCqlvQR1VEjmP2Omon7n7aKAngf7lUAX1L2zqX7iC6VuIwf1tdOjOTjPLBNYDnVJcUyKpf2FT/8IVTN/q5K9JNd42s4OB+4hd63gzsCy1JSWU+hc29S9cwfQtuDnu8qIry7V09zq5cQ+U+hc29S9cdb1vIU6VYGYjzex/3P0DoMjMeqe6pkRS/8Km/oUrlL4FN+I2s2lAOjDA3buYWRvgRXfPS3FpCaH+hU39C1dIfQtxjvtkd+9lZm8DuPtGMwv+DirlqH9hU//CFUzfQpwqKTGzRkS3hTKzQ4DdqS0podS/sKl/4QqmbyEG9x+J3Tg4y8xuIHaPuN+mtqSEUv/Cpv6FK5i+BTPHbWZzgJ+6+1oz6wacRuzGpS+5+7upre7AqX9hU//CFWTf3D2IB/BDYBWgy7xDAAAAq0lEQVTwP0B6qutR/9Q/9a9+PELsWzAjbgAzaw5cBwwF/kK5+Sd3/32q6koU9S9s6l+4QutbaEeVlBC7wPlBQCZ1dMfBAVD/wqb+hSuovgUT3GY2FPg98CzQy92/SXFJCaX+hU39C1eIfQtmqsTMFgAT3f29VNeSDOpf2NS/cIXYt2CCW0REYkI8jltEpEFTcIuIBEbBLSISGAW3iEhgFNwiIoFRcIuIBOb/A70XE7TkNjr/AAAAAElFTkSuQmCC\n",
"text/plain": [
"<Figure size 432x288 with 1 Axes>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Leverage \n",
"lbo.loc['Coverage'] = lbo.apply(lambda x: -x['EBITDA']/x['Interest'])\n",
"lbo.loc['Debt ($m)'] = lbo.loc['Debt']/1000000\n",
"lbo.loc['Equity ($m)'] = lbo.loc['Equity']/1000000\n",
"lbo.T[['Debt ($m)','Equity ($m)','Coverage']].plot.bar()\n",
"plt.title('LBO Leverage Analysis')"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/plain": [
"Text(0.5,1,'Free cash flow analysis of LBO')"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
},
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAZMAAAEeCAYAAABPMvhnAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4wLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvFvnyVgAAIABJREFUeJzt3XmcVNWZ//HPAyKbIghoFMRmJgjI1oEOsiiBKNqaRNwIGiOgMExcYvyZuJIAok5MTIiSuIyjCIojCsZtwKBGiWyCTWgFBAUTlBZEFkEUkMXn98c93Smaqm7oW1Bdxff9etWLqnPPvec81U09fc+5da65OyIiInHUyHQHREQk+ymZiIhIbEomIiISm5KJiIjEpmQiIiKxKZmIiEhsSiaS88wsz8zczA7bx/p1zexFM9tsZpPNbLCZzTrQ/TwQ0tF3M7vVzB5OV5/CMfd4j9N5bMkMJRNJycxWmtk2M/si4XF8pvt1EFwEHAs0dvf+me5Mprn7f7n70DQftsL32MxGmdnEZDuW+738zMymmtkJ5er0MLPXzGxLSFgvmtnJaY5BEiiZSGV+4O5HJDxWl6+wr3/xZ5ETgffdfVemO5LD4r7HP3D3I4DjgLXAH0s3mFl34GXgeeB4oCXwNjDbzP4tVq8lJSUT2W8Jw0ZDzOwj4LVQ3s3M5pjZJjN728x6J+xzlJk9YmZrzOxjM7vDzGqmOH7NMLTyQfjLckHpX55mdq+ZrTKzz0P5aQn7dTWzorBtrZmNKXfoS83sIzNbb2bDU7R9GzACGBD+8h2SpE4PM3sr/MX7lpn1COV9zGxRQr1XzWx+wutZZnZeinYrimuUmT1tZo+F92OJmRUkbL854b1618zOT9HGfWb2+3JlL5rZdeH5TeFns8XM3jOz0xPanxie1zGziWa2Ifyc3zKzY1O019bMZoR6S8zs3H19j/eVu28HpgCJZx2/BR5z93vdfYu7b3T3XwJvAqOq2pZUwt310CPpA1gJnJGkPA9w4DGgPlAXaAZsAM4h+iOlb3jdNOzzHPDfof4xwHzgP1O0ewOwCGgNGNCJaDgE4MdAY+Aw4OfAJ0CdsG0ucFl4fgTQrVx//yf0tRPwFdA2RfujgIkJrwcDs8Lzo4HPgMtCHy4JrxsDdYBtQJOw7RNgNXBkaHdbaRxJ2qworlHA9vDe1gR+DbyZsG9/or/AawADgC+B45L0vWvoT43wugmwlWi4qTWwCjg+4T379/LvB/CfwItAvdCXLkCDJPHUAlYAtwKHA98FtgCtk73Hlf0MUv1ehn5MIEoepa93A32S7Hc5sCbT/69y9aEzE6nMc+Evy01m9ly5baPc/Ut330b0YTjN3ae5+9fu/gpQBJwT/nI9G7gu1P8U+ANwcYo2hwK/dPf3PPK2u28AcPeJ7r7B3Xe5+++B2kQfhAA7gW+aWRN3/8Ld3yx33NvcfZu7v0007NGpCu/H94Dl7v546MOTwDKiYZftIeZeQAHwDjAL6Al0C/ttSHbQSuKCKCFMc/fdwOOJfXf3ye6+OrzvTwHLiRJH+TbmA5uB00PRxcAMd19L9AFcGzjZzGq5+0p3/yBJV3cSJb1vuvtud1/g7p8nqdeNKKHf5e473P014P+Ikm86PGdmm4DPif5wuTuUH02UVNck2WcNUQKVA0DJRCpznrs3DI/yQzSrEp6fCPRPSDybgFOJxrRPJPpLdU3Ctv8mOkNJ5gQg2QcZZvZzM1sahpg2AUfxrw+IIcBJwLIw/PL9crt/kvB8K9GH3f46HviwXNmHRGdmAH8DehMllL8BM4DvhMffUh20kriS9b1O6VyVmQ00s+KE97Y9qT80JxAlfsK/jwO4+wrgOqIzgk/NbJIlv9jicWA6MMnMVpvZb82sVpJ6xwOr3P3rhLLE9ymu89y9IVECvAb4m5l9g+gs8Wui37vyjgPWp6l9KUfJROJIXHJ6FfB4QuJp6O713f2usO0roEnCtgbu3i7FcVcB/16+MMwj3AT8EGgUPkw2Ew2F4e7L3f0SoiT1G2CKmdVPU6ylVhMlx0QtgI/D8/LJ5G9Ukkwqi6siZnYi0fDdNURDaA2BxRXsOxHoZ2adgLZEw48AuPv/uvupIT4neg/34O473f02dz8Z6AF8HxiYpJ3VwAlmlvgZk/g+pUU4O/oz0ZnVqe7+JdFwZ7Kr8H4I/DWd7cu/KJlIukwEfmBmZ4UJ9Dpm1tvMmrv7GqKra35vZg3MrIaZ/buZfSfFsR4GbjezVhbpaGaNieYedgHrgMPMbATQoHQnM/uxmTUNfw1vCsW70xznNOAkM/uRmR1mZgOIJn//L2yfQzQ81RWY7+5LiD6cTwHeSHHMCuOqRH2iD/51AGZ2OdGZSVLuXgK8RXSG8UwYosTMWpvZd82sNtH8zDaSvHfhIoMOFl088TnRsFey93ge0dzNjWZWy6KLMX4ATNrHuABqhN+j0kftJP0xM+sHNAKWhuKbgUFmdq2ZHWlmjczsDqA7cNt+tC/7QclE0sLdVwH9iCZc1xGdXdzAv37HBhJNxL5LNBQxheRDEQBjgKeJEtDnwCNEE9jTgZeA94mGTLaz51BbIbDEzL4A7gUuDvMYaRPmPL5PNEm+AbgR+L67rw/bvwT+Dixx9x1ht7nAh2GuKJnK4qqoP+8Cvw9trAU6ALMr2W1CqPd4Qllt4C6iYaBPiM7ubk2y7zeIfnafE314/43oD4ny/doBnEs0V7YeuB8Y6O7L9iWu4BKipFb6SBz6fDH8nD8H7gQGhcSNu88CzgIuIJon+RD4FtGZy/L9aF/2g7nr5lgihxIz60WUAPLKzWmIVJnOTEQOIWGy/GfAw0okkk5KJiKHCDNrSzSXdBxwT4a7IzlGw1wiIhKbzkxERCQ2JRMREYkt11Z7TalJkyael5eX6W6IiGSVBQsWrHf3ppXVO2SSSV5eHkVFRZnuhohIVjGz8ssHJaVhLhERia3SZGJm48zsUzNbnFB2tJm9YmbLw7+NQrmZ2VgzW2Fm75hZ54R9BoX6y81sUEJ5FzNbFPYZa2ZW1TZERCQz9uXMZDzRMhWJbgb+6u6tiBZOuzmUnw20Co9hwAMQJQZgJNH6RF2BkaXJIdQZlrBfYVXaEBGRzKl0zsTd3zCzvHLF/YhWRoVonZ8ZRKue9iO6SY0Db5pZQzM7LtR9xd03ApjZK0Chmc0gurHO3FD+GHAe0TpF+9VGWExwv+zcuZOSkhK2b0/r8k2SI+rUqUPz5s2pVSvZCusikqiqE/DHln54u/saMyu9L0Uz9lygriSUVVRekqS8Km3slUzMbBjR2QstWrTYK4iSkhKOPPJI8vLyCKNrIkB0B9INGzZQUlJCy5YtM90dkWov3RPwyT6RvQrlVWlj70L3h9y9wN0Lmjbd+8q27du307hxYyUS2YuZ0bhxY521iuyjqiaTtWH4ivBv6dLaJUR3ySvVnOgmORWVN09SXpU2qkSJRFLR74bIvqtqMnkBKL0iaxDwfEL5wHDFVTdgcxiqmg6cGW5S0wg4E5getm0xs27hKq6B5Y61P21kpZo1a5Kfn1/2WLlyJQDz58+nV69etG7dmjZt2jB06FC2bt3K+PHjadq0aVn9gQOjm9wNHjyYli1blpWPHTs2g1GJyKGm0jkTM3uSaCK8iZmVEF2VdRfwtJkNAT7iX7fInAacA6wguk/15QDuvtHMbie6wxvA6NLJeOBKoivG6hJNvL8UyverjXTIu3lqug4FwMq7vldpnbp161JcXLxH2dq1a+nfvz+TJk2ie/fuuDvPPPMMW7ZsAWDAgAH86U9/2utYd999NxdddFF6Oi8iB1RFnzf78tlR3ezL1VyXpNh0epK6Dlyd4jjjgHFJyotIcpvRcEe7/WojV9x3330MGjSI7t27A9Fwi5KEiFRn+gZ8hm3btq1saOr8888HYPHixXTp0iXlPk899VTZPo8++mhZ+Q033FBWvmjRogPedxGRUofM2lzVVbJhrspomEtEqhudmVRD7dq1Y8GCBZnuhojIPlMyqYauueYaJkyYwLx588rKJk6cyCeffJLBXomIpKZhrmro2GOPZdKkSfziF7/g008/pUaNGvTq1YsLLrgg010TOWhy7WqnXKdkkiATv6BffPFF0vLu3bszc+bMvcoHDx7M4MGD9yofP358mnsmIrLvNMwlIiKxKZmIiEhsSiYiIhKbkomIiMSmZCIiIrEpmYiISGxKJiIiEpu+Z5Jo1FFpPt7mSqscccQRKb9rUuqee+5h2LBh1KtXL10920txcTGrV6/mnHPOAaLb1n744YfMmDFjj++1PPfcc7zzzjuMGDGCN954g+uuu4533nmHSZMmJV0X7M4772Ty5MkALFq0iA4dOgBwxRVXUKNGDerXr8/ll6ftLgIikiE6M8kC99xzD1u3bt2vfXbv3r1f9YuLi5k2bVrZ65/85CfMmjWLjz76iCFDhvDxxx8D8Nvf/parrroKgBYtWjB+/Hh+9KMfpTzu8OHDKS4upri4uGxRy+LiYq699lquuOIK3cRLJEcomVQTM2bMoHfv3lx00UW0adOGSy+9FHdn7NixrF69mj59+tCnTx8AXn75Zbp3707nzp3p379/2ZlNXl4eo0eP5tRTT2Xy5Ml88MEHFBYW0qVLF0477TSWLVsGwOTJk2nfvj2dOnWiV69e7NixgxEjRpQtbf/UU0/xwAMPMGnSJMaNG8evf/1rmjVrxvvvv0/t2rVp0qRJWXsdO3akRo2q/RrVq1ePvLw85s+fn4Z3UEQyScNc1cjChQtZsmQJxx9/PD179mT27Nlce+21jBkzhtdff50mTZqwfv167rjjDl599VXq16/Pb37zG8aMGcOIESMAqFOnDrNmzQLg9NNP58EHH6RVq1bMmzePq666itdee43Ro0czffp0mjVrxqZNmzj88MMZPXo0RUVFZUvbX3nllQwYMICuXbsyfPhwbrvtNmbPnk3nzp3TGnNBQQEzZ86ka9euaT2uiBxcSibVSNeuXWnevDlA2f3gTz311D3qvPnmm7z77rv07NkTgB07dpTdkRGie51AtObXnDlz6N+/f9m2r776CoCePXsyePBgfvjDH6ZcPPL+++/nww8/ZPfu3WWJas2aNTRt2jRN0UaOOeaYsjMmEcleSibVSO3atcue16xZk127du1Vx93p27cvTz75ZNJj1K9fH4Cvv/6ahg0bJr3x1oMPPsi8efOYOnUq+fn5SeuYGXl5eXtMvtetW5fNmyu/qGD48OFMnRqt+FrZjb+2b99O3bp1Kz2miFRvmjPJAkceeSRbtmwBoFu3bsyePZsVK1YAsHXrVt5///299mnQoAEtW7Ysu5LK3Xn77bcB+OCDDzjllFMYPXo0TZo0YdWqVXu0kUrbtm3L2q3InXfeWTbRXpn333+f9u3bV1pPRKo3nZkk2odLeTNh2LBhnH322Rx33HG8/vrrjB8/nksuuaRs2OqOO+7gpJNO2mu/J554giuvvJI77riDnTt3cvHFF9OpUyduuOEGli9fjrtz+umn06lTJ1q0aMFdd91Ffn4+t9xyS9lwWaJevXrx85//HHfHzHjrrbc4//zz+eyzz3jxxRcZOXIkS5Ys2a/YZs+ezciRI6v2xhzidL8PqU7M3TPdh4OioKDAi4qK9ihbunQpbdu2zVCPstPPfvYzfvCDH3DGGWfEPtbChQsZM2YMjz/+eBp6dmBU59+RXE8miq96MLMF7l5QWT0Nc8l+ufXWW/f7Oy+prF+/nttvvz0txxKRzNIwl+yXY489lnPPPTctx+rbt29ajiMimaczExERiU3JREREYlMyERGR2JRMMqxmzZrk5+fTvn17+vfvn3Jy+5xzzmHTpk37ffzVq1cnXc13X+Xl5bF+/foq71/dlV/gUkSqRhPwCTpM6JDW4y0atKjSOqUr6QJceumlPPjgg1x//fVl290dd6/yB97xxx/PlClTqrTvvtq9ezc1a9Y8oG0cKMXFxRQVFZUtvS8iVaMzk2rktNNOY8WKFaxcuZK2bdty1VVX0blzZ1atWlV2hlC67T/+4z9o164dZ555Jtu2bQNgxYoVnHHGGXTq1InOnTvzwQcfsHLlyrJvmI8fP55+/fpRWFhI69atue2228raPu+88+jSpQvt2rXjoYceqrSvRxxxBCNGjOCUU05h7ty5LFiwgO985zt06dKFs846izVr1gDQu3dvrrvuOnr06EH79u3LVgieP38+PXr04Fvf+hY9evTgvffeK3sPEr8537NnT9555x1GjRrFoEGDOPPMM8nLy+PPf/4zN954Ix06dKCwsJCdO3cCVNiPm266ia5du3LSSScxc+bMpKsli0jVKJlUE7t27eKll14qu3nUe++9x8CBA1m4cCEnnnjiHnWXL1/O1VdfzZIlS2jYsCHPPPMMEJ3ZXH311bz99tvMmTOH4447bq925s+fzxNPPEFxcTGTJ0+m9Iuc48aNY8GCBRQVFTF27Fg2bNhQYX+//PJL2rdvz7x58zjllFP46U9/ypQpU1iwYAFXXHEFw4cP36PunDlzuP/++7niiisAaNOmDW+88QYLFy5k9OjR3HrrrQAMHTqU8ePHA9FSK1999RUdO3YEomVgpk6dyvPPP8+Pf/xj+vTpw6JFi6hbty5Tp05l586dFfZj165dzJ8/n3vuuYfbbrutbLXkAQMGUFxcnPRb/yKybzTMlWHbtm0jPz8fiP4qHzJkCKtXr+bEE0+kW7duSfdp2bJl2T5dunRh5cqVbNmyhY8//pjzzz8fiJaiT6Zv3740btwYgAsuuIBZs2ZRUFDA2LFjefbZZwFYtWoVy5cvL6uXTM2aNbnwwguBKPEtXry47Hsju3fv3iORXXLJJUC0HMvnn3/Opk2b2LJlC4MGDWL58uWYWdmZRf/+/bn99tu5++67GTdu3B4LTZ599tnUqlWLDh06sHv3bgoLCwHo0KEDK1eurLQfpSskl75nIpI+SiYZljhnkqh09d9kyq8uvG3bNvZ1WRwz2+v1jBkzePXVV5k7dy716tWjd+/ebN++vcLj1KlTp2yexN1p164dc+fO3ec2f/WrX9GnTx+effZZVq5cSe/evYHohll9+/bl+eef5+mnnyZxCZzSuGvUqEGtWrXKjlujRg127dpVaT9K90+1IrOIVF2sYS4z+39mtsTMFpvZk2ZWx8xamtk8M1tuZk+Z2eGhbu3wekXYnpdwnFtC+XtmdlZCeWEoW2FmNyeUJ23jUNagQQOaN2/Oc889B0T3Lkl2Zdgrr7zCxo0b2bZtG8899xw9e/Zk8+bNNGrUiHr16rFs2TLefPPN/Wq7devWrFu3ruxDfOfOnXss+Fg6FzFr1iyOOuoojjrqKDZv3kyzZs0Ayoa1Sg0dOpRrr72Wb3/72xx99NFp60cy+7JasohUrsrJxMyaAdcCBe7eHqgJXAz8BviDu7cCPgOGhF2GAJ+5+zeBP4R6mNnJYb92QCFwv5nVNLOawH3A2cDJwCWhLhW0cUh7/PHHGTt2LB07dqRHjx588skne9U59dRTueyyy8jPz+fCCy+koKCAwsJCdu3aRceOHfnVr36VcngtlcMPP5wpU6Zw00030alTJ/Lz85kzZ07Z9kaNGtGjRw9+8pOf8MgjjwBw4403csstt9CzZ8+97lffpUsXGjRowOWXX57WfiTTp08f3n33XU3Ai8RU5VWDQzJ5E+gEfA48B/wReAL4hrvvMrPuwCh3P8vMpofnc83sMOAToClwM4C7/zocdzowKjQzyt3PCuW3hLK7gHXJ2qiov1o1ODoDSLw178HQu3dvfve731FQUOmio2VWr15N7969WbZsWZXvL58u1fl3JFtWna0qxVc97OuqwVWeM3H3j83sd8BHwDbgZWABsMndSwekS4Bm4XkzYFXYd5eZbQYah/LEcZXEfVaVKz8l7JOqjT2Y2TBgGECLFi2qFqgcVI899hjDhw9nzJgxaUkk75Sk/qJnx+YNYx9fRCJVTiZm1gjoB7QENgGTiYakyis99bEU21KVJ/skqaj+3oXuDwEPQXRmkqzOoWTw4MF7XB11MMyYMWO/6g8cOJCBAwcemM6IyAET50+/M4B/uvs6d98J/BnoATQMw1gAzYHV4XkJcAJA2H4UsDGxvNw+qcrXV9CGiIhkQJxk8hHQzczqWXSN5unAu8DrQOliUIOA58PzF8JrwvbXPJqweQG4OFzt1RJoBcwH3gJahSu3DieapH8h7JOqDRERyYAqJxN3nwdMAf4OLArHegi4CbjezFYQzW88EnZ5BGgcyq/nXxPvS4CniRLRX4Cr3X13mBO5BpgOLAWeDnWpoA0REcmAWF9adPeRwMhyxf8Auiapux3on+I4dwJ3JimfBuy1wqG7J21DREQyQ2tzZVjpEvSlj9JlPubPn0+vXr1o3bo1bdq0YejQoWzdupXx48fTtGnTsvqlk9WDBw8uW2YlPz+fHj16ZDAqETnUaDmVBEvbpPf7BG2XLa20TrLlVNauXUv//v2ZNGkS3bt3x9155plnyr6pPWDAgKTfFbn77rtj3btERLJbRZ9h+/J5FIeSSTV03333MWjQILp37w5Ea1kpSYhIdaZhrgwrXTU4Pz+/bMXfxYsX06VLl5T7lN5/Iz8/n0cffbSs/IYbbigrv/TSSw9430VESunMJMNSrRpcEQ1ziUh1ozOTaqhdu3YsWLAg090QEdlnSibV0DXXXMOECROYN29eWdnEiROTrgIsIlIdKJlUQ8ceeyyTJk3iF7/4Ba1bt6Zt27bMnDmTBg0aVLhf4pxJfn4+O3bsOEg9FpFDneZMEhzoS+eS+eKLL5KWd+/enZkzZ+5VnmqxxvI3mNpXWlVXRNJBZyYiIhKbkomIiMSmZCIiIrEd8smkqrctltyn3w2RfXdIJ5M6deqwYcMGfWjIXtydDRs2UKdOnUx3RSQrHNJXczVv3pySkhLWrVuX6a5kzNrPtqXctnRL3YPYkwMjTnx16tShefPm6e6SSE46pJNJrVq1aNmyZaa7kVFn3zw15baVd33vIPbkwMj1+ESqi0N6mEtERNJDyURERGJTMhERkdiUTEREJDYlExERie2QvpprX+TpaiARkUrpzERERGJTMhERkdiUTEREJDYlExERiU3JREREYlMyERGR2JRMREQkNiUTERGJTclERERiUzIREZHYlExERCS2WMnEzBqa2RQzW2ZmS82su5kdbWavmNny8G+jUNfMbKyZrTCzd8ysc8JxBoX6y81sUEJ5FzNbFPYZa2YWypO2ISIimRH3zORe4C/u3gboBCwFbgb+6u6tgL+G1wBnA63CYxjwAESJARgJnAJ0BUYmJIcHQt3S/QpDeao2REQkA6qcTMysAdALeATA3Xe4+yagHzAhVJsAnBee9wMe88ibQEMzOw44C3jF3Te6+2fAK0Bh2NbA3ee6uwOPlTtWsjZERCQD4pyZ/BuwDnjUzBaa2cNmVh841t3XAIR/jwn1mwGrEvYvCWUVlZckKaeCNkREJAPiJJPDgM7AA+7+LeBLKh5usiRlXoXyfWZmw8ysyMyK1q1btz+7iojIfoiTTEqAEnefF15PIUoua8MQFeHfTxPqn5Cwf3NgdSXlzZOUU0Ebe3D3h9y9wN0LmjZtWqUgRUSkclVOJu7+CbDKzFqHotOBd4EXgNIrsgYBz4fnLwADw1Vd3YDNYYhqOnCmmTUKE+9nAtPDti1m1i1cxTWw3LGStSEiIhkQ97a9PwWeMLPDgX8AlxMlqKfNbAjwEdA/1J0GnAOsALaGurj7RjO7HXgr1Bvt7hvD8yuB8UBd4KXwALgrRRsiIpIBsZKJuxcDBUk2nZ6krgNXpzjOOGBckvIioH2S8g3J2hARkczQN+BFRCQ2JRMREYlNyURERGJTMhERkdiUTEREJDYlExERiU3JREREYlMyERGR2JRMREQkNiUTERGJTclERERiUzIREZHYlExERCQ2JRMREYlNyURERGJTMhERkdiUTEREJDYlExERiU3JREREYlMyERGR2JRMREQkNiUTERGJTclERERiUzIREZHYlExERCQ2JRMREYlNyURERGJTMhERkdiUTEREJDYlExERiU3JREREYlMyERGR2JRMREQktsPiHsDMagJFwMfu/n0zawlMAo4G/g5c5u47zKw28BjQBdgADHD3leEYtwBDgN3Ate4+PZQXAvcCNYGH3f2uUJ60jbixiEhuW9qmbcptbZctPYg9yT3pODP5GZD4U/gN8Ad3bwV8RpQkCP9+5u7fBP4Q6mFmJwMXA+2AQuB+M6sZktR9wNnAycAloW5FbYiISAbESiZm1hz4HvBweG3Ad4EpocoE4LzwvF94Tdh+eqjfD5jk7l+5+z+BFUDX8Fjh7v8IZx2TgH6VtCEiIhkQ98zkHuBG4OvwujGwyd13hdclQLPwvBmwCiBs3xzql5WX2ydVeUVtiIhIBlQ5mZjZ94FP3X1BYnGSql7JtnSVJ+vjMDMrMrOidevWJasiIiJpEOfMpCdwrpmtJBqC+i7RmUpDMyud2G8OrA7PS4ATAML2o4CNieXl9klVvr6CNvbg7g+5e4G7FzRt2rTqkYqISIWqnEzc/RZ3b+7ueUQT6K+5+6XA68BFodog4Pnw/IXwmrD9NXf3UH6xmdUOV2m1AuYDbwGtzKylmR0e2ngh7JOqDRERyYAD8T2Tm4DrzWwF0fzGI6H8EaBxKL8euBnA3ZcATwPvAn8Brnb33WFO5BpgOtHVYk+HuhW1ISIiGRD7eyYA7j4DmBGe/4PoSqzydbYD/VPsfydwZ5LyacC0JOVJ2xARkczQN+BFRCQ2JRMREYlNyURERGJTMhERkdiUTEREJDYlExERiU3JREREYlMyERGR2JRMREQkNiUTERGJTclERERiS8vaXIesUUdVsG3zweuHiEiGKZmISJmlbdqm3NZ22dKD2BPJNhrmEhGR2JRMREQkNiUTERGJTXMmB0iHCR1Sbls0aNFB7EkMFVxg0KFli5Tbsia+CuTEzy+FimJ7+iD240DJ9fiqK52ZiIhIbEomIiISm5KJiIjEpjkTOXRV9KXTCuaERGRvOjMREZHYlExERCQ2JRMREYlNyUSTSB8lAAAJUklEQVRERGLTBLxILtLFBXKQ6cxERERiUzIREZHYlExERCQ2zZmISPbRnFC1ozMTERGJTclERERi0zCXyH7SfdJF9qYzExERia3KZyZmdgLwGPAN4GvgIXe/18yOBp4C8oCVwA/d/TMzM+Be4BxgKzDY3f8ejjUI+GU49B3uPiGUdwHGA3WBacDP3N1TtVHVWEREqpWKLjAYtfng9WM/xDkz2QX83N3bAt2Aq83sZOBm4K/u3gr4a3gNcDbQKjyGAQ8AhMQwEjgF6AqMNLNGYZ8HQt3S/QpDeao2REQkA6qcTNx9TemZhbtvAZYCzYB+wIRQbQJwXnjeD3jMI28CDc3sOOAs4BV33xjOLl4BCsO2Bu4+192d6Cwo8VjJ2hARkQxIy5yJmeUB3wLmAce6+xqIEg5wTKjWDFiVsFtJKKuovCRJORW0Ub5fw8ysyMyK1q1bV9XwRESkErGTiZkdATwDXOfun1dUNUmZV6F8n7n7Q+5e4O4FTZs23Z9dRURkP8RKJmZWiyiRPOHufw7Fa8MQFeHfT0N5CXBCwu7NgdWVlDdPUl5RGyIikgFVTibh6qxHgKXuPiZh0wvAoPB8EPB8QvlAi3QDNochqunAmWbWKEy8nwlMD9u2mFm30NbAcsdK1oaIiGRAnC8t9gQuAxaZWXEouxW4C3jazIYAHwH9w7ZpRJcFryC6NPhyAHffaGa3A2+FeqPdfWN4fiX/ujT4pfCggjZERCQDqpxM3H0Wyec1AE5PUt+Bq1McaxwwLkl5EdA+SfmGZG2IiEhm6BvwIiISm5KJiIjEpmQiIiKxKZmIiEhsSiYiIhKbkomIiMSmZCIiIrHpTouSdroTocihR2cmIiISm5KJiIjEpmQiIiKxKZmIiEhsSiYiIhKbkomIiMSmZCIiIrEpmYiISGxKJiIiEpuSiYiIxKZkIiIisSmZiIhIbEomIiISm5KJiIjEpmQiIiKxKZmIiEhsSiYiIhKb7rSYAboToYjkGp2ZiIhIbEomIiISm5KJiIjEpmQiIiKxKZmIiEhsSiYiIhKbkomIiMSWtcnEzArN7D0zW2FmN2e6PyIih7KsTCZmVhO4DzgbOBm4xMxOzmyvREQOXVmZTICuwAp3/4e77wAmAf0y3CcRkUOWuXum+7DfzOwioNDdh4bXlwGnuPs15eoNA4aFl62B9w5iN5sA6w9ieweb4steuRwbKL50O9Hdm1ZWKVvX5rIkZXtlRXd/CHjowHdnb2ZW5O4FmWj7YFB82SuXYwPFlynZOsxVApyQ8Lo5sDpDfREROeRlazJ5C2hlZi3N7HDgYuCFDPdJROSQlZXDXO6+y8yuAaYDNYFx7r4kw90qLyPDaweR4steuRwbKL6MyMoJeBERqV6ydZhLRESqESUTERGJTclE5BBiZp0y3QepGjM7wsw6mVmDTPclGSWTA8jMijPdh7jMrJmZTTSz183sRjM7LGHbM5nsW1xmdpKZvWhmz4crAx82s41mNsfMWme6f3GZWcdyj07AVDPrYGYdM92/uMxsUMLz481supltMLM3zKxVJvuWDmY23syahOd9gaXAvcBiM7sgo51LQhPwMZnZuak2Af/j7scczP6km5lNB14E3gSGAO2Bc939MzNb6O7fymgHYzCzvwH3AEcAtwO/BJ4EzgWucve+GexebGb2NdFl9DsSiguAIsDdvVdGOpYmZvZ3d+8cnk8C3iC60ul84D/d/YxM9i8uM1vk7h3C89nAZe7+DzM7BnjZ3fMz28M9ZeWlwdXMM8BTJPkGPlD3IPflQDjG3f8UnheFvwbfCEk02/8SaeDuzwKY2Uh3nxjKnzWzX2WwX+nyI+BK4E53fxnAzP7p7qdltlsHRBt3vzg8n2xmt2a0N+lRw8yOdPctRP/X/gng7p+aWa3Mdm1vSibxLQJ+nex7Lma2KgP9SbfaZlbb3b8CcPcJZrYWeAWol9muxVYz4fm95bYdfjA7ciC4+yQzmwr8l5ldAfyC7P8DIFFzMxtDNArQxMxqufvOsC0XPtvuAF4zsz8Cs4BJZvY88F2i/3/VSi684Zl2PfBFim39D2ZHDpBHge7AjNICd/+LmV0M3J2pTqXJf5vZEe7+hbv/sbTQzL5JQrzZLPxV+1MzKwCeIBrSyxW3JDxfDBwJbDSzbwAvZaZL6ePuT5rZ34kWqz2J6PO6D/Csu0/NaOeS0JyJyCHCzAxo6O6fZbovknuUTEREJDZdGiwiIrEpmaSBmdU0swsz3Y8DJZfjy+XYQPFlu2yKT8kkDdx9N3BdpvtxoORyfLkcGyi+bJdN8WnOJE3M7JdEV3U9BXxZWu7un2esU2mUy/Hlcmyg+LJdtsSnZJImKb5T4u7e4qB35gDI5fhyOTZQfNkuW+JTMhERkdj0pcU0MrM2wMlAndIyd//fzPUovXI5vlyODRRftsuG+HRmkiZhXPNMoA3R7YTPAma5e7Vb3bMqcjm+XI4NFF+2y5b4dDVX+gwgWupgjbtfBnQit878cjm+XI4NFF+2y4r4lEzSZ1u4jG+XmR0JfAL8W4b7lE65HF8uxwaKL9tlRXzVLrtlsYVm1hAYR3S/iM+Bv2e2S2mVy/Hlcmyg+LJdVsSnOZMDIKw628Ddq90PPB1yOb5cjg0UX7arzvFpmCuNzOxiMxvu7iuAdWbWJdN9Sqdcji+XYwPFl+2yIT6dmaSJmf0JqAX0cve2ZnY0MN3dv53hrqVFLseXy7GB4st22RKf5kzSp4e7dzazhQDuvtHMsv5ufQlyOb5cjg0UX7bLivg0zJU+O82sBuG2qGbWGPg6s11Kq1yOL5djA8WX7bIiPiWT9LkPeAZoama3Ed2z+TeZ7VJa5XJ8uRwbKL5slxXxac4kJjObBlzl7ivNrB1wBmDAq+6+OLO9iy+X48vl2EDxZbZ38WVbfJoziW888LKZTQB+6+5LMtyfdBtP7sY3ntyNDRRfthtPFsWnM5M0MLP6wAigEHichPFMdx+TqX6lSy7Hl8uxgeLLVL/SJZvi05lJeuwkumlNbeBIquHkWEy5HF8uxwaKL9tlTXxKJjGZWSEwBngB6OzuWzPcpbTK5fhyOTZQfNku2+LTMFdMZjYT+El1H8+sqlyOL5djA8WX7bItPiUTERGJTd8zERGR2JRMREQkNiUTERGJTclERERiUzIREZHYlExERCS2/w9/aKtvjANnOgAAAABJRU5ErkJggg==\n",
"text/plain": [
"<Figure size 432x288 with 1 Axes>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"lbo.T[['FCFF','Interest*(1-T)','Principal repayment','FCFE']].plot.bar()\n",
"plt.title('Free cash flow analysis of LBO')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Handy function / look-up table"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"('D', 0.14)"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"is_inrange = lambda low,high,value: low<value and value<high\n",
"\n",
"rating_table = {(-100000,0.5):('D',0.14),\n",
" (0.5,0.8):('C',0.127),\n",
" (0.8,1.25):('CC',0.115),\n",
" (1.25,1.5):('CCC',0.10),\n",
" (1.5,2.0):('B-',0.08),\n",
" (2.0,2.5):('B',0.065),\n",
" (2.5,3.0):('B+',0.048),\n",
" (3.0,3.5):('BB',0.035),\n",
" (3.5,4.0):('BBB',0.023),\n",
" (4.5,6.0):('A-',0.020),\n",
" (6.0,7.5):('A',0.018),\n",
" (7.5,9.5):('A+',0.015),\n",
" (9.5,12.5):('AA',0.010),\n",
" (12.5,100000):('AAA',0.008)\n",
" }\n",
"def get_rating_spread(coverage):\n",
" \"\"\"\n",
" @param coverage: interest coverage ratio\n",
" @return rating and spread\n",
" \"\"\"\n",
" for low,high in rating_table:\n",
" if is_inrange(low,high,coverage): \n",
" return rating_table[(low,high)]\n",
"get_rating_spread(0.3)\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 3 Comparable Company Analysis\n",
"\n",
"<ol>\n",
" <li>Select companies from the same industry, with similar market cap/EV/EBITDA (see my previous post for how to scrape this information).</li>\n",
" <li>Use DCF to find the intrinsic values</li>\n",
" <li>List out the multiples: higer values mean the company is overvalued</li>\n",
" <ul>\n",
" <li>Operating multiples: EV/Sales, EV/EBITDA</li>\n",
" <li>Equity multiples: P/E,P/B,P/(Levered) Cash flow</li>\n",
" </ul>\n",
" <li>Find the transaction multiples: from recent acquisitions in the same industry based on the purchase price</li>\n",
"</ol>\n",
"\n",
"Industry-specific cases:\n",
"<ul>\n",
" <li>Financial services, Banks, Insurance, Technology: P/B (mostly liquid assets)</li>\n",
" <li>Retail, Airlines: EV/EBITDAR (rental/lease expenses)</li>\n",
" <li>Oil & gas: EV/EBITDAX, EV/Reserves, EV/Production, EV/Capacity</li>\n",
" <li>High growth (e.g. Tech, speciality retail, IPO): PEG, (EV/EBITDA)/EBITDA_CAGR</li>\n",
"</ul>\n",
"\n",
"Source:\n",
"<ol>\n",
" <li>https://www.investopedia.com/terms/c/comparable-company-analysis-cca.asp</li>\n",
" <li>http://www.streetofwalls.com/finance-training-courses/investment-banking-technical-training/comparable-company-analysis/</li>\n",
" <li>For a list of industry-specific multiples: https://valuationacademy.com/industry-specific-multiples/</li>\n",
"</ol>"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 4 Mergers and Acquisitions Model: Accretion/Dilution Analysis\n",
"\n",
"This asks the question of how the merger will affect the synergies or financial indicators. It is an accretion when the combined entity's EPS > acquirer's EPS; and the opposite for dilution."
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 2
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython2",
"version": "2.7.14"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment