Interest Calculating - Excel

							Week 9 Spreadsheet

IRGN424 - Corporate Finance
Fall 2007
Brian K. Richter

This .xls file contains two worksheets about valuation of firms, their
equity, and share prices, using different variations on Discounted Cash
Flow (DCF) techniques.

One on 'Impact of Taxation' that helps to show that there is a tax
advantage to issuing debt.

The other 'Optimal D-V w Credit Ratings' helps to illustrate the
realities a firm may face when it decides on its debt policy taking into
account how the bond market prices debt.
And another on WACC under the assumptions of the MM propositions
and what happens to r_equity, r_debt as leverage increases.


Note: For the 'Optimal D-V w Credit Ratings' Worksheet, you need to
go to 'Excel Options', 'Formulas' and enable 'Iterative Calculation' to
avoid 'Circular References' errors.
   Standard DCF vs. Adjusted Present Value (APV)                           Questions
                                                                           Changing CFs on machine in Standard DCF
   This example is based on the data for Sangria Corporation in            to the Implied Return on Equity to Invest
   Ch. 19 of BMA (p 504-6). It is for a company that has one               about the suitability of using WACC to dis
   project only.

   The goal is to compare valuations calculated using Standard
   DCF analyses with those done using the Adjusted Present
   Value Approach (APV).
                                                                           Which method--the standard DCF or the A
   A secondary goal is to test our understanding of the                    of ther firm? Why?
   assumptions implicit in DCF analyses.



                                                                           Which do we use Book or Market Values?




Assumptions about Sangria Corporation
Book Value of Debt                                                  500
Book Value of Equity                                                500
Market Value of Debt                                                500
Market Value of Equity Shares                                        7.5
Equity Shares Outstanding                                           100
Market Value of Equity

Investment in Crushing Machine                                    1250.0
Perpetual Crushing Machines CFs                                    173.1

Sangria Valuation using Standard DCF Method

Caluating Free Cash Flows
Investment in Crushing Machine
Perpetual Crushing Machines CFs
Tax
After Tax CF

Standard DCF Valuation
PV of Investment CFs
PV Perpetual CF
NPV of Company

Other Calculations of Interest
After Tax Interest Payments Annually
After Tax Equity Earnings Annually

Implied Return on Equity to Investors
Questions
Changing CFs on machine in Standard DCF Valuation and what happens
to the Implied Return on Equity to Investors? What does this indicate
about the suitability of using WACC to discount?




Which method--the standard DCF or the APV--yields a higher valuation
of ther firm? Why?




Which do we use Book or Market Values? Why?




       Cost of Debt (rD)                                        6.0%
       Cost of Equity (rE)                                     12.4%
       Tax Rate                                                 35%

       D/V
       E/V

       After Tax WACC
       Opportunity Cost of Capital (e.g. WACC)

       Sangria Valuation using Adjusted Present Value Method

       Caluating Free Cash Flows
       Investment in Crushing Machine
       Perpetual Crushing Machines CFs
       Tax
       After Tax CF

       NPV of Base Case DCF
       PV of Investmetn CFs
       PV of Perpetual CFs
       NPV of Base Case

       Calculating NPV of Interest Tax Shield
       $ of Debt Financing Used                                  500
NPV of Interest Tax Shield

Adjusted Present Value Calculation
APV Value of Firm
 DCF Valuation from the perspective of an External Analyst             Questions
                                                                       What percentage of the value of the firm is i
 The purpose of this spreadsheet is to think about the DCF valuation
 of a firm as if you were an External Analyst. If you are in such a
 position you make assumptions about the future based on ratios        Should we be concerned with how much of t
 computed from firms financial statements.
                                                                       horizon value? Why or Why not?
 Here we also set up our analysis to calculated Free Cash Flows (the
 firm has) from publicly available financial statements e.g. Income
 Statements and Balance Sheets.
                                                                       By what percentage does the value of the fir
 Dark grey cells represent historical data while light blue ones       terminal growth rate rises from 3% to 4%?
 represent assumptions.
                                                                       Would you pay 3.25 for a share? Why or wh



Pro Forma Income Statement (in $M)
                                                Data from
                                               Most Recent
                                                 Period                                Forecast for Future Periods
Year                                                 0             1        2           3
Sales                                                     83.6
Cost of goods sold                                        63.1
EBITDA
Depreciation                                               3.3
EBIT
Tax
Earnings after Taxes


Balance Sheet, Selected Data on Fixed Assets (, Depreciation), and Working Capital (in $M)
Gross fixed assets
Less accumulated depreciation                       29.0
Net fixed assets                                    66.0
Net working capital                                 11.1


Data Computed for Calculation of Free Cash Flows (in $M)
Investment (change) in gross fixed
assets                                             11.0

Investment (change) in working capital                     1.0


Valuation of Firm (in $M)
Future Free cash flow
PV of Future Free Cash Flows
NPV of FCFs
Horizon Value
PV Horizon value
Enterprise Value

Equity Valuation
Value of Firm's Equity (in $M)
Share Price($)


Assumptions about Firm's Future Activities:
Sales growth rate (%)                          6.7%     7.0%    7.0%    7.0%
Costs (% of sales)                            75.4%    74.0%   74.5%   74.5%
Working capital (% of sales)                  13.3%    13.0%   13.0%   13.0%
Net fixed assets (%of sales)                  78.9%    79.0%   79.0%   79.0%
Depreciation (% of net fixed assets)           5.0%    14.0%   14.0%   14.0%


Core Assumptions
Tax rate                                      35.0%
After Tax WACC                                 9.0%
Long-term growth forecast                      3.0%
Market Value of Debt (in $M)                    36.0
Equity Shares Outstanding (M)                     15
tage of the value of the firm is in the horizon value?



e concerned with how much of the value of the firm is in the
e? Why or Why not?




entage does the value of the firm change if the expected
wth rate rises from 3% to 4%?

ay 3.25 for a share? Why or why not?




   Forecast for Future Periods
                4           5              6        Terminal
 4.0%    4.0%    4.0%    3.0%
75.0%   75.0%   75.5%   76.0%
13.0%   13.0%   13.0%   13.0%
79.0%   79.0%   79.0%   79.0%
14.0%   14.0%   14.0%   14.0%