EXCEL MODELING AND ESTIMATION IN CORPORATE FINANCE,
FOURTH
EDITION
Contents
Preface,
Desk
Copy,
Purchase on Amazon,
Free US Yield Curve Dynamics.
PART 1 TIME VALUE OF MONEY..1
Chapter 1 Single
Cash Flow..............................................1
1.1
Present Value........................................................................................1
1.2 Future Value..........................................................................................2
Problems........................................................................................................3
Chapter 2 Annuity............................................................4
2.1 Present Value........................................................................................4
2.2 Future
Value..........................................................................................5
2.3 System of
Four Annuity Variables....................................................6
Problems........................................................................................................7
Chapter 3 NPV Using Constant
Discounting.....................8
3.1
Nominal Rate.........................................................................................8
3.2 Real Rate................................................................................................9
Problems......................................................................................................10
Chapter 4 NPV
Using General Discounting.....................11
4.1
Nominal Rate.......................................................................................11
4.2 Real
Rate..............................................................................................13
Problems......................................................................................................15
Chapter 5 Loan
Amortization.........................................16
5.1
Basics...................................................................................................16
5.2 Sensitivity
Analysis...........................................................................17
Problems.....................................................................................................19
PART 2 VALUATION................................20
Chapter 6 Bond
Pricing..................................................20
6.1
Annual
Payments...............................................................................20
6.2 EAR, APR, and Foreign
Currencies..............................................21
6.3
Duration and
Convexity....................................................................26
6.4 Price
Sensitivity................................................................................28
6.5
Immunization.....................................................................................30
6.6
System of Five Bond
Variables.......................................................36
Problems.....................................................................................................37
Chapter
7 Estimating the Cost of Capital.......................40
7.1 Static CAPM
Using Fama-MacBeth Method................................40
7.2 APT or
Intertemporal CAPM Using Fama-MacBeth Method...46
Problems.....................................................................................................52
Chapter
8 Stock Valuation..............................................53
8.1
Dividend Discount Model.................................................................53
Problems....................................................................................................54
Chapter 9 Firm and Project Valuation............................55
9.1
Cash Flows for Five Equivalent Methods......................................55
9.2 Adjusted
Present Value...................................................................58
9.3 Free Cash
Flow to Equity.................................................................59
9.4 Free Cash
Flow to the Firm.............................................................60
9.5 Dividend
Discount Model.................................................................61
9.6 Residual
Income................................................................................62
9.7
Five Equivalent Methods..................................................................63
Problems....................................................................................................72
Appendix:
Reconciling the Residual Income Method with Other
Approaches to Valuing Firms or
Projects..........................................73
Chapter
10 The Yield Curve............................................79
10.1
Obtaining It From Treasury Bills and Strips..........................79
10.2 Using It To
Price A Coupon Bond...............................................80
10.3 Using It
To Determine Forward Rates.......................................81
Problems...................................................................................................82
Chapter
11 U.S. Yield Curve Dynamics...........................83
11.1 Dynamic
Chart..............................................................................83
Problems..................................................................................................87
PART 3 CAPITAL STRUCTURE....89
Chapter 12 Capital Structure...........................................89
12.1
Modigliani-Miller With No Taxes..............................................89
12.2
Modigliani-Miller With Corporate Taxes.................................91
12.3
Trade-off Model: Tax Shield vs. Distress Cost.........................93
Problems....................................................................................................95
PART 4 CAPITAL BUDGETING....96
Chapter 13 Project NPV.................................................96
13.1 Basics.............................................................................................96
12.2 Forecasting Cash
Flows..............................................................99
13.3
Working Capital........................................................................100
13.4
Sensitivity Analysis...................................................................102
Problems................................................................................................105
Chapter 14 Cost-Reducing Project................................106
14.1 Basics...........................................................................................106
14.2 Sensitivity Analysis...................................................................109
Problems................................................................................................109
Chapter 15 Break-Even Analysis...................................111
15.1 Based On
Accounting Profit....................................................111
15.2 Based On NPV............................................................................114
Problems................................................................................................118
PART 5 FINANCIAL PLANNING.119
Chapter 16 Corporate Financial Planning.....................119
16.1 Actual...........................................................................................119
16.2
Forecast........................................................................................122
16.3 Cash Flow.....................................................................................126
16.4 Ratios............................................................................................128
16.5 Sensitivity....................................................................................130
16.6 Full-Scale Estimation................................................................131
Problems................................................................................................137
Chapter 17 Du Pont System of Ratio Analysis..............140
17.1
Basics...........................................................................................140
Problems................................................................................................141
Chapter
18 Life-Cycle Financial Planning.....................142
18.1 Basics...........................................................................................142
18.2 Full-Scale Estimation.................................................................144
Problems................................................................................................152
PART 6 INTERNATIONAL
CORPORATE
FINANCE....153
Chapter 19 International Parity...................................153
19.1 System of Four Parity Conditions.........................................153
19.2 Estimating Future Exchange Rates.......................................155
Problems..............................................................................................157
PART 7 OPTIONS AND
CORPORATE
FINANCE...158
Chapter
20 Binomial Option Pricing...........................158
20.1 Estimating Volatility...............................................................158
20.2 Single Period............................................................................159
20.3 Multi-Period..............................................................................162
20.4 Risk Neutral.............................................................................166
20.5 Average of N and
N-1...............................................................169
20.6 Convergence to
Normal...........................................................172
20.7 American With Discrete Dividends......................................174
20.8 Full-Scale...................................................................................178
Problems..............................................................................................183
Chapter 21 Real Options.............................................185
21.1 NPV Correctly vs. NPV Ignoring Option............................185
Problems..............................................................................................188
Chapter 22 Black Scholes Option Pricing....................189
22.1 Basics.........................................................................................189
22.2
Continuous Dividend................................................................190
22.3 Implied
Volatility......................................................................194
Problems..............................................................................................196
Chapter
23 Debt and Equity Valuation........................198
23.1 Two Methods..............................................................................198
21.2 Impact of Risk...........................................................................200
Problems..............................................................................................201
PART
6 EXCEL SKILLS...................202
Chapter 24
Useful Excel Tricks...................................202
24.1 Quickly Delete The Instruction Boxes and Arrows........202
24.2 Freeze Panes...........................................................................202
24.3 Spin Buttons and the Developer Tab...................................203
24.4 Option Buttons and Group Boxes........................................204
24.5 Scroll Bar................................................................................206
24.6 Install Solver or the Analysis
ToolPak..............................207
24.7 Format Painter........................................................................207
24.8 Conditional Formatting.........................................................208
24.9 Fill Handle...............................................................................209
24.10 2-D Scatter Chart................................................................209
24.11 3-D Scatter Chart................................................................211
CONTENTS ON CD
Excel
Modeling in Corporate Finance Fourth Edition.pdf
Ready-To-Build
spreadsheets: Ch
01 Single Cash Flow - Ready-To-Build.xlsx Ch
02 Annuity - Ready-To-Build..xlsx Ch
03 NPV Using Constant Discounting - Ready-To-Build..xlsx Ch
04 NPV Using General Discounting - Ready-To-Build..xlsx Ch
05 Loan Amortization - Ready-To-Build..xlsx Ch
06 Bond Valuation - Ready-To-Build..xlsx Ch
07 Estimating the Cost of Capital.xlsx Ch
08 Stock Valuation - Ready-To-Build..xlsx Ch
09 Firm and Project Valuation - Ready-To-Build..xlsx Ch
10 The Yield Curve - Ready-To-Build..xlsx Ch
11 US Yield Curve Dynamics - Ready-To-Build..xlsx Ch
12 Capital Structure - Ready-To-Build..xlsx Ch
13 Project NPV - Ready-To-Build..xlsx Ch
14 Cost-Reducing Project - Ready-To-Build..xlsx Ch
15 Break Even Analysis - Ready-To-Build..xlsx Ch
16 Corporate Financial Planning - Ready-To-Build..xlsx Ch
17 DuPont System of Ratio Analysis - Ready-To-Build..xlsx Ch
18 Life-Cycle Financial Planning - Ready-To-Build..xlsx Ch
19 International Parity - Ready-To-Build..xlsx Ch
20 Binomial Option Pricing - Ready-To-Build..xlsx Ch
21 Real Options - Ready-To-Build..xlsx Ch
22 Black-Scholes Option Pricing - Ready-To-Build..xlsx Ch
23 Debt and Equity Valuation - Ready-To-Build..xlsx
Files
in Excel 97-2003 (xls) Format