Capital Budgeting Data

User Generated

qebo88

Writing

Description

Prompt: Provide your recommendation on a potential investment project for Home Depot Inc. based on the net present value (NPV) and internal rate of return (IRR). Compare these calculations for their use in evaluating a potential investment. Complete your calculations on the designated tab (Capital Budgeting Data) in the Final Project Student Workbook.

Specifically, the following critical elements must be addressed:

IV. Capital Budgeting Data

A. Suppose the company is considering a potential investment project to add to its portfolio. Calculate the following items:

1. The net present value (NPV) of the project

2. The internal rate of return (IRR) of the project

B. What are the implications of these calculations? In other words, based on each of the calculations, and being mindful of the need to balance portfolio risk with return, would you recommend that the company pursue the investment? Why or why not? Be sure to substantiate your claims.

C. What is the difference between NPV and IRR? Which one would you choose for evaluating a potential investment and why? Be sure to support your reasoning with evidence.

*Case study link for Home Depot - https://www.sec.gov/Archives/edgar/data/354950/000035495015000008/hd-212015x10xk.htm

Unformatted Attachment Preview

FIN 550 Milestone Three Guidelines and Rubric Overview: 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. In this milestone, you will submit a draft of the Capital Budgeting Data section of the final project, along with your supporting explanations. Prompt: Provide your recommendation on a potential investment project for Home Depot Inc. based on the net present value (NPV) and internal rate of return (IRR). Compare these calculations for their use in evaluating a potential investment. Complete your calculations on the designated tab in the Final Project Student Workbook. Specifically, the following critical elements must be addressed: IV. Capital Budgeting Data A. Suppose the company is considering a potential investment project to add to its portfolio. Calculate the following items: 1. The net present value (NPV) of the project 2. The internal rate of return (IRR) of the project B. What are the implications of these calculations? In other words, based on each of the calculations, and being mindful of the need to balance portfolio risk with return, would you recommend that the company pursue the investment? Why or why not? Be sure to substantiate your claims. C. What is the difference between NPV and IRR? Which one would you choose for evaluating a potential investment and why? Be sure to support your reasoning with evidence. 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 on the designated tab in the Final Project Student Workbook. Use double spacing, 12-point Times New Roman font, and one-inch margins. Sources should be formatted according to APA style. Rubric Critical Elements Capital Budgeting Data: Potential Investment Capital Budgeting Data: Pursuing the Investment Capital Budgeting Data: Difference Articulation of Response Proficient (100%) Accurately calculates requested figures Needs Improvement (80%) Calculates figures, but with gaps in accuracy or detail Analyzes the implications of each Analyzes the implications of each calculation on the recommendation to calculation on the recommendation to pursue the investment, substantiating pursue the investment, but response or claims substantiation is cursory or illogical Accurately characterizes the difference Characterizes the difference between between NPV and IRR and explains which NPV and IRR and explains which would would be chosen for evaluating a be chosen for evaluating a potential potential investment and why, investment and why, but response is supporting reasoning with evidence cursory or inaccurate or evidence is not supportive Submission has no major errors related Submission has major errors related to 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 the implications of each calculation on the recommendation to pursue the investment 30 Does not characterize the difference between NPV and IRR and does not explain which would be chosen for evaluating a potential investment and why 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 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 o business during a reporting period, minus cash outlays for working expenditures, and dividends during the same period. This is a stro the ability of an entity to remain in business. Note: For this part of the Milestone, please capital lease payment property. Usually Free Cash Flows (FCFs) are used to calculate NPV Flow calculations will be covered later in the course and thus can’t the initial Milestone #1 analysis. Interest Rate (given) - in our scenario we will use 8% interest rate implicit rate, the average rate that lease consumer face on the cu net change in cash generated by the operations of a eriod, minus cash outlays for working capital, capital during the same period. This is a strong indicator of ain in business. estone, please capital lease payments under ows (FCFs) are used to calculate NPV. Free Cash red later in the course and thus can’t be used for sis. scenario we will use 8% interest rate. This rate is an e 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 (fill in Cash what year you Div/share ($) are using) Dividend Yield Stockholder's Stock Price Equity (in millions) 201# 201# 201# #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 (fill in Cash Dividend what year you Div/Share ($) Yield are using) +1.75 201# 1.75 #DIV/0! 201# 1.75 #DIV/0! 201# 1.75 #DIV/0! Stockholder's Stock Price Equity (in millions) 0 0 0 #DIV/0! #DIV/0! #DIV/0! 2. The dividend yield if the firm doubled it's outstanding shares Year (fill in Cash Dividend what year you Div/Share ($) Yield are using) 201# 0 #DIV/0! 201# 0 #DIV/0! 201# 0 #DIV/0! Stockholder's Stock Price Equity (in millions) doubled 0 #DIV/0! 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 (fill in Cash Stock Price what year you Div/Share ($) are using) +1.75 201# 1.75 #DIV/0! 201# 1.75 #DIV/0! 201# 1.75 #DIV/0! Return on Investment CALCULATE ROI (Dividends + Capital gain)/ Divided b (D1 + (P1-P0)) / PO PART II: BOND ISSUANCE Current Bonds from Financial Statements Present Value Periods Interest Payments Future Value PV N I PMT FV ($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 PMTs, assume zero coupon b 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 ells) Explanations: Cash Dividend - distribution of the corporate income. They are appear on Income Statement. Note: Part of Statement of Cash Flows. Please be aware that co Dividend Yield - annual cash dividend per share of common sto of a share of the common stock. (Dividend yield = Annual Divide Note: Current Stock Price is not part of the Financial Statement for Dividend Yield d per share by 1.75 d yield you calculated above ALCULATE ROI Dividends + Capital gain)/ Divided by the original Price 1 + (P1-P0)) / PO Stockholder's Equity = Assets - Liabilities. This represents the o Owners are called stockholder because they hold stocks or shar goal of every corporate manager is to generate shareholder val Return on Equity - for this part we will modify and use return o Using the formula: Dividend (+1.75)/+[(new price-old price)/old Note - for this part, you will need extra price from 2011 Bonds are a long-term debt for corporations. By buying a bond, to the corporation. The borrower promises to pay specified inte lifetime and at the maturity, payback the entire principle. In ca have priority over stockholders for any payment distributions. Bonds = Debt...............Bondholders = Lenders Stock=Equity................Stockholders = Owners Calculation: Please note that for bond calculations, only one bo that February 1st, 2015 is the origination date. The value on fin considered PV (Present value). Maturity date would be also ass payment schedule would be adjusted to February 1 and August available in 8-k 2006) 036-2016 = 20 years *2 = 40 periods lly: 5.875%/2 = 2.9375% e regular PMTs, assume zero coupon bonds 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 December 16 PV (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes; due February 1, 2036; inter 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% 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 payment Pv - Present value. Enter as negative. Example $1,000 should be Type - leave blank see help on the right hand side) Updated: 01/22/2018 by ZB of the corporate income. They are not expenses and do not . sh Flows. Please be aware that corporation list 5 years worth of dividend per share of common stock divided by the market price ck. (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 ger is to generate shareholder value. rt 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 wer promises to pay specified interest rate during the loans payback the entire principle. In case of bankruptcy, bondholders rs for 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 adjusted to February 1 and August 1. (issued in 2006 and details as used from page 44: cember 16, 2036; interest payable semi-annually on June 16 and llion Notes; due February 1, 2036; interest payable semi-annually on llion - using Excel Formula e the formula below sign. Example: 4% as 0.04 er a whole number. Example 50 does not assume regular payments. Assume zero coupon bond for our example. egative. 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 IRR $0.00 #NUM! 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 FCF2 FCF3 FCF4 Amounts* Pv* 0.00 Total Pv* *In millions 0.00 0.00 0.00 0.00 2. Change in interest rate and its implications - Lower Interest Rate (5%) Interest Rate FCF1 FCF2 FCF3 FCF4 Amounts* Pv* 0.00 Total Pv* *In millions 0.00 0.00 0.00 0.00 3. Change in interest rate and its implications - Higher Interest Rate (15%) Interest Rate FCF1 FCF2 FCF3 FCF4 Amounts* Pv* 0.00 Total Pv* *In millions 0.00 0.00 0.00 0.00 Explanation: We will use Milestone 1 and Time Value of Money for Milestone 4 a Two cases will be analyzed: Lower Interest Rate at 5% Higher Interest Rate at 15% FCF5 0.00 FCF5 0.00 FCF5 0.00 Money for Milestone 4 analysis
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Thank you so much

Running head: Capital budgeting

1

Capital budgeting:
Name:
Institution affiliation:
Date:

Capital budgeting

2

IV. Capital Budgeting Data
A.

1. The NPV of the project is $13,291,616
2. The IRR of the project 16%
B.
Even though the initial investment of 65,000,000 is quite large, I recommend that Home
depot Inc. should pursue the investment. This is with regard to the fact that as per the NPV of
$13,291,616, the returns to be realized from the project will be worth the risk. This is so since the
NPV >0. Additionally, the IRR which is equal to 16% is greater than Home Depot’s cost of
capital i.e. 8%.

C.
NPV differs from IRR in the following ways
a.

The results of NPV are in a dollar value that the project will produce while the

results for IRR are in percentage return that the project will create.

Capital budgeting
b.

3

The focus of NPV is on project surplus while for IRR the focus is on ...


Anonymous
I was having a hard time with this subject, and this was a great help.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags