paper_lbo_model_example.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adapted from: http://www.streetofwalls.com/finance-training-courses/private-equity-training/paper-lbo-model-example/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Given LBO Parameters and Assumptions\n",
    "\n",
    "XYZ Private Equity Partners purchases ABC Target Company for 5.0x Forward 12 months (FTM) EBITDA at the end of Year 0.\n",
    "\n",
    "The debt-to-equity ratio for the LBO acquisition will be 60:40.\n",
    "\n",
    "Assume the weighted average interest rate on debt to be 10%.\n",
    "\n",
    "ABC expects to reach $100 million in sales revenue with an EBITDA margin of 40% in Year 1.\n",
    "\n",
    "Revenue is expected to increase by 10% year-over-year (y-o-y).\n",
    "\n",
    "EBITDA margins are expected to remain flat during the term of the investment.\n",
    "\n",
    "Capital expenditures are expected to equal 15% of sales each year.\n",
    "\n",
    "Operating working capital is expected to increase by $5 million each year.\n",
    "\n",
    "Depreciation is expected to equal $20 million each year.\n",
    "\n",
    "Assume a constant tax rate of 40%.\n",
    "\n",
    "XYZ exits the target investment after Year 5 at the same EBITDA multiple used at entry (5.0x FTM EBITDA).\n",
    "\n",
    "Assume all debt pay-down occurs at the moment of sale at the end of Year 5 (this eliminates the iterative/circular dependency between debt pay-down/cash balances and interest expense in a computer-based LBO model).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By using the instructions we have created a dictionary with Entry Assumptions using the information provided"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. CALCULATE THE PURCHASE PRICE OF ABC.\n",
    "\n",
    "Using a 5.0x entry multiple, calculate the price paid by multiplying by Year 1 EBITDA.\n",
    "$40 million in EBITDA (which represents a 40% EBITDA margin on $100 million in revenue) multiplied by 5. \n",
    "The purchase price is $200 million."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'entry_multiple': 5,\n",
       " 'Rev_Y1': 100,\n",
       " 'EBITDA_Y1_Margin': 0.4,\n",
       " 'debt_ratio': 0.6,\n",
       " 'equity_ratio': 0.4,\n",
       " 'EBITDA_Y1': 40.0,\n",
       " 'price_paid': 200.0}"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "entry_ass = {\n",
    "    \"entry_multiple\":5,\n",
    "    \"Rev_Y1\":100,\n",
    "    \"EBITDA_Y1_Margin\":.4,\n",
    "    \"debt_ratio\": .6,\n",
    "    \"equity_ratio\": .4,\n",
    "}\n",
    "entry_ass['EBITDA_Y1']=entry_ass['Rev_Y1']*entry_ass['EBITDA_Y1_Margin']\n",
    "entry_ass[\"price_paid\"]=entry_ass[\"entry_multiple\"]*entry_ass[\"EBITDA_Y1\"]\n",
    "entry_ass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. CALCULATE THE DEBT AND EQUITY FUNDING AMOUNTS USED FOR THE PURCHASE PRICE.\n",
    "\n",
    "The given information assumes debt to equity ratio of 60:40 for the purchase price.\n",
    "\n",
    "Debt portion = 60% × $200 million, or $120 million.\n",
    "\n",
    "Equity portion = 40% × $200 million, or $80 million."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'entry_multiple': 5,\n",
       " 'Rev_Y1': 100,\n",
       " 'EBITDA_Y1_Margin': 0.4,\n",
       " 'debt_ratio': 0.6,\n",
       " 'equity_ratio': 0.4,\n",
       " 'EBITDA_Y1': 40.0,\n",
       " 'price_paid': 200.0,\n",
       " 'debt_portion': 120.0,\n",
       " 'equity_portion': 80.0}"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "entry_ass[\"debt_portion\"]=entry_ass['debt_ratio']*entry_ass['price_paid']\n",
    "entry_ass['equity_portion']=entry_ass['equity_ratio']*entry_ass['price_paid']\n",
    "entry_ass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. BUILD THE INCOME STATEMENT."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Rev</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBITDA</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: D&amp;A</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBIT</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Interest</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBT</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Taxes</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Earnings</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  1    2    3    4    5    6\n",
       "Rev             NaN  NaN  NaN  NaN  NaN  NaN\n",
       "EBITDA          NaN  NaN  NaN  NaN  NaN  NaN\n",
       "less: D&A       NaN  NaN  NaN  NaN  NaN  NaN\n",
       "EBIT            NaN  NaN  NaN  NaN  NaN  NaN\n",
       "less: Interest  NaN  NaN  NaN  NaN  NaN  NaN\n",
       "EBT             NaN  NaN  NaN  NaN  NaN  NaN\n",
       "less: Taxes     NaN  NaN  NaN  NaN  NaN  NaN\n",
       "Earnings        NaN  NaN  NaN  NaN  NaN  NaN"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#(in Millions)\n",
    "years=[1,2,3,4,5,6]\n",
    "rows=[\"Rev\",\"EBITDA\",\"less: D&A\",\"EBIT\", \"less: Interest\",\"EBT\", \"less: Taxes\",\"Earnings\"]\n",
    "lbo_is = pd.DataFrame(data=None,index=rows,columns=years)\n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'rev_growth': 1.1,\n",
       " 'int_rate': 0.1,\n",
       " 'EBITDA_margin': 0.4,\n",
       " 'tax_rate': 0.4,\n",
       " 'capex % of rev': 0.15,\n",
       " 'change_in_NWC': 5}"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create a dictionary for Income Statement assumptions\n",
    "is_ass={\n",
    "    \"rev_growth\": 1.1,\n",
    "    \"int_rate\": .10,\n",
    "    \"EBITDA_margin\": entry_ass['EBITDA_Y1_Margin'],\n",
    "    'tax_rate':.4,\n",
    "    'capex % of rev':.15,\n",
    "    'change_in_NWC':5,\n",
    "}\n",
    "is_ass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Rev</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>121</td>\n",
       "      <td>133.1</td>\n",
       "      <td>146.41</td>\n",
       "      <td>161.051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBITDA</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: D&amp;A</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBIT</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Interest</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBT</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Taxes</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Earnings</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  1    2    3      4       5        6\n",
       "Rev             100  110  121  133.1  146.41  161.051\n",
       "EBITDA          NaN  NaN  NaN    NaN     NaN      NaN\n",
       "less: D&A       NaN  NaN  NaN    NaN     NaN      NaN\n",
       "EBIT            NaN  NaN  NaN    NaN     NaN      NaN\n",
       "less: Interest  NaN  NaN  NaN    NaN     NaN      NaN\n",
       "EBT             NaN  NaN  NaN    NaN     NaN      NaN\n",
       "less: Taxes     NaN  NaN  NaN    NaN     NaN      NaN\n",
       "Earnings        NaN  NaN  NaN    NaN     NaN      NaN"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Start with building out Revenue, assuming a 10% growth rate we will use a While loop to forecast\n",
    "lbo_is[1].loc['Rev']=entry_ass['Rev_Y1']\n",
    "i = 2\n",
    "while i <= len(lbo_is.loc['Rev']):\n",
    "    lbo_is[i].loc['Rev']=lbo_is[(i-1)].loc['Rev']*1.10\n",
    "    i+=1\n",
    "    \n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Rev</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>121</td>\n",
       "      <td>133.1</td>\n",
       "      <td>146.41</td>\n",
       "      <td>161.051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBITDA</th>\n",
       "      <td>40</td>\n",
       "      <td>44</td>\n",
       "      <td>48.4</td>\n",
       "      <td>53.24</td>\n",
       "      <td>58.564</td>\n",
       "      <td>64.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: D&amp;A</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBIT</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Interest</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBT</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Taxes</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Earnings</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  1    2     3      4       5        6\n",
       "Rev             100  110   121  133.1  146.41  161.051\n",
       "EBITDA           40   44  48.4  53.24  58.564  64.4204\n",
       "less: D&A       NaN  NaN   NaN    NaN     NaN      NaN\n",
       "EBIT            NaN  NaN   NaN    NaN     NaN      NaN\n",
       "less: Interest  NaN  NaN   NaN    NaN     NaN      NaN\n",
       "EBT             NaN  NaN   NaN    NaN     NaN      NaN\n",
       "less: Taxes     NaN  NaN   NaN    NaN     NaN      NaN\n",
       "Earnings        NaN  NaN   NaN    NaN     NaN      NaN"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Build out EBITDA using % EBITDA margin assumptions\n",
    "lbo_is.loc['EBITDA']=lbo_is.loc['Rev']*is_ass['EBITDA_margin']\n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Rev</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>121</td>\n",
       "      <td>133.1</td>\n",
       "      <td>146.41</td>\n",
       "      <td>161.051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBITDA</th>\n",
       "      <td>40</td>\n",
       "      <td>44</td>\n",
       "      <td>48.4</td>\n",
       "      <td>53.24</td>\n",
       "      <td>58.564</td>\n",
       "      <td>64.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: D&amp;A</th>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBIT</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Interest</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBT</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Taxes</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Earnings</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  1    2     3      4       5        6\n",
       "Rev             100  110   121  133.1  146.41  161.051\n",
       "EBITDA           40   44  48.4  53.24  58.564  64.4204\n",
       "less: D&A       -20  -20   -20    -20     -20      -20\n",
       "EBIT            NaN  NaN   NaN    NaN     NaN      NaN\n",
       "less: Interest  NaN  NaN   NaN    NaN     NaN      NaN\n",
       "EBT             NaN  NaN   NaN    NaN     NaN      NaN\n",
       "less: Taxes     NaN  NaN   NaN    NaN     NaN      NaN\n",
       "Earnings        NaN  NaN   NaN    NaN     NaN      NaN"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lbo_is.loc['less: D&A']=-20\n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Rev</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>121</td>\n",
       "      <td>133.1</td>\n",
       "      <td>146.41</td>\n",
       "      <td>161.051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBITDA</th>\n",
       "      <td>40</td>\n",
       "      <td>44</td>\n",
       "      <td>48.4</td>\n",
       "      <td>53.24</td>\n",
       "      <td>58.564</td>\n",
       "      <td>64.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: D&amp;A</th>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBIT</th>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>28.4</td>\n",
       "      <td>33.24</td>\n",
       "      <td>38.564</td>\n",
       "      <td>44.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Interest</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBT</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Taxes</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Earnings</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  1    2     3      4       5        6\n",
       "Rev             100  110   121  133.1  146.41  161.051\n",
       "EBITDA           40   44  48.4  53.24  58.564  64.4204\n",
       "less: D&A       -20  -20   -20    -20     -20      -20\n",
       "EBIT             20   24  28.4  33.24  38.564  44.4204\n",
       "less: Interest  NaN  NaN   NaN    NaN     NaN      NaN\n",
       "EBT             NaN  NaN   NaN    NaN     NaN      NaN\n",
       "less: Taxes     NaN  NaN   NaN    NaN     NaN      NaN\n",
       "Earnings        NaN  NaN   NaN    NaN     NaN      NaN"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lbo_is.loc['EBIT']=lbo_is.loc['EBITDA']+lbo_is.loc['less: D&A']\n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Rev</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>121</td>\n",
       "      <td>133.1</td>\n",
       "      <td>146.41</td>\n",
       "      <td>161.051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBITDA</th>\n",
       "      <td>40</td>\n",
       "      <td>44</td>\n",
       "      <td>48.4</td>\n",
       "      <td>53.24</td>\n",
       "      <td>58.564</td>\n",
       "      <td>64.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: D&amp;A</th>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBIT</th>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>28.4</td>\n",
       "      <td>33.24</td>\n",
       "      <td>38.564</td>\n",
       "      <td>44.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Interest</th>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBT</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Taxes</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Earnings</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  1    2     3      4       5        6\n",
       "Rev             100  110   121  133.1  146.41  161.051\n",
       "EBITDA           40   44  48.4  53.24  58.564  64.4204\n",
       "less: D&A       -20  -20   -20    -20     -20      -20\n",
       "EBIT             20   24  28.4  33.24  38.564  44.4204\n",
       "less: Interest  -12  -12   -12    -12     -12      -12\n",
       "EBT             NaN  NaN   NaN    NaN     NaN      NaN\n",
       "less: Taxes     NaN  NaN   NaN    NaN     NaN      NaN\n",
       "Earnings        NaN  NaN   NaN    NaN     NaN      NaN"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lbo_is.loc['less: Interest']=-is_ass['int_rate']*entry_ass['debt_portion']\n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Rev</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>121</td>\n",
       "      <td>133.1</td>\n",
       "      <td>146.41</td>\n",
       "      <td>161.051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBITDA</th>\n",
       "      <td>40</td>\n",
       "      <td>44</td>\n",
       "      <td>48.4</td>\n",
       "      <td>53.24</td>\n",
       "      <td>58.564</td>\n",
       "      <td>64.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: D&amp;A</th>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBIT</th>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>28.4</td>\n",
       "      <td>33.24</td>\n",
       "      <td>38.564</td>\n",
       "      <td>44.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Interest</th>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBT</th>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>16.4</td>\n",
       "      <td>21.24</td>\n",
       "      <td>26.564</td>\n",
       "      <td>32.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Taxes</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Earnings</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  1    2     3      4       5        6\n",
       "Rev             100  110   121  133.1  146.41  161.051\n",
       "EBITDA           40   44  48.4  53.24  58.564  64.4204\n",
       "less: D&A       -20  -20   -20    -20     -20      -20\n",
       "EBIT             20   24  28.4  33.24  38.564  44.4204\n",
       "less: Interest  -12  -12   -12    -12     -12      -12\n",
       "EBT               8   12  16.4  21.24  26.564  32.4204\n",
       "less: Taxes     NaN  NaN   NaN    NaN     NaN      NaN\n",
       "Earnings        NaN  NaN   NaN    NaN     NaN      NaN"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lbo_is.loc['EBT']=lbo_is.loc['EBIT']+lbo_is.loc['less: Interest']\n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Rev</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>121</td>\n",
       "      <td>133.1</td>\n",
       "      <td>146.41</td>\n",
       "      <td>161.051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBITDA</th>\n",
       "      <td>40</td>\n",
       "      <td>44</td>\n",
       "      <td>48.4</td>\n",
       "      <td>53.24</td>\n",
       "      <td>58.564</td>\n",
       "      <td>64.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: D&amp;A</th>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBIT</th>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>28.4</td>\n",
       "      <td>33.24</td>\n",
       "      <td>38.564</td>\n",
       "      <td>44.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Interest</th>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBT</th>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>16.4</td>\n",
       "      <td>21.24</td>\n",
       "      <td>26.564</td>\n",
       "      <td>32.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Taxes</th>\n",
       "      <td>-3.2</td>\n",
       "      <td>-4.8</td>\n",
       "      <td>-6.56</td>\n",
       "      <td>-8.496</td>\n",
       "      <td>-10.6256</td>\n",
       "      <td>-12.9682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Earnings</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  1    2     3      4        5        6\n",
       "Rev             100  110   121  133.1   146.41  161.051\n",
       "EBITDA           40   44  48.4  53.24   58.564  64.4204\n",
       "less: D&A       -20  -20   -20    -20      -20      -20\n",
       "EBIT             20   24  28.4  33.24   38.564  44.4204\n",
       "less: Interest  -12  -12   -12    -12      -12      -12\n",
       "EBT               8   12  16.4  21.24   26.564  32.4204\n",
       "less: Taxes    -3.2 -4.8 -6.56 -8.496 -10.6256 -12.9682\n",
       "Earnings        NaN  NaN   NaN    NaN      NaN      NaN"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lbo_is.loc['less: Taxes']=lbo_is.loc['EBT']*-is_ass['tax_rate']\n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Rev</th>\n",
       "      <td>100</td>\n",
       "      <td>110</td>\n",
       "      <td>121</td>\n",
       "      <td>133.1</td>\n",
       "      <td>146.41</td>\n",
       "      <td>161.051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBITDA</th>\n",
       "      <td>40</td>\n",
       "      <td>44</td>\n",
       "      <td>48.4</td>\n",
       "      <td>53.24</td>\n",
       "      <td>58.564</td>\n",
       "      <td>64.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: D&amp;A</th>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "      <td>-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBIT</th>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>28.4</td>\n",
       "      <td>33.24</td>\n",
       "      <td>38.564</td>\n",
       "      <td>44.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Interest</th>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "      <td>-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBT</th>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>16.4</td>\n",
       "      <td>21.24</td>\n",
       "      <td>26.564</td>\n",
       "      <td>32.4204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: Taxes</th>\n",
       "      <td>-3.2</td>\n",
       "      <td>-4.8</td>\n",
       "      <td>-6.56</td>\n",
       "      <td>-8.496</td>\n",
       "      <td>-10.6256</td>\n",
       "      <td>-12.9682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Earnings</th>\n",
       "      <td>4.8</td>\n",
       "      <td>7.2</td>\n",
       "      <td>9.84</td>\n",
       "      <td>12.744</td>\n",
       "      <td>15.9384</td>\n",
       "      <td>19.4522</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  1    2     3       4        5        6\n",
       "Rev             100  110   121   133.1   146.41  161.051\n",
       "EBITDA           40   44  48.4   53.24   58.564  64.4204\n",
       "less: D&A       -20  -20   -20     -20      -20      -20\n",
       "EBIT             20   24  28.4   33.24   38.564  44.4204\n",
       "less: Interest  -12  -12   -12     -12      -12      -12\n",
       "EBT               8   12  16.4   21.24   26.564  32.4204\n",
       "less: Taxes    -3.2 -4.8 -6.56  -8.496 -10.6256 -12.9682\n",
       "Earnings        4.8  7.2  9.84  12.744  15.9384  19.4522"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lbo_is.loc['Earnings']=lbo_is.loc['EBT']+lbo_is.loc['less: Taxes']\n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. CALCULATE CUMULATIVE LEVERED FREE CASH FLOW (FCF)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Earnings</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>plus: D&amp;A</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: capex</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: NWC</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FCF</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               1    2    3    4    5    6\n",
       "Earnings     NaN  NaN  NaN  NaN  NaN  NaN\n",
       "plus: D&A    NaN  NaN  NaN  NaN  NaN  NaN\n",
       "less: capex  NaN  NaN  NaN  NaN  NaN  NaN\n",
       "less: NWC    NaN  NaN  NaN  NaN  NaN  NaN\n",
       "FCF          NaN  NaN  NaN  NaN  NaN  NaN"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lbo_fcf=pd.DataFrame(data=None, index=[\"Earnings\", \"plus: D&A\", \"less: capex\", \"less: NWC\", \"FCF\"], columns=years)\n",
    "lbo_fcf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "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>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Earnings</th>\n",
       "      <td>4.8</td>\n",
       "      <td>7.2</td>\n",
       "      <td>9.84</td>\n",
       "      <td>12.744</td>\n",
       "      <td>15.9384</td>\n",
       "      <td>19.4522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>plus: D&amp;A</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: capex</th>\n",
       "      <td>-15</td>\n",
       "      <td>-16.5</td>\n",
       "      <td>-18.15</td>\n",
       "      <td>-19.965</td>\n",
       "      <td>-21.9615</td>\n",
       "      <td>-24.1577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>less: NWC</th>\n",
       "      <td>-5</td>\n",
       "      <td>-5</td>\n",
       "      <td>-5</td>\n",
       "      <td>-5</td>\n",
       "      <td>-5</td>\n",
       "      <td>-5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FCF</th>\n",
       "      <td>4.8</td>\n",
       "      <td>5.7</td>\n",
       "      <td>6.69</td>\n",
       "      <td>7.779</td>\n",
       "      <td>8.9769</td>\n",
       "      <td>10.2946</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               1     2      3       4        5        6\n",
       "Earnings     4.8   7.2   9.84  12.744  15.9384  19.4522\n",
       "plus: D&A     20    20     20      20       20       20\n",
       "less: capex  -15 -16.5 -18.15 -19.965 -21.9615 -24.1577\n",
       "less: NWC     -5    -5     -5      -5       -5       -5\n",
       "FCF          4.8   5.7   6.69   7.779   8.9769  10.2946"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lbo_fcf.loc['Earnings']=lbo_is.loc['Earnings']\n",
    "lbo_fcf.loc['plus: D&A']=-lbo_is.loc['less: D&A']\n",
    "lbo_fcf.loc['less: capex']=lbo_is.loc['Rev']*-is_ass['capex % of rev']\n",
    "lbo_fcf.loc['less: NWC']=-is_ass['change_in_NWC']\n",
    "lbo_fcf.loc['FCF']=lbo_fcf.loc[['Earnings','plus: D&A','less: capex', 'less: NWC']].sum()\n",
    "lbo_fcf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "33.94590000000002"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cumulative_fcf=lbo_fcf[[1,2,3,4,5]].loc['FCF'].sum()\n",
    "cumulative_fcf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5. CALCULATE ENDING PURCHASE PRICE (EXIT VALUE) AND RETURNS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'exit_EBITDA': 64.42040000000004,\n",
       " 'exit_multiple': 5,\n",
       " 'beginning_debt': 120.0,\n",
       " 'cumulative_FCF': 33.94590000000002}"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exit_returns={\n",
    "    \"exit_EBITDA\":lbo_is[6].loc['EBITDA'],\n",
    "    'exit_multiple':entry_ass['entry_multiple'],\n",
    "    'beginning_debt':entry_ass['debt_portion'],\n",
    "    'cumulative_FCF':cumulative_fcf,\n",
    "}\n",
    "exit_returns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'exit_EBITDA': 64.42040000000004,\n",
       " 'exit_multiple': 5,\n",
       " 'beginning_debt': 120.0,\n",
       " 'cumulative_FCF': 33.94590000000002,\n",
       " 'exit_TEV': 322.1020000000002,\n",
       " 'ending_debt': 86.05409999999998,\n",
       " 'ending_equity': 236.04790000000023,\n",
       " 'MOIC': 2.950598750000003}"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exit_returns['exit_TEV']= exit_returns['exit_EBITDA']*exit_returns['exit_multiple']\n",
    "exit_returns['ending_debt']= exit_returns['beginning_debt']-exit_returns['cumulative_FCF']\n",
    "exit_returns['ending_equity']=exit_returns['exit_TEV']-exit_returns['ending_debt']\n",
    "exit_returns['MOIC']=exit_returns['ending_equity']/entry_ass['equity_portion']\n",
    "exit_returns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}