Milestone One: Time Value of Money (please fill in YELLOW cells)
Interest Rate
8%
FCF1
FCF2
FCF3
FCF4
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
Explanations:
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.
FCF5
$0.00
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 + (P1P0)) / 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 Semiannual payment: 20362016 = 20 years *2 = 40 periods
2.9375 Interest paid semiannually: 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 + (P1P0)) / 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 priceold price)/old
Note  for this part, you will need extra price from 2011
Bonds are a longterm 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 8k 2006)
0362016 = 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 SeniorNote 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 priceold price)/old price]
eed extra price from 2011
or corporations. By buying a bond, the bondowner 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 semiannually on June 16 and
llion
Notes; due February 1, 2036; interest payable semiannually 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%)
AfterTax 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 Setup
ACCEPT
Initial investment $65,000,000
REJECT
Straightline 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
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 wellrounded 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 3page Microsoft Word document, not including your calculations, which should be completed
in the Final Project Student Workbook. Use double spacing, 12point Times New Roman font, and oneinch margins. Sources should be cited according to APA style.
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
reasoning
response 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
Submission has no major errors related Submission has major errors related to
Articulation of Response
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%
...
Purchase answer to see full
attachment