Jupiter.plymouth.edu
SKETCHES in FINANCE 14OL
adapted for undergraduate finance online
CONTENTS
PREFACE 1
1. INTRODUCTION to FINANCIAL MGT 1
2. FINANCIAL ANALYSIS 4
3. TAXES 6
4. RISK and RETURN 8
5. PORTFOLIO MANAGEMENT 11
6. VALUATION CONCEPTS 13
7. VALUATION OF BONDS 16
8. VALUATION OF STOCKS 18
9. CAPITAL BUDGETING 20
10. QUANTIFYING UNCERTAINTY 24
11. FINANCIAL PLANNING 26
12. COST OF CAPITAL 28
13. LEVERAGE MODELS 31
14. DIVIDEND POLICY 33
15. EXERCISES 35
PREFACE
The content for Sketches in Finance was compiled from lecture notes created for various financial courses taught by the author over many years. The content is not intended to break new ground, to promote any particular investment philosophy, to be a complete reference book, or to be the source of high entertainment. The collection is intended to replace the traditional financial textbook by touching the peaks of topics considered essential in an introductory course but with an economy of verbiage and minimal cost to the student. The exercises at the end of the book are designed to be completed in an online course environment.
1. INTRODUCTION to FINANCIAL MANAGEMENT
Introductory undergraduate finance courses are variously known as financial management, corporate finance or managerial finance and the courses are presented on the assumption that many students will eventually be employed by corporations and will own shares in corporations. Some students will become active managers of corporations, some even in the field of finance. And all students will be faced with financial decisions on a personal level. The skills and concepts reviewed in this book are designed to address the challenges of managing finances, be they individual or corporate.
1.1 Firm Advantage: The world of finance is often focused on the firm (i.e. the corporation, the enterprise, the business, the organization) because that’s where leveraged economic activity occurs. Economic activity, the exchange of goods and/or services, certainly takes place at the individual level, too, in which two people engage in an economic transaction. But a firm has an advantage over the individual by virtue of scale (e.g. more people, more capital, larger markets) and is thus able to generate many times more revenue and profit than individuals are able to generate on their own. As a consequence of this “firm advantage”, individuals at the beginning of their productive years look to find employment with corporations, rather than trying to start businesses of their own, because it is with the firms where the quick money is most likely to be found.
1.2 The Individual: Thus, an individual working for a firm has these two objectives: to work hours (days, years, a lifetime) for wages to fulfill his or her self-interests and also, to contribute to the financial well-being of the firm for which he or she works. The first objective can be assumed to be inherent to human nature (the survival instinct) and has been both celebrated and condemned – celebrated as the primary energy of progress and aggregate well-being (think Adam Smith, Ayn Rand) and condemned as destructive and anti-social (think Karl Marx, Vladimir Lenin). The second objective, contributing to the financial well-being of the firm, is the very core reason that the individual is originally hired by the firm. Some individuals quickly realize that their own employment security, and the related rewards, is a function of their ability to significantly contribute to the financial health of the firm. Financial management is the study of the practice of maximizing this financial health.
1.3 Stock Price: Maximizing a firm’s financial health has become a mantra in our capitalistic economy, and, like an individual’s self-interests, the notion is constantly being defended and vilified as well as being refined to a simple normative model. [A normative model is one that defines how the world should work in order to be consistent with other generally accepted assumptions]. This model says that that the objective of the firm should be to maximize the stock price.
1.4 Owners: Why is stock price the ultimate goal? Underlying this model is the assumption that the firm exists for the benefit of the owners of the firm. They are the ones who invested the capital to grow the firm, they are the ones who hired the agents to nurture the firm, and they are the ones who risked their own wealth so that the firm could flourish. And the owners’ wealth, or net worth, is directly proportional to the value of their respective shares in the firm. As the value of those shares fluctuates, so does the wealth of the owners. So the owners’ self-interested objective is to have the stock price, the price per share (pps), be as high as possible.
1.5 Managers: As the primary objective of the firm is to maximize stockholders’ wealth as reflected in the stock price, then the primary function of financial management is also to address that objective. Management accomplishes this through convincing investors that the "quality of future earnings" is strong – that is, that future earnings are both "likely" (have a high probability of coming to pass) and robust (exhibiting healthy growth through time). If management is effectively convincing, then a demand for a share of ownership in the firm will apply the upward pressure on the price, and management will have succeeded in fulfilling their objective - for the time being.
1.6 Agents: The relationship between the individual (the employee, the manager) and the firm is explored in the field of “agency theory”, where it is recognized that the employees (the “agents”) are motivated by both self-interests and the rewards from furthering the firm’s interests. The assumption of the theory is that although the corporation exists primarily for the benefit of the owners, that people who are engaged by the corporation to work to achieve those objectives are less than perfectly committed to those corporate objectives. The rationale underlying this conflict is based on the perceived realities of human nature - the realities are that a person can be paid to work toward someone else's goals, but that the "agent" will not fully suppress their own personal objectives. The agent's personal objectives are assumed to be the same rational economic goals that drive the owners, the maximization of their own personal wealth. The theory suggests that the conflict between the owners and the agents is inherent in the relationship, that the conflict cannot be eliminated, although it can be acknowledged and mitigated.
1.7 Compensation: Various practices are designed to address the agency conflict - the most obvious is for the owners to share their ownership with the agents. The sharing is achieved by the construction of compensation packages that are enhanced with a distribution of stock that, in effect, gives two roles to the employee (agent and owner) with the hope that the employees will more fully embrace the objectives of the other owners. Unfortunately for the "pure" owners (stockholders without any other relationship with the firm other than owning stock), sharing ownership has not proven to be effective in fully suppressing the agents' personal objectives.
1.8 Areas of Financial Management: The agents/managers exercise the tools of financial management to support the objectives of the firm through simultaneous actions in four major areas: 1) Capital Structure, 2) Capital Budgeting, 3) Dividend Policy, and 4) Cash Flow Management. These areas are explained further as follows:
1.9 Capital Structure: A firm is built on a foundation of money, the firm’s “capital structure”, which comes primarily from selling ownership shares (stocks) and by long-term borrowing (bonds). The capital structure decision includes determining when and how much stock to issue to outside investors, and when and how much debt (through bond offerings) to incur. The question is sometimes framed as “what proportion of debt to total assets” should be incurred in order to maximize stock price. Academic models that address this issue are ambiguous, with some models concluding that capital structure should have no direct impact on stock price, and others suggesting that a firm should borrow as much as they can. The question may also be framed as “where should the money come from?”
1.10 Capital Budgeting: A firm’s survival depends on the firm investing in future “projects”, where projects is a general term for any funds consuming activity – e.g. buying another firm, opening a new market, or developing a new product. The essence of a capital budget is a plan of expenditures and rewards related to the project under consideration. Incremental cash flows are estimated and appraised before a final decision is made to undertake a project. Some models used to assess capital budgets are based on traditional time-value of money concepts, are widely used in industry today, and are popular fodder for finance classes. There is also a wide-range of esoteric capital budgeting models that provide thought-provoking perspectives on forecasting future events.
1.11 Dividend Policy: A firm secures funding via capital structure decisions, invests the funding into capital budgeting projects, and distributes the earnings from those investments via dividend payouts to the stockholders. After tax earnings are either distributed as dividends or retained by the firm – the determination of the split between dividends and retained earnings is discretionary according to the will of the board of directors (albeit with some legal constraints).
1.12 Cash Flow Management: The earnings of a firm as shown on the bottom of its income statement are not necessarily representative of the actual cash flows. The art of forecasting real cash flows, tracking actual inflows and outflows, and taking action to control the flows are functions of the financial manager. The objectives are to reduce both the financing costs and the risk of depleting cash, thus ensuring the long term health of the firm and the maintenance of its share price.
2. FINANCIAL ANALYSIS
Financial managers use the techniques of financial analysis to measure the corporation’s financial health, to assist in identifying strengths and weaknesses and to track the effectiveness of management initiatives. A classic approach to financial analysis is through the use of ratios and this technique is used both by internal managers and external investors. Some typical ratios include those listed immediately following. Note that the first eight ratios use data taken solely from the income statement and the balance sheets, whereas the last two ratios use current market data combined with those financial statements.
2.1 Current Ratio shows the liquidity of the firm by comparing the current assets relative to the current liabilities. A healthy firm would want a current ratio (current assets divided by current liabilities) greater than 1.00, and a ratio of 2 or 3 would certainly add a margin of comfort. To calculate, divide current assets by current liabilities.
2.2 Quick Ratio, or “Acid Test”, is a more stringent measure of liquidity, in that it subtracts inventory from the current assets before doing the same calculation as with the Current Ratio. Inventory is not considered to be as liquid as other current assets. To calculate, subtract inventory from current assets before dividing the remaining current assets by current liabilities.
2.3 Inventory Turns is another measure of the firm's asset management prowess. In this case, the skill in managing inventory is measured – having the right stuff on the shelf when needed, not having too much in stock, and moving stuff quickly. Inventory Turns represents the number of times the firm has "filled and emptied" its shelves in a year. More turns are better than fewer turns. There is a wide range of what is a normal number of turns, depending on the nature of the business. Some firms use average inventory in the turns calculation, others use year-end inventory. And some firms divide inventory into sales, and others divide inventory into cost of goods sold. To calculate, use COGS divided by year-end inventory.
2.4 Days Sales Outstanding (DSO) is a measure of the firm's ability to manage one portion of its assets - accounts receivable. Equivalent to "average collection period", DSO is a measure of how quickly receivables can be converted into cash. Fewer days are better than more days, and an average of a month and a half is normal for many industries. DSO is calculated by dividing sales into receivables to get the proportion of sales still outstanding and then multiplying the results by 365, the number of days in a year.
2.5 Debt Ratio measures the proportion of debt (total liabilities) relative to the total capitalization (total assets) of the firm. Somewhat counter-intuitively, more debt is not necessarily a bad condition for a firm (debt is still something to be avoided for the individual). A firm that avoids borrowing, at the expense of foregoing profitable projects, may be losing opportunities that its competitors end up taking advantage of. Debt ratios will usually fall between 0 and 1.00. Over 1.00 is definitely a bad sign. But between 0 and .80, it's difficult to say whether a change is better or worse without further information. To calculate, use total liabilities divided by total assets.
2.6 Times Interest Earned (TIE) indicates a firm's ability to cover its interest payments due on its bonds with the firm’s earnings from operations. Said another way, TIE is the number of times the firm could pay its interest obligations with its earnings before interest and taxes (EBIT). Less than 1.00 would be a bad sign for sure, and multiples like 2, 3 and higher are healthy signs. To calculate, divide the interest payments into the earnings before interest and taxes (EBIT).
2.7 Profit Margin is the percentage of the top line (sales, revenues) that flows down to the bottom line (EAC, net profit, net income) after taking out all the firm’s expenses. A firm that keeps two cents for every dollar of sales is typical, although there are certainly more profitable firms, too. To calculate, divide EAC by Sales.
2.8 Return on Assets (ROA) is pretty self-explanatory. ROA measures the firm's bottom line profits as a percentage of the total assets of the firm. Sometimes called "basic earning power", ROA suggests that a firm should be able to earn a (relatively) fixed percentage of every dollar invested in the firm. A 12% ROA is not unusual. To calculate ROA, divide EAC by Total Assets.
2.9 P/E Ratio is unlike the ratios above in that it is not generated solely from the income statement and balance sheets – it also requires the price per share (pps) from current market data. This ratio measures the price that investors are willing to pay for a dollar of earnings. The earnings are usually historical, trailing twelve months (ttm), although the “P/E (est)” ratio compares current pps to estimated (future) earnings. The P/E ratio is often abbreviated to PE ratio and is sometimes called the firm's "multiple". The reciprocal of the PE is called the "earnings yield", the percentage of net earnings generated each year for a given price per share, and is perhaps a more intuitive way of understanding the relationship between pps and earnings available to common stockholders (EAC). Remember, EAC belongs to the owners of the firm – the shareholders. As of the closing bell on 20 July 2012, the average PE for the S&P500 was 15.6. As PE is a measure of the "pricey-ness" of a stock, a PE of 16 was average, over 20 was getting pricey.
2.10 Market to Book is similar to the PE ratio in that it relies on current pps data. In this ratio, "market" refers to the value that investors have given the firm, the "market capitalization" or MKT CAP (as seen on Yahoo). Market cap is the total current market value of all outstanding stock, or pps times number-of-shares. For example, pretend that you wanted to buy IBM, as in, buying the whole company. How much would it cost? That's market cap. In mkt/book, "book" is how the accountants value the company and is found on the balance sheet as "shareholders equity". Mkt/book is another measure of pricey-ness, and typical ratios run 2.0 to 4.0. To calculate, divide the current market cap by the most recent book value.
3. TAXES
Financial managers are well-aware of the reality of taxes – as taxes are prevalent in most business transactions, they are a significant cost, they are somewhat predictable and manageable, and they vary according to a myriad of rules and exceptions. What follows is a sampling of the issues and vocabulary related to the tax implications of transactions in stocks and bonds at both the corporate and the individual level.
3.1 Corporate tax treatment on bonds:
Interest on bonds is paid by the firm to bondholders before taxes. That is, dollars paid to bondholders are subtracted from the firm's operating profits prior to the calculation of the taxes due on those operating profits. Consequently the taxes that are eventually paid on those reduced profits are less than they would have been had interest not been paid out. From one perspective, this reduction in taxes amounts to a subsidy that the government "pays" to the firm for borrowing money.
3.2 Corporate tax treatment on stocks: Dividends paid to stockholders on stocks are calculated and paid by the firm after the firm has paid its taxes. Hence there is no reduction in the firm's net profits (or their taxes) as a result of the payment of dividends. So, from the firm’s perspective, the comparison of the cost of borrowing (through issuing bonds) and the cost of equity (based on the dividends paid to stockholders) show a strong tax advantage to bonds.
3.3. Individual Federal Taxes: “Ordinary” income for Federal tax purposed has a very specific meaning - “ordinary” defines a category of income that is taxed at the "ordinary" tax rates. Ordinary income includes: wages, salary, tips, interest, and dividends. Further, "gross taxable ordinary income" includes the sum of all the above. But there is an exception: dividends, while classified as "ordinary" income, are given preferential tax treatment compared to the other ordinary sources of income – that is, dividends are taxed at a maximum of 15%. From an individual’s perspective, in comparing the tax implications of receiving interest on bonds or dividends on stocks, the dividends have the advantage as their taxes are capped at 15%, whereas interest is taxed at the marginal rate of the individual – which may be as high as 35%.
3.4 Federal Taxes on "ordinary income" is calculated by first taking the "gross taxable ordinary income" and subtracting both the "personal exemption" [$3800 for 2012] and the "standard deduction" [$5950 for 2012]. Exception1: Some individuals will choose to "itemize" their deductions, rather than take the standard deduction. The rule of thumb is to use whatever method minimizes the taxes. The gross taxable ordinary income minus the exemptions and deductions leave a "net taxable ordinary income". Exception2: Because dividends have a 15% cap, dividends are also subtracted from the total ordinary income prior to taxing the ordinary. Then the tax on dividends is calculated independently and later folded back into total taxes due. The net ordinary income figure is then applied to the "tax tables" to find the "federal taxes on ordinary income". The ordinary tax rate table taxes the first block of ordinary income at the lowest rate, the next block of income at the next higher rate, and so on.
3.5 Effective Tax Rate: The effective tax rate for an individual or a corporation is the percent of total income, including ordinary income and capital gains, that is actually paid as taxes. It is calculated by taking actual taxes paid divided by total income.
3.6 Marginal Tax Rate: The marginal tax rate is the rate at which a hypothetical additional dollar added to the total income would be taxed. It is the rate of the highest bracket for the total income of interest.
3.7 Capital Gains: Capital gains are a source of wealth accumulation for both the individual and the corporation, but are different than ordinary income (wages, interest, dividends) or corporate profits, and capital gains are taxed differently than those other sources. Capital gains are the difference between the original purchase price of an asset, the “cost basis”, and the eventual selling price of that asset. While capital gains do apply to assets such as real estate and vehicles, in the context of finance, the assets are typically stocks and bonds. Capital gains are currently taxed at an individual's marginal tax bracket, or at 15%, whichever is rate lower. Capital gains taxes are due for the year in which the asset is actually sold, that is, when the gains are realized.
3.8 Capital Losses: If an asset is sold at a price lower than the cost basis, a capital loss is realized. Capital losses are sometimes looked on as a favorable event as the losses can reduce taxes by netting the losses against any gains for the year for the taxable entity (an individual or a firm). Any net capital losses (for the entity for the year) may be "carried back" three years and carried forward fifteen years until depleted. When using the carry back option, tax preparers start by reducing the gains of three years ago, because in the subsequent year, the gains of three years ago will no longer be available to offset losses with the carryback/carryforward method.
3.9 New Hampshire Interest and Dividend Tax: The State of New Hampshire is somewhat unique from other states in the US in that there is no state income tax (at least not on wages), no sales tax, nor a capital gains tax. NH does have a business profits tax and an interest and dividends tax for individuals, which has been in existence, and basically unchanged, for many years. The model for the interest and dividends tax is as follows:
1. Interest and dividends are taken from the Federal 1040 form. (There are a few exceptional instruments that are taxed at the Federal level, but exempt from NH taxes.)
2. Every individual receives a $2400 exemption. This amount is deducted from the total interest and dividends found on the Federal 1040.
3. The NH tax is 5% of the amount calculated in step #2.
4. RISK and RETURN
The concepts of risk and return are inherent in many financial models, with the assumptions that the objective of investors is to maximize the returns on their investments at the same time as they try to minimize the probability of losing principal. The study of risk and return focuses on the measurement of these two constructs and their theoretical and empirical relationship.
4.1 Returns: The returns of an investment are a measure of the rate of growth of the value of the investment. For a typical investment in stocks or bonds, the return is the percent change in the market value of the underlying instrument (the share of stock or the bond) plus the percent gain from the interest or dividends received on the instrument.
4.2 Historical Returns: Returns (K) may be measured historically (i.e. in retrospect, looking backwards) and these historical returns can be measured precisely, as a matter of record, using the model “change over original”. Mathematically, this is expressed K= (Δ/orig). In this model change has two components – one is the difference in market value of the instrument between the starting point of the return measurement and the end point of the return measurement. The second component of change is the interest or dividends that have been received related to the instrument being measured. The two points in time (beginning and ending) might be when the shares were bought versus when the shares were sold, or perhaps a point in time one year ago versus today. In either case, the denominator (“orig”) is always the earlier point in time.
4.3 Expected Returns: There exist return models that look forward in time rather than looking backwards. These are the expected return models – they are based on the same general concept as historical returns (i.e. “change over original”), but use estimated parameters instead of actuals. These predictive models tend to be elaborate and woefully inaccurate, but they are helpful in making rational investment decisions.
4.4 Returns on Cash: Returns on a cash instrument (e.g. a certificate of deposit (CD)) is the total amount of interest paid as a percent of the original investment. The market value of a cash instrument tends to stay constant, but the interest that is earned may include "interest on the interest" such that the actual effective returns over time may be greater than the quoted rate of interest. Historical models and expected models are often the same, because interest rates are often guaranteed, thus making calculations quite certain. See also: Rate to Yield Model
4.5 Returns on Stocks: Returns on stocks are expressed as “Ke” [pronounced “K sub E”] in which the “e” stands for “equity”, which stands for “ownership”, and a share of stock is the instrument that defines the partial ownership of the firm . Total historical stock return is the annualized percent gain (or loss) in price per share (pps) plus the percent return from dividends. Mathematically: Ke=[(new pps –old pps) + dividends] / original pps. The two classic models used to estimate expected returns are the Capital Asset Pricing Model (CAPM) (See 3.10) and Gordon's Model (See 7.1).
4.6 Return on Bonds: There are three models for the returns on bonds: Value of the Bond (Vb), the Wall Street Journal’s (WSJ) “last yield” , and Rodriguez’s Model. The Vb Model (See 6.4) can be used to calculate returns by finding the value of K in the model that yields a Vb that is equivalent to the actual current market value. The WSJ model, sometimes called "current yield" or "last yield", is the annual coupon payment (in dollars) divided by the current closing price. Rodriguez's Model is an estimator of "Yield to Maturity".
4.7 Returns on Portfolios: Returns on a portfolio (Kport) may be calculated at the micro level. That is, portfolio returns are equal to the change in the market value of all the securities in the portfolio plus all the dividends related to those securities, divided by the total beginning market value of the portfolio. As an alternative, the returns of the portfolio may be calculated at the macro level. That is, the return of the portfolio is the weighted average of the returns of the individual securities - weighted by the market value of the securities, or Wtd Avg = Σ kiwi, or the sum of the individual returns (k) times the individual weights (w).
4.8 Risk (standard deviation): The traditional method of measuring equity risk is to regard the volatility of the returns of the instrument (e.g. a share of stock). The notion is that while investors certainly want to maximize returns, they also want to have steady returns from period to period and to avoid drastic swings from wonderfully high returns to devastating lows. This volatility of returns, or “bounciness”, is measured using the standard deviation (σ) of the returns. Investors who are willing to assume more risk do so in anticipation of receiving greater returns and low risk investments tend to have the lowest returns.
4.9 Risk (beta): An optional method of measuring volatility of stock returns is to compare the stock’s returns to that of the overall market. The comparison is done using the statistical technique of regression analysis, in which the dependent variable (y), the individual security’s returns, is compared to the independent variable (x), the market’s returns. The slope (m) of the resulting regression line, y=mx+b, is designated as beta and is a measure of a security’s volatility relative to the market. In Excel, the slope is function is: =SLOPE(known y’s, known x’s).
4.10 Risk of Bonds: The risk on a bond is the probability of the firm not being able to pay the full amount of the interest payments due to the bondholder, or not being able to return the face value of the bond when due at the date of maturity. This risk is measured by Moody's, Standard & Poor's and Fitch Ratings who use the "probability of default" as the primary criteria for grading the bond. Their rating scale is similar to an academic scale of A,B,C, & D, where A is good, and D is not.
4.11 Capital Asset Pricing Model (CAPM): A classic model showing the relationship between the historic risk of a security and the expected return of that security is the Capital Asset Pricing Model (CAPM). Introduced by William Sharpe in 1964, the model incorporates the concept of beta, or the volatility of a security's return relative to the volatility of the returns of the market (as measured, for example, by the S&P 500).
When historic return data for a particular security (Ke) are regressed against historic return data for the market (Kmkt), the resulting regression line illustrates graphically how much of the changes in the returns of the security are "caused by" the changes in the returns of the market. Remember, in its general form, regression analysis compares a dependent variable, "y", to an independent variable, "x", and yields a "regression line" of the form y = mx + b. In this particular application to financial returns, the dependent variables are the returns on the security (Ke) and the independent variables are the returns of the market (Kmkt), where the US market is usually defined as the S&P 500. Thus, when the returns of an individual security are regressed against the returns of the market, the resulting slope of the regression line (the line of best fit, or the line of least squares) is defined as "beta" (or the volatility of a security's return relative to the volatility of the returns of the market). Obviously, when the returns of the market are regressed against the returns of the market, the slope of the line will be 1.00, and therefore, by definition, the beta of the market is 1.00.
The CAPM model estimates the return on a security as being the return on a risk free security (as exemplified by a 3-month US T-Bill or the LIBOR), plus a "risk premium". In CAPM the risk premium is estimated to be β(Kmkt-Krf), or beta times the difference between the expected return of the market and the risk free rate. Thus the CAPM model: Ke = Krf + β ( Kmkt – Krf ).
4.12 Sample CAPM question: Use CAPM to find the expected returns of a security, given that a 3-month Treasury bill yields .93 %, that the expected returns on the S&P500 are 17 % and the beta of the security is 1.50. Draw the related Security Market Line (SML) on a graph where the y-axis is “Returns” and the x-axis is “Beta”.
Solution: The CAPM set up would be Ke=.0093 + 1.5(.17-.0093) for an expected yield of 25.035%.
4.13 Drawing the Security Market Line: The graph should have points (X,Y)=(βeta, K) at (0, .93%) for the Risk Free instrument (the US T-Bill); (1.0,17%) for the market; and (1.5,25%) for the security in question.
1) Plot the "risk/return" point for the "Risk free instrument" (a US Treasury or LIBOR). The risk is zero and the expected return is the risk free rate.
2) Plot the "risk/return" point for the market. The risk (or beta) is (by definition) 1.00 and the expected return is given.
3) Draw a line from the risk free rate through the market risk/return point and beyond. This is the “Security Market Line”, or SML.
4) Check: Plot a point at the security’s risk/return point. It should fall on the line.
5. PORTFOLIO MANAGEMENT
The practice of finance is not limited to the financial management of the firm. Considerable wealth is held by unincorporated entities such as individuals, trusts, non-profit organizations, municipalities and a wide range of other institutions. And financial institutions, while often technically incorporated, manage their financial assets with portfolio management models more than with "corporate" revenue-to-profit models. The wealth held in portfolios, or collections of the financial instruments, and the management of these portfolios requires financial professionals with portfolio management skills.
5.1 Modern Portfolio Theory (MPT): The discipline of portfolio management had a major incarnation with the introduction of Modern Portfolio Theory (MPT), envisioned by Harry Markowitz as part of his PhD thesis at University of Chicago. Prior to MPT, portfolio management focused on active stock picking – that is, speculating and selecting those stocks with the highest expected returns. This is an intuitive and still popular focus of many investors. However, with MPT, Markowitz quantified the notion of minimizing risk through diversification, and lowering the probability of losing principal. This is not to say that "maximizing returns" is wrong-headed or undesirable, but it suggests that "minimizing risk" is a worthwhile and simultaneous endeavor. His model identifies a set of portfolios that have the lowest risk for a given return - or said another way, a set of portfolios with the highest return for a given level of risk. This set is the “efficient frontier”.
5.2 PPS vs. Time: The development of the MPT model can be illustrated with a series of charts as shown to the right. The first chart shows daily stock prices, or price per share (pps), through time. In this example the time span is one year and shows pps at the close of each trading day. The blue dash line is a projection of the original value at the start of the measured year. The short red vertical line represents the change (Δ) in pps over the course of the year.
5.3 Returns vs. Time: The second chart shows returns (K) for each of those same trading days using the classic “K = Δ/orig” model, or “returns equal the change in price divided by the original price”. “K” is a percentage, and is expressed either as a percent (i.e. 25%) or as a decimal (i.e. .25). Although dividends are ignored in this return calculation example, total stock returns are more correctly calculated with dividends, taxes and transaction costs factored in.
5.4 Volatility of Returns: The third chart illustrates the calculation of the variability, (the "bounciness" of the data, or volatility) of the returns. The statistic that reflects this volatility is the standard deviation (σ) of the returns, and is, by definition, the risk of the stock. There is a natural attraction to think of risk as the volatility of the price, and indeed, a bouncy price will cause bouncy returns, however, the volatility of the returns is the accepted measure. Note that data in this third chart is the same as the second chart, but a line has been added representing the mean and a normal distribution of returns based on the mean and standard deviation.
5.5 Multiple Securities: MPT suggests that the standard deviation of the returns, the risk, can be mitigated by the introduction of a second security whose return pattern is significantly different than the original security. In the adjacent chart the original security (from the previous charts) is shown as "A" in blue, and the second security is shown as "B" in red. Their respective distributions, based on their respective means and standard deviations, are shown on the right side of the chart. The two distributions might be similar, but probably won't be identical.
5.6 Return of Portfolio: As the two securities are combined into a single portfolio, the resulting composite return (the return of the portfolio) is shown in blue in this chart. A correlation coefficient is used to compare return patterns, with a low correlation indicating greater differences in patterns, and results in a portfolio with less volatility (less risk) than either of the original securities.
6. VALUATION CONCEPTS
The process of valuation in finance is consistent with the more general meaning of valuation – that is, assigning a value to, assessing, or appraising some asset. And, not surprisingly, the assets commonly valued in finance are stocks and bonds.
6.1 Definition: Consider that a share of stock (or a bond, or a CD) is simply a contract (a written legal agreement) between two parties that entitles one party to certain rights in consideration of an agreed upon purchase price. The valuation issue is: how much is that contract (the stock/the bond) worth? What is its value? How much should the buyer pay for the contract? The answer is: The buyer should pay the current value of the future cash flows related to that contract. The term “contract” might better be replaced with "financial instruments", or a "share of stock", or a "bond". And "purchase price" may be replaced by "price per share" (pps) or the Value of a Bond (Vb). And rather than "current value", we’ll use "present value", to be consistent with the common names of the models used in this valuation process.
6.2 Time Value Variables:
There are four basic models for calculating time value of money: FV, PV, FVa, PVa. The variables used in the models are:
FV = the future value of a lump sum invested for a period of time
PV = the present value of a known future value
FVa = the future value of an annuity
PVa = the present value of an annuity
k = the rate for the period of compounding
n = the number of periods of compounding
PMTS = the payments related to annuity
Annuity = a condition in which equal payments are paid (or received) every period (daily, weekly, monthly, etc.) for a set length of time.
6.3 Future Value (FV): The simplest model addresses the future value of a lump sum deposited at a given interest rate for a given amount of time, with interest compounded periodically.
Example: $1000 (the present value) is invested at 5% per annum for 3 years with monthly compounding. How much will that $1000 grow to (or “what will be the future value”) in three years?
As an algebraic equation: FV=PV(1+k)n
As entered into a calculator: FV=PV x (1+(k))^n
Substituting: FV=1000 x (1+(.05/12))^36 =1161.47
Note that the annual rate is 5% (or .05 as a decimal), and so .05/12 would be the monthly rate. So k=(.05/12)=.00416666…..repeating. As a practical matter, when doing the calculations by hand it is better to enter “(.05/12)” than to enter “.00416667” because the former will have less rounding error than the latter.
Also note that “n” is the number of periods of compounding, every month for three years, 3x12=36. “n” will always be a multiple and will not be subject to rounding error.
6.4 Present Value (PV): Using the first model (above), and solving for PV, yields the model for calculating the present value of a known future value that has been discounted by a fixed rate.
Example: I’ll need $10,000 in 5 years. How much must I deposit today at 3%, compounded weekly, in order to accumulate the required amount?
As an algebraic equation: PV=FV(1+k)-n
As entered into a calculator: PV=FV x (1+(k))^(-n)
Substituting: PV=10000 x (1+(.03/52))^(-260)= 8607.45
Note: k and n are handled the same way as in the first example and are handled the same way in the following models. That is, k is the rate for one period of compounding, and n is the number of periods of compounding.
6.5 Future Value of an annuity (FVa): The model for calculating the future value of regular payments for a given length of time.
Example: A payroll withholding account is set up to withhold $200 every week into an account that guarantees a 4% return. How much will be in the account after 40 years?
As an algebraic equation: FVa=PMTS [(1+k)n-1] / k
As entered into a calculator: FVa=PMTS x ((((1+(k))^n))-1)/(k)
Substituting: FVa=200 x ((((1+(.04/52))^2080))-1)/(.04/52)= $1,026,996.59
6.6 Present value of an Annuity (PVa): The model for calculating the present value of regular payments for a given length of time.
Example: A lucky person wins a lottery prize advertised as being worth a half million dollars. The fine print says the winner will receive $25,000 per year for 20 years or a “lump sum cash equivalent”. The lottery uses a 7% discount rate to calculate the cash equivalent – how much would that be?
As an algebraic equation: PVa=PMTS [1-(1+k)-n] / k
As entered into a calculator: PVa=PMTS x ((1-((1+(k))^(-n))))/(k)
Substituting: PVa=25000 x ((1-((1+.07)^(-20))))/(.07)= $264,850.36
6.7 Rate to Yield Model: There may be a difference between the rate that is quoted on a financial instrument and the percent return, or effective yield, which the investor actually realizes from that same instrument. The difference is due to the effects of compounding interest, and the effective yield will be slightly greater than the quoted rate as long as the period between compounding is less than a year. This model is applicable to fixed rate instruments such as Certificates of Deposits (CDs).
Effective (annual) yields may be derived from quoted (annual) rates and the frequency of compounding. The actual contractual holding period of the instrument is irrelevant because both the quoted rates and the effective yields are expressed on an annualized basis. Note that the beginning value (PV) is also irrelevant, as it doesn't appear in the final model (it gets canceled out through algebraic reduction). Also, intuitively, the returns, or yields, on an instrument are not affected by multiples of the instrument.
The model, Effective Yield = (1+k)n-1 is derived from combining (( /orig) and FV=PV(1+k)n, where:
( = the difference between the original (beginning) value (PV), and the ending value (FV), after having been held for one year.
orig = the original beginning value (PV)
k = the rate for the period of compounding
n = the number of compounding periods
Sample Rate to Yield Question: Calculate the effective yield on a three month CD, $5,000 denomination, with a quoted rate of 2.5%, compounded weekly.
(1+k)n-1
(1+.025/52)52-1
(1.0004808)52-1
1.0253089-1
.0253089
=2.53089 %
6.8 Loan Payments: Typical “vanilla” loans (so called to differentiate traditional loans from exotic adjustable rate, up-front points, etc.) have regular payments equal to the sum of the interest due on the outstanding balance plus a payment that contributes to paying off the principal. The actual formula is
PMTS= (PVa x k) / [1-(1+k)^-n] where PVa=present value of the annuity, or the amount that the bank is willing to loan to the borrower if the borrower signs a contract promising to pay the bank a fixed amount (the annuity) every month (the timing of the payments doesn’t HAVE to be monthly, but that’s the traditional timing). K= the rate for the period of compounding (usually the annual/quoted rate divided by 12 months of the year). And n= the number of periods of compounding.
6.9 Amortization Table: The amortization of the loan can be expressed in a table in which every period (or month) the interest is calculated on the outstanding loan balance using the following logic:
Interest $ = (annual rate/12 months of the year) X outstanding balance of the loan.
The interest$ are subtracted from the fixed monthly payments to yield the "reduction in the balance of the loan" (Red'n Bal). And when the Red'n Bal is subtracted from the "Beginning Balance" the result is the ending balance for that period. The ending balance of one period is the beginning balance of the next period.
7. VALUATION OF BONDS
Bond valuation is determined consistent with the concepts described above. That is, the value of any financial instrument is equal to the present value of the future cash flows related to the instrument (in this case, the bond). The following illustrates how the value of a bond (Vb) is calculated.
7.1 Bond specifications: In this example, the hypothetical bond is issued by Sample, Inc., and guarantees to pay the bondholder a fixed 5 1/4 % (annual) coupon rate, that is, 5.25% each year of the face value or denomination. All bonds have maturity dates, that is, the date when the firm returns the principal to the bondholder, and in this example, the bond matures in the year 2020 [Note: This valuation was done in 2012 when there were 8 years to maturity. Through time, the year of maturity does not change, but the years remaining to maturity changes every year.] This bond would be listed as:
SMPL 5.25% 2020 (or some variation of company_name , coupon_rate, date_of_maturity). These three properties of the bond are fixed and they do not change over the life of the bond.
7.2 Bond Mechanics: A bond is essentially an I.O.U. with the strength of a legal contract. This legal certainty translates into a highly predictable future cash flow for the investor – fixed interest payments and a single payment in the amount of the bond’s denomination at maturity. By convention, the actual payments are paid every six months in amounts equal to half of the amount due annually and the denomination of corporate bonds (the face value) is typically $1000; this example holds to those conventions. If the issuer on the bond is unable to meet their interest obligations in a timely manner, or if they are unable to pay off the bond at maturity, then the bondholders (the “creditors”) have the legal recourse to force the issuer into bankruptcy proceedings.
The present value of the future cash flows varies according to the rate at which those future cash flows are discounted. The higher the discount rate, the lower the present value of those fixed cash flows. The discount rate is determined by the current market rate for comparable (same risk category) bonds and fluctuates continually according to market pressures. For the sample bond example we’ll assume that the market rates are currently at 4%.
7.3 Bond Cash Flows: Therefore, the actual future cash flows will be:
1) An annuity, every six months, equal to: the (annual) coupon rate of 5.25% (=.0525) times the face value (=$1000) equals $52.50, divided by 2 [because $52.50 is the annual coupon payment. The annual coupon payment is divided in half because payments are made every 6 months]=$26.25. These payments will be made for the next 8 years, twice a year, or 8 x 2 = 16 payments.
2) The pay-off at maturity equal to the face value of the bond, or $1000.
7.4 Value of the Bond (Vb) Calculation: Given the future cash flows (above), the (present) value of the bond (Vb) can be calculated as the sum of the present value of the coupon payments using the present value of the annuity model plus the present value of the lump sum face value paid at maturity using the present value of a lump sum model.
1) Using PVa=PMTS [1-(1+k)-n] / k, where PMTS = $26.25, K=.04/2, and n=16, the present value of the coupon payments equals $356.41
2) Using PV=FV(1+k)-n where FV= $1000, K=.04/2, and n=16, the present value of the lump sum paid at maturity equals $728.45
3) The sum of 1) and 2) is the current value of the bond when discounted at the market rate of 4%, or, the Value of the Bond (Vb)= $1084.86
7.5 Rodriguez’s Model: The estimated return on a bond, or yield to maturity (YTM) may be estimated using a quick and dirty model proposed by Robert Rodriguez. The foundation of the model is simply the classic (Δ/orig) where Δ is the change in value of the account, and “orig” is the original base value (or basis). Further, Δ is made up of two components – the coupon payments and the capital gains. The basis is estimated as a weighted average value of the bond.
7.6 Rodriguez’s Variables: YTM=Kdb=[(Pmts)+ (FV-Vb)/n] / [(FV+2*Vb)/3], where:
YTM=Yield to Maturity, or the return on the bond if held to the date of maturity.
Kdb=Return on a debt instrument [i.e. a bond], assumes 1-year interval between interest payments.
Pmts=the dollar value of the annual interest payments,= the annual coupon rate times the nominal face value of the bond (generally assumed to be $1000).
FV=future value or face value =$1000. The lump sum paid at the date of maturity.
Vb=the street value of the bond as of the most recent trade.
n=number of whole years to maturity.
7.7 Rodriguez’s Example: What is the expected return on the following bond:
ABC 10.9 s 2034 123 ½ [valued as of 2013]?
Kdb=[(109)+(1000-1235)/21] / [(1000+ 2* 1235)/3]=.0845615=8.46%
Note: If using the equation above in Excel, the variables Pmts, Vb and n should be changed to refer to cells rather than being left as “hard coded” values in the equation. Also, Excel does not recognize brackets, therefore they should be changed to open (or close) parenthesis.
8. VALUATION OF STOCKS
In practice, the value of a share is determined in the market through the auction process, as exemplified by trading of shares on Wall Street. But in theory, the value of a share of stock (or the price per share, pps) is the present value of the cash flows related to owning that stock.
8.1 Gordon’s Model: Myron Gordon offered a model for the theoretical valuation of stock based on the same premise as that shown above. That is, the value of a share of stock (P) is the present value of the future cash flows associated with that particular security. Gordon suggested that the only real cash flow associated with a share of stock is the dividends. Further, he assumed that dividends can be expected to grow at a constant growth rate (g), often tied to expected growth in earnings, and that these cash flows should be discounted at the required rate of return of the equity investor (Ke). Using a little Calculus, Gordon found that P= D1/(Ke-g) where D1 is next year’s dividends, and Ke and g are as defined above.
8.2 Variations: There are four variations of Gordon's model - two for common stocks and two for preferred stocks. Preferred stocks differ from Common (in this context) in that preferred dividends are fixed – they stay constant from quarter to quarter. And the two variations (for each of the two types of stocks) differ only in the variable that is being solved for - in one case, the unknown variable is P (the theoretical price per share), and in the other K, either Ke or Kpr, the expected return to the stockholder.
1. Common: P = D1/(Ke-g)
2. " Ke = (D1/P) + g
3. Preferred: P = D/Kpr
4. " Kpr = D/P
8.3 Caveats:
1. All variables are on an annualized basis.
2. With Common, D1 is generally not a given. D1 is estimated by taking the actual dividends over the past 4 quarters (Do), and increasing them by the estimated growth rate. That is, D1 = Do (1 + g).
3. With Preferred, because dividends are fixed, there is no growth (g) in dividends, so that term is missing from the preferred models.
4. These models are valid in their mathematics, but they are forward looking models as opposed to "historical", or backward looking models. Consequently some of the variables are only best guess estimates, and the resulting prices or returns cannot be guaranteed. Some analysts factor in the notion that dividends follow earnings, and that earnings growth determines dividend growth. Other analysts recognize that earnings are not real cash flows, and that real cash flows are more relevant to value than earnings. Consequently, they modify this model by regarding expected cash flows rather than expected dividends.
8.4 Model Weaknesses: Several inherent weaknesses of stock valuation models in general can be illustrated using Gordon's model as a “straw man”. First, some input data is historical, or empirical, in nature. The data itself is true enough, but there are no guarantees that the data will hold true in the future. Second, some input data is speculative, and looking into the future is a foggy view at best. And third, even if the historical data holds true for the future AND the speculative data is luckily "dead-on", the resulting perfect answer of what the intrinsic value of the stock should be, as often as not, is not likely to be the same as the actual current market price. This leads to an investor's valuation dilemma.
The dilemma is that regardless of the integrity of a valuation, there is little assurance that the market will tend towards that valuation. For example, if an analyst determines that a particular stock is worth $60, and the spot price (current market price) is $50, the rational investor would buy the stock (at $50) and wait for the rest of the market to wise up and drive the stock to $60. But the nature of the market is that stocks do not consistently trend toward their valuations.
9. CAPITAL BUDGETING
9.1 Capital Projects: The terminology of capital budgeting refers to "capital projects", or those activities that require some up-front investment in hopes of reaping longer-term rewards. Capital projects may take the form of buying another company, building a new manufacturing plant, developing a new product, purchasing equipment, entering a new market – there is a wide range of investments considered “capital projects”. One could say that “attaining a college degree” is a capital project. Invest time and money now in hopes of greater returns in the future.
9.2 Capital vs. Expense: There is an accounting distinction between capital spending and expense spending: capitalized items become assets on the balance sheet and are depreciated over time versus expense items which are shown as costs for the current time period on the income statement. The current US tax code favors capital spending by allowing accelerated depreciation so as to realize tax savings sooner than later.
9.3 Capital Budgeting Process: The capital budgeting process involves 1) identifying potential projects, 2) estimating the incremental cash flows related to the respective projects, 3) identifying and quantifying risk associated with each project, 4) application of various financial models to quantify the projects value to the firm, prioritize the project relative to other potential projects, and to make the final decision as to whether to pursue the project.
9.4 NPV & DCF: The net present value (NPV) method and the discounted cash flow (DCF) method are two names for the same process. It is a technique used to take an estimated cash flow and to discount it to yield a net present value.
9.5 Cash Flows (CF) are the marginal changes in cash to the firm that are anticipated as a result of the adoption of a project. These cash flows would reflect estimated changes in revenue and direct expenses and the magnitude of these changes are generally estimated by engineers and cost accountants. Financial analysts assume the responsibility of calculating the net present value of the cash flows. Cash flows are typically estimated for ten years into the future as an industry standard. In academia, a five year estimate is considered sufficient. Here is a typical expression of cash flows.
Year 1 2 3 4 5
Cash Flow ($000) 55 65 75 85
9.6 Discount Rates: To discount a cash flow (CF), one must first determine the discount rate [Not to be confused with the Fed's “Discount Rate” which is an entirely different concept]. For academic purposes, the discount rate is often given as a constant, although sometimes it is a variable increasing through time. In the following models, we use "k" as the variable for the discount rate. For corporate purposes, there are various theoretical approaches to determine the rate's intrinsic value. For example, one approach suggests using the weighted average cost of capital of the firm and then adding a risk premium for the particular project to yield a discount rate.
9.7 Ascending Discount Rates: In capital budgeting spreadsheets, the discount rate used to discount the cash flows is sometimes a variable rate rather than a fixed rate for the entire period of the project. Typical of ascending rates is a constant increase through time (ascending) to reflect the increasing uncertainty about the cash flows actually materializing as forecasted. The assumption for the project may be something like: "The discount rate for this project, for this year (year 0), is 14%. However, with the increase in uncertainty about years 1 through 5, we're adding ¼% each year to the discount rate."
| |Year |0 |1 |2 |3 |4 |
| | | | | | | |
9.8 Discount Factors: Once the discount rates have been determined, the discount factors can be calculated using: discount factor = (1+k)-n , where "k" is the discount rate and "n" is the number of years hence. You'll recognize this from PV=FV(1+k)-n, where "FV" is the future value, or the future cash flow.
The spreadsheet formula format for the discount factor is: =(1+k)^(-n), where actual cell locations are substituted for k and n.
9.9 Present Values: Using the PV formula above, each year's cash flow is multiplied by its respective discount factor to get its present value. This step is applied to every year including "year 0".
9.10 Net Present Value: The present values for all the years (including year 0) are added together to get the "net present value (NPV)", or in other words, the "discounted cash flow (DCF)".
9.11 Firm Value: The NPV is the current value of all the project’s cash flows, current and future. A firm's total net value increases by the NPV of a project when the firm commits to that project. This “increase” may seem counter-intuitive: a firm writes a check for $100K, committing itself to a new project, and suddenly the value of the firm goes up by $120K? Sure. The value of the project is $120K, and the firm is going to engage in the project.
9.12 Stock Price: This notion of increasing the value of the firm can be carried a little further. If the value of the firm goes up, then so too goes the stock price. They are, after all, directly proportional. The value of firm divided by number of shares equals stock price. Hence, the theoretical change in stock price for taking on a project will be the NPV of the project divided by the number of shares.
9.13 Project’s Rate of Return: The NPV relates to the rate of return of the project in the following way: If the discount rate (think "cost of money") is less than the return of the project, then the project will "make money". More technically, if the discount rate is less than the internal rate of return, then the NPV will be positive. And, conversely, if the discount rate is greater than the internal rate of return, then the NPV will be negative. Note also, that the discount rate is externally determined (e.g. by credit markets, and risk factors), whereas the project's rate of return is internally determined by the project's cash flows and discount rate. Finally, if the discount rate and the project’s rate of return are equal, then the project has no positive value and isn't worth doing. This will be reflected in an NPV=0 and is intuitively illustrated with the scenario of borrowing money at 20% and putting the money in a 20% project.
9.14 Internal Rate of Return (IRR): Twisting the preceding paragraph around a bit, we could say that the internal rate of return (IRR) on a project is that rate that when applied to the cash flow yields a net present value of zero. And that is the definition of IRR.
9.15 Modified Internal Rate of Return (IRR*): The IRR model referred to in the paragraph above has a short-coming. The IRR model assumes that cash flows generated by one project can be re-invested into a second project that will yield the same rate as the original cash-generating project – and this may not be a valid assumption. Thus, a modified internal rate of return model has been concocted to address this shortcoming.
9.16 Reinvestment Assumptions: The IRR* model assumes that the positive cash flows generated by a project are reinvested at the firm's discount rate to the end of the last year of the cash flow. This assumption differs from the reinvestment assumption of the ordinary IRR model, which assumes implicitly that reinvestment of the cash flows are made at the derived IRR.
9.17 IRR* Mechanics: In IRR*, the sum of the reinvested cash flows are an estimate of the future terminal value (TV) of the project (not counting the original investment). The terminal value is subsequently reevaluated to reflect its net present value, using a rate (the IRR*) that will discount the TV to an amount exactly equal to the original investment. By definition, the hypothetical discount rate which yields a net present value (NPV) of zero is the project's internal rate of return.
9.18 IRR* Model: To calculate IRR*, use the equation IRR*=[TV/|orig|]^(1/n) minus 1
Where TV=Terminal value; |orig|= the absolute value of the original investment; n=total number of years of the project (usually 5 yrs in this academic context).
1) Calculate the “reinvestment factors” for years 1,2,3,4 & 5 using (1+k)^(n-t) where k= the original discount rate, n=total number of years, and t= the year number (i.e. yr1=1, yr2=2, etc.).
2) Calculate the future values of the yearly cash flows by multiplying each year’s CF by its respective reinvestment factor.
3) Total the future values to get the “terminal value”.
4) Use IRR*=[TV/|orig|]^(1/n) minus 1 to get the answer.
9.19 Payback Method: The payback method in capital budgeting is a technique used to determine the date when a project is expected to payback the original invested funds. The payback date can be either based on undiscounted cash flows (i.e. the original cash flow estimates) or on discounted cash flows (the present value of the original cash flows). The timing of the cash flows, after year zero, are assumed to be "straight line" receipt of cash throughout the calendar year (as opposed to lump sum receipts at the end of each year, as assumed by the NPV/DCF and IRR* models).
Steps in calculating payback date:
1. Calculate "cumulative cash flows", that is, the net total of all cash flows as of the end of each year. Remember that the cash flows used in this calculation may be either the undiscounted or the discounted cash flows depending on the specifications of the problem.
2. Determine the "breakeven year" [BEYR]. It is the first year with a positive "cumulative cash flow".
3. Divide the "absolute value of the cumulative cash flow for the year before the breakeven year" by the cash flow for the breakeven year. This is the percentage of the year at which the project will breakeven.
4. Multiply the above percentage by 365 days to get the number of days into the BEYR that the project breaks even.
5. Look up that day on a "Julian calendar", where a Julian calendar numbers each day starting with January 1st through December 31st as day1,2,3…etc. through day 365.
9.20 Replacement Chain method: The Replacement Chain method is used to compare two mutually exclusive capital proposals of unequal lives. The result of applying the model to the projects is an NPV for each project for one or more iterations (the "links" of the chain) of each project. The model can be applied to more than two mutually exclusive projects, but the set-up becomes cumbersome and the Equivalent Annual Annuity method may be more appropriate.
Steps of Replacement Chain Method:
1) Determine the number of years of cash flow (the "project lives") for each project by finding the lowest common denominator (LCD) of all the "project lives". This LCD is the number of years for which each project’s cash flows must be recalculated.
2) For each project assume that during the last year of each iteration a reinvestment (equal to the original investment) must be made in order to generate positive cash flows for the following iteration. This is true for every iteration of the project except for the very last year when all projects reach a common terminal finish time. In the very last year there is no reinvestment for future cash flows, so the value of the cash flows of the very last year equals the cash flow of the last year of the original project.
3) Once the revised cash flows have been determined, generate the discount factors for those years. Calculate the present value for each cash flow. Calculate the Net Present Value for the project. Compare projects; choose that project with largest NPV.
9.21 Equivalent Annual Annuity (EAA): The EAA method is an alternative to the Replacement Chain method, for use in evaluating projects with unequal lives. The EAA model derives a new cash flow of the project that has the same financial value of the NPV, except that the dollar value of the EAA is for payments or benefits that are equally spread over the life of the project (an annuity). The annual annuity can be compared between projects, and the project with the highest annuity should be chosen over lower annuity.
Steps: Using the same equation as the "auto loan" payment equation, where PVa=Net Present Value of the project, K=discount rate, n=no of years, calculate PMT. In Excel, the formula could be
=(NPV*K)/(1-((1+K)^-n)), and where NPV, K and n are replaced with cell references. The resulting PMT is the benefit from the project (the EAA), spread out over the life of the project.
10. QUANTIFYING UNCERTAINTY
The Gaussian (or Normal) Distribution (or even sometimes the “bell curve”) is one of the more useful and oft used statistical tools in financial analysis and is the basis for measuring risk in stocks and bonds as well as assessing capital projects. The distribution is completely described (or, “fully specified”) with two variables – the expected value and the standard deviation. In a financial application, for example capital budgeting, the expected value might be the most likely dollar value of NPV and the standard deviation would be the measure of how tightly clustered around the expected value possible outcomes will fall. Note: In finance, the units of interest are usually dollars or returns. Other disciplines use other units.
10.1 The Curve and its Area: The bell curve sits above a horizontal axis. The axis represents a continuum of all possible outcomes, ranging from negative infinity through to positive infinity. The distribution is bilaterally symmetrical (by definition) such that a vertical line drawn exactly down through the middle of the curve, the center-line(CL), creates two equally sized areas: the area to the left of a center line which is 50% of the total area under the curve, and the area to the right which is also 50%. These two areas illustrate the notion that there is a 50% probability of getting an outcome less than the expected value, and a 50% probability of getting more than the expected value from this project. The percentage of the area of interest relative to the total area under the curve is a surrogate for the probability of getting an outcome between the right and left bounds of the area of interest.
10.2 Application: Here is a typical application of the Normal Distribution applied to a capital budgeting project. Assume that a capital project has an expected NPV of $100K with a standard deviation of $20K. What is the probability of getting a NPV from the project of more than $75K?
Here are the steps required to answer the question:
10.3 Draw the curve. Being able to visualize the different areas beneath the curve is helpful in determining the total area of probability. In this example, one might start by drawing a horizontal line (the continuum of outcomes), then drawing the curve such that the high point (the hump) crests at the expected value, and then drawing the vertical center line down through the hump and through the continuum, passing through the expected value. Note that on the extreme right and left sides the continuum line is assumed to continue forever and that the line of the curve gets closer and closer but never quite touches the horizontal continuum of outcomes. The lines are said to have an asymptotic relationship.
10.4 Draw the Line(s) of Interest”: The sample question (above) addresses probabilities of “more than $75K”. Therefore a vertical line at $75K will bound an area of interest- that is, the area under the curve and to right of the “line of interest”. The upper (right hand) bound is at positive infinity.
10.5 Label the Areas: Label the discrete areas (those areas bounded by a vertical line or infinity) from left to right. [Different questions with different parameters will have different area designations.] In this question, Area A (to the left of the $75K line) represents the probability of getting less than $75K from this project. Area B (between the $75K line and the center line) represents the probability of getting between $75K and $100K. Area C (to the right of the center line) represents the probability of getting more than $100K.
10.6 Calculate the Areas: Determining the size of an area is a 3-step process that can be summarized as: 1) calculate dollars, 2) convert dollars to z-score, 3) convert z-score to area.
1) Determine the distance in dollars on the continuum between the Center Line [@$100K] and the point/dollars of interest [@ $75K], or $100K-$75K=$25K;
2) Convert the dollars in step 1 to a z-score, or number of standard deviations. The calculation is “the continuum length of interest in dollars divided by one standard deviation (in dollars)”; $25K/$20K = 1.25
3) Find the area related to the z-score. While there are several methods to convert z-score to area, let’s use the MS Excel function “=NORMSDIST(z-score)-.5”. In this example: “=NORMSDIST(1.25)-.5” yields an area of .3944. This is the area, i.e. the probability, of getting an outcome between $75K and $100K. The probability of getting over $100K is 50% (.5000) by definition, plus the probability of getting between $75K and $100K is 39.44%, so the probability of getting over $75K is 89.44%.
10.7 Finding Areas (alternatives): There are three popular methods of determining area, given a z-score: 1) Using an MSExcel function, 2) using a table, and 3) using a TI-83 calculator.
1) NORMSDIST(z-score), the Excel function, returns the area from the far left (negative infinity) to a vertical line (the vertical line of interest). Positive z-scores are assumed to be that number of standard deviations to the right of the center line, such that any positive z-score inserted into this Excel function will return an area that includes all the area to the left of the center line (.5000) plus the area from the center line to the vertical line of interest. Negative z-scores are assumed be measured to the left of the center line, such that NORMSDIST(z-score) will return an area that is less than .5000.
2) Table: There is a z-score table available at . Use the left-hand column to look up z-scores given to one place past the decimal. View to the right (in the same row) to narrow the z-score to two places past the decimal. Caveat: Areas shown in the table relate to positive standard deviations and areas adjacent to the center line.
3) TI – 83: Use [2nd] function key, plus [VARS] key to get a menu. Cursor down to the 2nd option. Enter. This yields “Normalcdf(… “. The (lowerlimit, upperlimit) of the area of interest are entered into the parenthesis as z-scores, where the z-score of the centerline is 0.00 and the z-score of infinity is expressed in engineering notation as “ 1E99” [Note: [2nd] + [EE]= “E”]
11. FINANCIAL PLANNING
11.1 The Process: “The Plan is nothing. Planning is everything.” [IBM Planning Workshop, 1980].The preceding quote suggests that it’s the planning process itself that provides the true value to the firm, and that the resulting document is a less significant tool. The truth of that statement may be subject to debate, but the point is valid, for in the process of putting the numbers together for a financial plan, managers must consider how their respective departments actually operate and what their relationship is with the rest of the firm.
11.2 The Plan: The process of financial planning is generally performed every year towards the end of the firm’s accounting year. The final plan is an estimated income statement and balance sheet that can be used as a benchmark, or budget, for control purposes throughout the following year. In order for a financial analyst to put a plan together, he or she needs the basic accounting knowledge of the nature of the various accounts on the income statement and balance sheet. It is good form for a financial analyst to explicitly state the various assumptions about the future that gave rise to the numbers in the plan.
11.3 Plan Approval: Once the plan has been put together by financial analysts, it is checked for internal consistency, and reviewed by management. The plan then creeps up the chain of command and is presented to the board of directors for approval.
11.4 The Mechanics: The following describe the assumptions that go into the “Quick & Dirty Financial Planning Model”. Some of the data are referred to as “old”, meaning they are the “actual”, or “historical” data from the previous year’s statements. “New” numbers are the “plan” numbers, or estimates for the following year.
11.5 THE PLAN INCOME STATEMENT:
1. Sales: The plan sales figure is given.
2. Variable: By definition, variable costs tend to be directly proportional to sales. Divide old Variable Costs by old Sales, multiply the results by plan Sales to get plan Variable Costs.
3. Gross Profit: Gross Profit is a residual – that is, Gross Profit is what remains after Variable costs are subtracted from Sales. Subtract plan Variable costs from plan Sales.
4. Fixed: Fixed costs are fixed. That is, fixed costs tend to stay constant from year to year. The reality is that fixed costs do change from period to period, and they are only called “fixed” to distinguish them from those costs that vary (the variable costs) according to sales. Use the same Fixed costs in the plan as in the previous year.
5. EBIT: Earnings Before Interest and Taxes [a.k.a. Operating Profit] is a residual. Subtract plan Fixed costs from plan Gross Profit.
6. Interest: Interest on the firm’s debt (raised through a bond issue) is a fixed percentage of the outstanding Debt. If the firm’s Debt doesn’t change, neither will the Interest payments. In this planning exercise, we assume that there is “no change in capital structure”, which means no change in debt and no change in interest.
7. Income Before Taxes: Inc B/T is a residual. Subtract Interest from EBIT.
8. Taxes: A quick estimate of plan Taxes is found by calculating the effective tax rate from the previous year (taxes divided by Inc B/T), and applying [multiplying] the old tax rate to the plan Income Before Tax.
9. Income After Tax: Inc A/T is a residual. Subtract plan Taxes from plan Inc B/T.
10: Preferred Dividends: Preferred is similar to Interest in that it’s a fixed percentage of the total value of the Preferred Stock. If the value of the Preferred stock doesn’t change from year to year, neither will the Preferred Dividends.
2. Earnings Available to Common (EAC): EAC is the “bottom line”, a.k.a. Net Income or Net Profits. The calculation is a residual – subtract Preferred from Income After Taxes.
12. Dividends: Dividends on Common Stock are paid after EAC. The amount paid is discretionary. For planning purposes an analyst might assume that dividends stay the same from year to year (See also 14.7a) or might assume that the payout ratio remains the same (See also 14.7b).
13. Change in Retained Earnings: EAC is separated into two pieces, Dividends and Retained Earnings. Dollars from EAC not paid out as Dividends are folded back into the firm as Change in Retained Earnings.
11.6 THE PLAN BALANCE SHEET:
14. Retained Earnings: Plan (cumulative) Retained Earnings are the sum of the old cumulative Retained Earnings plus the plan Change in Retained Earnings (from the income statement).
15. Common Stock: Assume that the value of Common Stock does not change from year to year.
16. Preferred Stock: Assume that the value of Preferred Stock does not change from year to year.
17. Debt: Assume that the value of Debt does not change from year to year.
18. Accounts Payable: Acct/Pay are a fixed percentage of purchases from year to year. To calculate, divide the old Acct/Pay by the sum of the old Variable and old Fixed costs. Take the results (a decimal) and multiply it by the sum of the plan Variable and plan Fixed costs.
19. Total Liabilities and Equity: Total Liabilities and Equity is the sum of the components, i.e. Acct/Pay through Retained Earnings.
20. Total Assets: Plan Total Assets equal plan Total Liabilities and Equity.
21. Property, Plant and Equipment: PP&E, the fixed assets, can be assumed to be constant from actual to plan year.
22. Inventory: Inventory is a fixed percentage of Sales. Divide the old Inventory by old Sales. Multiply the resulting percentage by plan Sales to get plan Inventory.
23. Accounts Receivable: Acct/Rec is also a fixed percentage of Sales. Divide the old Acct/Rec by old Sales. Multiply the resulting percentage by plan Sales to get plan Acct/Rec.
24. Cash: Plan Cash is a residual in this model. Subtract the sum of Acct/Rec, Inventory, and PP&E from Total Assets to get Cash.
12. COST OF CAPITAL
Raising capital for the financing of the firm is one of the major functions of the financial manager. The cost of that capital to the firm is a both a considerable expense to the firm as well as a significant value against which other financial decisions are made. Understanding how to measure accurately the cost of capital therefore becomes a critically valuable skill.
12.1 Cost as a percentage: Whereas “costs” are often measured in dollars, in the “cost of capital” context, costs are measured as a percentage of the capital raised. For example, if the interest expense on a $1M loan is $60K per year, then in a “cost of capital” context, the costs are expressed as “6%”. Comparisons are often made between the cost of capital to the firm and rates of return on their projects. That is, if new capital for a project will cost the firm 10%, then that project must have an internal rate of return of more than 10%, the so called “hurdle rate”, in order to add value to the firm.
12.2 Sources of Capital: There are four major sources of capital to the firm: Debt is often the least expensive source and is raised through issuing bonds. Preferred stock is similar to debt in that it creates fixed payments and is relatively low in cost to the firm as it is low in risk to the investor – second to debt. Common stock is the highest risk to the investor and consequently the highest cost to the firm. Retained Earnings is the fourth source, sometimes called “internally generated capitalization”. Each source has its own set of risks to the investor, obligations to the firm, and costs to the firm.
12.2 Future versus Historical Costs: The calculation of cost of capital may be from the perspective of looking forward to the costs of new capital yet to be raised (the Marginal Cost of Capital, MCC) or looking backward on the exiting capital that has already been raised (the Weighted Average Cost of Capital, WACC). As different sources have different costs, the financial manager looking forward to raising additional capital must know how to calculate the effective future cost of each source. And the financial manager looking at the existing capital structure of the firm needs to be able to calculate the cost of capital that has been raised up to the current point in time.
12.3 Concept of Costs: There exist a general overall approach, or concept, that guides the calculation of costs that applies to all sources. That is, the cost of capital to the firm is similar to the rate of return to the investor. The difference is that costs (to the firm) are slightly higher than returns (to the investor) because of a third party intermediary (e.g. the investment banker) who receives a transaction cost that is born by the firm. The attractiveness of this conceptual approach is that traditional models that are used to calculate returns on stocks and bonds can also be used to calculate costs of capital to the firm.
12.4 Transaction costs: The difference between cost to the firm and return to the investor is the transaction costs charged by the financial intermediary, traditionally an investment bank, who handles the issuing of the bonds or stock. In reality, these transaction costs are negotiated between the firm and the investment bank and the structure of the transaction costs can be complex with a combination of fixed costs, variable costs, discounts and premiums. As an academic exercise, transaction costs can be illustrated more simply as either a dollar cost per instrument (e.g. $3 per bond) or as a percentage of the retail price of the instrument (e.g. 2% of stock price). The model “street minus transaction costs equals net received by the firm” holds true using either dollar cost or percentage cost. For example, an investor pays $1000 “on the street”, the investment bank that sold the bond takes a $3 transaction fee and forwards the remainder, $997, to the firm. Or the sale of a share of stock at $60 price per share, less a 2% transaction fee, leaves 98%, or $58.80 net received by the firm.
12.5 Adjusting for Taxes: The actual interest payments made by the firm to the bondholders are subtracted from the Operating Profit prior to the calculation of Income Before Taxes. The impact of this accounting is that Income Before Taxes is reduced from what it would have been without the Interest payments. As Income Before Taxes is reduced, so too are the actual Taxes. This reduction in Taxes amounts to an IRS “subsidy” (loophole?) and directly reduces the effective cost of debt to the firm. A model for calculating the impact of this subsidy is: BeforeTaxCost x (1 – TaxRate) = AfterTaxCost
For example, assume a BeforeTaxCost$ of Interest is $40K and the tax rate is 30%. The effective AfterTaxCost$ would be $28K. This model also works for cost of debt, as a percentage, as in this second example: Assume a BeforeTaxCost% of Interest of 8% and a tax rate of 30%. The effective AfterTaxCost% would be 5.6%. This model only applies to Debt, not to Preferred, Common or Retained Earnings, as Debt is the only instrument that enjoys this tax advantage.
12.6 Cost of Debt: Raising capital through debt (i.e. through a bond offering) has the lowest cost of the four sources because of the low risk to the investor and because of the tax advantage of interest payments relative to other sources. The calculation of cost of debt uses the same valuation models used to calculate return to the investor, i.e. Value of the Bond (Vb), and Rodriguez’s Model, except that the closing price of the bond is first adjusted to reflect “net to the firm” instead of the original “street” price. The Vb and Rodriguez’s models calculate the before tax cost of debt, whereas many applications require an after tax cost of debt. In these latter cases, the cost of debt is adjusted consistent with 12.5 above.
12.7 Cost of Preferred: The calculation of the cost of preferred stock is similar to the calculation of the other components in that the process uses a model for the rate of return to the investor with an adjustment for transaction costs. In this case, the investor return model is from Gordon’s suite of models; it’s the one for Preferred, Kpr=D/P. Caveats: 1. The D in the model represents annual dividends, whereas the dividends are often originally expressed as quarterly dividends. 2. P is the net price received by the firm.
12.8 Cost of Common (Ke): The calculation for the cost of common similarly uses the variation of Gordon’s Model that addresses common stock returns, Ke=(D1/P)+g where Ke is the cost of common (i.e. the cost of equity). D1 is next year’s dividends. Caveats: 1. D1 must be calculated by taking last year’s dividends (D0) and increasing them by the annual growth rate in dividends (g), or D1=D0 x (1+g).
2. The price per share of the stock, must be adjusted to reflect net to the firm by subtracting the transaction costs from the street price. 3. The same notation, “Ke”, is often used for both “return to the investor” and “cost to the firm”- yet those two values are slightly different. Therefore, be sensitive to the context in which you use the notation.
12.9 Cost of Retained Earnings (Kre): The calculation of the cost of Retained Earnings (Kre) is similar, but not identical, to the cost of Common. The assumption with Retained Earnings is that capital retained by the firm essentially belongs to the shareholders and therefore the appropriate “cost” to the firm for using those funds is exactly equal to whatever the common stockholders’ risk adjusted required rate of return is. In order to calculate Kre, use Gordon’s Model for common (as in the previous section) except do NOT subtract the transaction costs from the price of the stock.
12.10 Weighted Average Cost of Capital (WACC): The overall cost of capital previously raised (the historical perspective) uses values from the firm’s balance sheet to determine the appropriate “weight” to give each source of funding. That is, the “costs” are “weighted” according to the amount of dollars in each of the four sources, Debt, Preferred, Common and Retained Earnings as shown on the most recent balance sheet. The model WACC=ΣKiWi is read: WACC equals the sum of the individual component costs multiplied by the individual component weights.
12.11 Sample WAAC Problem:
1. Calculate the component costs of Debt, Preferred, Common, and Retained Earnings, given:
a. bonds: XYZ 12s2021 close @ 120
b. preferred: pays $1.25 div/qtr, closed @ $41.00/share
c. common: div (ttm) =$6.00, expected growth in div=5% closed @ $48.00
d. effective corporate tax rate: from the Income Statement (Tax Rate = Taxes / Inc B/T)
e. transaction costs : $4 / Bond; $1 /share for Preferred; 2% Common
12.12 Component Calculations:
a) Calculate Before tax cost of Debt, using Rodriguez's Model, where
PMTS = 12% of Face Value = $120
Face Value = $1000
Street Price = 120% of Face Value, or 1.20 x $1000 = $1200
Vb = Net to the Firm= Street price - transaction costs, or $1200 - 4 = 1196
n=no of yrs to maturity = 2021-2013 = 8 Income Statement
Thus, Kdb (B/T) = .0845 Inc B/Tax $91,000
and "Before Tax Cost" x (1- tax rate)= "After Tax Cost", Taxes 27,300
where "Tax Rate"= Taxes / Income Before Tax tax rate = .30
So, Kdb (A/T), cost of debt after taxes = .0591
b) Calculate cost of Preferred, using Gordon's Model, where
D = (1.25 x 4 qtrs) = $ 5.00 / yr
and Street Price = 41.00, trans = 1.00, Net to firm = 40.00
So, D/P, cost of Preferred = .1250
c) Calculate cost of Common, using Gordon's Model, where
D1 = D0 (1 + g), or, D1 = (6.00) x (1 + .05) = $ 6.30
and Street Price = 48.00, trans =2%, so "Net to firm" = 48 x .98 = 47.04
So, Ke, cost of common = .1839
d) Calculate the cost of Retained Earnings, using Gordon's Model,
D1 = D0 (1 + g), or, D1 = (6.00) (1 + .05) = $ 6.30
and Street Price = 48.00
So, Kre, cost of Retained Earnings = .1813
12.13 Calculate the WACC: Use the values from the balance sheet and the shortcut kiwi method:
a) $ cost (K) extension
debt 513,000 x .0591 = 30318
pref 234,000 x .1250 = 29250
comm. 122,000 x .1839 = 22436
ret earng 600,000 x .1813 = 108780
total $1,469,000 190784
b) 190784/1469000 = .1299 WACC = 12.99%
13. LEVERAGE MODELS
The notion of financial leverage is comparable to mechanical leverage in that in both contexts one end of the lever behaves as a multiple of the opposite end of the lever. In finance, a firm can realize growth in earnings many times larger than the growth in revenue through leverage – and this leverage is achieved by shifting variable costs to fixed costs. Fixed costs on the income statement are roughly analogous to the fulcrum in a physical lever in that the relative placement of the fulcrum and the relative proportion of fixed costs to variable costs determine the degree of leverage experienced.
13.1 Degree of Operating Leverage (DOL): The income statement is often divided in two parts. The top portion, the operations portion, from Sales down through Operating Profit (or Earnings Before Interest and Taxes (EBIT)) represents the earnings from “what the firm does”. The bottom line of this portion can be enhanced (leveraged) by increasing the fixed costs relative to variable costs. This operating leverage is defined as percent change in EBIT for a one percent change in Sales and is calculated by dividing the percent change in EBIT by the percent change in Sales. An alternative model for calculating the Degree of Operating Leverage (DOL) is to divide Gross Profit by EBIT. The alternative model is used when only a single period of financials is available and percent change cannot be determined.
13.2 Degree of Financial Leverage (DFL): The bottom portion of the Income Statement is the financial portion, from EBIT through Net Income (or Earnings Available to Common (EAC)). By definition, the Degree of Financial Leverage (DFL) is the percent change in EAC for a one percent change in EBIT and is calculated by taking the percent change in EAC divided by the percent change in EBIT. The significant fixed cost that drives the DFL is the Interest expense on bonds, and thus a “highly leveraged firm” is a firm that has significant debt. The alternative model of calculating DFL (using one period of financials) is given as follows:
DFL= EBIT / [EBIT – {Interest + (Preferred Dividends/(1-tax rate))}]
13.3 Degree of Total Leverage (DTL): The Total Leverage of the firm is the percent change in EAC for a one percent change in Sales and is calculated by taking the “percent change in EAC divided by percent change in Sales”. The alternative calculation is to multiply DOL by DFL. In times of rising sales, it is advantageous to be highly levered – in times of declining sales, the financial advantage belongs to firms with minimal leverage (i.e. minimal fixed costs, minimal debt). A synonymous term for “leverage”, appearing more in British finance, is “gearing”, as in “a highly geared firm”. All these terms (fixed costs, bonds, debt, leverage, gearing) have similar implications for the financials of the firm and suggest higher risk that is often rewarded in higher profitability.
13.4 Captal Structure Decision: One of the major issues in financial management [See also: 1.8 and 1.9 above] is how to raise capital in the way that maximizes the share price of the firm. A firm raises capital through the issue of new equity or new debt and each source has its advantages and disadvantages. Issuing debt increases the fixed costs of the firm but is generally cheaper than raising equity, while issuing equity dilutes the shares of the existing shareholders, but adds negligible reoccurring maintenance costs carrying the weight of a legal obligation. And while there are rules of thumb about when to do one and when to do the other, there is no single outstanding model that offers a robust theoretical justification for its use.
13.5 Modigliani and Miller (MM): Merton Miller famously conducted empirical research to determine how corporate financial structure affected firms' stock values, but found no particular relationship between the wide range of financial structures (as measured by the debt to equity ratio) and stock value. He then teamed up with Franco Modigliani to explain this lack of correlation. Together they refined an economic argument, now known as the MM Theorem, that rationalized the lack of correlation between structure and value in a rarified economic environment (no transaction cost, no taxes, no asymmetrical information). A variation of their pure model, one that accounts for taxes, does show a straight line increase in value as the debt to total assets ratio increases, but this model has major flaws, has been grossly misrepresented, and erroneously implies that a firm should borrow as much as they can get their hands on. For the student, the lesson is: Capital structure is an important issue, but no single model exists for determining what a perfect capital structure should look like.
14. DIVIDEND POLICY
14.1 The Dividend Decision: One of the major corporate financial decision areas is the dividend decision, or “how much of after tax earnings should be paid out to stockholders in the form of dividends”. [See also 1.8 and 1.11]. The criteria for determining dividends should be, in theory, "what policy will maximize the stock price". And while there are some legal constraints regarding the magnitude of a dividend payout, the decision otherwise lies wholly with the board of directors.
14.2 Accounting: Quarterly Earnings Available to Common (EAC) is split into Common Dividends and Retained Earnings. That is, funds not paid out as dividends are folded back into the firm as Change in Retained Earnings (ΔRE). The ratio of Dividends to EAC is the “payout ratio”. The ratio of ΔRE to EAC is the “retention rate. The dividends may represent a significant contribution to an investor’s total overall return from owning the stock, and Retained Earnings can represent a significant source of funding for the growth of the firm (sometimes called “internally generated capitalization”).
14.3 Modigliani & Miller (MM): MM addressed the dividend decision from the perspective similar to their approach to the capital structure decision – that is, from a strictly academic/theoretical viewpoint. Their assumptions regarding transaction costs, taxes, and other market “frictions” were equally rarefied and their conclusion was that the magnitude of dividends is irrelevant to the determination of stock price, or more bluntly, that dividend policy doesn’t matter. They note that stock price is more a function of return on assets (ROA), or the “basic earning power” of the firm. They do acknowledge that changes in dividend policy may send ambiguous messages to stockholders about the future of the firm, the “signaling effect”, which may, in turn, create changes in demand for the stock. They also acknowledge that changes in dividend policy may alter the market for the stock – a phenomenon elaborated upon in their “clientele theory”.
14.4 Gordon & Lintner: Gordon and Lintner base their theory of the significance of dividends on Gordon’s discounted dividend model, P0=D1/(ke-g), which implies that dividends are the sole determinant of stock price. Mathematically, changes in stock prices would be directly proportional to changes in dividends. Their theoretical justification for this opinion is liquidity preference, that is, investors prefer having dividends paid to them now, rather than seeing funds folded back into the firm in hopes of higher future rewards. MM responded by calling this argument a "bird in the hand theory" after the proverb “a bird in the hand is worth two in the bush”.
14.5 Empirical Test: Harkavy conducted a traditional event study in which certain events are compared to actual changes in stock prices in order to observe any possible causal relationship between two variables. In his empirical test he compared changes in payout ratios, the independent variable, to changes in stocks price, the dependent variable, and found a slight positive correlation between the two ratios. Both MM and Gordon & Lintner claimed that Hakavy's study lent credibility to their respective competing theories – MM asserting that the correlation was de minimus and Gordon & Lintner claiming that the positive correlation in the data was consistent with their position.
14.6 Theory of Residuals: Walters suggested a normative model that posited that firms should use Earnings Available to Common shareholders (EAC) to fund projects whose Internal Rate of Return (IRR) are expected to be greater than the stockholders required rate of return. The theory was that a firm’s EAC should be reinvested at the highest available IRR, regardless of whether that IRR is available internally or with the investors. Funds that are not expended on these high IRR projects should be paid out to stockholders in the form of dividends.
14.7 Realworld models: The theoretical models above suggest what should be done. The realworld models below describe what firms actually do. Here is a sample of various dividend policies:
a. Constant dollars: The firm pays out same total dollar amount every quarter regardless of the Net Income available.
b. Constant dollars with a “kicker”: Although the total dollar value of the firm’s quarterly dividends generally stay constant, occasionally the firm will add an unanticipated extra amount, a bonus or “kicker”.
c. Constant increases: Some firms take pride in increasing the dollar amount of their dividends every quarter.
d. Constant payout ratio: Other firms attempt to hold to a steady payout ratio (div/EAC) each quarter. See also 14.2 above.
e. Custom Policies: An example of a custom policy is the one time dividend policy of the Norton Company, a closely held, but technically a publically traded firm. During a time of double digit inflation (late 1970’s), their board of directors, many of whom were descendants of the founding family, decided to match dividend increases to general price increases as measured by the Consumer Price Index (CPI). In this way, shareholders could see the effective buying power of their dividends stay constant in spite of the rising cost of living.
14.8 End Note: Dividend policy is a fitting end to a course in financial management, remembering that the function of finance in the firm is to “get the money” (capitalize the firm through stocks and bonds), “invest the money” (in capital projects that will yield a robust return), and “divvy up” the rewards (by distributing dividends to the owners).
15. EXERCISES
The following exercises are designed to provide the opportunity to express various financial models in a spreadsheet medium. There is a common format in the exercises whereby the student does the following:
1) Read about the model in Sketches to gain a conceptual understanding of the exercise.
2) Download the sample spreadsheet that contains a template for your work. The sample is complete with input and output values, but the Excel formulas that created the output values have been “masked”.
3) Save the file to a personal local drive of your choice. This drive might be a USB flash drive, a hard drive on your PC, or perhaps your PSU M: Drive. Change the name of your saved file by using the “Save As” operation. You might name it johndoe1, using your preferred name – but you can call it whatever you want. You may save it in the contemporary .xlsx format, or the old .xls format, but it must be compatible with Microsoft Excel.
4) Your challenge is to recreate the correct formulas, in the appropriate cells, such that your formulas generate the exact same output values as in the original sample.
5) After generating the correct formulas, you are often (but not always) asked to change the input variables as directed in the exercise instructions. The outputs will change automatically according to the formulas that you had generated in the previous step.
6) Save the file (with the updated input variables) to a local file for your records using the filename of your choice and in either “.xls” or “.xlsx” format.
7) Submit your file to the Moodle2 course page, either as a “draft” or “submit for grading”. The draft mode allows you to re-submit a new file later if you have some afterthoughts. The “submit for grading” option prevents you from submitting any additional (updated) files. The instructor can change a file to a draft, by request from the student. After the due date of the assignment, drafts and final submits will be graded.
Some other protocols include:
Enter your name in cell A1.
Place cursor in A1 immediately before your final save.
Do not alter the original formatting of the spreadsheet.
Add a note or comment to your submission. The comments assures the instructor that the exercises are being submitted by real people and not some robot in an online mill. And if you give the instructor substantive feedback, they are more likely to reciprocate.
If you have questions about an exercise, address them to the Moodle forum for the appropriate week. If you read a colleague’s question on the forum and think that you can help answer the question, please reply. Part of the learning process is in creating well-structured questions and answers. Students and instructors alike benefit from participating in the weekly forums.
Exercise1: Excel & Moodle2 practice
This exercise provides the opportunity to practice some basic spreadsheet operations and to submit an exercise to Moodle2.
Steps:
1. Open sample1.xls on the Moodle2 course page.
2. Save the file to a personal local drive of your choice.
3. In cell A1, enter your name.
4. In C3, write a formula that will increase the Sales for 2012 (B3) by the growth rate shown in D8. For example “=B3+(B3*D8)” or “=B3*(1+D8)”. After entering the formula your new C3 should still display “5600”.
5. In the C3 formula, add a “$” in front of the “D” in order to hold the column constant when you next copy C3 into D3. That is, “=B3+(B3*$D8)”.
6. Copy C3 to D3.
6. In B4, write a formula that will calculate costs as a percent of B3, where the percent is given in D9. That is, “=B3*$D9”.
7. In B5, write a formula that calculates profits as the difference between sales and costs.
8. Copy cells B4 & B5 into C4, C5, D4,& D5. You should be comfortable copying the two cells (as a single block of cells) into the four cells rather than copying the cells individually.
9. Change the data: Change Sales for 2012 to 5500. Change the sales growth rate to 14%, and the “costs (as % of sales)” to .30.
10. Save your working file.
11. Submit your file to Moodle2. Add a note to your submission.
Exercise2 Ratio Analysis
This exercise calculates some common ratios used in the analysis of the financial health of a firm. Starting with an actual analysis from previous years, students write the formulas that will match the displayed results in the sample. Then, updated income statements and balance sheets are entered and new ratios are automatically calculated.
Steps:
1) Open sample2.xls. Save it to your local drive.
2) Regard IBM's income statement and balance sheet from 2008, at:
These are also available directly from the Moodle course page.
These were copied from Yahoo, for the year ending 31Dec08 and the prior year. The data from these IBM statements have been retyped into the tabs “income” and “balance” at the bottom of sample2.xls. The tab “ratios” shows the results of doing a ratio analysis on these data.
3) Assume that you are a financial analyst updating this ratio analysis. Start by getting the latest data on IBM from “”>”Get Quotes” for “IBM”>”Income Statement” & “Balance Sheet” These are found in the left column of the Yahoo page, down toward the bottom. Note: IBM’s accounting year ends on December 31st. Therefore you should find and use the financial statements for years ending 2013 and 2012. Type the data from Yahoo into the tabs “income” and “balance” in to your working file.
4) Also regard IBM's current market data from finance.. This data is found on the market page that comes up (as in step 3 above) when you type “IBM” into the “Get Quotes” box. Try to do this step during the hours when the market is open (9:30AM to 4:00PM) so that your data will be unique for the actual time that you record the data.
5) Get the price per share (pps), the big bold number on the market page, directly from Yahoo. Enter the pps into cell B13.
6) Get "Mkt Cap" and enter it into cell B14. Note: Yahoo generally reports Mkt Cap in billions of dollars. You should enter the value into B14 in millions by moving the decimal three places to the right.
7) Get the Earnings per Share (EPS) data and enter it into cell B15. Overwrite the date and time that you downloaded this data into the appropriate cells (see the old dates?).
8) As you update the formulas for the various ratios (1 through 10), use Excel formulas and cell references (not values). For example use “=B7/F5”, rather than typing in the actual numbers
Excel Hint: When working in the “RATIO” tab, for example in cell B3, and you want to write a formula using data in the “BALANCE” tab, the format is “=balance!B8/balance!F6”
9) Notice column E in the ratio tab. These are brief comments regarding the change in the ratios and whether they are going in a favorable direction. To say that a value is getting bigger or smaller is not saying whether the firm is getting better or worse in the particular area that the ratio measures. Change the comments to reflect the changes from 2012 to 2013. Your comments will be the real “analysis”.
10) Put your name in cell A1 of the first tab, and ensure that A1 is selected when you make the final save of your file.
11) Submit your file to Moodle with your comments.
Exercise3: US Federal Tax Model for Individuals
This exercise uses the current model for calculating Federal taxes for an individual. While its format of this exercise is more simplified than the IRS Form 1040 and the rate table is becoming dated, the logic is the same as current tax rules. The New Hampshire tax model is also included.
Steps:
1. Open sample3.xls.
2. Save the file to a local drive. Enter your name in cell A1.
3. In cell C4, enter the formula to calculate the total gross “ordinary income” (wages, interest, & dividends).
4. Set C8 equal to C3.
5. Set C9 equal to Total Gross Ordinary Income minus (personal exemption, standard deduction and dividends). This is the amount that is taxed using the tax table.
6. In C11, calculate the tax due on the first $8700 earned.
7. In C12, calculate the tax on the amount between $8700 and $35,350.
8. In C13, calculate the tax on the amount between $35,350 and $85650.
9. In D14, calculate the amount between $85650 and the amount shown in C9. This is the “residual” for this exercise.
10. In C15, calculate the tax on the residual.
11. In C16, calculate the tax on dividends shown in C3.
12. In C17, calculate the total tax on ordinary income (sum of C11 thru C16).
13. In C18, calculate the tax on capital gains (C5).
14. In C19, calculate the total Federal taxes due on ordinary income and capital gains.
15. In C20, enter the marginal tax rate.
16. In C21, enter the effective tax rate paid.
17. In C22, calculate the amount of NH tax due.
18. Make the following changes:
Wages, C1=90,000
Interest, C2=10,000
Dividends, C3=15,000
19. Save your file, submit to Moodle2, include a “note”.
Exercise4: Capital Asset Pricing Model (CAPM)
This exercise regards the semantics and calculations related to CAPM, including the input variables, outputs and graphical representation.
Steps:
1. Open sample4.xls. Save it to a local working file.
2. In cell B7 enter the formula as shown in A3, except use Excel formatting and cell references. For example, instead of “Krf”, enter “B4”. The results of your formula in B7 should match the value shown in the original sample4 file. Note: You’ll need to add a multiplication sign [*] in the Excel formula that is only implied in the algebraic format.
3. Edit the new formula by adding “$” in front of each row number (e.g. B$4). This will hold those rows constant as you copy the formula to the cells below as described in the following steps.
4. Copy the formula in B7 to B13. Edit the formula (created in the steps above), changing the beta reference from B$5 to A13.
5. Copy the new edited formula in B13 down through B22.
6. Change the data inB4, B5 and B6 to reflect the following:
Change LIBOR (Krf) to 4%, change the beta of the security to 1.15 and change the expected returns on the S&P500 (Kmkt) to 13 %.
The line in the chart in the sample file should shift slightly when you make these changes.
7. Save your file.
8. Submit your working file to Moodle2. Add a note to your submission.
EXERCISE 5 Tiers 1,2, & 3
Using actual market stock price data, build a portfolio and calculate the risk and returns on the individual stocks and the portfolio. [Note: Cell references are from 27 February 2013]
Steps:
1. Download sample5.xls. Save as a local file. Regard the given data. It represents the daily closing price per share (pps) of several stocks in a real portfolio. The block of data with the dates and the pps are called “tier1” for this exercise. Also, the column on the far right (^GSPC) is the S&P500 Index for those days.
2. Build another tier (“tier2”) immediately below tier1. Start by copying all the dates from tier1 to tier2.
3. The cell content in tier2 will be "Market value", or "No. of shares multiplied by closing price". To calculate these market values (for every stock and every day), start in upper left cell for the first firm, for the first day, in tier2. [on or about cell C255] Multiply “No of shares” (C3) times first day’s close (C4). In your formula, set “No of Shares” to be absolute, as in =C$3*C4. Copy this formula all the way across (for every firm) [C255:AH255] and all the way down (for every day) in tier2 [C255:C505].
4. In column B, tier2, [B255] calculate the total portfolio market value. This is the sum of firms’ market values. Use the =SUM(C255:AH255) function in the daily portfolio cells to calculate the daily totals. Also, copy all the S&P data “as is” from Tier1 Col AI, to Tier2 Col AI.
5. Build another tier directly below tier2. Start by copying all the dates again to A506:A756.
6. The cell content in tier3 will be “daily returns” (as a percent) based on the change in pps for every firm for every day (except the first day). Daily returns for the portfolio column are calculated the same as with the individual firms. Start by building a formula in the cell for the second day of the portfolio[B507]. Use the basic model of (new mkt value-old mkt value)/old mkt value. [=(B256-B255)/B255] [What happens if you neglect to include parenthesis in your formula?]
7. After building the initial daily return formula for day2 for the portfolio, copy the formula for the remainder of the days (copying down) [to B756] and for all the firms and the S&P (copying across) to AH.
8. The last step is to calculate the risk and return data for the portfolio, for all the firms, and for the S&P500. In the first empty row [perhaps around 757?] at the bottom of your spreadsheet, and in Col. B, use the formula =STDEVP(B507:B756) to calculate the standard deviation of the daily returns of the portfolio (i.e the “risk”). In the next row down use “=(B505-B255)/B255” to calculate the overall annual return Call this row “TOTAL Returns”. Copy both of these formulas across all the firms and include the S&P500.
9. Save your file. Submit to Moodle2. Add a note.
EXERCISE 6 Valuation Concepts
This exercise requires the building of a single spreadsheet with multiple modules. Each module is a little valuation calculator for an assortment of financial instruments. Your task is to build formulas in the output cells so that when the inputs are changed, the outputs will change accordingly. Use the inputs and outputs in sample6.xls to check your formulas. Then replace the input with the new data (shown below in “New Inputs”) to get new outputs.
Steps:
1. Download sample6.xls. Save the file to a local drive.
2. Module1: Build an Excel formula (in cell C11) to calculate the future value of a lump sum, given an interest rate, length of time of investment, period of compounding, and initial (present)value of the lump sum. Remember FV=PV(1+k)n. Also change cells C9 and C10 to a formula to calculate the periodic rate (k) and the number of periods of compounding (n).
3. Module2: Build an Excel formula to calculate the present value of a lump sum, given an interest rate, length of time of investment, period of compounding, and future value of the lump sum. Remember PV=FV(1+k)-n.
4. Module3: Calculate in cell C27 the effective yield on a 6 month, $2,000 Certificate of Deposit (CD) with a quoted rate of 2.5% that pays interest every Friday at Noon.
5. Module4: Calculate the monthly payments on a typical auto loan, given that the amount of the loan is $20,000, the annual rate is 6%, and the length of the loan is 5 years.
Then, build a complete [for every period of the loan] Amortization Table, using the monthly payments (calculated above).
6. Change the inputs to the following:
1) B5 PV=15000
B6 No. of Periods/yr=12
B7 Annual rate=2.5%
B8 No of Yrs=6
2) B15 FV=1,000,000
B16 No of Periods/yr=52
B17 Annual Rate=6%
B18 No of Yrs=50
3) B25 No of Periods/yr=365
B26 Annual Rate=3.2%
4) B29 Loan Amt=250,000
B30 Rate = 4.25%
B31 Years=20 [Note: Extend your table down the appropriate number of additional rows in order to display the entire loan]
7. Save your changed file, submit changed file to Moodle2. Add a note.
Exercise7 Valuation of Bonds
The first module of this exercise calculates the value of bond (Vb) given various inputs. A second module in the spreadsheet uses the same model (Vb), except that it assumes that you already know the value of the bond (i.e. what its street price is), but that you want to calculate the bond’s effective yield (given that you may be paying a premium or discount for a fixed coupon payment).
Steps:
1. Download sample7.xls. Save locally. Insert your name into cell A1.
2. In the first module, note that the inputs are: coupon rate, years to maturity, and prevailing market rates for comparable securities.
3. Write the formulas into cells C8, C9 and C10 to calculate the present value of the coupon payments, the present value of the face value [assumed to be $1000 at maturity], and the sum of the those two, that is, the value of the bond (a.k.a. the “close”, or the “street price”).
4. Copy cells C8,C9 & C10 to C16,C17 & C18.
5. In the 2nd module (the lower block), use "trial and error" in "market rates" [cell B15] to find that rate that calculates the actual closing price [shown in B19]. That is, find a market rate that makes C18 equal to B19. [Here’s a hint to make the trial & error process a little easier: Go to Excel Options/ Advanced/ Editing Options/ uncheck the "After pressing Enter, move selection". Then "OK". This keeps your cursor on B15 while you try different effective market rates.]
6. Or for a more sophisticated approach [than step 4 above], use the Excel routine that does the trial and error for you. Try the “Data” tab, “What-If-Analysis”, “Goal Seek”, where “Set cell”=C18 “To value”=1200 [or whatever value is in B19], “by changing”=B15.
7. Now, change the following cells:
B5=.055
B6=14
B7=.032
B19=1300
8. Re-do step 5 [or step 6] to determine the new market rate in B15 for the new “close” in B19.
9. Save your file, submit to Moodle2. Add a note.
Exercise8 Valuation of Stocks
This exercise creates a spreadsheet that calculates the theoretical value (price) and expected returns of a couple of shares of stock according to Gordon’s Model. A couple of caveats: 1) Gordon’s model for Common uses D1 [next year’s dividends], not Do [last year’s dividends]. 2) Preferred stock dividends are shown as quarterly dividends, whereas the model uses annual dividends. Ensure that your formulas address both these issues.
Steps:
1. Download sample8.xls. Save a copy of the file to a local drive. Enter your name in cell A1.
2. In the first box, there is given an expected growth rate in dividends (g), last year's dividends (D0), and the common stock holders required rate of return (Ke). Enter the proper formula in C7 that yields the theoretical price per share as suggested by Gordon’s Model.
3. In the second box, growth and last dividends are given along with the actual observed price per share. Enter the formula to calculate the required rate of return in G6.
4. In the third box, cell C13, enter the formula for price per share of preferred stock, given quarterly dividends and the required rate of return.
5. In the fourth box, cell G12, enter the formula for required rate of return, given price per share of preferred stock and quarterly dividends.
6. Change the inputs as follows:
In box1: g=7.2%; D0=1.75; Ke=14%
In box2: g=7.2%; D0=1.95; pps=27.50
In box 3: div/qtr=$2.00; Kpr=5%
In box3: div/qtr=$1.00; Price=$50
7. Save a local copy. Submit a copy to Moodle2 along with a “Note”.
EXERCISE 9 Capital Budgeting NPV & IRR
This exercise creates spreadsheet that calculates the net present value (NPV) of a hypothetical project, estimates change in price per share (est Δpps) for the firm’s stock, and calculates the modified internal rate of return (IRR*) for the project.
Steps:
1. Open sample9.xls. Save the file to a local drive. Enter your name in cell A1.
2. In cells B5 through G5, calculate discount factors based on (1+K)-n where K is the discount factor in B2, and “n” is the “year number” shown in C3 through G3.
3. In cells B6 through G6, calculate the present value of the future cash flows by multiplying the cash flows in row 4 by its respective discount factor in row 5. [PV=FV(1+k)-n]
4. In B7, calculate the sum of the six years of discounted cash flows [use the “=sum()” Excel function.]
5. In B9, calculate the estimated change in stock price by dividing the project’s NPV [B7] by the number of shares [B8]. Remember: NPV is shown “in thousands”. Adjust your formula in B9 to change NPV to “whole dollars” before dividing by no. of shares.
6. Set C10 equal to B2. In C11 through G11, calculate the reinvestment factors, based on (1+K)n where K is the reinvestment rate (which equals the original discount rate), and “n” equals the number of years that the cash flow will be reinvested, that is, the number of years to the end of year [in this case] 5. This “no. of years of reinvestment” can be calculated by using “total years of project minus year of project”. For example, in C11, the “n” for “year1”, n=5-C3, or 5-1, or 4.
7. In Row 12 calculate the future values by multiplying each of the five positive cash flows (in row 4) by the reinvestment factors in Row11.
8. In call G13, calculate the terminal value (TV), which is the sum of the five reinvested cash flows.
9. G14 equals the absolute value of the original investment (in B4).
10. In G15, divide G13 by G14. Raise the results to the 1/5th [or .2]. Then subtract 1.00.
11. G16 is the same number, just reformatted to display percentage.
12. Change the input data as follows:
Discount Rate [B2]=15%
No of shares [B8]= half million shares
13. Save your file. Submit to Moodle2. Include a “Note”.
Exercise 10 Quantifying Uncertainty: The Normal Distribution
This exercise uses a scratch sheet and a spread sheet to estimate probabilities, given an expected value and standard deviation.
Steps:
1. Download sample10.xls, save the file locally, and change cell A1 to your name.
2. Regard the sample question: What is the probability of getting an NPV over $100, given an expected NPV value of $300 and a standard deviation of $200?
3. Just a suggestion: Use a scratch sheet (a blank sheet of real paper) to draw a bell curve, its center line, and the “line of interest” similar to the graph on the left in sample10.xls. This scratch sheet’s purpose is to help you visualize the “areas under the curve” and the continuum of various possible outcomes. You will not be asked to “turn in” your scratch sheet for grading.
4. Mark on your scratch sheet the areas of interest, A, B, & C as defined in sample10.xls.
5. Find area A: Measure distance in $ from the center line (CL) at $300 to vertical “line of interest” at $100. [answer: 100-300=-200]
6. Calculate distance in z-score from the CL to $100 [where z=$/std dev; z=-200/200=-1.00]
7. In cell B7, find area A by using Excel’s “NORMSDIST” function, i.e. =normsdist(-1) to get area A=.1587, i.e. the probability of getting an NPV between negative infinity and +$100 is 15.87%.
8. Cell B8, find area B the probability of getting between $100 and $300, by using the logic 50% minus area A, or 34.13%. Put in a formula that will yield this value. Use previously calculated cell designations and/or Excel functions in your formulas, not values [using values of 1 and/or .5 is OK].
9. Cell B9, the probability of getting above the expected value, is, by definition, 50%. While you may know that intuitively, try =1-normsdist(0) to get the value.
10. Cell B10, the probability of getting more than $100 is the sum of previously calculated cells. Write a formula with cell designations, not values, that yields .8413.
11. Cell B11, write the formula to yield the probability of getting an NPV of less than $500.
12. Cell B12, write the formula to yield the probability of getting an NPV of more than $500.
13. Cell B13, write the formula to yield the probability of getting an NPV of more than $700.
14. Cell B14, write the formula to yield the probability of getting an NPV of less than $0.
15. Cell B15, write the formula to yield the probability of getting an NPV between $200 & $450.
16. Save your file, submit to Moodle, add a comment telling me how much you enjoyed this exercise.
Exercise 11 Financial Planning
This exercise builds a financial planning spreadsheet.
Steps:
1) Download sample11.xls, change A1 to your name, save the file locally.
2) Change the cells in the right hand plan income statement and balance sheet to formulas based on the assumptions that follow. Note that the order of calculation is from top to bottom of the income statement, but the pattern is more convoluted when calculating the plan balance sheet. Follow the order of the steps below:
ON THE INCOME STATEMENT:
1. Sales (H4): Leave this cell “as is” for now. You’ll change it later.
2. Variable (H5): Variable costs are directly proportional to sales. Divide old Variable by old Sales, multiply the results by plan Sales to get plan Variable Costs. Format your formula as “=round(your formula,0). This will round your answer to the nearest dollar.
3. Gross Profit (H6): Gross Profit is a residual – that is, Gross Profit is what remains after Variable costs are subtracted from Sales. Subtract plan Variable costs from plan Sales.
4. Fixed (H7): Because Fixed costs tend to stay constant from year to year, use the same Fixed costs in the plan as in the previous year. Still, enter a formula (=B7), and not the value.
5. EBIT (H8): Earnings Before Interest and Taxes [a.k.a. Operating Profit] is a residual, so subtract plan Fixed costs from plan Gross Profit.
6. Interest (H9): Interest on the firm’s debt (raised through a bond issue) is a fixed percentage of the outstanding Debt. As the firm’s Debt doesn’t change, neither will the Interest payments.
7. Income Before Taxes (H10): Inc B/T is a residual. Subtract Interest from EBIT.
8. Taxes (H11): Estimate plan Taxes by calculating the effective tax rate from the previous year (taxes divided by Inc B/T), and applying [multiplying] the old tax rate to the plan Income Before Tax. Again, round your answer using the =round() function, similar to what you did with variable costs (H5).
9. Income After Tax (H12): Inc A/T is a residual. Subtract plan Taxes from plan Inc B/T.
10: Preferred Dividends (H13): Preferred is similar to Interest in that it’s a fixed percentage of the total value of the Preferred Stock. As the value of the Preferred stock doesn’t change from year to year, neither will the Preferred Dividends.
11. Earnings Available to Common (H14): EAC is the “bottom line”, a.k.a. Net Income or Net Profits. The calculation is a residual – subtract Preferred from Inc A/T.
12. Dividends (H15): Dividends on Common Stock are paid after EAC. For this plan, keep the plan dividends the same dollar amount as the old dividends. But enter a formula, not a value.
13. Change in Retained Earnings (H16): Change in Retained Earnings is a residual, or EAC minus dividends.
ON THE BALANCE SHEET:
14. Retained Earnings (K14): Cumulative Retained Earnings are the sum of the old cumulative Retained Earnings plus the plan Change in Retained Earnings.
15. Common Stock (K13): Assume that the value of Common Stock does not change from year to year. But enter a formula, not a value.
16. Preferred Stock (K12): Assume that the value of Preferred Stock does not change from year to year. But enter a formula, not a value.
17. Debt (K11): Assume that the value of Debt does not change from year to year. But enter a formula, not a value.
18. Accounts Payable (K10): Acct/Pay are a fixed percentage of purchases from year to year. To calculate, divide the old Acct/Pay by the sum of the old Variable and old Fixed costs. Take the results (a decimal) and multiply it by the sum of the plan Variable and plan Fixed costs. Round the results similar to what you did in H4.
19. Total Liabilities and Equity (K15): Total Liabilities and Equity is the sum of the components, i.e. Acct/Pay through Retained Earnings.
20. Total Assets (K8): Plan Total Assets equal plan Total Liabilities and Equitiy.
21. Property Plant and Equipment (K7): PP&E, the fixed assets, can be assumed to be constant from actual to plan year. But enter a formula, not a value.
22. Inventory (K6): Inventory is a fixed percentage of Sales. Divide the old Inventory by old Sales. Multiply the resulting percentage by plan Sales to get plan Inventory. Round the results.
23. Accounts Receivable (K5): Acct/Rec is also a fixed percentage of Sales. Divide the old Acct/Rec by old Sales. Multiply the resulting percentage by plan Sales to get plan Acct/Rec. Round the results.
24. Cash (K4): Plan Cash is a residual in this model. Subtract the sum of Acct/Rec, Inventory, and PP&E from Total Assets to get Cash.
25. Change the Sales Assumption: Now that your spreadsheet is built, change the Sales assumption in the plan to $4,100,000.
26. Save the results. Submit to Moodle2. Add a note.
Exercise12 Weighted Average Cost of Capital (WACC)
This exercise builds the formulas that calculate the weighted average cost of capital for a firm. An income statement and balance sheet is given to provide some of the required input variables. Also given is the following data for bonds, preferred and common stock.
a. bonds: XYZ 12s2020 close @ 120
b. preferred: pays $1.25 div/qtr, closed @ $41.00/share
c. common: div (ttm) =$6.00, expected growth in div=5% closed @ $48.00
d. effective corporate tax rate: from the Income Statement (taxes / Inc B/T)
e. transaction costs : $4 / Bond; $1 /share for Preferred; 2% Common
Steps:
1) Download sample12.xls, save it locally as exer12.xls.
2) Enter a formula in C8 that calculates the “net received by the firm”. This is the closing bond price in dollars [C6] minus the transaction cost [C7].
3) Enter a formula in E4 that uses Rodriguez’s Model to calculate the “cost of debt before tax”.
4) Enter a formula in E5 that calculates the tax rate from the income statement [H11/H10].
5) In E6, calculate the cost of debt after tax using the model BeforeTax(1-tax rate)=AfterTax.
6) In C13, calculate the net received by the firm for preferred.
7) In E10, calculate the cost of preferred.
8) In C16, calculate next year’s dividends using D0(1+g).
9) In C20, calculate the net received by the firm.
10) In E15, calculate the cost of common.
11) In E21, calculate the cost of retained earnings.
12) In C24 through C27, set these cells equal to the corresponding data in the given balance sheet [K11 thru K14]. Total the four components into C28.
13) Set D24 thru D27 to equal the calculated component costs (above).
14) In E24 thru E27, calculate the extensions by multiplying each of the four “Balance [sheet] $” by its respective “Comp[onent] costs”. Total the “ext[ensions]” into E28.
15) The WACC is the total of the extensions divided by the total of the four balance sheet values [E28/C28]. Enter this calculation into C29 (as a decimal) and D29 (as a percent). This is your answer.
16) Make the following changes:
C6=1300
C11=43.00
H11=30000
K11=600000
C17=50.00
17) Save your file. Submit to Moodle2. Add a note.
Exercise13: Leverage Models
This exercise calculates the degrees of operating, financial and total leverage of a firm using a couple of different approaches.
Steps:
1. Open sample13.xls, enter yourname in A1, and save the file locally.
2. In D20,D21, & D22 write the formulas to calculate the dollar change (from 2011 to 2012) in Sales, EBIT, and EAC using (plan$ minus actual$).
3. In E20,21,22 write the formulas to calculate the percent change in Sales, EBIT, and EAC using:
(plan$-actual$)/actual$.
4. In H20,21,22 write the formulas to calculate DOL, DFL and DTL using the models based on the definition of each, that is, using the percent change method.
5. In J20,21,22 write the formulas to calculate DOL, DFL, and DTL for 2011 using the alternative models.
6. In M20,21,22 write the formulas to calculate DOL, DFL, and DTL for 2012 using the alternative models.
7. Save your file. In this exercise you are not asked to change any of the input data, as in some previous exercises.
8. Submit to Moodle2. Add a “Note”.
Exercise14: Dividend Policy Modeling
This exercise converts verbal descriptions of dividend policies into spreadsheet models.
Steps:
1. Open sample14.xls. Save the file to a local drive. Enter your name in cell A1.
2. The next steps 3 through 6 refer to the income statement (B2:B15) and B17.
3. In cell B15, write the formula to calculate “change in retained earnings” (EAC minus Div)
4. In cell B18, write the formula to calculate “dividends per share”
5. In cell B19, write the formula to calculate “payout ratio”.
6. In cell B20, write the formula to calculate “retention rate”.
The following steps refer to a different firm whose quarterly data appears in E9 thru E24 and F9 thru F16. F17 thru F24 represent different dividend values for Q1’12 thru Q4’13 depending on the applicable dividend policy as defined in section 14.7 in Sketches in Finance.
7. In cells F17 thru F24, write the formulas to calculate the total dividends that would result from a dividend policy as described in section 14.7 a). Use Q4’11 data (in F16) as the base year.
8. In cells G17 thru G24, write the formulas to calculate the total dividends that would result from a dividend policy as described in 14.7 b). Assume a $10,000 kicker in Q4’12.
9. In cells H17 thru H24, write the formulas to calculate the total dividends that would result from a dividend policy as described in 14.7 c). Assume increases in the amount shown in H10 and increases starting in Q1’12, with base quarter Q4’11.
10. In cells I17 thru I24, write the formulas to calculate the total dividends that would result from a dividend policy as described in 14.7 d). Assume the same payout ratio as Q4’11. Format the cell to show whole dollars (i.e. no places past decimal point).
11. In cells J17 thru J24, write the formulas to calculate the total dividends that would result from a dividend policy as described in 14.7 e). Assume the CPI increases shown in K17 thru K24. [Note: The CPI increases shown in K17 Thru K24 are for each quarter and are unrealistically high.] Also assume that the base quarter is Q4’11.
12. Change F16 to 7000.
13. Change H10 to 200.
14. Save your file. Submit to Moodle2. Include a note.
Exercise 15 Final Exam
The sample spreadsheet for this exercise has ten tabs, or “sheets”. You will do your work in tabs 2 through 10 (RATIOS through DIVIDENDS), and your answers will appear automatically in the “MAIN” tab. Do not modify anything on the MAIN tab except for your name in cell A1. And in the tabs RATIO through DIVIDENDS, don’t add any rows or columns. Just follow the instructions
RATIOS
1. Open sample15. Note the tabs at the bottom.
2. Open “Ratio” tab. Open exer15inc.htm (available on Moodle). Find Apple’s total sales [revenue] and type the data into B3. Enter your number “in millions” by leaving off “000”. Note three zeros have already been stripped as “Numbers are in Thousands” on the original Yahoo page. Find Net Income and type the data (in millions) into B4.
3. Open exer15bal.htm. Find Apple’s Total Assets and Shareholders’ Equity and type the data (in millions) into B5 and B6 respectively.
4. Open exer15aapl.htm. Find Apple’s Market Cap, Stock Price(the big bold number), and E.P.S. and type the data into B7, B8 and B9 respectively. Update B10.
5. In B13 write the formula for Profit Margin (Total income/Sales).
6. In B14 write the formula for Return on assets (Total income/total assets).
7. In B15 write the formula for the P/E Ratio (pps/EPS).
8. In B16 write the formula for Market/Book (MarketCap/SH Equity).
9. In B17 put the price per share data.
CAPM
1. Open “CAPM” tab.
2. In cell B6 enter the formula shown in A2. The results of your formula in B6 should match the value shown in the original B6. Note: Add a multiplication sign [*] in the Excel formula that is only implied in the algebraic format.
3. Edit the new formula by adding “$” in front of each row number. This will hold those rows constant as you copy the formula to the cells below (as described in the following steps).
4. Copy the formula in B6 to B12. Edit the formula (created in the steps above), changing the beta reference from B$4 to A12.
5. Copy the formula in B12 down through B21.
6. Change the data inB3, B4 and B5 to reflect the following:
LIBOR is currently 5%, the beta of the new security is 1.25 and the expected returns on the S&P500 are now 12 %.
VALUATION
1. Open “VALUE” tab.
2. Write the formula in B5 for payments on a loan using B2, B3, B4 as inputs.
3. In B8 write the formula =B2.
4. In C8 write the formula for monthly interest due at the rate given in B3(divide B3 by 12 to get monthly rate)times B8.
5. D8=B5
6. E8=D8-C8.
7. F8=B8-E8.
8. B9=F8.
9. Copy C8 through F8 to C9 though F9.
10. Copy B9 through F9 down for the life of the loan.
11. Add period numbers in column A.
12. Change loan amount B2 to $30000.
13. Change annual rate B3 to 5%.
BONDS
1. Open “BONDS” tab.
2. Write formulas in C6, C7, and C8 to calculate the same values as shown in the sample.
3. Change B3 to .075.
4. Change B4 to 6 years.
5. Change B5 to .045.
STOCKS
1. Open “STOCKS” tab.
2. Write formulas in C7, G6, C13, and G12 to calculate the same values as shown in the cells in the sample.
3. Change B6 to .15.
4. Change F4 to .06.
5. Change B12 to .07.
6. Change F11 to .60.
PLAN
1. Open “PLAN” tab.
2. Write formulas in H5 through H16 and K4 through K15 to calculate the same values as shown in the cells in the sample.
3. Change H4 to 4200000.
COST OF CAPITAL
1. Open “WACC” tab.
2. Write formulas in all of the cells with bold red numbers to get the same values as shown in the cells in the sample.
3. Change C5 to 10.
4. Change C12 to .50.
5. Change C18 to 3%.
LEVERAGE
1. Open “Leverage” tab.
2. Put formulas in D20 through D22, E20 through E22, H20 through H22, J20 through J22, and M20 through M22 to get the same values as shown in the cells in the sample.
3. Change H4 to 4200000. Note: H5 through H16 do not have active cells and will not change automatically with your change in H4. That’s OK.
DIVIDENDS
1. Open “DIVIDEND” tab.
2. In cell B15, write the formula to calculate “change in retained earnings”.
3. In cell B18, write the formula to calculate “dividends per share”.
4. In cell B19, write the formula to calculate “payout ratio”. (DIV/EAC)
5. In cell B20, write the formula to calculate “retention rate”. (∆RE/EAC)
6. In cells F17 thru F24, write the formulas to calculate the total dividends that would result from a dividend policy as described in 14.7 a) in Sketches in Finance. Q4’11 is the base year.
7. In cells G17 thru G24, write the formulas to calculate the total dividends that would result from a dividend policy as described in 14.7 b). Assume a $10,000 kicker in Q4’12.
8. In cells H17 thru H24, write the formulas to calculate the total dividends that would result from a dividend policy as described in 14.7 c). Assume increases in the amount shown in H10 and increases starting in Q1’12, with base quarter Q4’11.
9. In cells I17 thru I24, write the formulas to calculate the total dividends that would result from a dividend policy as described in 14.7 d). Assume the same payout ratio as Q4’11. Format the cell to show whole dollars (i.e. no places past decimal point).
10. In cells J17 thru J24, write the formulas to calculate the total dividends that would result from a dividend policy as described in 14.7 e). Assume the CPI increases shown in K17 thru K24. [Note: The CPI increases shown in K17 Thru K24 are for each quarter and are unrealistically high.] Also assume that the base quarter is Q4’11.
11. Change F16 to 7000.
12. Change H10 to 200.
SUBMITTING THE FILE
1. Open the “MAIN” tab.
2. Put your name in Cell A1.
3. Save the file locally and then submit the file to Moodle.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.