This have to be completed in the attached spreadsheet.

3-1 Homework: Stock Valuation CalculationsThis homework submission should include all calculations, completed on the designated tab of the Homework Student Workbook, and a document explaining the implications of your findings for the business or business transaction. After reading the assigned chapters, address the following questions:

1. Turbo Technology Computers is experiencing a period of rapid growth. Earnings and dividends are expected to grow at a rate of 15% during the next two years, at 13% in the third year, and at a constant rate of 6% thereafter. Turbo?s last dividend was $1.15, and the required rate of return on the stock is 12%.

Complete the following calculations:

1. Calculate the value of the stock today.
2. Calculate P1^ and P2^.
3. Calculate the dividend yield and capital gains yield for Years 1, 2, and 3.
4. Kassidy?s Kabob House has preferred stock outstanding that pays a dividend of $5 at the end of each year. The preferred sells for $50 a share. What is the stock?s required rate of return? Assume the market is in equilibrium with the required return equal to the expected return.
5. McCaffrey?s Inc. has never paid a dividend, and when the firm might begin paying dividends is not known. Its current free cash flow (FCF) is $100,000, and this FCF is expected to grow at a constant 7% rate. The weighted average cost of capital (WACC) is 11%. McCaffrey?s currently holds $325,000 of non-operating marketable securities. Its long-term debt is $1,000,000, but it has never issued preferred stock. McCaffrey?s has 50,000 shares of stock outstanding.

Calculate the following:

1. McCaffrey?s value of operations
2. The company?s total value
3. The estimated value of common equity
4. The estimated per-share stock price

Milestone One: Time Value of Money (please fill in YELLOW cells) Interest Rate 8% FCF1 Amounts* FCF2 FCF3 FCF4 113 111 108 101 Pv* (104.63) ($95.16) ($85.73) ($74.24) Total Pv* *In millions (425.78) Pv=FVN/(1+I)^N PV(I,N,0,FV) Explanations: FCF5 97 ($66.02) FCF (Free Cash Flow) is the net change in cash generated by the op business during a reporting period, minus cash outlays for working expenditures, and dividends during the same period. This is a stron the ability of an entity to remain in business. Note: For this part of the Milestone, please use page 43 -capital lea under property. Interest Rate (given) - in our scenario we will use 8% interest rate. implicit rate, the average rate that lease consumer face on the curr ash generated by the operations of a ash outlays for working capital, capital e period. This is a strong indicator of . use page 43 -capital lease payments ill use 8% interest rate. This rate is an nsumer face on the current market. Milestone Two: Stock Valuation and Bond Issuance (please fill in the YELLOW cells) PART I: STOCK VALUATION Dividend from Financial Statements: Year Cash Div/share Dividend ($) Yield 2012 2013 2014 1.16 1.56 1.88 Stockholder's Equity Stock Price (in millions) 2.30% 2.20% 2.30% 17,777 50.43478261 12,522 70.90909091 9,322 81.73913043 1. Stock Valuation - The new dividend yield if the company increased its dividend per share by 1.75 Year Cash Div/Share Dividend ($) +1.75 Yield 2012 2013 2014 2.91 3.31 3.63 Stockholder's Equity Stock Price (in millions) 5.77% 4.67% 4.44% 17,777 50.43478261 12,522 70.90909091 9,322 81.73913043 2. The dividend yield if the firm doubled it's outstanding shares Year Cash Div/Share Dividend ($) Yield 2012 2013 2014 0.58 0.78 0.94 Stockholder's Equity Stock Price (in millions) -doubled 1.15% 1.10% 1.15% 35,554 50.43478261 25,044 70.90909091 18,644 81.73913043 3. The rate of return on equity (i.e., the cost of stock) based on the new dividend yield you calculated above Year Cash Div/Share Stock Price ($) +1.75 2012 2013 2014 2.91 50.43478261 3.31 70.90909091 3.63 81.73913043 PART II: BOND ISSUANCE Curent Bonds from Financial Statements Return on Investment 3.72% 3.78% Present Value Periods Interest Payments Future Value PV N I PMT FV ($2,963) 40 Semi-annual payment: 2036-2016 = 20 years *2 = 40 period 2.9375 Interest paid semi-annually: 5.875%/2 = 2.9375% 0 This bond does not make regular PMT except for interest $9,433.58 CALCULATING FV (please see help on the right hand side) 1. The new value of the bond if overall rates in the market increased by 5% Present Value Periods Interest Payments Future Value PV N I PMT FV ($2,963) 40 5.4375 Please adjust interest 0 $24,634.04 CALCULATING FV (please see help on the right hand side) 2. The new value of the bond if overall rates in the market decreased by 5% Present Value Periods Interest Payments Future Value PV N I PMT FV ($2,963) 40 0.4375 Please adjust interest 0 $3,528.32 CALCULATING FV (please see help on the right hand side) 3. The value of the bond if overall rates in the market stayed exactly the same - identical to CURRENT BOND VALUE from Financial Statements lls) Explanations: Cash Dividend - distribution of the corporate income. They are no on Income Statement. Note: Part of Statement of Cash Flows. Please be aware that corp dividends, but only 3 years worth of dividend yields. (Hint: resear Dividend Yield - annual cash dividend per share of common stock a share of the common stock. (Dividend yield = Annual Dividend/ Note: Current Stock Price is not part of the Financial Statements for Dividend Yield per share by 1.75 Stockholder's Equity = Assets - Liabilities. This represents the ow Owners are called stockholder because they hold stocks or share of every corporate manager is to generate shareholder value. Return on Equity - for this part we will modify and use return on Using the formula: Dividend (+1.75)/+[(new price-old price)/old p Note - for this part, you will need extra price from 2011 yield you calculated above Bonds are a long-term debt for corporations. By buying a bond, th the corporation. The borrower promises to pay specified interest and at the maturity, payback the entire principle. In case of bank priority over stockholders for any payment distributions. Bonds = Debt...............Bondholders = Lenders Stock=Equity................Stockholders = Owners Calculation: Please note that for bond calculations, only one bond that February 1st, 2015 is the origination date. The value on finan considered PV (Present value). Maturity date would be also assum payment schedule would be adjusted to February 1 and August 1 The following Senior-Note was used from page 44: 5.875% Senior Notes; due December 16, 2036; interest payable s December 16 PV (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes; due February 1, 2036; intere February 1 and August 1 Calculation: Please note that for bond calculations, only one bond that February 1st, 2015 is the origination date. The value on finan considered PV (Present value). Maturity date would be also assum payment schedule would be adjusted to February 1 and August 1 036-2016 = 20 years *2 = 40 periods lly: 5.875%/2 = 2.9375% regular PMT except for interest see help on the right hand side) The following Senior-Note was used from page 44: 5.875% Senior Notes; due December 16, 2036; interest payable s December 16 PV (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes; due February 1, 2036; intere February 1 and August 1 PV (Present Value) = 2,963 million 5.875%+5% = 10.875%/2 = 5.4375% see help on the right hand side) 5.875%-5% = 0.875%/2 = 0.4375% see help on the right hand side) FV (Future Value Calculation) - using Excel Formula Step 1) Select Formulas Step 2) Click on Financial Step 3) Select FV - you will see the formula below Step 4) Enter the following: Rate - enter as decimal, no % sign. Example: 4% as 0.04 Nper - number of period. Enter a whole number. Example 50 Pmt - payment. Our example does not assume regular payments Pv - Present value. Enter as negative. Example $1,000 should be Type - leave blank f the corporate income. They are not expenses and do not appear sh Flows. Please be aware that corporation list 5 years worth of orth of dividend yields. (Hint: research F-1) dividend per share of common stock divided by the market price of (Dividend yield = Annual Dividend/Current Stock Price) ot part of the Financial Statements - calculated suing the formula - Liabilities. This represents the ownership of a corporations. r because they hold stocks or share of the company. The main goal to generate shareholder value. t we will modify and use return on investment instead. +1.75)/+[(new price-old price)/old price] eed extra price from 2011 or corporations. By buying a bond, the bond-owner lends money to r promises to pay specified interest rate during the loans lifetime he entire principle. In case of bankruptcy, bondholders have any payment distributions. olders = Lenders olders = Owners for bond calculations, only one bond was used and we assume origination date. The value on financial statements will be . Maturity date would be also assumed for February 2036 and djusted to February 1 and August 1. as used from page 44: cember 16, 2036; interest payable semi-annually on June 16 and lion Notes; due February 1, 2036; interest payable semi-annually on - using Excel Formula e the formula below sign. Example: 4% as 0.04 er a whole number. Example 50 does not assume regular payments disbursing principal gative. Example $1,000 should be -1000 Milestone Three: Capital Budgeting Data (please fill in YELLOW cells) Initial Outlay CF1 ($65,000,000) Cash Flows (Sales) - Operating Costs (excluding Depreciation) - Depreciation Rate of 20% Operating Income (EBIT) - Income Tax (Rate 35%) After-Tax EBIT + Depreciation Cash Flows ($65,000,000) NPV IRR $50,000,000 $25,500,000 (13,000,000) 37,500,000 13,125,000 24,375,000 13,000,000 37,375,000 $9,785,570.71 50% CF2 $45,000,000 $25,500,000 (13,000,000) 32,500,000 11,375,000 21,125,000 13,000,000 34,125,000 Select from drop down: ACCEPT ACCEPT WACC 8% CF3 CF4 $65,500,000 $25,500,000 (13,000,000) 53,000,000 18,550,000 34,450,000 13,000,000 47,450,000 CF5 $55,000,000 $25,500,000 (13,000,000) 42,500,000 14,875,000 27,625,000 13,000,000 40,625,000 $25,000,000 $25,500,000 (13,000,000) 12,500,000 4,375,000 8,125,000 13,000,000 21,125,000 Capital Budgeting Example Set-up ACCEPT Initial investment $65,000,000 REJECT Straight-line Depreciation of 20% Income Tax @35% WACC of 8% approximately. (HD WACC was about 8.83%) Cash Flow (which in this case are Sales Revenues) are as follows: CF1: $50,000,000 CF2: $45,000,000 CF3: $65,500,000 CF4: $55,000,00 CF5: $25,000,000 Operating Costs CF1: $25,500,000 CF2: $25,500,000 CF3: $25,500,000 CF4: $25,500,000 CF5: $25,500,000 WACC- why do we use WACC rate for new projects? If the project doesn't earn more percent than WACC, the corporation should abandon the project and invest money elsewhere. Initial Investment - always negative. Corporation has to invest money ("lose" it till they recover it via sales) in order to gain future benefit. Milestone Four: Interest Rate Implication (please fill in YELLOW cells) 1. Original Scenario from Milestone 1 - Time Value of Money using 8% Interest Rate 8.00% FCF1 Amounts* FCF2 FCF3 FCF4 113 111 108 101 Pv* (104.63) (95.16) (85.73) (74.24) Total Pv* *In millions (425.78) 2. Change in interest rate and its implications - Lower Interest Rate (5%) Interest Rate 5.00% FCF1 Amounts* FCF2 FCF3 FCF4 113 111 108 101 Pv* (107.62) (100.68) (93.29) (83.09) Total Pv* *In millions (460.69) 3. Change in interest rate and its implications - Higher Interest Rate (15%) Interest Rate 15.00% FCF1 Amounts* Pv* Total Pv* *In millions FCF2 FCF3 FCF4 113 111 108 101 (98.26) (83.93) (71.01) (57.75) (359.18) Explanation: We will use Milestone 1 and Time Value of Money for Milesotne Two cases will be analyzed: Lower Interest Rate at 5% Higher Interest Rate at 15% FCF5 97 (66.02) FCF5 97 (76.00) FCF5 97 (48.23) ime Value of Money for Milesotne 4 analysis SUMMARY TAB TAB 1 1. Time Value of Money FCF1 Amounts* 113 Total Pv* TAB 2 FCF2 FCF3 111 (425.78) PART I - Stock Valuation Year Cash Dividend Div/share Yield ($) 2012 2013 2014 1.16 1.56 1.88 PART II - Bond Issuance Current Bond Value Future Value FV 2.30% 2.20% 2.30% 9,433.58 New Value +5% Interest I Payments PMT Future Value FV 5.4375 0 $24,634.04 New Value - 5% Interest I Payments PMT Future Value FV 0.4375 0 $3,528.32 Stockholder's Equity (in millions) 17,777 12,522 9,322 FCF4 108 FCF5 101 97 TAB 3 Capital Budgeting Initial Outlay CF1 ($65,000,000) CF2 $50,000,000 $25,500,000 NPV IRR TAB 4 CF3 $45,000,000 $25,500,000 $9,785,570.71 ACCEPT 50% ACCEPT Interest Rate Implication Interest Rate Total Pv* 8.00% Interest Rate Total Pv* 5.00% Interest Rate Total Pv* 15.00% (425.78) (460.69) (359.18) $65,500,000 $25,500,000 CF4 $55,000,000 $25,500,000 CF5 $25,000,000 $25,500,000 Milestone One: Time Value of Money (please fill in YELLOW cells) Interest Rate 8% FCF1 Amounts* FCF2 FCF3 FCF4 113 111 108 101 Pv* (104.63) ($95.16) ($85.73) ($74.24) Total Pv* *In millions (425.78) Pv=FVN/(1+I)^N PV(I,N,0,FV) Explanations: FCF5 97 ($66.02) FCF (Free Cash Flow) is the net change in cash generated by the op business during a reporting period, minus cash outlays for working expenditures, and dividends during the same period. This is a stron the ability of an entity to remain in business. Note: For this part of the Milestone, please use page 43 -capital lea under property. Interest Rate (given) - in our scenario we will use 8% interest rate. implicit rate, the average rate that lease consumer face on the curr ash generated by the operations of a ash outlays for working capital, capital e period. This is a strong indicator of . use page 43 -capital lease payments ill use 8% interest rate. This rate is an nsumer face on the current market. Milestone Two: Stock Valuation and Bond Issuance (please fill in the YELLOW cells) PART I: STOCK VALUATION Dividend from Financial Statements: Year Cash Div/share Dividend ($) Yield 2012 2013 2014 1.16 1.56 1.88 Stockholder's Equity Stock Price (in millions) 2.30% 2.20% 2.30% 17,777 50.43478261 12,522 70.90909091 9,322 81.73913043 1. Stock Valuation - The new dividend yield if the company increased its dividend per share by 1.75 Year Cash Div/Share Dividend ($) +1.75 Yield 2012 2013 2014 2.91 3.31 3.63 Stockholder's Equity Stock Price (in millions) 5.77% 4.67% 4.44% 17,777 50.43478261 12,522 70.90909091 9,322 81.73913043 2. The dividend yield if the firm doubled it's outstanding shares Year Cash Div/Share Dividend ($) Yield 2012 2013 2014 0.58 0.78 0.94 Stockholder's Equity Stock Price (in millions) -doubled 1.15% 1.10% 1.15% 35,554 50.43478261 25,044 70.90909091 18,644 81.73913043 3. The rate of return on equity (i.e., the cost of stock) based on the new dividend yield you calculated above Year Cash Div/Share Stock Price ($) +1.75 2012 2013 2014 2.91 50.43478261 3.31 70.90909091 3.63 81.73913043 PART II: BOND ISSUANCE Curent Bonds from Financial Statements Return on Investment 3.72% 3.78% Present Value Periods Interest Payments Future Value PV N I PMT FV ($2,963) 40 Semi-annual payment: 2036-2016 = 20 years *2 = 40 period 2.9375 Interest paid semi-annually: 5.875%/2 = 2.9375% 0 This bond does not make regular PMT except for interest $9,433.58 CALCULATING FV (please see help on the right hand side) 1. The new value of the bond if overall rates in the market increased by 5% Present Value Periods Interest Payments Future Value PV N I PMT FV ($2,963) 40 5.4375 Please adjust interest 0 $24,634.04 CALCULATING FV (please see help on the right hand side) 2. The new value of the bond if overall rates in the market decreased by 5% Present Value Periods Interest Payments Future Value PV N I PMT FV ($2,963) 40 0.4375 Please adjust interest 0 $3,528.32 CALCULATING FV (please see help on the right hand side) 3. The value of the bond if overall rates in the market stayed exactly the same - identical to CURRENT BOND VALUE from Financial Statements lls) Explanations: Cash Dividend - distribution of the corporate income. They are no on Income Statement. Note: Part of Statement of Cash Flows. Please be aware that corp dividends, but only 3 years worth of dividend yields. (Hint: resear Dividend Yield - annual cash dividend per share of common stock a share of the common stock. (Dividend yield = Annual Dividend/ Note: Current Stock Price is not part of the Financial Statements for Dividend Yield per share by 1.75 Stockholder's Equity = Assets - Liabilities. This represents the ow Owners are called stockholder because they hold stocks or share of every corporate manager is to generate shareholder value. Return on Equity - for this part we will modify and use return on Using the formula: Dividend (+1.75)/+[(new price-old price)/old p Note - for this part, you will need extra price from 2011 yield you calculated above Bonds are a long-term debt for corporations. By buying a bond, th the corporation. The borrower promises to pay specified interest and at the maturity, payback the entire principle. In case of bank priority over stockholders for any payment distributions. Bonds = Debt...............Bondholders = Lenders Stock=Equity................Stockholders = Owners Calculation: Please note that for bond calculations, only one bond that February 1st, 2015 is the origination date. The value on finan considered PV (Present value). Maturity date would be also assum payment schedule would be adjusted to February 1 and August 1 The following Senior-Note was used from page 44: 5.875% Senior Notes; due December 16, 2036; interest payable s December 16 PV (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes; due February 1, 2036; intere February 1 and August 1 Calculation: Please note that for bond calculations, only one bond that February 1st, 2015 is the origination date. The value on finan considered PV (Present value). Maturity date would be also assum payment schedule would be adjusted to February 1 and August 1 036-2016 = 20 years *2 = 40 periods lly: 5.875%/2 = 2.9375% regular PMT except for interest see help on the right hand side) The following Senior-Note was used from page 44: 5.875% Senior Notes; due December 16, 2036; interest payable s December 16 PV (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes; due February 1, 2036; intere February 1 and August 1 PV (Present Value) = 2,963 million 5.875%+5% = 10.875%/2 = 5.4375% see help on the right hand side) 5.875%-5% = 0.875%/2 = 0.4375% see help on the right hand side) FV (Future Value Calculation) - using Excel Formula Step 1) Select Formulas Step 2) Click on Financial Step 3) Select FV - you will see the formula below Step 4) Enter the following: Rate - enter as decimal, no % sign. Example: 4% as 0.04 Nper - number of period. Enter a whole number. Example 50 Pmt - payment. Our example does not assume regular payments Pv - Present value. Enter as negative. Example $1,000 should be Type - leave blank f the corporate income. They are not expenses and do not appear sh Flows. Please be aware that corporation list 5 years worth of orth of dividend yields. (Hint: research F-1) dividend per share of common stock divided by the market price of (Dividend yield = Annual Dividend/Current Stock Price) ot part of the Financial Statements - calculated suing the formula - Liabilities. This represents the ownership of a corporations. r because they hold stocks or share of the company. The main goal to generate shareholder value. t we will modify and use return on investment instead. +1.75)/+[(new price-old price)/old price] eed extra price from 2011 or corporations. By buying a bond, the bond-owner lends money to r promises to pay specified interest rate during the loans lifetime he entire principle. In case of bankruptcy, bondholders have any payment distributions. olders = Lenders olders = Owners for bond calculations, only one bond was used and we assume origination date. The value on financial statements will be . Maturity date would be also assumed for February 2036 and djusted to February 1 and August 1. as used from page 44: cember 16, 2036; interest payable semi-annually on June 16 and lion Notes; due February 1, 2036; interest payable semi-annually on - using Excel Formula e the formula below sign. Example: 4% as 0.04 er a whole number. Example 50 does not assume regular payments disbursing principal gative. Example $1,000 should be -1000 Milestone Three: Capital Budgeting Data (please fill in YELLOW cells) Initial Outlay CF1 ($65,000,000) Cash Flows (Sales) - Operating Costs (excluding Depreciation) - Depreciation Rate of 20% Operating Income (EBIT) - Income Tax (Rate 35%) After-Tax EBIT + Depreciation Cash Flows ($65,000,000) NPV IRR $50,000,000 $25,500,000 (13,000,000) 37,500,000 13,125,000 24,375,000 13,000,000 37,375,000 $9,785,570.71 50% CF2 $45,000,000 $25,500,000 (13,000,000) 32,500,000 11,375,000 21,125,000 13,000,000 34,125,000 Select from drop down: ACCEPT ACCEPT WACC 8% CF3 CF4 $65,500,000 $25,500,000 (13,000,000) 53,000,000 18,550,000 34,450,000 13,000,000 47,450,000 CF5 $55,000,000 $25,500,000 (13,000,000) 42,500,000 14,875,000 27,625,000 13,000,000 40,625,000 $25,000,000 $25,500,000 (13,000,000) 12,500,000 4,375,000 8,125,000 13,000,000 21,125,000 Capital Budgeting Example Set-up ACCEPT Initial investment $65,000,000 REJECT Straight-line Depreciation of 20% Income Tax @35% WACC of 8% approximately. (HD WACC was about 8.83%) Cash Flow (which in this case are Sales Revenues) are as follows: CF1: $50,000,000 CF2: $45,000,000 CF3: $65,500,000 CF4: $55,000,00 CF5: $25,000,000 Operating Costs CF1: $25,500,000 CF2: $25,500,000 CF3: $25,500,000 CF4: $25,500,000 CF5: $25,500,000 WACC- why do we use WACC rate for new projects? If the project doesn't earn more percent than WACC, the corporation should abandon the project and invest money elsewhere. Initial Investment - always negative. Corporation has to invest money ("lose" it till they recover it via sales) in order to gain future benefit. Milestone Four: Interest Rate Implication (please fill in YELLOW cells) 1. Original Scenario from Milestone 1 - Time Value of Money using 8% Interest Rate 8.00% FCF1 Amounts* FCF2 FCF3 FCF4 113 111 108 101 Pv* (104.63) (95.16) (85.73) (74.24) Total Pv* *In millions (425.78) 2. Change in interest rate and its implications - Lower Interest Rate (5%) Interest Rate 5.00% FCF1 Amounts* FCF2 FCF3 FCF4 113 111 108 101 Pv* (107.62) (100.68) (93.29) (83.09) Total Pv* *In millions (460.69) 3. Change in interest rate and its implications - Higher Interest Rate (15%) Interest Rate 15.00% FCF1 Amounts* Pv* Total Pv* *In millions FCF2 FCF3 FCF4 113 111 108 101 (98.26) (83.93) (71.01) (57.75) (359.18) Explanation: We will use Milestone 1 and Time Value of Money for Milesotne Two cases will be analyzed: Lower Interest Rate at 5% Higher Interest Rate at 15% FCF5 97 (66.02) FCF5 97 (76.00) FCF5 97 (48.23) ime Value of Money for Milesotne 4 analysis SUMMARY TAB TAB 1 1. Time Value of Money FCF1 Amounts* 113 Total Pv* TAB 2 FCF2 FCF3 111 (425.78) PART I - Stock Valuation Year Cash Dividend Div/share Yield ($) 2012 2013 2014 1.16 1.56 1.88 PART II - Bond Issuance Current Bond Value Future Value FV 2.30% 2.20% 2.30% 9,433.58 New Value +5% Interest I Payments PMT Future Value FV 5.4375 0 $24,634.04 New Value - 5% Interest I Payments PMT Future Value FV 0.4375 0 $3,528.32 Stockholder's Equity (in millions) 17,777 12,522 9,322 FCF4 108 FCF5 101 97 TAB 3 Capital Budgeting Initial Outlay CF1 ($65,000,000) CF2 $50,000,000 $25,500,000 NPV IRR TAB 4 CF3 $45,000,000 $25,500,000 $9,785,570.71 ACCEPT 50% ACCEPT Interest Rate Implication Interest Rate Total Pv* 8.00% Interest Rate Total Pv* 5.00% Interest Rate Total Pv* 15.00% (425.78) (460.69) (359.18) $65,500,000 $25,500,000 CF4 $55,000,000 $25,500,000 CF5 $25,000,000 $25,500,000 Growth rates Next two years 3rd year Thereafter Last dividend Required rate of return 15% 13% 6% $1.15 12% 1 D0 Dividend value Price today D1 $1.15 $25.23 D2 $1.32 2 P1 P2 P3 $26.93 $28.64 $30.36 3 Year Dividend yield Capital gains yield 1 4.91% 6.76% 2 5.31% 6.35% 3 5.66% 6.00% 4 Dividend Price Required rate of return $5 $50 10.00% 5 Current FCF Growth rate WACC Non-operating marketable securities Long-term debt Outstanding shares $100,000 7% 11% $325,000 $1,000,000 50,000 1 Value of operations $2,675,000.00 2 Total value of the firm $4,000,000.00 3 Value of common equity $3,000,000.00 4 Value of per share stock $60.00 D3 $1.52 $1.72 D4 D5 $1.82 Terminal value (Y3) Terminal value (Y4) $1.93 $30.36 $32.18 Milestone One: Time Value of Money (please fill in YELLOW cells) Interest Rate 8% FCF1 Amounts* FCF2 FCF3 FCF4 113 111 108 101 Pv* (104.63) ($95.16) ($85.73) ($74.24) Total Pv* *In millions (425.78) Pv=FVN/(1+I)^N PV(I,N,0,FV) Explanations: FCF5 97 ($66.02) FCF (Free Cash Flow) is the net change in cash generated by the op business during a reporting period, minus cash outlays for working expenditures, and dividends during the same period. This is a stron the ability of an entity to remain in business. Note: For this part of the Milestone, please use page 43 -capital lea under property. Interest Rate (given) - in our scenario we will use 8% interest rate. implicit rate, the average rate that lease consumer face on the curr ash generated by the operations of a ash outlays for working capital, capital e period. This is a strong indicator of . use page 43 -capital lease payments ill use 8% interest rate. This rate is an nsumer face on the current market. Milestone Two: Stock Valuation and Bond Issuance (please fill in the YELLOW cells) PART I: STOCK VALUATION Dividend from Financial Statements: Year Cash Div/share Dividend ($) Yield 2012 2013 2014 1.16 1.56 1.88 Stockholder's Equity Stock Price (in millions) 2.30% 2.20% 2.30% 17,777 50.43478261 12,522 70.90909091 9,322 81.73913043 1. Stock Valuation - The new dividend yield if the company increased its dividend per share by 1.75 Year Cash Div/Share Dividend ($) +1.75 Yield 2012 2013 2014 2.91 3.31 3.63 Stockholder's Equity Stock Price (in millions) 5.77% 4.67% 4.44% 17,777 50.43478261 12,522 70.90909091 9,322 81.73913043 2. The dividend yield if the firm doubled it's outstanding shares Year Cash Div/Share Dividend ($) Yield 2012 2013 2014 0.58 0.78 0.94 Stockholder's Equity Stock Price (in millions) -doubled 1.15% 1.10% 1.15% 35,554 50.43478261 25,044 70.90909091 18,644 81.73913043 3. The rate of return on equity (i.e., the cost of stock) based on the new dividend yield you calculated above Year Cash Div/Share Stock Price ($) +1.75 2012 2013 2014 2.91 50.43478261 3.31 70.90909091 3.63 81.73913043 PART II: BOND ISSUANCE Curent Bonds from Financial Statements Return on Investment 3.72% 3.78% Present Value Periods Interest Payments Future Value PV N I PMT FV ($2,963) 40 Semi-annual payment: 2036-2016 = 20 years *2 = 40 period 2.9375 Interest paid semi-annually: 5.875%/2 = 2.9375% 0 This bond does not make regular PMT except for interest $9,433.58 CALCULATING FV (please see help on the right hand side) 1. The new value of the bond if overall rates in the market increased by 5% Present Value Periods Interest Payments Future Value PV N I PMT FV ($2,963) 40 5.4375 Please adjust interest 0 $24,634.04 CALCULATING FV (please see help on the right hand side) 2. The new value of the bond if overall rates in the market decreased by 5% Present Value Periods Interest Payments Future Value PV N I PMT FV ($2,963) 40 0.4375 Please adjust interest 0 $3,528.32 CALCULATING FV (please see help on the right hand side) 3. The value of the bond if overall rates in the market stayed exactly the same - identical to CURRENT BOND VALUE from Financial Statements lls) Explanations: Cash Dividend - distribution of the corporate income. They are no on Income Statement. Note: Part of Statement of Cash Flows. Please be aware that corp dividends, but only 3 years worth of dividend yields. (Hint: resear Dividend Yield - annual cash dividend per share of common stock a share of the common stock. (Dividend yield = Annual Dividend/ Note: Current Stock Price is not part of the Financial Statements for Dividend Yield per share by 1.75 Stockholder's Equity = Assets - Liabilities. This represents the ow Owners are called stockholder because they hold stocks or share of every corporate manager is to generate shareholder value. Return on Equity - for this part we will modify and use return on Using the formula: Dividend (+1.75)/+[(new price-old price)/old p Note - for this part, you will need extra price from 2011 yield you calculated above Bonds are a long-term debt for corporations. By buying a bond, th the corporation. The borrower promises to pay specified interest and at the maturity, payback the entire principle. In case of bank priority over stockholders for any payment distributions. Bonds = Debt...............Bondholders = Lenders Stock=Equity................Stockholders = Owners Calculation: Please note that for bond calculations, only one bond that February 1st, 2015 is the origination date. The value on finan considered PV (Present value). Maturity date would be also assum payment schedule would be adjusted to February 1 and August 1 The following Senior-Note was used from page 44: 5.875% Senior Notes; due December 16, 2036; interest payable s December 16 PV (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes; due February 1, 2036; intere February 1 and August 1 Calculation: Please note that for bond calculations, only one bond that February 1st, 2015 is the origination date. The value on finan considered PV (Present value). Maturity date would be also assum payment schedule would be adjusted to February 1 and August 1 036-2016 = 20 years *2 = 40 periods lly: 5.875%/2 = 2.9375% regular PMT except for interest see help on the right hand side) The following Senior-Note was used from page 44: 5.875% Senior Notes; due December 16, 2036; interest payable s December 16 PV (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes; due February 1, 2036; intere February 1 and August 1 PV (Present Value) = 2,963 million 5.875%+5% = 10.875%/2 = 5.4375% see help on the right hand side) 5.875%-5% = 0.875%/2 = 0.4375% see help on the right hand side) FV (Future Value Calculation) - using Excel Formula Step 1) Select Formulas Step 2) Click on Financial Step 3) Select FV - you will see the formula below Step 4) Enter the following: Rate - enter as decimal, no % sign. Example: 4% as 0.04 Nper - number of period. Enter a whole number. Example 50 Pmt - payment. Our example does not assume regular payments Pv - Present value. Enter as negative. Example $1,000 should be Type - leave blank f the corporate income. They are not expenses and do not appear sh Flows. Please be aware that corporation list 5 years worth of orth of dividend yields. (Hint: research F-1) dividend per share of common stock divided by the market price of (Dividend yield = Annual Dividend/Current Stock Price) ot part of the Financial Statements - calculated suing the formula - Liabilities. This represents the ownership of a corporations. r because they hold stocks or share of the company. The main goal to generate shareholder value. t we will modify and use return on investment instead. +1.75)/+[(new price-old price)/old price] eed extra price from 2011 or corporations. By buying a bond, the bond-owner lends money to r promises to pay specified interest rate during the loans lifetime he entire principle. In case of bankruptcy, bondholders have any payment distributions. olders = Lenders olders = Owners for bond calculations, only one bond was used and we assume origination date. The value on financial statements will be . Maturity date would be also assumed for February 2036 and djusted to February 1 and August 1. as used from page 44: cember 16, 2036; interest payable semi-annually on June 16 and lion Notes; due February 1, 2036; interest payable semi-annually on - using Excel Formula e the formula below sign. Example: 4% as 0.04 er a whole number. Example 50 does not assume regular payments disbursing principal gative. Example $1,000 should be -1000 Milestone Three: Capital Budgeting Data (please fill in YELLOW cells) Initial Outlay CF1 ($65,000,000) Cash Flows (Sales) - Operating Costs (excluding Depreciation) - Depreciation Rate of 20% Operating Income (EBIT) - Income Tax (Rate 35%) After-Tax EBIT + Depreciation Cash Flows ($65,000,000) NPV IRR $50,000,000 $25,500,000 (13,000,000) 37,500,000 13,125,000 24,375,000 13,000,000 37,375,000 $9,785,570.71 50% CF2 $45,000,000 $25,500,000 (13,000,000) 32,500,000 11,375,000 21,125,000 13,000,000 34,125,000 Select from drop down: ACCEPT ACCEPT WACC 8% CF3 CF4 $65,500,000 $25,500,000 (13,000,000) 53,000,000 18,550,000 34,450,000 13,000,000 47,450,000 CF5 $55,000,000 $25,500,000 (13,000,000) 42,500,000 14,875,000 27,625,000 13,000,000 40,625,000 $25,000,000 $25,500,000 (13,000,000) 12,500,000 4,375,000 8,125,000 13,000,000 21,125,000 Capital Budgeting Example Set-up ACCEPT Initial investment $65,000,000 REJECT Straight-line Depreciation of 20% Income Tax @35% WACC of 8% approximately. (HD WACC was about 8.83%) Cash Flow (which in this case are Sales Revenues) are as follows: CF1: $50,000,000 CF2: $45,000,000 CF3: $65,500,000 CF4: $55,000,00 CF5: $25,000,000 Operating Costs CF1: $25,500,000 CF2: $25,500,000 CF3: $25,500,000 CF4: $25,500,000 CF5: $25,500,000 WACC- why do we use WACC rate for new projects? If the project doesn't earn more percent than WACC, the corporation should abandon the project and invest money elsewhere. Initial Investment - always negative. Corporation has to invest money ("lose" it till they recover it via sales) in order to gain future benefit. Milestone Four: Interest Rate Implication (please fill in YELLOW cells) 1. Original Scenario from Milestone 1 - Time Value of Money using 8% Interest Rate 8.00% FCF1 Amounts* FCF2 FCF3 FCF4 113 111 108 101 Pv* (104.63) (95.16) (85.73) (74.24) Total Pv* *In millions (425.78) 2. Change in interest rate and its implications - Lower Interest Rate (5%) Interest Rate 5.00% FCF1 Amounts* FCF2 FCF3 FCF4 113 111 108 101 Pv* (107.62) (100.68) (93.29) (83.09) Total Pv* *In millions (460.69) 3. Change in interest rate and its implications - Higher Interest Rate (15%) Interest Rate 15.00% FCF1 Amounts* Pv* Total Pv* *In millions FCF2 FCF3 FCF4 113 111 108 101 (98.26) (83.93) (71.01) (57.75) (359.18) Explanation: We will use Milestone 1 and Time Value of Money for Milesotne Two cases will be analyzed: Lower Interest Rate at 5% Higher Interest Rate at 15% FCF5 97 (66.02) FCF5 97 (76.00) FCF5 97 (48.23) ime Value of Money for Milesotne 4 analysis SUMMARY TAB TAB 1 1. Time Value of Money FCF1 Amounts* 113 Total Pv* TAB 2 FCF2 FCF3 111 (425.78) PART I - Stock Valuation Year Cash Dividend Div/share Yield ($) 2012 2013 2014 1.16 1.56 1.88 PART II - Bond Issuance Current Bond Value Future Value FV 2.30% 2.20% 2.30% 9,433.58 New Value +5% Interest I Payments PMT Future Value FV 5.4375 0 $24,634.04 New Value - 5% Interest I Payments PMT Future Value FV 0.4375 0 $3,528.32 Stockholder's Equity (in millions) 17,777 12,522 9,322 FCF4 108 FCF5 101 97 TAB 3 Capital Budgeting Initial Outlay CF1 ($65,000,000) CF2 $50,000,000 $25,500,000 NPV IRR TAB 4 CF3 $45,000,000 $25,500,000 $9,785,570.71 ACCEPT 50% ACCEPT Interest Rate Implication Interest Rate Total Pv* 8.00% Interest Rate Total Pv* 5.00% Interest Rate Total Pv* 15.00% (425.78) (460.69) (359.18) $65,500,000 $25,500,000 CF4 $55,000,000 $25,500,000 CF5 $25,000,000 $25,500,000 Growth rates Next two years 3rd year Thereafter Last dividend Required rate of return 15% 13% 6% $1.15 12% 1 D0 Dividend value Price today D1 $1.15 $25.23 D2 $1.32 2 P1 P2 P3 $26.93 $28.64 $30.36 3 Year Dividend yield Capital gains yield 1 4.91% 6.76% 2 5.31% 6.35% 3 5.66% 6.00% 4 Dividend Price Required rate of return $5 $50 10.00% 5 Current FCF Growth rate WACC Non-operating marketable securities Long-term debt Outstanding shares $100,000 7% 11% $325,000 $1,000,000 50,000 1 Value of operations $2,675,000.00 2 Total value of the firm $4,000,000.00 3 Value of common equity $3,000,000.00 4 Value of per share stock $60.00 D3 $1.52 $1.72 D4 D5 $1.82 Terminal value (Y3) Terminal value (Y4) $1.93 $30.36 $32.18 Assignment 3-1, Question 1 1a. Calculate the value of the stock today: 1. Calculate the PV of the dividends paid during the supernatural growth period: $ 1.1500 1.3225 1.4944 D1 = D2 = D3 = PV of Dividends = % 1.15 1.13 1.06 x x x 1.3225 1.1200 = = = 1.4944 1.2544 + 2. Find the PV of Turbo's stock price at the end of Year 3: P3 ^ = ____D4____ rs-g = = PV of P3^ = = __ _D3(1+g)______ rs-g 1.6791 0.12 - 0.06 $ 27.986 27.9856 1.4049 = $ 19.9196 3. Sum the two components to find the value of the stock today: Value of current stock (P0) = $ 3.50 + 1b. Calculate P1^ and P2^. P1 ^ = 1.4944 1.1200 + 1.5841 1.2544 + P2 ^ = 1.5841 1.1200 + 27.9856 1.1200 = 1c. Calculate the dividend yields and capital gains yield for Years 1, 2, and 3. Year 1 2 3 Dividend Yield 5.65% 6.00% 6.00% + + + + The above solution has been prepared on the basis of concepts of time value of money. From the above it has been increase in time and capital gain reduces over the time, but the total return remains intact at 12%. natural growth period: $ 1.3225 1.4944 1.5841 + $ $ 1.5841 1.4049 = 19.92 = $ 23.42 27.9856 1.2544 = $ 24.91 26.40 $ 3.4997 Capital Gains Yield 6.35% 6.00% 6.00% = Total Return 12.00% 12.00% 12.00% ey. From the above it has been implied, that the dividend yield increases with the ntact at 12%. Assignment 3-1, Question 2 rps = $ $ 5.00 50.00 = 10.00% The required rate of return will be dividend income (return) divided by the stock price. It has been provided that and hence the stock is correctly priced. Hence, the required rate of return is 10.00% e. It has been provided that market is at equilibrium Assignment 3-1, Question 3 3a. Calculate McCaffrey's value of operations. FCF(1+g) WACC - g Vop = = 107000 4.00% + Value of nonoperating asse + 325000 3b. Calculate the company's total value. Total Value = Value of Operations =$ 2,675,000 3c. Calculate the estimated value of common equity. Value of equity = Total value =$ 3,000,000 - Value of debt $ 1,000,000 3d. Calculate the estimated per-share stock price. Price per share = Value of Equity =$ 2,000,000 Number of Shares 50000 The above questions deals with valuation of equity shares. The concept of dicounted cash flow method has been us of the operations. The value has been calculated using the concept of perpetuity of time value of money. The value $ 2675000 and the value of non-operating assets is $ 325000 which leads to the value of firm $ 3000,000. Since, WA the result is value of firm. Further the value of equity is arrived at reducing the value of debt and the per share price number of shares outstanding. The value of equity is $ 2,000,000 and value per share is $ 40.00 107000 4.00% = $ 2,675,000 = $ 3,000,000 = $ 2,000,000 = $ 40.00 lue of nonoperating assets 25000 Value of debt 1,000,000 Number of Shares 0000 flow method has been used in order to calculate the value lue of money. The value of operations as calculated is m $ 3000,000. Since, WACC was used as a discount rate, t and the per share price was calculated by dividing the Assignment 3-1, Question 1 1a. Calculate the value of the stock today: 1. Calculate the PV of the dividends paid during the supernatural growth period: $ 1.1500 1.3225 1.4944 D1 = D2 = D3 = PV of Dividends = % 1.15 1.13 1.06 x x x 1.3225 1.1200 = = = 1.4944 1.2544 + 2. Find the PV of Turbo's stock price at the end of Year 3: P3 ^ = ____D4____ rs-g = = PV of P3^ = = __ _D3(1+g)______ rs-g 1.6791 0.12 - 0.06 $ 27.986 27.9856 1.4049 = $ 19.9196 3. Sum the two components to find the value of the stock today: Value of current stock (P0) = $ 3.50 + 1b. Calculate P1^ and P2^. P1 ^ = 1.4944 1.1200 + 1.5841 1.2544 + P2 ^ = 1.5841 1.1200 + 27.9856 1.1200 = 1c. Calculate the dividend yields and capital gains yield for Years 1, 2, and 3. Year 1 2 3 Dividend Yield 5.65% 6.00% 6.00% + + + + The above solution has been prepared on the basis of concepts of time value of money. From the above it has been increase in time and capital gain reduces over the time, but the total return remains intact at 12%. natural growth period: $ 1.3225 1.4944 1.5841 + $ $ 1.5841 1.4049 = 19.92 = $ 23.42 27.9856 1.2544 = $ 24.91 26.40 $ 3.4997 Capital Gains Yield 6.35% 6.00% 6.00% = Total Return 12.00% 12.00% 12.00% ey. From the above it has been implied, that the dividend yield increases with the ntact at 12%. Assignment 3-1, Question 2 rps = $ $ 5.00 50.00 = 10.00% The required rate of return will be dividend income (return) divided by the stock price. It has been provided that and hence the stock is correctly priced. Hence, the required rate of return is 10.00% e. It has been provided that market is at equilibrium Assignment 3-1, Question 3 3a. Calculate McCaffrey's value of operations. FCF(1+g) WACC - g Vop = = 107000 4.00% + Value of nonoperating asse + 325000 3b. Calculate the company's total value. Total Value = Value of Operations =$ 2,675,000 3c. Calculate the estimated value of common equity. Value of equity = Total value =$ 3,000,000 - Value of debt $ 1,000,000 3d. Calculate the estimated per-share stock price. Price per share = Value of Equity =$ 2,000,000 Number of Shares 50000 The above questions deals with valuation of equity shares. The concept of dicounted cash flow method has been us of the operations. The value has been calculated using the concept of perpetuity of time value of money. The value $ 2675000 and the value of non-operating assets is $ 325000 which leads to the value of firm $ 3000,000. Since, WA the result is value of firm. Further the value of equity is arrived at reducing the value of debt and the per share price number of shares outstanding. The value of equity is $ 2,000,000 and value per share is $ 40.00 107000 4.00% = $ 2,675,000 = $ 3,000,000 = $ 2,000,000 = $ 40.00 lue of nonoperating assets 25000 Value of debt 1,000,000 Number of Shares 0000 flow method has been used in order to calculate the value lue of money. The value of operations as calculated is m $ 3000,000. Since, WACC was used as a discount rate, t and the per share price was calculated by dividing the Assignment 3-1, Question 1 1a. Calculate the value of the stock today: 1. Calculate the PV of the dividends paid during the supernatural growth period: $ 1.1500 1.3225 1.4944 D1 = D2 = D3 = PV of Dividends = % 1.15 1.13 1.06 x x x 1.3225 1.1200 = = = 1.4944 1.2544 + 2. Find the PV of Turbo's stock price at the end of Year 3: P3 ^ = ____D4____ rs-g = = PV of P3^ = = __ _D3(1+g)______ rs-g 1.6791 0.12 - 0.06 $ 27.986 27.9856 1.4049 = $ 19.9196 3. Sum the two components to find the value of the stock today: Value of current stock (P0) = $ 3.50 + 1b. Calculate P1^ and P2^. P1 ^ = 1.4944 1.1200 + 1.5841 1.2544 + P2 ^ = 1.5841 1.1200 + 27.9856 1.1200 = 1c. Calculate the dividend yields and capital gains yield for Years 1, 2, and 3. Year 1 2 3 Dividend Yield 5.65% 6.00% 6.00% + + + + The above solution has been prepared on the basis of concepts of time value of money. From the above it has been increase in time and capital gain reduces over the time, but the total return remains intact at 12%. natural growth period: $ 1.3225 1.4944 1.5841 + $ $ 1.5841 1.4049 = 19.92 = $ 23.42 27.9856 1.2544 = $ 24.91 26.40 $ 3.4997 Capital Gains Yield 6.35% 6.00% 6.00% = Total Return 12.00% 12.00% 12.00% ey. From the above it has been implied, that the dividend yield increases with the ntact at 12%. Assignment 3-1, Question 2 rps = $ $ 5.00 50.00 = 10.00% The required rate of return will be dividend income (return) divided by the stock price. It has been provided that and hence the stock is correctly priced. Hence, the required rate of return is 10.00% e. It has been provided that market is at equilibrium Assignment 3-1, Question 3 3a. Calculate McCaffrey's value of operations. FCF(1+g) WACC - g Vop = = 107000 4.00% + Value of nonoperating asse + 325000 3b. Calculate the company's total value. Total Value = Value of Operations =$ 2,675,000 3c. Calculate the estimated value of common equity. Value of equity = Total value =$ 3,000,000 - Value of debt $ 1,000,000 3d. Calculate the estimated per-share stock price. Price per share = Value of Equity =$ 2,000,000 Number of Shares 50000 The above questions deals with valuation of equity shares. The concept of dicounted cash flow method has been us of the operations. The value has been calculated using the concept of perpetuity of time value of money. The value $ 2675000 and the value of non-operating assets is $ 325000 which leads to the value of firm $ 3000,000. Since, WA the result is value of firm. Further the value of equity is arrived at reducing the value of debt and the per share price number of shares outstanding. The value of equity is $ 2,000,000 and value per share is $ 40.00 107000 4.00% = $ 2,675,000 = $ 3,000,000 = $ 2,000,000 = $ 40.00 lue of nonoperating assets 25000 Value of debt 1,000,000 Number of Shares 0000 flow method has been used in order to calculate the value lue of money. The value of operations as calculated is m $ 3000,000. Since, WACC was used as a discount rate, t and the per share price was calculated by dividing the