A plus writer

profileroebob7l6g
completedworkbook.xlsx
Home>Business & Finance homework help>Accounting homework help>A plus writer

1 Time Value of Money

Milestone One: Time Value of Money (please fill in shaded YELLOW cells, row 6D - 6H) Explanations:
Interest Rate 8% 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.
FCF1 FCF2 FCF3 FCF4 FCF5
Amounts* 1006 928 845 739 658
Pv* (931.48) ($795.61) ($670.79) ($543.19) ($447.82)
Total Pv* (3388.89)
*In millions 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.
Pv=FVN/(1+I)^N PV(I,N,0,FV)

2 Stock and Bond Valuation

Milestone Two: Stock Valuation and Bond Issuance (please fill in the shaded YELLOW cells) Explanations:
Cash Dividend - distribution of the corporate income. They are not expenses and do not appear on Income Statement. Note: Part of Statement of Cash Flows. Please be aware that corporation list 5 years worth of dividends, but only 3 years worth of dividend yields (Hint: research F-1).
PART I: STOCK VALUATION
Dividend from Financial Statements:
Year Cash Div/share ($) Dividend Yield Stockholder's Equity (in millions) Stock Price Dividend Yield - annual cash dividend per share of common stock divided by the market price of a share of the common stock (Dividend yield = Annual Dividend/Current Stock Price). Note: Current Stock Price is not part of the Financial Statements - calculated using the formula for Dividend Yield
2012 1.16 3.28% 17,898 35.32
2013 1.56 6.44% 17,777 24.23
2014 1.88 4.79% 12,522 39.26
1. Stock Valuation - The new dividend yield if the company increased its dividend per share by 1.75
Year Cash Div/Share ($) +1.75 Dividend Yield Stockholder's Equity (in millions) Stock Price Stockholder's Equity = Assets - Liabilities. Equity represents the ownership of a corporation. Owners are called stockholders because they hold stocks or shares of the company. The goal of every corporate manager is to generate shareholder value.
2012 2.91 8.24% 17,898 35.32
2013 3.31 13.66% 17,777 24.23
2014 3.63 9.25% 12,522 39.26
2. The dividend yield if the firm doubled it's outstanding shares Return on Equity - for this part we will modify and use return on investment instead. Using the formula: Dividend (+1.75)/+[(new price-old price)/old price] Note - for this part, you will need extra price from 2011
Year Cash Div/Share ($) Dividend Yield Stockholder's Equity (in millions) -doubled Stock Price
2012 0.58 1.64% 35,796 35.32
2013 0.78 3.22% 35,554 24.23
2014 0.94 2.39% 25,044 39.26
Bonds 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 loan's lifetime and at the maturity, payback the entire principle. In case of bankruptcy, bondholders have priority over stockholders for any payment distributions. Bonds = Debt...............Bondholders = Lenders Stock=Equity................Stockholders = Owners
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 ($) +1.75 Stock Price Return on Investment
2012 2.91 35.32
2013 3.31 24.23 3.00%
2014 3.63 39.26 4.25%
Calculation: Please note that for bond calculations, only one bond is used and we assume February 1, 2015 is the origination date. The value on financial statements will be considered PV (Present value). Maturity date is assumed for February 2036 and payment schedule 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 semi-annually on June 16 and December 16 PV (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes; due February 1, 2036; interest payable semi-annually on February 1 and August 1 PV (Present Value) = 2,963 million
PART II: BOND ISSUANCE
Curent Bonds from Financial Statements
Present Value PV ($2,963)
Periods N 40 Semi-annual payment: 2036-2016 = 20 years *2 = 40 periods
Interest I 2.9375 Interest paid semi-annually: 5.875%/2 = 2.9375%
Payments PMT 0 This bond does not make regular PMT except for interest
Future Value FV $9,433.58 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 PV ($2,963)
Periods N 40
Interest I 5.4375 Please adjust interest 5.875%+5% = 10.875%/2 = 5.4375%
Payments PMT 0 FV (Future Value Calculation) - using Excel Formula
Future Value FV $24,634.04 CALCULATING FV (please see help on the right hand side) Step 1) Select Formulas
Step 2) Click on Financial
Step 3) Select FV - you will see the formula below
2. The new value of the bond if overall rates in the market decreased by 5% Step 4) Enter the following:
Rate - enter as decimal, no % sign. Example: 4% as 0.04
Present Value PV ($2,963) Nper - number of period. Enter a whole number. Example 50
Periods N 40 Pmt - payment. Our example does not assume regular payments disbursing principal
Interest I 0.4375 Please adjust interest 5.875%-5% = 0.875%/2 = 0.4375% Pv - Present value. Enter as negative. Example $1,000 should be -1000
Payments PMT 0 Type - leave blank
Future Value FV $3,528.32 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

3 Capital Budgeting Data

Milestone Three: Capital Budgeting Data (please fill in the shaded YELLOW cells)
WACC 8% Capital Budgeting Example Set-up ACCEPT
Initial investment $65,000,000 REJECT
Straight-line Depreciation of 20%
Initial Outlay CF1 CF2 CF3 CF4 CF5 Income Tax @35%
($65,000,000) WACC of 8% approximately. (HD WACC was about 8.83%)
Cash Flows (Sales) $50,000,000 $45,000,000 $65,500,000 $55,000,000 $25,000,000 Cash Flow (which in this case are Sales Revenues) are as follows:
- Operating Costs (excluding Depreciation) $38,500,000 $38,500,000 $38,500,000 $38,500,000 $38,500,000 CF1: $50,000,000
- Depreciation Rate of 20% (13,000,000) (13,000,000) (13,000,000) (13,000,000) (13,000,000) CF2: $45,000,000
Operating Income (EBIT) 24,500,000 19,500,000 40,000,000 29,500,000 (500,000) CF3: $65,500,000
- Income Tax (Rate 35%) 8,575,000 6,825,000 14,000,000 10,325,000 (175,000) CF4: $55,000,00
After-Tax EBIT 15,925,000 12,675,000 26,000,000 19,175,000 (325,000) CF5: $25,000,000
+ Depreciation 13,000,000 13,000,000 13,000,000 13,000,000 13,000,000 Operating Costs
Cash Flows ($65,000,000) 28,925,000 25,675,000 39,000,000 32,175,000 12,675,000 CF1: $25,500,000
CF2: $25,500,000
Select from drop downs below: CF3: $25,500,000
NPV ($21,453,688.38) REJECT CF4: $25,500,000
CF5: $25,500,000
IRR 34% ACCEPT
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.

4 Interest Rate Implications

Milestone Four: Interest Rate Implication (please fill in shaded YELLOW cells) Explanation:
We will use Milestone 1 and Time Value of Money for Milesotne 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 FCF2 FCF3 FCF4 FCF5
Amounts* 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 5.00%
FCF1 FCF2 FCF3 FCF4 FCF5
Amounts* 113 111 108 101 97
Pv* (107.62) (100.68) (93.29) (83.09) (76.00)
Total Pv* (460.69)
*In millions
3. Change in interest rate and its implications - Higher Interest Rate (15%)
Interest Rate 15.00%
FCF1 FCF2 FCF3 FCF4 FCF5
Amounts* 113 111 108 101 97
Pv* (98.26) (83.93) (71.01) (57.75) (48.23)
Total Pv* (359.18)
*In millions

SUMMARY

SUMMARY TAB Note: This process could take up to 20 seconds
TAB 1 1. Time Value of Money TAB 3 Capital Budgeting
1006 928 845 739 658
($65,000,000)
$50,000,000 $45,000,000 $65,500,000 $55,000,000 $25,000,000
FALSE FALSE FALSE FALSE FALSE $38,500,000 $38,500,000 $38,500,000 $38,500,000 $38,500,000
TAB 2 PART I - Stock Valuation FALSE $9,785,570.71
FALSE 50%
1.16 3.28% 17,898 TRUE TAB 4 Interest Rate Implication
1.56 6.44% 17,777 TRUE
1.88 4.79% 12,522 TRUE
PART II - Bond Issuance
Current Bond Value FALSE
$9,433.28 FALSE $9,433.58
New Value +5% FALSE
5.4375 TRUE 5.4375
$24,634.04 FALSE $24,634.04
New Value - 5%
0.4375 TRUE 0.4375
$3,528.32 FALSE $3,528.32

RUN Summary

CLEAR DATA