Contains detailed coverage of the powerful features of Excel 2007 essential for financ
Table of Contents
About This Book xix
CHAPTER 1 Introduction to Financial Analysis and Modeling 1
Steps in Creating a Model 5
How This Book Is Organized 9
Part One Excel for Financial Analysis and Modeling
CHAPTER 2 Excel 2007 and the Previous Versions 15
The New Features in Excel 2007 15
The New User Interface 16
Other New Features 19
New File Formats in Excel 2007 23
CHAPTER 3 Excel Basics 25
Improving Your Excel Skills 26
The Basic Excel Features 32
Naming Workbooks and Worksheets 33
Finding the Commands You Need 33
Working with Worksheets 33
Working with Rows, Columns, and Cells 34
Using the Toolbars 34
Using Keyboard Shortcuts 34
Navigating the Worksheet 36
Formatting 36
Entering and Working with Formulas 37
Using Paste Special 37
Controlling the Worksheet View 38
Using Undo and Redo 38
Customizing the Excel Work Environment 38
Controlling When Formulas Are Calculated 39
Printing 39
Creating Charts 40
Working with More Than One Workbook 40
Using the Scroll Lock 41
CHAPTER 4 Advanced Excel Features 43
Copying Formulas Using Absolute and Relative Cell References 46
Using Names in Formulas 56
A1 and R1C1 Reference Styles 57
Using Comments in Cells 58
Data Validation 60
Controls 62
Custom Number Formats 65
Hiding and Protecting 67
Custom Views 71
Arrays and Array Formulas 72
Outline 75
CHAPTER 5 Making Decisions and Looking Up Values 77
Making Decisions 78
Looking Up Values 97
Conditional Formatting 103
CHAPTER 6 Analyzing Databases 109
Sorting 112
Filtering 120
Tables 124
PivotTable 135
CHAPTER 7 Answering What-If Questions 161
Data Tables 162
Scenario Manager 166
CHAPTER 8 Finding Iterative Solutions 171
Circular Reference 171
Goal Seek 174
Solver 177
CHAPTER 9 Doing Statistical Analysis 185
Descriptive Statistics 187
Frequency Distributions and Histograms 192
Counting, Ranking, and Calculating Quantiles 195
Normal Probability Distributions 203
Random Numbers 207
Statistical Tests 218
CHAPTER 10 Using the Financial Functions 221
Annuity Functions 221
Investment Analysis Functions 228
Bond Functions 232
Depreciation Functions 237
CHAPTER 11 Other Useful Excel Functions 243
Mathematical Functions 243
Date and Time Functions 248
Text Functions 251
Information Functions 254
Part Two Financial Modeling Using Excel
CHAPTER 12 Building Good Excel Models and Debugging Them 257
Attributes of Good Excel Models 257
Documenting Excel Models 260
Debugging Excel Models 262
Using Formula Auditing Tools for Debugging 265
Learning Modeling Using Excel 267
CHAPTER 13 Financial Statements Forecasting 271
Review of Theory and Concepts 271
Modeling Examples 281
CHAPTER 14 Time Value of Money 327
Review of Theory and Concepts 327
Modeling Examples 341
CHAPTER 15 Financial Planning and Investments 365
Review of Theory and Concepts 365
Some Basic Issues 365
Saving and Investing for Retirement 368
Structuring Portfolios 371
Modeling Examples 372
Model 1: Return with Changing Reinvestment Rates 372
Model 2: Saving for Retirement 376
Model 3: Retirement Planning in Real Dollars 379
Model 4: Retirement Planning in Nominal Dollars 380
Model 5: Portfolio Structuring 385
CHAPTER 16 Analyzing Market History 389
Review of Theory and Concepts 389
Modeling Examples 394
CHAPTER 17 Bond Pricing and Duration 409
Review of Theory and Concepts 409
Modeling Examples 416
CHAPTER 18 Simulating Stock Prices 427
Review of Theory and Concepts 427
Modeling Examples 438
CHAPTER 19 Options and Option Portfolios 449
Review of Theory and Concepts 449
Modeling Examples 460
CHAPTER 20 Binomial Option Pricing 487
Review of Theory and Concepts 487
Modeling Examples 491
Model 1: European Options on Stocks with
Known Dividend Yield 491
Model 2: American Options on Stocks Paying
No Dividend 495
Model 3: American Options on Stocks with
Known Dividend Yields 498
Model 4: American Options on Stocks with
Known Dollar Dividends 500
Part Three VBA for Financial Modeling
CHAPTER 21 Introduction to VBA 505
Example of a VBA Model 510
Recording Macros 520
Using VBA’s Help 524
The Strategy to Learn VBA Efficiently 526
CHAPTER 22 VBA Essentials 529
The Visual Basic Editor 530
Some Basics 535
Variables, Constants, and Arrays 545
Objects, Properties, and Methods 557
Branching 561
Looping 567
Using Built-In Functions 574
CHAPTER 23 Sub and Function Procedures 577
Sub Procedures 577
Function Procedures 582
CHAPTER 24 Debugging VBA Codes 589
The Four Basic Types of Bugs 590
The VBA Debugging Tools 591
Part Four Financial Modeling Using VBA
CHAPTER 25 How to Build Good VBA Models 597
Attributes of Good VBA Models 597
Documenting VBA Models 601
Learning Modeling Using VBA 603
CHAPTER 26 Time Value of Money 607
Review of Theory and Concepts 607
Modeling Examples 611
CHAPTER 27 Financial Planning and Investments 663
Modeling Examples 663
CHAPTER 28 Analyzing Market History 691
Modeling Examples 691
CHAPTER 29 Simulating Stock Prices 717
Modeling Examples 717
CHAPTER 30 Options and Option Portfolios 725
Modeling Examples 725
CHAPTER 31 Binomial Option Pricing 741
Modeling Examples 741
APPENDIX A Keyboard Shortcuts for Excel 755
APPENDIX B VBA Quick Reference 757
APPENDIX C Excel and VBA Built-In Functions 759
About the CD-ROM 777
Index 781
