dcf_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to create a DCF using Pandas and Jupyter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1) Cleaning up the data and organizing it in order to begin performing analyses later"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the csv data\n",
    "dcf = pd.read_csv(\"~/workspace/csv_files/dcf-analysis_inputs.csv\")\n",
    "dcf.columns = ['Row', '2013', '2014', '2015', '2016', '2017', '2018', '2019',\n",
    "       '2020', '2021', '2022']\n",
    "dcf = dcf.set_index([\"Row\"])\n",
    "dcf\n",
    "tdcf={}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "dcf.rename(index = {'( + ) Depreciation':'Depr','( – ) Capital expenditures':\"capex\",'( – ) Change in working capital':'wc', '( – ) Change in deferred taxes':\"dt\"}, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2) Create a dictionary with overall assumptions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'net debt': 25.6,\n",
       " 'shares outstanding': 35.159,\n",
       " 'tax rate': 0.35,\n",
       " 'terminal multiple': 5,\n",
       " 'terminal growth rate': 0.005,\n",
       " 'WACC': 0.12}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assumptions = {\n",
    "    \"net debt\": 25.6,\n",
    "    \"shares outstanding\": 35.159,\n",
    "    \"tax rate\": .35,\n",
    "    \"terminal multiple\": 5,\n",
    "    \"terminal growth rate\": .005,\n",
    "    \"WACC\":.12,\n",
    "}\n",
    "assumptions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3) Now begin adding rows to calculate unlevered free cash flow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</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",
       "    <tr>\n",
       "      <th>Row</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>EBITDA</th>\n",
       "      <td>94.875000</td>\n",
       "      <td>155.900000</td>\n",
       "      <td>160.100000</td>\n",
       "      <td>161.261360</td>\n",
       "      <td>162.433088</td>\n",
       "      <td>163.615275</td>\n",
       "      <td>164.808015</td>\n",
       "      <td>166.011402</td>\n",
       "      <td>167.225531</td>\n",
       "      <td>168.450499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBITA</th>\n",
       "      <td>82.692857</td>\n",
       "      <td>139.657143</td>\n",
       "      <td>143.857143</td>\n",
       "      <td>144.892637</td>\n",
       "      <td>145.937375</td>\n",
       "      <td>146.991438</td>\n",
       "      <td>148.054911</td>\n",
       "      <td>150.735021</td>\n",
       "      <td>152.353279</td>\n",
       "      <td>153.445487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Depr</th>\n",
       "      <td>12.182143</td>\n",
       "      <td>16.242857</td>\n",
       "      <td>16.242857</td>\n",
       "      <td>16.368723</td>\n",
       "      <td>16.495713</td>\n",
       "      <td>16.623836</td>\n",
       "      <td>16.753103</td>\n",
       "      <td>15.276381</td>\n",
       "      <td>14.872252</td>\n",
       "      <td>15.005012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>capex</th>\n",
       "      <td>-12.750000</td>\n",
       "      <td>-18.000000</td>\n",
       "      <td>-19.000000</td>\n",
       "      <td>-19.169607</td>\n",
       "      <td>-19.340728</td>\n",
       "      <td>-19.513376</td>\n",
       "      <td>-19.687566</td>\n",
       "      <td>-19.863310</td>\n",
       "      <td>-20.040623</td>\n",
       "      <td>-20.219519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wc</th>\n",
       "      <td>1.955512</td>\n",
       "      <td>-3.790204</td>\n",
       "      <td>-1.293488</td>\n",
       "      <td>-0.410356</td>\n",
       "      <td>-0.414019</td>\n",
       "      <td>-0.417715</td>\n",
       "      <td>-0.421444</td>\n",
       "      <td>-0.425206</td>\n",
       "      <td>-0.429001</td>\n",
       "      <td>-0.432831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dt</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>taxes</th>\n",
       "      <td>-28.942500</td>\n",
       "      <td>-48.880000</td>\n",
       "      <td>-50.350000</td>\n",
       "      <td>-50.712423</td>\n",
       "      <td>-51.078081</td>\n",
       "      <td>-51.447003</td>\n",
       "      <td>-51.819219</td>\n",
       "      <td>-52.757257</td>\n",
       "      <td>-53.323648</td>\n",
       "      <td>-53.705920</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             2013        2014        2015        2016        2017        2018  \\\n",
       "Row                                                                             \n",
       "EBITDA  94.875000  155.900000  160.100000  161.261360  162.433088  163.615275   \n",
       "EBITA   82.692857  139.657143  143.857143  144.892637  145.937375  146.991438   \n",
       "Depr    12.182143   16.242857   16.242857   16.368723   16.495713   16.623836   \n",
       "capex  -12.750000  -18.000000  -19.000000  -19.169607  -19.340728  -19.513376   \n",
       "wc       1.955512   -3.790204   -1.293488   -0.410356   -0.414019   -0.417715   \n",
       "dt       0.000000    0.000000    0.000000    0.000000    0.000000    0.000000   \n",
       "taxes  -28.942500  -48.880000  -50.350000  -50.712423  -51.078081  -51.447003   \n",
       "\n",
       "              2019        2020        2021        2022  \n",
       "Row                                                     \n",
       "EBITDA  164.808015  166.011402  167.225531  168.450499  \n",
       "EBITA   148.054911  150.735021  152.353279  153.445487  \n",
       "Depr     16.753103   15.276381   14.872252   15.005012  \n",
       "capex   -19.687566  -19.863310  -20.040623  -20.219519  \n",
       "wc       -0.421444   -0.425206   -0.429001   -0.432831  \n",
       "dt        0.000000    0.000000    0.000000    0.000000  \n",
       "taxes   -51.819219  -52.757257  -53.323648  -53.705920  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dcf.loc['taxes']=dcf.loc['EBITA']*-assumptions['tax rate']\n",
    "dcf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</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",
       "    <tr>\n",
       "      <th>Row</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>EBITDA</th>\n",
       "      <td>94.875000</td>\n",
       "      <td>155.900000</td>\n",
       "      <td>160.100000</td>\n",
       "      <td>161.261360</td>\n",
       "      <td>162.433088</td>\n",
       "      <td>163.615275</td>\n",
       "      <td>164.808015</td>\n",
       "      <td>166.011402</td>\n",
       "      <td>167.225531</td>\n",
       "      <td>168.450499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBITA</th>\n",
       "      <td>82.692857</td>\n",
       "      <td>139.657143</td>\n",
       "      <td>143.857143</td>\n",
       "      <td>144.892637</td>\n",
       "      <td>145.937375</td>\n",
       "      <td>146.991438</td>\n",
       "      <td>148.054911</td>\n",
       "      <td>150.735021</td>\n",
       "      <td>152.353279</td>\n",
       "      <td>153.445487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Depr</th>\n",
       "      <td>12.182143</td>\n",
       "      <td>16.242857</td>\n",
       "      <td>16.242857</td>\n",
       "      <td>16.368723</td>\n",
       "      <td>16.495713</td>\n",
       "      <td>16.623836</td>\n",
       "      <td>16.753103</td>\n",
       "      <td>15.276381</td>\n",
       "      <td>14.872252</td>\n",
       "      <td>15.005012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>capex</th>\n",
       "      <td>-12.750000</td>\n",
       "      <td>-18.000000</td>\n",
       "      <td>-19.000000</td>\n",
       "      <td>-19.169607</td>\n",
       "      <td>-19.340728</td>\n",
       "      <td>-19.513376</td>\n",
       "      <td>-19.687566</td>\n",
       "      <td>-19.863310</td>\n",
       "      <td>-20.040623</td>\n",
       "      <td>-20.219519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wc</th>\n",
       "      <td>1.955512</td>\n",
       "      <td>-3.790204</td>\n",
       "      <td>-1.293488</td>\n",
       "      <td>-0.410356</td>\n",
       "      <td>-0.414019</td>\n",
       "      <td>-0.417715</td>\n",
       "      <td>-0.421444</td>\n",
       "      <td>-0.425206</td>\n",
       "      <td>-0.429001</td>\n",
       "      <td>-0.432831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dt</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>taxes</th>\n",
       "      <td>-28.942500</td>\n",
       "      <td>-48.880000</td>\n",
       "      <td>-50.350000</td>\n",
       "      <td>-50.712423</td>\n",
       "      <td>-51.078081</td>\n",
       "      <td>-51.447003</td>\n",
       "      <td>-51.819219</td>\n",
       "      <td>-52.757257</td>\n",
       "      <td>-53.323648</td>\n",
       "      <td>-53.705920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unlev_net_income</th>\n",
       "      <td>53.750357</td>\n",
       "      <td>90.777143</td>\n",
       "      <td>93.507143</td>\n",
       "      <td>94.180214</td>\n",
       "      <td>94.859294</td>\n",
       "      <td>95.544435</td>\n",
       "      <td>96.235692</td>\n",
       "      <td>97.977763</td>\n",
       "      <td>99.029631</td>\n",
       "      <td>99.739566</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       2013        2014        2015        2016        2017  \\\n",
       "Row                                                                           \n",
       "EBITDA            94.875000  155.900000  160.100000  161.261360  162.433088   \n",
       "EBITA             82.692857  139.657143  143.857143  144.892637  145.937375   \n",
       "Depr              12.182143   16.242857   16.242857   16.368723   16.495713   \n",
       "capex            -12.750000  -18.000000  -19.000000  -19.169607  -19.340728   \n",
       "wc                 1.955512   -3.790204   -1.293488   -0.410356   -0.414019   \n",
       "dt                 0.000000    0.000000    0.000000    0.000000    0.000000   \n",
       "taxes            -28.942500  -48.880000  -50.350000  -50.712423  -51.078081   \n",
       "unlev_net_income  53.750357   90.777143   93.507143   94.180214   94.859294   \n",
       "\n",
       "                        2018        2019        2020        2021        2022  \n",
       "Row                                                                           \n",
       "EBITDA            163.615275  164.808015  166.011402  167.225531  168.450499  \n",
       "EBITA             146.991438  148.054911  150.735021  152.353279  153.445487  \n",
       "Depr               16.623836   16.753103   15.276381   14.872252   15.005012  \n",
       "capex             -19.513376  -19.687566  -19.863310  -20.040623  -20.219519  \n",
       "wc                 -0.417715   -0.421444   -0.425206   -0.429001   -0.432831  \n",
       "dt                  0.000000    0.000000    0.000000    0.000000    0.000000  \n",
       "taxes             -51.447003  -51.819219  -52.757257  -53.323648  -53.705920  \n",
       "unlev_net_income   95.544435   96.235692   97.977763   99.029631   99.739566  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dcf.loc['unlev_net_income']=dcf.loc['EBITA']+dcf.loc[\"taxes\"]\n",
    "dcf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</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",
       "    <tr>\n",
       "      <th>Row</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>EBITDA</th>\n",
       "      <td>94.875000</td>\n",
       "      <td>155.900000</td>\n",
       "      <td>160.100000</td>\n",
       "      <td>161.261360</td>\n",
       "      <td>162.433088</td>\n",
       "      <td>163.615275</td>\n",
       "      <td>164.808015</td>\n",
       "      <td>166.011402</td>\n",
       "      <td>167.225531</td>\n",
       "      <td>168.450499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBITA</th>\n",
       "      <td>82.692857</td>\n",
       "      <td>139.657143</td>\n",
       "      <td>143.857143</td>\n",
       "      <td>144.892637</td>\n",
       "      <td>145.937375</td>\n",
       "      <td>146.991438</td>\n",
       "      <td>148.054911</td>\n",
       "      <td>150.735021</td>\n",
       "      <td>152.353279</td>\n",
       "      <td>153.445487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Depr</th>\n",
       "      <td>12.182143</td>\n",
       "      <td>16.242857</td>\n",
       "      <td>16.242857</td>\n",
       "      <td>16.368723</td>\n",
       "      <td>16.495713</td>\n",
       "      <td>16.623836</td>\n",
       "      <td>16.753103</td>\n",
       "      <td>15.276381</td>\n",
       "      <td>14.872252</td>\n",
       "      <td>15.005012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>capex</th>\n",
       "      <td>-12.750000</td>\n",
       "      <td>-18.000000</td>\n",
       "      <td>-19.000000</td>\n",
       "      <td>-19.169607</td>\n",
       "      <td>-19.340728</td>\n",
       "      <td>-19.513376</td>\n",
       "      <td>-19.687566</td>\n",
       "      <td>-19.863310</td>\n",
       "      <td>-20.040623</td>\n",
       "      <td>-20.219519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wc</th>\n",
       "      <td>1.955512</td>\n",
       "      <td>-3.790204</td>\n",
       "      <td>-1.293488</td>\n",
       "      <td>-0.410356</td>\n",
       "      <td>-0.414019</td>\n",
       "      <td>-0.417715</td>\n",
       "      <td>-0.421444</td>\n",
       "      <td>-0.425206</td>\n",
       "      <td>-0.429001</td>\n",
       "      <td>-0.432831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dt</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>taxes</th>\n",
       "      <td>-28.942500</td>\n",
       "      <td>-48.880000</td>\n",
       "      <td>-50.350000</td>\n",
       "      <td>-50.712423</td>\n",
       "      <td>-51.078081</td>\n",
       "      <td>-51.447003</td>\n",
       "      <td>-51.819219</td>\n",
       "      <td>-52.757257</td>\n",
       "      <td>-53.323648</td>\n",
       "      <td>-53.705920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unlev_net_income</th>\n",
       "      <td>53.750357</td>\n",
       "      <td>90.777143</td>\n",
       "      <td>93.507143</td>\n",
       "      <td>94.180214</td>\n",
       "      <td>94.859294</td>\n",
       "      <td>95.544435</td>\n",
       "      <td>96.235692</td>\n",
       "      <td>97.977763</td>\n",
       "      <td>99.029631</td>\n",
       "      <td>99.739566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unlev_fcf</th>\n",
       "      <td>55.138012</td>\n",
       "      <td>85.229796</td>\n",
       "      <td>89.456512</td>\n",
       "      <td>90.968975</td>\n",
       "      <td>91.600260</td>\n",
       "      <td>92.237180</td>\n",
       "      <td>92.879786</td>\n",
       "      <td>92.965629</td>\n",
       "      <td>93.432259</td>\n",
       "      <td>94.092228</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       2013        2014        2015        2016        2017  \\\n",
       "Row                                                                           \n",
       "EBITDA            94.875000  155.900000  160.100000  161.261360  162.433088   \n",
       "EBITA             82.692857  139.657143  143.857143  144.892637  145.937375   \n",
       "Depr              12.182143   16.242857   16.242857   16.368723   16.495713   \n",
       "capex            -12.750000  -18.000000  -19.000000  -19.169607  -19.340728   \n",
       "wc                 1.955512   -3.790204   -1.293488   -0.410356   -0.414019   \n",
       "dt                 0.000000    0.000000    0.000000    0.000000    0.000000   \n",
       "taxes            -28.942500  -48.880000  -50.350000  -50.712423  -51.078081   \n",
       "unlev_net_income  53.750357   90.777143   93.507143   94.180214   94.859294   \n",
       "unlev_fcf         55.138012   85.229796   89.456512   90.968975   91.600260   \n",
       "\n",
       "                        2018        2019        2020        2021        2022  \n",
       "Row                                                                           \n",
       "EBITDA            163.615275  164.808015  166.011402  167.225531  168.450499  \n",
       "EBITA             146.991438  148.054911  150.735021  152.353279  153.445487  \n",
       "Depr               16.623836   16.753103   15.276381   14.872252   15.005012  \n",
       "capex             -19.513376  -19.687566  -19.863310  -20.040623  -20.219519  \n",
       "wc                 -0.417715   -0.421444   -0.425206   -0.429001   -0.432831  \n",
       "dt                  0.000000    0.000000    0.000000    0.000000    0.000000  \n",
       "taxes             -51.447003  -51.819219  -52.757257  -53.323648  -53.705920  \n",
       "unlev_net_income   95.544435   96.235692   97.977763   99.029631   99.739566  \n",
       "unlev_fcf          92.237180   92.879786   92.965629   93.432259   94.092228  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now to finanlly arrive at unlevered free cash flow\n",
    "dcf.loc['unlev_fcf']=dcf.loc['unlev_net_income']+dcf.loc[['Depr', 'capex', 'wc', 'dt']].sum()\n",
    "dcf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4) Arriving at the Terminal Value and DCF Valuation (using Exit Multiple and Terminal Growth methods)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "812.165438"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# DCF Valuation - Exit EBITDA Method. Assuming exit in 2017\n",
    "exit_EBITDA = dcf[\"2017\"].loc['EBITDA']*assumptions['terminal multiple']\n",
    "exit_EBITDA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2013     55.138012\n",
       "2014     85.229796\n",
       "2015     89.456512\n",
       "2016     90.968975\n",
       "2017    903.765698\n",
       "2018     92.237180\n",
       "2019     92.879786\n",
       "2020     92.965629\n",
       "2021     93.432259\n",
       "2022     94.092228\n",
       "Name: total_cash_EBITDA, dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dcf.loc['total_cash_EBITDA']=dcf.loc['unlev_fcf']\n",
    "dcf['2017'].loc['total_cash_EBITDA'] +=exit_EBITDA\n",
    "dcf.loc['total_cash_EBITDA']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "841.6595876111905"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "discounted_multiple = np.npv(assumptions[\"WACC\"],dcf[['2013','2014','2015','2016','2017']].loc['total_cash_EBITDA'])\n",
    "discounted_multiple"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "800.5066188289566"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# DCF Valuation - Terminal Growth Method. Assuming exit in 2017\n",
    "terminal_growth = dcf['2017'].loc['unlev_fcf']*(1+assumptions['terminal growth rate'])/(assumptions['WACC']-assumptions['terminal growth rate'])\n",
    "terminal_growth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2013     55.138012\n",
       "2014     85.229796\n",
       "2015     89.456512\n",
       "2016     90.968975\n",
       "2017    892.106879\n",
       "2018     92.237180\n",
       "2019     92.879786\n",
       "2020     92.965629\n",
       "2021     93.432259\n",
       "2022     94.092228\n",
       "Name: total_cash_growth, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dcf.loc['total_cash_growth']=dcf.loc['unlev_fcf']\n",
    "dcf['2017'].loc['total_cash_growth']+=terminal_growth\n",
    "dcf.loc['total_cash_growth']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "834.2501972551397"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "discounted_growth = np.npv(assumptions[\"WACC\"],dcf[['2013','2014','2015','2016','2017']].loc['total_cash_growth'])\n",
    "discounted_growth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "23.938666845222862"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Value Per Share:\n",
    "# Exit EBITDA Method\n",
    "value_ps_multiple = discounted_multiple / assumptions['shares outstanding']\n",
    "value_ps_multiple"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "23.727927337385584"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Terminal Growth Method\n",
    "value_ps_growth = discounted_growth / assumptions['shares outstanding']\n",
    "value_ps_growth"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5) Applying Sensitivity Analyses "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Currently pending, still figuring that out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sensitivity_analysis (value,x,y):\n",
    "    "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}