LBO MODEL EXAMPLE.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "182e46cc",
   "metadata": {},
   "source": [
    "## Paper LBO Model Example"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25a1e187",
   "metadata": {},
   "source": [
    "When interviewing for a junior private equity position, a candidate must prepare for in-office modeling tests \n",
    "on potential private equity investment opportunities—especially LBO scenarios. Hence, the model serves to help \n",
    "those candidates preparing for an interview or planning to join PE World. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "192c53c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "Source: http://www.streetofwalls.com/finance-training-courses/private-equity-training/paper-lbo-model-example/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ba48b60",
   "metadata": {},
   "source": [
    "## Parameters and Assumptions to build the LBO Model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be2aaa6a",
   "metadata": {},
   "source": [
    "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",
    "\n",
    "By using the instructions we have created a dictionary with Entry Assumptions using the information provided"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7abee7f",
   "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "761c5c52",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libs\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "949b713d",
   "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": 2,
     "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",
   "id": "5138409e",
   "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",
    "    Debt portion = 60% × $200 million, or $120 million.\n",
    "    Equity portion = 40% × $200 million, or $80 million."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c56da958",
   "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": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# getting the Equity and Debt portion of the total price amount\n",
    "\n",
    "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",
   "id": "7c4a0aab",
   "metadata": {},
   "source": [
    "### 3. BUILD THE INCOME STATEMENT\n",
    "\n",
    "     (Notice that, because the exit value at the end of Year 5 will be based on a forward EBITDA multiple,\n",
    "      we must calculate six year’s worth of income statement, not 5. Also note that the numbers might not \n",
    "      agree perfectly because of rounding. It is reasonable to round your intermediate calculations to the \n",
    "      nearest integer in carrying over calculations to the next step.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9185e5bb",
   "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": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create the DataFrame for the income statement: All figures are in Millionss\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": 5,
   "id": "cfbfdaee",
   "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": 5,
     "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": 6,
   "id": "12207167",
   "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.0</td>\n",
       "      <td>121.0</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.0  121.0  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": 6,
     "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",
    "\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": 7,
   "id": "831dace8",
   "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.0</td>\n",
       "      <td>121.0</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.0</td>\n",
       "      <td>44.0</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.0  121.0  133.1  146.41  161.051\n",
       "EBITDA          40.0   44.0   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": 7,
     "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": 8,
   "id": "e9ea7809",
   "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.0</td>\n",
       "      <td>121.0</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.0</td>\n",
       "      <td>44.0</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.0  121.0  133.1  146.41  161.051\n",
       "EBITDA          40.0   44.0   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": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Setting Depreciation & Amortization\n",
    "lbo_is.loc['less: D&A']=-20\n",
    "lbo_is "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "41ead675",
   "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.0</td>\n",
       "      <td>121.0</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.0</td>\n",
       "      <td>44.0</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.0</td>\n",
       "      <td>24.0</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.0  121.0  133.1  146.41  161.051\n",
       "EBITDA          40.0   44.0   48.4  53.24  58.564  64.4204\n",
       "less: D&A        -20    -20    -20    -20     -20      -20\n",
       "EBIT            20.0   24.0   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": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Subtract Depreciation & Amortization (D&A) to get EBIT.\n",
    "lbo_is.loc['EBIT']=lbo_is.loc['EBITDA']+lbo_is.loc['less: D&A']\n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e1baf098",
   "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.0</td>\n",
       "      <td>121.0</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.0</td>\n",
       "      <td>44.0</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.0</td>\n",
       "      <td>24.0</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.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</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.0  121.0  133.1  146.41  161.051\n",
       "EBITDA          40.0   44.0   48.4  53.24  58.564  64.4204\n",
       "less: D&A        -20    -20    -20    -20     -20      -20\n",
       "EBIT            20.0   24.0   28.4  33.24  38.564  44.4204\n",
       "less: Interest -12.0  -12.0  -12.0  -12.0   -12.0    -12.0\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": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate interest expense using the debt amount portion used for purchase multiplied \n",
    "# by the interest rate to calculate the yearly interest expense line item.\n",
    "\n",
    "lbo_is.loc['less: Interest']=-is_ass['int_rate']*entry_ass['debt_portion']\n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e2dc5375",
   "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.0</td>\n",
       "      <td>121.0</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.0</td>\n",
       "      <td>44.0</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.0</td>\n",
       "      <td>24.0</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.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBT</th>\n",
       "      <td>8.0</td>\n",
       "      <td>12.0</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.0  121.0  133.1  146.41  161.051\n",
       "EBITDA          40.0   44.0   48.4  53.24  58.564  64.4204\n",
       "less: D&A        -20    -20    -20    -20     -20      -20\n",
       "EBIT            20.0   24.0   28.4  33.24  38.564  44.4204\n",
       "less: Interest -12.0  -12.0  -12.0  -12.0   -12.0    -12.0\n",
       "EBT              8.0   12.0   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": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate Earnings Before Tax (EBT).\n",
    "\n",
    "lbo_is.loc['EBT']=lbo_is.loc['EBIT']+lbo_is.loc['less: Interest']\n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c5eb637d",
   "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.0</td>\n",
       "      <td>121.0</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.0</td>\n",
       "      <td>44.0</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.0</td>\n",
       "      <td>24.0</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.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBT</th>\n",
       "      <td>8.0</td>\n",
       "      <td>12.0</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.96816</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.0  121.0  133.1   146.41   161.051\n",
       "EBITDA          40.0   44.0   48.4  53.24   58.564   64.4204\n",
       "less: D&A        -20    -20    -20    -20      -20       -20\n",
       "EBIT            20.0   24.0   28.4  33.24   38.564   44.4204\n",
       "less: Interest -12.0  -12.0  -12.0  -12.0    -12.0     -12.0\n",
       "EBT              8.0   12.0   16.4  21.24   26.564   32.4204\n",
       "less: Taxes     -3.2   -4.8  -6.56 -8.496 -10.6256 -12.96816\n",
       "Earnings         NaN    NaN    NaN    NaN      NaN       NaN"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Subtract taxes using the tax rate to get to tax-effected EBT (a proxy for Net Income)\n",
    "\n",
    "lbo_is.loc['less: Taxes']=lbo_is.loc['EBT']*-is_ass['tax_rate']\n",
    "lbo_is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b229983c",
   "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.0</td>\n",
       "      <td>121.0</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.0</td>\n",
       "      <td>44.0</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.0</td>\n",
       "      <td>24.0</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.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>-12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EBT</th>\n",
       "      <td>8.0</td>\n",
       "      <td>12.0</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.96816</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.45224</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   1      2      3       4        5         6\n",
       "Rev              100  110.0  121.0   133.1   146.41   161.051\n",
       "EBITDA          40.0   44.0   48.4   53.24   58.564   64.4204\n",
       "less: D&A        -20    -20    -20     -20      -20       -20\n",
       "EBIT            20.0   24.0   28.4   33.24   38.564   44.4204\n",
       "less: Interest -12.0  -12.0  -12.0   -12.0    -12.0     -12.0\n",
       "EBT              8.0   12.0   16.4   21.24   26.564   32.4204\n",
       "less: Taxes     -3.2   -4.8  -6.56  -8.496 -10.6256 -12.96816\n",
       "Earnings         4.8    7.2   9.84  12.744  15.9384  19.45224"
      ]
     },
     "execution_count": 13,
     "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",
   "id": "ca585775",
   "metadata": {},
   "source": [
    "### 4. CALCULATE CUMULATIVE LEVERED FREE CASH FLOW (FCF)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "53fc30c4",
   "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": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create the DataFrame for the Free Cash Flow\n",
    "\n",
    "lbo_fcf=pd.DataFrame(data=None, index=[\"Earnings\", \"plus: D&A\", \"less: capex\", \"less: NWC\", \"FCF\"], columns=years)\n",
    "lbo_fcf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e752b89",
   "metadata": {},
   "source": [
    "#### Preparing Free Cash Flow\n",
    "    \n",
    "    (NOTE: We do not need Year 6 capital expenditures, or Free Cash Flow for that matter, \n",
    "     because EBITDA does not incorporate capex and because only FCF in Years 1-5 can be used to pay down debt.)\n",
    "    \n",
    "     Calcs step:\n",
    "\n",
    "     1. Start with EBT (Tax-effected) and then add back non-cash expenses (D&A).\n",
    "     2. Add D&A to EBT\n",
    "     3. Subtract capital expenditures (Capex).\n",
    "     4. Subtract the annual increase in operating working capital to get to Free Cash Flow (FCF).\n",
    "     5. Calculate Cumulative Free Cash Flow during the life of the LBO by adding FCF year-to-year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2f92c8e7",
   "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.45224</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.0</td>\n",
       "      <td>-16.5</td>\n",
       "      <td>-18.15</td>\n",
       "      <td>-19.965</td>\n",
       "      <td>-21.9615</td>\n",
       "      <td>-24.15765</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.29459</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.45224\n",
       "plus: D&A      20    20     20      20       20        20\n",
       "less: capex -15.0 -16.5 -18.15 -19.965 -21.9615 -24.15765\n",
       "less: NWC      -5    -5     -5      -5       -5        -5\n",
       "FCF           4.8   5.7   6.69   7.779   8.9769  10.29459"
      ]
     },
     "execution_count": 15,
     "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": 16,
   "id": "c70c09b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "33.94590000000002"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate Cumulative Free Cash Flow during the life of the LBO by adding FCF year-to-year\n",
    "\n",
    "cumulative_fcf=lbo_fcf[[1,2,3,4,5]].loc['FCF'].sum()\n",
    "cumulative_fcf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc55eba8",
   "metadata": {},
   "source": [
    "### CALCULATE ENDING PURCHASE PRICE (EXIT VALUE) AND RETURNS\n",
    "\n",
    "  Cals steps:\n",
    "        \n",
    "        1. Calculate Total Enterprise Value (TEV) at Exit: Take Forward EBITDA at exit (Year 6 EBITDA) along \n",
    "           with a 5.0x exit multiple to calculate Exit TEV.\n",
    "        2. Calculate Net Debt at Exit (also known as Ending Debt): \n",
    "           Beginning Debt – Debt Pay-down = Ending Debt.\n",
    "           Beginning Debt – Cumulative FCF = Ending Debt\n",
    "            \n",
    "        3. Calculate ending Equity Value (EV) by subtracting Ending Debt from Exit TEV.\n",
    "        4. Calculate the Multiple-of-Money (MoM) EV return (Ending EV ÷ Beginning EV)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "7809aff6",
   "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": 17,
     "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": 18,
   "id": "24187c87",
   "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",
       " 'MoM': 2.950598750000003}"
      ]
     },
     "execution_count": 18,
     "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['MoM']=exit_returns['ending_equity']/entry_ass['equity_portion']\n",
    "exit_returns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5e4abd3",
   "metadata": {},
   "source": [
    "### Estimate IRR based on the MoM multiple.\n",
    "\n",
    "    The following table is useful for estimating IRR based upon 5-year MoM multiples:\n",
    "    2.0x MoM over 5 years ~15% IRR\n",
    "    2.5x MoM over 5 years ~20% IRR\n",
    "    3.0x MoM over 5 years ~25% IRR\n",
    "    3.7x MoM over 5 years ~30% IRR\n",
    "    \n",
    "    Therefore, we can assume that the implied IRR for the paper LBO case study is approximately 25%, or slightly below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d413d2f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}