hello

For this milestone, submit a draft of the Time Value of Money section of the final project, along with your supporting explanations. Base your calculations on the data provided inthis case study:https://www.sec.gov/Archives/edgar/data/354950/000035495015000008/hd-212015x10xk.htm.Be sure to substantiate your claims.

Submit your calculations on the designated tab of theFinal Project Student Workbookand your supporting explanations as a Microsoft Word document.

Milestone One: Time Value of Money (please fill in shaded YELLOW cells, row 6D - 6H) Interest Rate 8% FCF1 FCF2 FCF3 FCF4 FCF5 Amounts* Pv* 0.00 Total Pv* *In millions 0.00 Pv=FVN/(1+I)^N $0.00 PV(I,N,0,FV) $0.00 $0.00 $0.00 Explanations: FCF (Free Cash Flow) is the net change in cash generated by the operations of a business during a reporting period, minus cash outlays for working capital, capital expenditures, and dividends during the same period. FCF is a strong indicator of the ability of an entity to remain in business. Note: For this part of the Milestone, please use page 43 -capital lease payments under property. Interest Rate (given) - in our scenario we will use 8% interest rate. This rate is an implicit rate, the average rate that lease consumers face on the current market. Milestone Two: Stock Valuation and Bond Issuance (please fill in the shaded YELLOW cells) PART I: STOCK VALUATION Dividend from Financial Statements: Year Cash Div/share Dividend ($) Yield Stockholder's Equity Stock Price (in millions) 2012 2013 2014 #DIV/0! #DIV/0! #DIV/0! 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 1.75 1.75 1.75 Stockholder's Equity Stock Price (in millions) #DIV/0! #DIV/0! #DIV/0! 0 0 0 #DIV/0! #DIV/0! #DIV/0! 2. The dividend yield if the firm doubled it's outstanding shares Year Cash Div/Share Dividend ($) Yield 2012 2013 2014 0 0 0 Stockholder's Equity Stock Price (in millions) -doubled #DIV/0! #DIV/0! #DIV/0! 0 0 0 #DIV/0! #DIV/0! #DIV/0! 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 1.75 1.75 1.75 #DIV/0! #DIV/0! #DIV/0! PART II: BOND ISSUANCE Curent Bonds from Financial Statements Return on Investment #DIV/0! #DIV/0! 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 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 Please adjust interest 0 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 Please adjust interest 0 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 OW cells) Explanations: Cash Dividend - distribution of the corporate income. They are not exp on Income Statement. Note: Part of Statement of Cash Flows. Please be aware that corporatio dividends, but only 3 years worth of dividend yields (Hint: research F-1 Dividend Yield - annual cash dividend per share of common stock divid a share of the common stock (Dividend yield = Annual Dividend/Curren Note: Current Stock Price is not part of the Financial Statements - calcu for Dividend Yield per share by 1.75 yield you calculated above Stockholder's Equity = Assets - Liabilities. Equity represents the owner Owners are called stockholders because they hold stocks or shares of t every corporate manager is to generate shareholder value. Return on Equity - for this part we will modify and use return on invest Using the formula: Dividend (+1.75)/+[(new price-old price)/old price] Note - for this part, you will need extra price from 2011 Bonds are a long-term debt for corporations. In buying a bond, the bon the corporation. The borrower promises to pay specified interest rate d and at the maturity, payback the entire principle. In case of bankruptc 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 is us February 1, 2015 is the origination date. The value on financial stateme (Present value). Maturity date is assumed for February 2036 and paym February 1 and August 1. The following Senior-Note was used from page 44: 5.875% Senior Notes; due December 16, 2036; interest payable semi-a December 16 PV (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes; due February 1, 2036; interest pay February 1 and August 1 Calculation: Please note that for bond calculations, only one bond is us February 1, 2015 is the origination date. The value on financial stateme (Present value). Maturity date is assumed for February 2036 and paym February 1 and August 1. 6-2016 = 20 years *2 = 40 periods : 5.875%/2 = 2.9375% egular PMT except for interest e 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 semi-a December 16 PV (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes; due February 1, 2036; interest pay February 1 and August 1 PV (Present Value) = 2,963 million 5.875%+5% = 10.875%/2 = 5.4375% e help on the right hand side) 5.875%-5% = 0.875%/2 = 0.4375% e 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 disbu Pv - Present value. Enter as negative. Example $1,000 should be -1000 Type - leave blank e income. They are not expenses and do not appear se be aware that corporation list 5 years worth of d yields (Hint: research F-1). hare of common stock divided by the market price of d = Annual Dividend/Current Stock Price). Financial Statements - calculated using the formula quity represents the ownership of a corporation. y hold stocks or shares of the company. The goal of reholder value. ify and use return on investment instead. price-old price)/old price] e from 2011 s. In buying a bond, the bond-owner lends money to pay specified interest rate during the loan's lifetime ciple. In case of bankruptcy, bondholders have distributions. rs rs lations, only one bond is used and we assume e value on financial statements will be considered PV or February 2036 and payment schedule adjusted to page 44: 36; interest payable semi-annually on June 16 and bruary 1, 2036; interest payable semi-annually on 4% as 0.04 mber. Example 50 me regular payments disbursing principal le $1,000 should be -1000 Milestone Three: Capital Budgeting Data (please fill in the shaded YELLOW cells) Initial Outlay Cash Flows (Sales) - Operating Costs (excluding Depreciation) - Depreciation Rate of 20% Operating Income (EBIT) - Income Tax (Rate 35%) After-Tax EBIT + Depreciation Cash Flows CF1 CF2 - $0 Select from drop downs below: NPV $0.00 IRR Err:523 WACC CF3 CF4 - CF5 - - Capital Budgeting Example Set-up Initial investment $65,000,000 Straight-line Depreciation of 20% Income Tax @35% WACC of 8% approximately. (HD WACC was about Cash Flow (which in this case are Sales Revenues) 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 project doesn't earn more percent than WACC, the corpo abandon the project and invest money elsewhere Initial Investment - always negative. Corporation money ("lose" it till they recover it via sales) in or benefit. ng Example Set-up nt $65,000,000 preciation of 20% ACCEPT REJECT proximately. (HD WACC was about 8.83%) h in this case are Sales Revenues) are as follows: we use WACC rate for new projects? If the project ore percent than WACC, the corporation should oject and invest money elsewhere. nt - always negative. Corporation has to invest till they recover it via sales) in order to gain future Milestone Four: Interest Rate Implication (please fill in shaded YELLOW cells) 1. Original Scenario from Milestone 1 - Time Value of Money using 8% Interest Rate 8.00% FCF1 Amounts* FCF2 FCF3 FCF4 FCF5 113 111 108 101 97 Pv* (104.63) (95.16) (85.73) (74.24) (66.02) Total Pv* *In millions (425.78) 2. Change in interest rate and its implications - Lower Interest Rate (5%) Interest Rate FCF1 Amounts* FCF2 FCF3 FCF4 FCF5 113 111 108 101 97 Pv* (113.00) (111.00) (108.00) (101.00) (97.00) Total Pv* *In millions (530.00) 3. Change in interest rate and its implications - Higher Interest Rate (15%) Interest Rate FCF1 Amounts* FCF2 FCF3 FCF4 FCF5 113 111 108 101 97 Pv* (113.00) (111.00) (108.00) (101.00) (97.00) Total Pv* *In millions (530.00) Explanation: We will use Milestone 1 and Time Value of Money for Milesotne 4 analysis Two cases will be analyzed: Lower Interest Rate at 5% Higher Interest Rate at 15% SUMMARY TAB TAB 1 Note: This process could take up to 2 1. Time Value of Money 0 TAB 2 0 0 0 PART I - Stock Valuation 0 0 0 PART II - Bond Issuance Current Bond Value 0 $9,433.58 0 5.4375 0 $24,634.04 0 0.4375 0 $3,528.32 New Value +5% New Value - 5% 0 ocess could take up to 20 seconds TAB 3 Capital Budgeting 0 0 TAB 4 Interest Rate Implication 0 0 $9,785,570.71 50%