# Interest Calculating - Excel

W
Description

Interest Calculating document sample

Shared by:
Categories
-
Stats
views:
22
posted:
11/22/2010
language:
English
pages:
9
Document Sample

```							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

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?

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

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(\$)

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%

```
Related docs