What are the implications of the change in present value based on risk? In other words, what does the change mean to the company, and how wouldyou, as a financial manager, interpret it? Be sure to justify your reasoning.

I have figured out the excel part of the homework just not sure what to do with the word document part that I need to complete

Milestone One: Time Value of Money (please fill in shaded YELLOW cells, row 6D - 6H) Interest Rate 8% FCF1 Amounts* FCF2 FCF3 FCF4 FCF5 113 111 108 101 97 Pv* (104.63) ($95.16) ($85.73) ($74.24) ($66.02) Total Pv* (425.78) *In millions Pv=FVN/(1+I)^N 1 PV(I,N,0,FV) 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. 2 Milestone Two: Stock Valuation and Bond Issuance (please fill in the shaded YELLOW cells) PART I: STOCK VALUATION Note: Part of Statement o of dividends, but only 3 y Dividend from Financial Statements: Year Cash Dividend Div/share ($) Yield Explanations: Cash Dividend - distributi appear on Income Statem Stockholder's Stock Price Equity (in millions) 2012 #DIV/0! 2013 #DIV/0! 2014 #DIV/0! Dividend Yield - annual ca price of a share of the com Price). Note: Current Stock Price formula for Dividend Yield 1. Stock Valuation - The new dividend yield if the company increased its dividend per share by 1.75 Year Cash Dividend Div/Share ($) Yield +1.75 Stockholder's Stock Price Equity (in millions) 2012 1.75 #DIV/0! 0 #DIV/0! 2013 1.75 #DIV/0! 0 #DIV/0! 2014 1.75 #DIV/0! 0 #DIV/0! 2. The dividend yield if the firm doubled it's outstanding shares Stockholder's Equity = As corporation. Owners are c company. The goal of eve Return on Equity - for thi Using the formula: Divide Note - for this part, you w 3 Return on Equity - for thi Year Cash Dividend Div/Share ($) Yield Stockholder's Stock Price Equity (in millions) -doubled 2012 0 #DIV/0! 0 #DIV/0! 2013 0 #DIV/0! 0 #DIV/0! 2014 0 #DIV/0! 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 Stock Price Return on Div/Share ($) Investment +1.75 1.75 #DIV/0! 2013 1.75 #DIV/0! #DIV/0! 2014 1.75 #DIV/0! #DIV/0! Curent Bonds from Financial Statements 4 N Interest I Payments PMT Bonds are a long-term de money to the corporation loan's lifetime and at the bondholders have priority Calculation: Please note t February 1, 2015 is the or considered PV (Present va schedule adjusted to Febr PART II: BOND ISSUANCE Periods Note - for this part, you w Bonds = Debt...............Bo Stock=Equity................Sto 2012 Present Value PV Using the formula: Divide ($2,963) 40 Semi-annual payment: 2036-2016 = 20 years *2 = 40 periods 2.9375 Interest paid semi-annually: 5.875%/2 = 2.9375% 0 This bond does not make regular PMT except for interest The following Senior-Not 5.875% Senior Notes; due and December 16 PV (Present Value) = 2,96 Our scenario: 5.875% Sen on February 1 and August PV (Present Value) = 2,96 5.875% Senior Notes; due and December 16 PV (Present Value) = 2,96 Future Value FV CALCULATING FV (please see help on the right hand side) Our scenario: 5.875% Sen on February 1 and August PV (Present Value) = 2,96 1. The new value of the bond if overall rates in the market increased by 5% Present Value PV Periods N Interest I Payments PMT ($2,963) 40 Please adjust interest 5.875%+5% = 10.875%/2 = 5.4375% 0 Future Value FV FV (Future Value Calculati CALCULATING FV (please see help on the right hand side) Step 1) Select Formulas Step 2) Click on Financial Step 3) Select FV - you wi 2. The new value of the bond if overall rates in the market decreased by 5% Step 4) Enter the followin Rate - enter as decimal, n Present Value PV Periods N Interest I Payments PMT Future Value FV ($2,963) Nper - number of period. 40 Pmt - payment. Our exam Please adjust interest 0 Pv - Present value. Enter a Type - leave blank 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 5 5.875%-5% = 0.875%/2 = 0.4375% xplanations: ash Dividend - distribution of the corporate income. They are not expenses and do not ppear on Income Statement. ote: Part of Statement of Cash Flows. Please be aware that corporation list 5 years worth f dividends, but only 3 years worth of dividend yields (Hint: research F-1). ividend Yield - annual cash dividend per share of common stock divided by the market rice of a share of the common stock (Dividend yield = Annual Dividend/Current Stock rice). ote: Current Stock Price is not part of the Financial Statements - calculated using the ormula for Dividend Yield tockholder's Equity = Assets - Liabilities. Equity represents the ownership of a orporation. Owners are called stockholders because they hold stocks or shares of the ompany. The goal of every corporate manager is to generate shareholder value. eturn on Equity - for this part we will modify and use return on investment instead. sing the formula: Dividend (+1.75)/+[(new price-old price)/old price] ote - for this part, you will need extra price from 2011 6 eturn on Equity - for this part we will modify and use return on investment instead. sing the formula: Dividend (+1.75)/+[(new price-old price)/old price] ote - for this part, you will need extra price from 2011 onds are a long-term debt for corporations. In buying a bond, the bond-owner lends money to the corporation. The borrower promises to pay specified interest rate during the oan's lifetime and at the maturity, payback the entire principle. In case of bankruptcy, ondholders have priority over stockholders for any payment distributions. onds = Debt...............Bondholders = Lenders tock=Equity................Stockholders = Owners alculation: Please note that for bond calculations, only one bond is used and we assume ebruary 1, 2015 is the origination date. The value on financial statements will be onsidered PV (Present value). Maturity date is assumed for February 2036 and payment chedule adjusted to February 1 and August 1. he following Senior-Note was used from page 44: .875% Senior Notes; due December 16, 2036; interest payable semi-annually on June 16 nd December 16 V (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes; due February 1, 2036; interest payable semi-annually n February 1 and August 1 V (Present Value) = 2,963 million 7 .875% Senior Notes; due December 16, 2036; interest payable semi-annually on June 16 nd December 16 V (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes; due February 1, 2036; interest payable semi-annually n February 1 and August 1 V (Present Value) = 2,963 million V (Future Value Calculation) - using Excel Formula tep 1) Select Formulas tep 2) Click on Financial tep 3) Select FV - you will see the formula below tep 4) Enter the following: ate - enter as decimal, no % sign. Example: 4% as 0.04 per - number of period. Enter a whole number. Example 50 mt - payment. Our example does not assume regular payments disbursing principal v - Present value. Enter as negative. Example $1,000 should be -1000 ype - leave blank 8 Milestone Three: Capital Budgeting Data (please fill in the shaded YELLOW cells) Initial Outlay CF1 CF2 Cash Flows (Sales) - Operating Costs (excluding Depreciation) - Depreciation Rate of 20% - - Operating Income (EBIT) - Income Tax (Rate 35%) - - After-Tax EBIT + Depreciation - - - - Cash Flows $0 Select from drop downs below: 9 NPV $0.00 IRR Err:523 WACC Capital Budgeting Example Set-up Initial investment $65,000,000 Straight-line Depreciation of 20% CF3 CF4 CF5 Income Tax @35% WACC of 8% approximately. (HD WACC was about 8.8 Cash Flow (which in this case are Sales Revenues) are - - - 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? I project doesn't earn more percent than WACC, the co should abandon the project and invest money elsewh Initial Investment - always negative. Corporation has money ("lose" it till they recover it via sales) in order future benefit. 10 ACCEPT REJECT (HD WACC was about 8.83%) se are Sales Revenues) are as follows: CC rate for new projects? If the percent than WACC, the corporation t and invest money elsewhere. negative. Corporation has to invest cover it via sales) in order to gain 11 Milestone Four: Interest Rate Implication (please fill in shaded YELLOW cells) Explanation: We will use Milestone 1 and Time Value of M 4 analysis 1. Original Scenario from Milestone 1 - Time Value of Money using 8% Two cases will be analyzed: Interest Rate 8.00% Lower Interest Rate at 5% Higher Interest Rate at 15% FCF1 Amounts* FCF2 FCF3 FCF4 FCF5 113 111 108 101 97 Pv* (104.63) (95.16) (85.73) (74.24) (66.02) Total Pv* (425.78) *In millions 2. Change in interest rate and its implications - Lower Interest Rate (5%) Interest Rate FCF1 Amounts* Pv* 12 FCF2 FCF3 FCF4 FCF5 113 111 108 101 97 (113.00) (111.00) (108.00) (101.00) (97.00) Total Pv* (530.00) *In millions 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* (530.00) *In millions 13 lestone 1 and Time Value of Money for Milesotne be analyzed: t Rate at 15% 14 15 SUMMARY TAB TAB 1 Note: This process could take up to 20 s 1. Time Value of Money TAB 2 113 111 108 101 97 1 1 1 1 1 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% 16 process could take up to 20 seconds TAB 3 Capital Budgeting 0 0 TAB 4 Interest Rate Implication 0 0 17 $9,785,570.71 50% 18 FIN 550 Milestone One Guidelines and Rubric Overview: Financial analysis involves examining historical data to gain information about the current and future financial health of a company. Financial analysis can be applied in a wide variety of situations to give business managers the information they need to make critical decisions. The ability to understand financial data is essential for any business manager. For the final project, you will use this case study to prepare a financial analysis report for Home Depot Inc. You will include in your analysis the background calculations and managerial analysis for each of the following topics: time value of money, stock and bond valuation, and capital budgeting. You will also discuss macroeconomic variables that might impact the company's financial decision making and strategic objectives. These topics will be covered over four milestones to be submitted throughout the course before you submit the final project. Note that while these elements may seem separate and unrelated, together they will present a well-rounded view of the company's finances with regard to the topics. For this milestone, you will submit a draft of the Time Value of Money section of the final project, along with your supporting explanations. Prompt: Calculate time value of money figures and use the results to support your explanations of the present and future value of Home Depot Inc. Complete your calculations on the designated tab in the Final Project Student Workbook. Specifically, the following critical elements must be addressed: I. Time Value of Money A. Calculate the following time value of money figures: 1. Calculate the present value of the company based on the given interest rate and expected revenues over time. 2. Suppose the risk of the company changes based on an internal event. Recalculate the present value of the company. 3. Suppose that a potential buyer has offered to buy this company in five years. Based on the present value you calculated above, what would be a reasonable amount for which the company should be sold at that future time? B. What are the implications of the change in present value based on risk? In other words, what does the change mean to the company, and how would you, as a financial manager, interpret it? Be sure to justify your reasoning. C. Based on the future value of the company that you calculated, and being mindful of the need to effectively balance portfolio risk with return, what recommendation would you make about purchasing the company as an investment at that price? Be sure to substantiate your reasoning. Guidelines for Submission: Your paper must be submitted as a 2- to 3-page Microsoft Word document, not including your calculations, which should be completed in the Final Project Student Workbook. Use double spacing, 12-point Times New Roman font, and one-inch margins. Sources should be cited according to APA style. Instructor Feedback: This activity uses an integrated rubric in Blackboard. Students can view instructor feedback in the Grade Center. For more information, review these instructions. Rubric Critical Elements Proficient (100%) Time Value of Money: Figures Accurately calculates requested figures Needs Improvement (80%) Calculates figures, but with gaps in accuracy or detail Time Value of Money: Analyzes implications of change in Analyzes implications of change in Implications present value based on risk, justifying present value based on risk, but response reasoning or reasoning is cursory or illogical Time Value of Money: Future Makes recommendation about Makes recommendation about Value purchasing company at future price, purchasing company at future price, but substantiating claims response or substantiation is cursory or illogical Articulation of Response Submission has no major errors related to Submission has major errors related to citations, grammar, spelling, syntax, or citations, grammar, spelling, syntax, or organization organization that negatively impact readability and articulation of main ideas Not Evident (0%) Does not calculate figures Value 30 Does not analyze implications of change in present value based on risk 30 Does not make recommendation about purchasing company at future price 30 Submission has critical errors related to citations, grammar, spelling, syntax, or organization that prevent understanding of ideas Earned Total 10 100%