Description
Book SynopsisThis book is written for financial analysts who have a working knowledge of Excel and VBA and who wish to enhance their marketability by improving their modeling expertise. The goal of the book is to broaden the reader's VBA skills and at the same time to progressively integrate Access, PowerPoint, and Outlook into an existing model. Significant attention is also directed to the design and execution of an Excel/VBA interface employing detailed UserForms. In addition, the construction of various reports, first in Excel, then Access, and finally PowerPoint is undertaken. Regardless of your area of financial expertise, this book serves as an essential guide to mastering leveraging the effects of Microsoft products in whatever applications you choose to build.
The focus of the book is a case study containing a structured finance model. The case study model was originally designed for the securitization of a portfolio of commercial small business loans. This model now needs to be expand
Table of Contents
Preface xv
Goals of the Book xv
What You Should Know xvi
Setting the Context for Learning xviii
The Structure of the Book xix
A Fish Story xxiii
A Perspective on Modeling xxiii
Approaching This Material xxiv
Style xxv
A Parting Remark xxv
On the Web Site xxvii
Acknowledgments xxxv
PART 1 First Steps 1
CHAPTER 1 Introduction 5
Overview 5
Why Was This Book Written? 5
Who Is the Target Audience? 9
What Is the Purpose of the Book? 10
Expanding Your Software Skills 10
Expanding Your Model Design Skills 12
Expanding Your Finance Knowledge 12
Organized to Teach 14
Chapter Organization 15
Accompanying Web Site 17
Learning the “Hard” Way 18
Note 19
CHAPTER 2 The Existing Model 21
Overview 21
Deliverables 22
Under Construction 23
Crisis du Jour 23
Overview of the Current Model 24
Current Model Environment 51
On the Web Site 51
CHAPTER 3 Conventions and Advice 55
Overview 55
Deliverables 56
VBA Conventions 56
Common Sense 70
On the Web Site 72
CHAPTER 4 Segregation of the Existing Model’s Functionality 73
Overview 73
Deliverables 73
Under Construction 73
Deliverables Checklist 74
Breaking Up Is Hard to Do 74
Accommodating Our Design Needs 76
Advantages of Functional Segregation 76
Disadvantages of Functional Segregation 79
On the Web Site 80
CHAPTER 5 Creating the Base Asset Model 81
Overview 81
Deliverables 81
Under Construction 82
The Big Picture: “Just the Assets, Ma’am” 82
Stepping through the Model 84
Testing the Completed Base Asset Model 119
On the Web Site 121
CHAPTER 6 Building the Base Liabilities Model 123
Overview 123
Deliverables 123
Under Construction 124
Liabilities Side of the Model 125
What to Leave In 126
Stepping through the Model 126
Reading the Cash Flows and Assumptions from a File 151
Testing the Completed Base Liabilities Model 162
On the Web Site 164
CHAPTER 7 Establishing the Model Environment 167
Overview 167
Deliverables 168
Under Construction 168
Importance of a Standardized Directory Structure 168
Creating Directories and Defining Their Functions 168
Operating Directories 170
Administrative Directories 175
Creating New Directories for the Model as We Need Them 176
On the Web Site 177
PART 2 Building the New Assets Model 179
CHAPTER 8 Designing the New Collateral Cash Flow Generator 181
Overview 181
Deliverables 183
Under Construction 183
Improving the CCFG Menus: Conversion to UserForms 183
Improving the CCFG Data-Handling Capabilities 199
Improving the CCFG Collateral Selection Process 209
Improving the CCFG Cash Flow Generation Process 212
Improving the CCFG Report Generation Process 215
Improving the CCFG Messaging Process 220
On the Web Site 220
CHAPTER 9 Writing the CCFG Menus and Data Sheets 221
Overview 221
Deliverables 221
Under Construction 222
Menus and UserForms 222
Menus of the CCFG 241
Main Menu 242
Run Options Menu 243
Collateral Pool Menu 255
Collateral Geographic Selection Criteria Menu 259
Financial Selection Criteria Menu 288
Cash Flow Amortization Parameters Menu 299
Collateral Reports Menu 303
On the Web Site 316
CHAPTER 10 Writing the Collateral Data Handling Code 317
Overview 317
Deliverables 318
Under Construction 318
Managing Multiple Portfolio Files 320
Initial Data Screening 335
Writing the Screening Process VBA Code 343
Writing the Initial Data Screening Reports 351
Writing the Demographic Methodology Risk Reports 360
Building a Representative Line Generator Program 365
Building the Rep Line Generator Program 369
On the Web Site 387
CHAPTER 11 Writing the Collateral Selection Code 389
Overview 389
Deliverables 390
Under Construction 390
Building the Code 391
Financial and Demographic Selection Code 392
Financial Collateral Selection Process Reporting 398
Geographic Selection Code 416
Geographic Concentration Code 419
Introduction to Geographic Reporting 433
On the Web Site 458
CHAPTER 12 Writing the Collateral Cash Flow Amortization Code 459
Overview 459
Deliverables 459
Under Construction 460
Quick Review of Existing CF Generation Code 461
Adding New Mortgage Types 462
New Prepayment and Default Methodologies 473
Uniform Methodology 474
Geographic Methodology 478
Demographic Methodology 493
Reporting the Results of the Cash Flow Calculations 503
Writing the Assumptions Report Package 511
On the Web Site 518
CHAPTER 13 Writing the CCFG Reporting Capability 519
Overview 519
Deliverables 520
Under Construction 520
Recapping the CCFG Report Package 521
CCFG Activities and Reports 522
Eligible Collateral Assessment Process 524
Geographic/Demographic Reports 555
Presentation Reports 560
PART 3 Building the New Liabilities Model 583
CHAPTER 14 Designing the Liabilities Waterfall Model 585
Overview 585
Deliverables 585
Under Construction 585
Structure of the Deal: Sources and Uses of Funds 586
Design Elements of the LWM 589
Main Menu 590
Report Package Menu UserForm 590
Structure Inputs Menu 591
Liabilities Waterfall Worksheet 592
Performance Summary Page 600
VBA Code Modules 600
Report Template Files 604
On the Web Site 616
CHAPTER 15 Writing the Liabilities Waterfall Model Spreadsheet 617
Overview 617
Deliverables 617
Under Construction 618
Deal Structure Inputs 618
Building the Liabilities Waterfall Model 623
Period Factors Worksheet 650
Total Cash Flows Section 651
Results Page Worksheet 651
On the Web Site 657
CHAPTER 16 Writing the LWM VBA Code 659
Overview 659
Deliverables 660
Under Construction 660
VBA Requirements of the LWM 665
Menus of the LWM 666
Main Program 679
Error Checking the Main Menu Inputs 681
Importing the Liability Structure Inputs 682
Importing the Cash Flows of the CCFG 686
Loading and Running the LWM 693
Reporting the Results 696
Other Functionality 718
PART 4 Access, PowerPoint, and Outlook 721
CHAPTER 17 Access: An Introduction 723
Overview 723
Deliverables 724
Under Construction 724
Basics of Access 724
Opening Access and Creating a Database 726
Access Tables 728
Getting the Data In: Entering Data 731
Getting the Data Back Out: Queries 740
Basics of Structured Query Language 754
Concluding Remarks 762
On the Web Site 762
CHAPTER 18 Implementing Access in the CCFG and LWM 763
Overview 763
Deliverables 764
Under Construction 764
Access and the Collateral Cash Flow Generator 766
Collateral Portfolio Data 767
Geographic Selection Criteria 781
Geographic Concentration Criteria 794
Initial Data Screening Criteria 796
Custom Financial Selection Criteria 805
Run Options Elections 815
Generating the Loan Portfolio 818
Monthly Cash Flows Scenarios 822
Access in CCFG Reporting 825
Access and the Liabilities Waterfall Model 827
Connecting the LWM to the CCFG 827
Structure Worksheet Objects 828
Retrieving the Monthly Cash Flow Scenarios 831
Storing LWM Run Results in the Database 832
Concluding Remarks 843
On the Web Site 843
CHAPTER 19 Implementing PowerPoint and Outlook in the CCFG 845
Overview 845
Deliverables 845
Under Construction 846
Using PowerPoint in the CCFG 846
Data Requirements 851
Adding a New Class of Reports 856
Introduction to PowerPoint in VBA 860
Preparing the Data for Use 867
First Time Homeowners Slide 869
Creating the Null Report 874
MS Outlook 876
On the Web Site 882
PART 5 Running the CCFG and the LWM 885
CHAPTER 20 Running the Models 887
Overview 887
Deliverables 887
Under Construction 888
Running the CCFG, Analyzing the Asset Side of the Deal 888
Liabilities Side of the Deal 923
Preparing a PowerPoint Presentation about Geographic Concentration 933
On the Web Site 935
Afterword 941
Exhibits Index 943
Subject Index 975
