HI,

I need help with my Finance Course. Will you be able to assist?

I have attached the instructions. Please let me know if you can help and let me know if you need any more information.

As per usual, please include in text citation and a reference page

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% Milestone One: Time Value of Money (please fill in YELLOW cells) Interest Rate Amounts* 8% FCF1 FCF2 FCF3 $113,000,000 $111,000,000 $108,000,000 Pv* ### Total Pv* *In millions ### Pv=FVN/(1+I)^N ### PV(I,N,0,FV) ### Explanations: FCF4 FCF5 $101,000,000 $97,000,000 ### ### FCF (Free Cash Flow) is the net change in cash gen business during a reporting period, minus cash outl expenditures, and dividends during the same perio the ability of an entity to remain in business. Note: For this part of the Milestone, please use pag under property. Usually Free Cash Flows (FCFs) are Cash Flow calculations will be covered later in the c for the initial Milestone #1 analysis. Interest Rate (given) - in our scenario we will use 8 implicit rate, the average rate that lease consumer Flow) is the net change in cash generated by the operations of a a reporting period, minus cash outlays for working capital, capital nd dividends during the same period. This is a strong indicator of entity to remain in business. art of the Milestone, please use page 43 -capital lease payments Usually Free Cash Flows (FCFs) are used to calculate NPV. Free lations will be covered later in the course and thus can't be used lestone #1 analysis. ven) - in our scenario we will use 8% interest rate. This rate is an e average rate that lease consumer 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 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 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 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 $0 - NPV $0.00 IRR Err:523 WACC CF2 CF3 Select from drop down: ACCEPT ACCEPT CF4 - CF5 - - 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 FCF1 Amounts* FCF2 FCF3 FCF4 113 111 108 101 Pv* (113.00) (111.00) (108.00) (101.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 113 111 108 101 Pv* (113.00) (111.00) (108.00) (101.00) Total Pv* *In millions (530.00) 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 (97.00) FCF5 97 (97.00) ime Value of Money for Milesotne 4 analysis SUMMARY TAB TAB 1 Could take up to 20 seconds 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 p to 20 seconds TAB 3 Capital Budgeting 0 0 TAB 4 Interest Rate Implication 0 0 $9,785,570.71 50%