Modified Instructions for Milestone #1
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. Assume the cost of capital is 8% initially as noted in your workbook.
NOTE: Assume each of the cash flows are end-of-year cash flows so you will be discounting
each of the cash flows back to the year 2014. For example, this means the 2015 cash flow of
$113m will be discounted one year, and so on for the remaining four years of cash flows.
Similarly, assume the $880m cash flows for the years after 2019 occur on 2020, so this $880m
cash flow needs to be discounted back to 2014 as well (financiers call this $880m cash flow the
“horizon value” of the firm, something we will discuss later this term). The sum of the PV of
these six cash flows will be your value of the firm in 2014.
2. Suppose the risk of the company changes based on an internal event. Recalculate the present
value of the company. Here, assume the level of firm risk increases, so that the new cost of
capital is 10%.
3. Suppose that a potential buyer has offered to buy this company in five years. Based on the
present value you calculated above based on the higher level of risk (i.e., the 10% cost of
capital), what would be a reasonable amount for which the company should be sold at that future
time? Hint: Don’t lose sight of the fact that we are talking about the year 2019 after five (5)
years has elapsed, and therefore the cash flows which remain at that point.
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.
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
Stock Price
Equity (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
Dividend
Div/Share ($) Yield
+1.75
2012
1.75 #DIV/0!
2013
1.75 #DIV/0!
2014
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
Cash
Div/Share ($)
2012
2013
2014
Dividend
Yield
0
0
0
#DIV/0!
#DIV/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
Cash
Stock Price
Div/Share ($)
+1.75
2012
1.75 #DIV/0!
2013
1.75 #DIV/0!
2014
1.75 #DIV/0!
PART II: BOND ISSUANCE
Return on
Investment
#DIV/0!
#DIV/0!
Curent 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 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
LOW cells)
Explanations:
Cash Dividend - distribution of the corporate income. They are not expen
appear on Income Statement.
Note: Part of Statement of Cash Flows. Please be aware that corporation
Dividend Yield - annual cash dividend per share of common stock divided
of a share of the common stock (Dividend yield = Annual Dividend/Curren
Note: Current Stock Price is not part of the Financial Statements - calcula
for Dividend Yield
per share by 1.75
yield you calculated above
Stockholder's Equity = Assets - Liabilities. Equity represents the ownersh
Owners are called stockholders because they hold stocks or shares of the
every corporate manager is to generate shareholder value.
Return on Equity - for this part we will modify and use return on investm
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 bondto the corporation. The borrower promises to pay specified interest rate
lifetime and at the maturity, payback the entire principle. In case of bank
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 bond is used
February 1, 2015 is the origination date. The value on financial statement
PV (Present value). Maturity date is assumed for February 2036 and paym
adjusted to February 1 and August 1.
The following Senior-Note was used from page 44:
Calculation: Please note that for bond calculations, only one bond is used
February 1, 2015 is the origination date. The value on financial statement
PV (Present value). Maturity date is assumed for February 2036 and paym
adjusted to February 1 and August 1.
6-2016 = 20 years *2 = 40 periods
: 5.875%/2 = 2.9375%
egular PMT except for interest
ee 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-ann
December 16
PV (Present Value) = 2,963 million
Our scenario: 5.875% Senior Notes; due February 1, 2036; interest payab
February 1 and August 1
PV (Present Value) = 2,963 million
5.875%+5% = 10.875%/2 = 5.4375%
ee help on the right hand side)
5.875%-5% = 0.875%/2 = 0.4375%
ee 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 disbursi
Pv - Present value. Enter as negative. Example $1,000 should be -1000
Type - leave blank
te income. They are not expenses and do not
ase be aware that corporation list 5 years worth of
hare of common stock divided by the market price
ield = Annual Dividend/Current Stock Price).
Financial Statements - calculated using the formula
quity represents the ownership of a corporation.
ey hold stocks or shares of the company. The goal of
areholder value.
dify and use return on investment instead.
w 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
ntire principle. In case of bankruptcy, bondholders
ment distributions.
ers
ers
ulations, only one bond is used and we assume
e value on financial statements will be considered
ed for February 2036 and payment schedule
page 44:
ulations, only one bond is used and we assume
e value on financial statements will be considered
ed for February 2036 and payment schedule
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
ple $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
IRR
$0.00
#NUM!
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 abou
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 proje
doesn’t earn more percent than WACC, the corp
abandon the project and invest money elsewher
Initial Investment - always negative. Corporation
money ("lose" it till they recover it via sales) in o
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
t 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 t
1. Time Value of Money
FALSE
TAB 2
FALSE
FALSE
FALSE
PART I - Stock Valuation
FALSE
FALSE
FALSE
PART II - Bond Issuance
Current Bond Value
FALSE
$9,433.58
New Value +5%
FALSE
FALSE
5.4375
$24,634.04
New Value - 5%
FALSE
FALSE
0.4375
$3,528.32
ote: This process could take up to 20 seconds
TAB 3
Capital Budgeting
FALSE
FALSE
FALSE
TAB 4
Interest Rate Implication
FALSE
FALSE
$9,785,570.71
50%
Purchase answer to see full
attachment