Exceptional Proff

profileroebob7l6g
workbookmile1.xlsx
Home>Business & Finance homework help>Accounting homework help>Exceptional Proff

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) refers to the net change of cash generated from business operations within a specified reporting period, less capital expenditures, cash outlay tied up in working capital and dividends issued within the same accounting period. The FCF is one of the best indicator of a resilient entity with a good going concern. Note: For this part of the Milestone, please use page 43 -capital lease payments under property.
FCF1 FCF2 FCF3 FCF4 FCF5
Amounts* 113.1 111.1 108.2 101.3 97.4
Pv* (105) (95) (86) (74) (66)
Total Pv* (427)
*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 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 0 17,700,000 165714.285714286
2013 1.56 0 12,522,000 156000000
2014 1.88 0 9,322,000 9400000
1. Stock Valuation - Position of the company's dividend yield when the firm increases its dividend per share as follow(above)
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 0 17,700,000 165714.285714286
2013 3.31 0 12,522,000 156000000
2014 3.63 0 9,322,000 9400000
2. Dividend yield when the company decides to double the 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 0 35,400,000 165714.285714286
2013 0.78 0 25,044,000 156000000
2014 0.94 0 18,644,000 9400000
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 ROE using the calculated dividend yield from above (i.e., the cost of stock)
Year Cash Div/Share ($) +1.75 Stock Price Return on Investment
2012 2.91 165,714
2013 3.31 156,000,000 943.69%
2014 3.63 9,400,000 2.69%
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: Senior Notes 5.875% ; Payable Dec 16th, 2036; interest is paid twice annually commencing 16th of June and December. PV (Present Value) = 2,963 million Our scenario: 5.875% Senior Notes that are due on 1st of February, 2036; and the interest due semi-annually on the 1st of February and August. 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.94 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,434 CALCULATING FV (please see help on the right hand side)
1. New Bond value when Market rates shoot up by 5%
Present Value PV (2,963)
Periods N 40
Interest I 0.054 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 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. New Bond value when Market rates reduce 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 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 16,428 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) $25,500,000 $25,500,000 $25,500,000 $25,500,000 $25,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) 37,500,000 32,500,000 53,000,000 42,500,000 12,500,000 CF3: $65,500,000
- Income Tax (Rate 35%) 13,125,000 11,375,000 18,550,000 14,875,000 4,375,000 CF4: $55,000,00
After-Tax EBIT 24,375,000 21,125,000 34,450,000 27,625,000 8,125,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) 37,375,000 34,125,000 47,450,000 40,625,000 21,125,000 CF1: $25,500,000
CF2: $25,500,000
Select from drop downs below: CF3: $25,500,000
NPV 9,785,571 ACCEPT CF4: $25,500,000
CF5: $25,500,000
IRR 50% 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* (105) (95) (86) (74) (66)
Total Pv* (427)
*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* (108) (101) (93) (83) (76)
Total Pv* (461)
*In millions
3. Change in interest rate and its implications - Higher Interest Rate (15%)
Interest Rate 15%
FCF1 FCF2 FCF3 FCF4 FCF5
Amounts* 113 111 108 101 97
Pv* (98) (84) (71) (58) (48)
Total Pv* (359)
*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
113 111 108 101 97
$65,000,000
$50,000,000 $45,000,000 $65,500,000 $55,000,000 $25,000,000
TRUE TRUE TRUE TRUE TRUE $25,500,000 $25,500,000 $25,500,000 $25,500,000 $25,500,000
TAB 2 PART I - Stock Valuation TRUE $9,785,570.71
TRUE 50%
1.16 0.00007% 17,700,000 TRUE TAB 4 Interest Rate Implication
1.56 0.000001% 12,522,000 TRUE
1.88 0.00002% 9,322,000 TRUE
PART II - Bond Issuance
Current Bond Value 5.00% TRUE
$9,433.58 TRUE $9,433.58
New Value +5% 15% TRUE
0.054375 TRUE 5.4375
$24,634.04 TRUE $24,634.04
New Value - 5%
0.04375 TRUE 0.4375
$16,428.25 TRUE $3,528.32

RUN Summary

CLEAR DATA