Finance beginner - Dividend Discount Model.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# To support both python 2 and python 3\n",
    "from __future__ import division, print_function, unicode_literals\n",
    "\n",
    "# Common imports\n",
    "import numpy as np\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "# to make this notebook's output stable across runs\n",
    "np.random.seed(42)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Introduction to Finance\n",
    "\n",

    "\n",
    "I have created finance 101 tutorials to help folks learn finance and learn to code.  These tutorials will provide a brief overview of finance prior jumping into the data. The 101s are optional, but highly recommended for beginners.\n",
    "The introduction to finance 101's are designed for two audiences in mind.\n",
    "\n",
    "1) For those new to finance\n",
    "  +  I will teach you finance, python, and give you the confidence to navigate the age of Big Data.\n",
    "  \n",
    "2) For those currently working in finance\n",
    "  +  I will teach you Python basics so that you can graduate from Excel spreadsheets to the Python notebooks!\n",
    "\n",
    "-------------------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Welcome to Finance 101: Financial Statements and Company Valuation (Dividend Discount Model)\n",
    "\n",
    "We will cover a lot of material in this 101, so grab a coffee and let's start digging in!\n",
    "Topics include:\n",
    "  +  Financial Statement Overview\n",
    "  +  Company Valuation Overview\n",
    "  +  Company Valuation Examples\n",
    "  +  Python Script and Tips\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Financial Statement Overview\n",
    "\n",
    "First, let's define some of the basics by giving an overview of financial statements. We will be evaluating the machinery and equipment maker, Caterpillar (CAT), financials. I picked CAT because it's one of my favorite companies and it used to be headquartered about 30 minutes from my hometown.\n",
    "\n",
    "**Income Statement:** The income statement is a summary of a business's performance over a period of time. An income statement contains revenue and expenses. It ultimately answers the question did the company make money over the specified time period. You can think of an income statement like your checking account. For example, over the last 3 months was your income higher than your expenses?\n",
    "\n",
    "**Balance Sheet:** A balance sheet is a snapshot in time of a company's health at a given point in time that separates out assets from liabilities. An asset is anything of value such as a piece of property or a piece of machinery. In personal finance, an asset would be anything that you own outright such as a car or any investments that you may have such as 401k retirement savings. A liability is a debt that is owed by the company to another party. In personal finance, a liability would be short term credit card debt or a mortgage. The balance sheet also illustrates how a company finances its operations with debt or equity. We will discuss capital structure in another session.\n",
    "\n",
    "\n",
    "-------------------------------------------------------------------------------------------------------------------------------\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Company Valuation Overview\n",
    "\n",
    "Ok, now that we have a basic understanding of how a company organizes its finances, let's jump into how companies are valued.\n",
    "\n",
    "There are two primary ways to evaluate a company:  discounted cash flow models (DCF) and models of relative valuation (1).  DCF models determine the value of a company based on its fundamentals such as information from the financial statements.  Relative valuation models use ratios such as Price-to-Earnings (P/E) ratio to determine valuation.  We will be focusing on a specific DCF model called the Dividend Discount Model (DDM) in this tutorial because of its simplicity and ease of use.  \n",
    "\n",
    "DDM is a great place to start when calculating company value because it illustrates the basic principles of company valuation.  This model assumes that \"A share of a company is worth the present value of all the cash flows an investor expects to pocket from it.\" (1)  In a very simplistic world, this is like how a bond is valued.  An investor buys a bond for an expected yield (i.e. 3%) and the bond price is determined according to this yield.  In terms of stocks, a dividend is like a bond yield because it is the cash that an investor receives from the investment.   \n",
    "\n",
    "We will discuss two examples of the DDM calculation in this tutorial.  First, we will review the simplest form of DDM which defines a company's value based on its dividend (D) divided by a required return on equity (R). Next, we will modify that formula to include the growth of the dividend in the calculation.\n",
    "\n",
    "Example 1 DDM Assumptions\n",
    "\n",
    "  +  Investor holds stock forever\n",
    "  +  Dividend does not grow\n",
    "  +  Calculation: Value = D / R \n",
    "    +    D = Dividend\n",
    "    +    R = Return on Equity\n",
    "       \n",
    "Example 2 DDM Assumptions\n",
    "  +  Investor holds stock forever\n",
    "  +  Dividend grows in line with company history\n",
    "  + Calculation: Value = (D*1+g) / (R - g)\n",
    "    + D = Dividend\n",
    "    + R = Return on Equity\n",
    "    + g = Dividend Growth Rate\n",
    " \n",
    "\n",
    "In our example, we will assume the investor holds the stock forever.  However, in the real world this is probably not very accurate because investors change investments all the time.  Additionally, the underlying principle, (i.e. bond and stock investment) can change and the investor isn't guaranteed to receive their original investment back and companies can cut the dividend so it is not guaranteed.  Either way, the fundamentals of the dividend discount model hold true throughout all valuation models.  More specifically, a company's value is based on future cash flows given to investors.  As we will see with the next financial 101 tutorials, the art of company valuation is replacing the dividend with a better estimate of cash flows.\n",
    "\n",
    "-------------------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Company Valuation Examples\n",
    "\n",
    "Next, we are going to execute some basic python code where we will review the Income Statement. This is the only financial statement that we need to calculate the DDM. We will be using the pandas library to import the CSV of the CAT Income Statement into a pandas dataframe. A dataframe allows us to have multiple rows and columns and is similar to looking at raw data from a CSV in Excel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "is_df = pd.read_csv(\"CAT_IS.csv\") # Source: (2)\n",
    "is_df.set_index(\"Account\", inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next let's take a look at the Income Statement that we imported."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TY2016</th>\n",
       "      <th>TY2015</th>\n",
       "      <th>TY2014</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Account</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Sales of Machinery, Energy &amp; Transportation</th>\n",
       "      <td>35773.00</td>\n",
       "      <td>44147.00</td>\n",
       "      <td>52142.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Revenues of Financial Products</th>\n",
       "      <td>2764.00</td>\n",
       "      <td>2864.00</td>\n",
       "      <td>3042.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total sales and revenues</th>\n",
       "      <td>38537.00</td>\n",
       "      <td>47011.00</td>\n",
       "      <td>55184.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cost of goods sold</th>\n",
       "      <td>28309.00</td>\n",
       "      <td>33546.00</td>\n",
       "      <td>40718.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Selling, general and administrative expenses</th>\n",
       "      <td>4686.00</td>\n",
       "      <td>4951.00</td>\n",
       "      <td>6529.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Research and development expenses</th>\n",
       "      <td>1951.00</td>\n",
       "      <td>2119.00</td>\n",
       "      <td>2380.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Interest expense of Financial Products</th>\n",
       "      <td>596.00</td>\n",
       "      <td>587.00</td>\n",
       "      <td>624.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Goodwill impairment charge</th>\n",
       "      <td>595.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other operating (income) expenses</th>\n",
       "      <td>1902.00</td>\n",
       "      <td>2023.00</td>\n",
       "      <td>1619.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total operating costs</th>\n",
       "      <td>38039.00</td>\n",
       "      <td>43226.00</td>\n",
       "      <td>51870.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Operating profit</th>\n",
       "      <td>498.00</td>\n",
       "      <td>3785.00</td>\n",
       "      <td>3314.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Interest expense excluding Financial Products</th>\n",
       "      <td>505.00</td>\n",
       "      <td>507.00</td>\n",
       "      <td>484.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other income (expense)</th>\n",
       "      <td>146.00</td>\n",
       "      <td>161.00</td>\n",
       "      <td>322.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Consolidated profit before taxes</th>\n",
       "      <td>139.00</td>\n",
       "      <td>3439.00</td>\n",
       "      <td>3152.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Provision (benefit) for income taxes</th>\n",
       "      <td>192.00</td>\n",
       "      <td>916.00</td>\n",
       "      <td>692.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Profit (loss) of consolidated companies</th>\n",
       "      <td>-53.00</td>\n",
       "      <td>2523.00</td>\n",
       "      <td>2460.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Equity in profit (loss) of unconsolidated affiliated companies</th>\n",
       "      <td>-6.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Net Income</th>\n",
       "      <td>-59.00</td>\n",
       "      <td>2523.00</td>\n",
       "      <td>2468.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Basic Shares Outstanding (millions)</th>\n",
       "      <td>584.30</td>\n",
       "      <td>594.30</td>\n",
       "      <td>617.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dividend per Share</th>\n",
       "      <td>3.08</td>\n",
       "      <td>3.01</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      TY2016    TY2015  \\\n",
       "Account                                                                  \n",
       "Sales of Machinery, Energy & Transportation         35773.00  44147.00   \n",
       "Revenues of Financial Products                       2764.00   2864.00   \n",
       "Total sales and revenues                            38537.00  47011.00   \n",
       "Cost of goods sold                                  28309.00  33546.00   \n",
       "Selling, general and administrative expenses         4686.00   4951.00   \n",
       "Research and development expenses                    1951.00   2119.00   \n",
       "Interest expense of Financial Products                596.00    587.00   \n",
       "Goodwill impairment charge                            595.00      0.00   \n",
       "Other operating (income) expenses                    1902.00   2023.00   \n",
       "Total operating costs                               38039.00  43226.00   \n",
       "Operating profit                                      498.00   3785.00   \n",
       "Interest expense excluding Financial Products         505.00    507.00   \n",
       "Other income (expense)                                146.00    161.00   \n",
       "Consolidated profit before taxes                      139.00   3439.00   \n",
       "Provision (benefit) for income taxes                  192.00    916.00   \n",
       "Profit (loss) of consolidated companies               -53.00   2523.00   \n",
       "Equity in profit (loss) of unconsolidated affil...     -6.00      0.00   \n",
       "Net Income                                            -59.00   2523.00   \n",
       "Basic Shares Outstanding (millions)                   584.30    594.30   \n",
       "Dividend per Share                                      3.08      3.01   \n",
       "\n",
       "                                                     TY2014  \n",
       "Account                                                      \n",
       "Sales of Machinery, Energy & Transportation         52142.0  \n",
       "Revenues of Financial Products                       3042.0  \n",
       "Total sales and revenues                            55184.0  \n",
       "Cost of goods sold                                  40718.0  \n",
       "Selling, general and administrative expenses         6529.0  \n",
       "Research and development expenses                    2380.0  \n",
       "Interest expense of Financial Products                624.0  \n",
       "Goodwill impairment charge                              0.0  \n",
       "Other operating (income) expenses                    1619.0  \n",
       "Total operating costs                               51870.0  \n",
       "Operating profit                                     3314.0  \n",
       "Interest expense excluding Financial Products         484.0  \n",
       "Other income (expense)                                322.0  \n",
       "Consolidated profit before taxes                     3152.0  \n",
       "Provision (benefit) for income taxes                  692.0  \n",
       "Profit (loss) of consolidated companies              2460.0  \n",
       "Equity in profit (loss) of unconsolidated affil...      8.0  \n",
       "Net Income                                           2468.0  \n",
       "Basic Shares Outstanding (millions)                   617.2  \n",
       "Dividend per Share                                      2.7  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "is_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Perfect the dataframe looks great and now we just need to use row 19 to get the most current dividend for our calculation of DDM.\n",
    "\n",
    "One option would be to create a variable as follows:\n",
    "\n",
    "D2016 = 3.08\n",
    "\n",
    "This is equivalent to hardcoding in Excel and is bad etiquette for python as well.  In python, it's much better to take a variable from the value in the dataframe.  We can create a dividend variable from the python database below by slicing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "D2016 = is_df.loc[\"Dividend per Share\",\"TY2016\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3.0800000000000001"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "D2016"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, great.  This worked and we now have a dividend value for 2016 that we will use for our DDM valuation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.09445\n"
     ]
    }
   ],
   "source": [
    "# In this section, I just document where I received the information from the calculate R.\n",
    "# Remember consider this as given for now.\n",
    "\n",
    "Beta = 1.29 #(3) \n",
    "Market_Risk_Prem =0.055 #(1) Chapter 7\n",
    "RF = 0.0235 #(4) 10 year treasury rate\n",
    "\n",
    "R = RF + Beta * Market_Risk_Prem\n",
    "print (R)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have the Dividend and Rate of Return.  Let's calculate the share price for DDM the formula for DDM Example 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "32.6098464796\n"
     ]
    }
   ],
   "source": [
    "DDM_V1 = D2016 / R\n",
    "print (DDM_V1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This valuation seems very low considering that CAT's stock price 52 week range is \\$90.34 to \\$140.44 and the current stock price is \\$137 as of 11/20/2017 (3).\n",
    "\n",
    "The major issue with the DDM example 1 calculation is that it assumes dividends will not grow.  We can clearly see from CAT's\n",
    "income statement that dividends have steadily increased from 2014 to 2016.  Let's run thru the math to determine how much dividends are growing over this time period."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "D2015 = is_df.loc[\"Dividend per Share\",\"TY2015\"]\n",
    "D2014 = is_df.loc[\"Dividend per Share\",\"TY2014\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.01\n",
      "2.7\n"
     ]
    }
   ],
   "source": [
    "print(D2015)\n",
    "print(D2014)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "D2015_growth_rate = D2015/D2014 -1\n",
    "D2016_growth_rate = D2016/D2015 -1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.114814814815\n",
      "0.0232558139535\n"
     ]
    }
   ],
   "source": [
    "print(D2015_growth_rate)\n",
    "print(D2016_growth_rate)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A neat feature that we can use in python is lists.  We will create a list to store these dividend growth rates.  The list will allow us to perform mathemtical calculations in a more user friendly manner."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "list = [D2015_growth_rate, D2016_growth_rate]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0.1148148148148147, 0.023255813953488413]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "g = sum(list) / len(list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.069035314384151558"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have calculated the average dividend growth rate for CAT, so let's use it to calculate DDM for example 2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "129.556147893\n"
     ]
    }
   ],
   "source": [
    "DDM_V2 = (D2016*(1+g))/ (R - g)\n",
    "print (DDM_V2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Conclusion\n",
    "\n",
    "The DDM example 2 is much closer to the actual stock price and calculates that CAT is overvalued.  In fact, the DDM model will generally underestimate the value of a company because it assumes that all relevant information is taken into account by the dividend.  This is a virtually impossible assumption to achieve because a dividend is not based on perfect information. In fact, companies are incentivized to keep the dividend and a downward dividend adjustment will have very negative consequences on the stock price (1).\n",
    "\n",
    "Additionally, there is a blind spot for the DDM because it is not able to evaluate companies that do not pay a dividend.  For example, the DDM would not be able to calculate a value for Alphabet Inc and Facebook.\n",
    "\n",
    "For these reasons, we will move to a more advanced company valuation model called the Weighted Average Cost of Capital (WACC) in the next tutorial.  This model is like the DDM model in that it uses cash flows to determine company value.  However, the WACC model does not use the dividend as a proxy for cash flows.  The WACC model is the most used discounted cash flow model and we will get to explore the balance sheet and statement of cash flows during the tutorial.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Please upvote this tutorial if you would like to learn more about the basics of finance and company valuation.  Also, please share your comments, so that we can tailor the training to your interests!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Works Cited:\n",
    "1. The Financial Times Guide to Understanding Finance, 2nd Edition by Javier Estrada Published by Pearson Business, 2011\n",
    "2. https://www.sec.gov/edgar.shtml\n",
    "3. https://finance.google.com/finance?q=CAT&ei=MO4SWui5GYfSjAHDvqqQDA\n",
    "4. https://ycharts.com/indicators/10_year_treasury_rate"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda env:DAND]",
   "language": "python",
   "name": "conda-env-DAND-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}