Rent 9780470562390 | Intermediate Structured Finance Modeling, with Website : Leveraging Excel, VBA, Access, and Pow... | William Preinitz; Matthew Niedermaier @knetbooks.com

Summary Table of Contents Author Biography

This book leads the reader through a pragmatic, hands-on approach to reaching an intermediate level of sophistication as a financial modeler. After outlining the fundamental concepts of Asset Backed Securitization the book presents a basic Structured Finance model that can be downloaded from the Web site and run in a series of cases. The stage is then set. The book takes this existing model and significantly expands both its scope and sophistication to meet a new business challenge. To accomplish this task the original model is bifurcated along... MORE its Asset/Liability lines forming two new models. These models then serve as the twin bases that will be successively developed throughout the remainder of the book.The final form of the two successor models (also available on the Web site), contain extensive interface, analytical and reporting improvements. Widespread implementation of Excel UserForms as menus, well organized cash flow analytics and extensive sets of report packages make these applications of interest to the general modeler as well as the financial specialist. Access is then introduced to streamline various input/output processes of both models. As a finishing touch the models are expanded to produce ready-to-use PowerPoint presentations and incorporate Outlook to automatically deliver the report packages.Extending the analyst's organizational, coding, modeling, and financial skills the book advances in a step-by-step manner to new and more powerful applications. Building on the reader's established knowledge of Excel and VBA you will progressively develop a pair of broader and more sophisticated models. The book contains 6 fully developed models with their supporting data, input, and reporting files as the instructional platform. The book contains over 725 exhibits of reports, examples, and code samples, designed to provide the analyst with a clear, concise, but detailed development plan. The Web site contains 5 additional chapters covering bond math, mortgage math, understanding Excel waterfalls, fundamental securitization principles and how to run the base model. Provides a code intensive approach to improving basic VBA and Excel skills and learning how to integrate them with a real life application. Outlines and implements a series of analytical subroutines to calculate uniform, geographic, and demographic analysis of mortgage cash flows. Also included are extensive stratification reporting and collateral concentration sizing algorithms. Also by William Preinitz: A Fast Track to Structured Finance Modeling, Monitoring and Valuation: Jump Start VBADeveloping Intermediate Financial Models - Leveraging Excel, VBA, Access, PowerPoint and Outlook; a Structured Finance Approach is a straightforward, no-nonsense approach to developing advanced skills as a financial modeler.

+ This book provides a pragmatic, hands-on, no-nonsense approach to reaching an Intermediate Level of sophistication as a financial modeler.

A pragmatic, hands-on approach to reaching an intermediate level of sophistication as a financial modeler Intermediate Structured Finance Modeling is packed with insight and tips to teach you step by step to expand your current level of organizational, coding, modeling, and financial skills to enhance and develop new and more powerful applications. It expertly employs a code-intensive approach to improve your VBA and Excel skills and demonstrates how to integrate them with a real-life application. Providing you with a set of immediately applicable modeling approaches, techniques, skills, and examples with a focus on large model development, you will learn how to: Organize and build a set of directories to contain the model system you will develop Separate an existing basic structured finance model into two special-purpose models, an Asset (collateral) model and a Liabilities Structure (bonds) model Expand the computational, data management, and reporting capabilities of both new models Employ Excel UserForms to build a sophisticated model interfaces Introduce Access databases and code to manage model data requirements and outputs Dramatically expand existing reporting packages, producing more sophisticated analytical results Automate the production of ready-to-use PowerPoint presentations Improve your models ability to distribute its results by integrating Outlook mailing lists If you have some modeling experience and are confident in your mastery of the fundamental features of VBA and Excel, Intermediate Structured Finance Modeling is the book for you. With working solutions to the challenges of developing larger???scale models, you will learn successful intermediate modeling techniques that can be applied to any real-world situation and activity. This book is the intermediate-level companion volume to A Fast-Track to Structured Finance Modeling, Monitoring, and Valuation: Jump Start VBA, an introductory work on the subject.

This 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 expanded to securitize a much larger portfolio of residential mortgages.�

The Original Structuring model will be bifurcated into a Sources of Funds model, the assets of the collateral pool, and a Uses of Funds model, the liabilities waterfall model.� This will give the reader experience working with an existing model as a base departure point of development rather than the less common �start from scratch� approach.� Once the Base Asset Model (BAM) and the Base Liabilities Models (BLM) are complete, each model will serve as the platform for future development.� From the BAM and BLM a pair of much more robust models, the Collateral Cash Flow Generator (CCFG) and the Liabilities Waterfall Model (LWM), respectively, are created.� The CCFG is designed to process a significantly greater quantity, variety, and complexity of collateral types.� It also introduces the analyst to Geographic based prepayment methods as well as those using Financial/Demographic factor approaches. The LWM, operating independently of the CCFG, reads the collateral cash flow scenarios and applies them across a two tiered, six tranche bond structure.� The liabilities waterfall contains provision for an interest rate swap, performance triggers and a variety of other credit enhancement features.

With the CCFG and LWM now complete Access is introduced.� Access will replace various Excel/VBA code modules in the CCFG such as the collateral data management, data scrubbing and reporting, data sufficiency testing, and eligibility screening.� Access is also employed to export and the various collateral cash flows. � In the LWM Access is used to import the cash flows, store the liability structure specifications, and export/save the resultant structure performance.� PowerPoint is then introduced in the CCFG to allow for the fully automated production of presentation report packages.� Last Outlook is added to the models to inform the analyst of model performance and to automatically distribute the various report packages to pre-designated mailing lists.

The book contains over 750 exhibits of reports, menus, calculation, and algorithmic examples and most of all code.� The complete Excel, VBA, Access, PowerPoint, and Outlook code for all of the models is included on the Web site.� The Web site also includes Web chapters containing detailed instructions on how to create and populate a directory environment to store this code, run the models, and organize the results.� Lastly there are other Web chapters containing supplemental information explaining the structure and function of the original model, bond and mortgage math (with calculation examples), representative line generator programs, and a tutorial on the construction and integration of UserForms.

This book is the intermediate level companion volume to A Fast-Track to Structured Finance Modeling, Monitoring, and Valuation: Jump Start VBA; Preinitz; Wiley 2009, an introductory work on the subject.

... MORE

Preface
Goals of the Book
What You Should Know
Setting the Context for Learning
The Structure of the Book
A Fish Story
A Perspective on Modeling
Approaching This Material
A Parting Remark
About the Web Site
First Steps
Introduction
Overview
Why Was This Book Written?
Who Is the Target Audience?
What Is the Purpose of the Book?
Expanding Your Software Skills
Expanding Your Model Design Skills
Expanding Your Finance Knowledge
Organized to Teach
Chapter Organization
The Accompanying Web Site
Learning the "Hard" Way
The Existing Model
Overview
Deliverables
Under Construction
The Crisis du Jour
Overview of the Current Model
Current Model Environment
On the Web Site
Conventions and Advice
Overview
Deliverables
VBA Conventions
Common Sense
On The Web Site
Segregation of the Model's Functionality
Overview
Deliverables
Under Construction
Deliverables Checklist
Breaking Up Is Hard to Do
Accommodating Our Design Needs
Advantages of Functional Segregation
Disadvantages of Functional Segregation
On the Web Site
Creating the Base Asset Model
Overview
Deliverables
Under Construction
The Big Picture: "Just the Assets, Ma'am"
Stepping Through the Model
Testing the Completed Model
On the Web Site
Building the Base Liabilities Model
Overview
Deliverables
Under Construction
Liabilities Side of the Model
What to Leave In
Stepping Through the Model
Reading the Cash Flows and Assumptions from a File
On the Web Site
Establishing the Model Environment
Overview
Deliverables
Under Construction
Importance of a Standardized Directory Structure
Creating Directories and Defining Their Functions
Operating Directories
Administrative Directories
Creating New Directories for the Model as We Need Them
On the Web Site
Building the New Assets Model
Designing the New Collateral Cash Flow Generator
Overview
Deliverables
Under Construction
Improving the CCFG Menus: Conversion to User Forms
Improving the CCFG Data Handling Capabilities
Improving the CCFG Collateral Selection Process
Improving the CCFG Cash Flow Generation Process
Improving the CCFG Report Generation Process
Improving the CCFG Messaging Process
On the Web Site
Writing the CCFG Menus and Data Sheets
Overview
Deliverables
Under Construction
Menus and User Forms
Menus of the CCFG
Main Menu
Run Options Menu
Collateral Pool Menu
Collateral Geographic Selection Criteria Menu
Financial Selection Criteria Menu
Cash Flow Amortization Parameters Menu
Collateral Reports Menu
On The Web Site
Writing the Collateral Data Handling Code
Overview
Deliverables
Under Construction
Managing Multiple Portfolio Files
Initial Data Screening
Writing the Initial Data Screening Reports
Writing the Demographic Methodology Risk Reports
Building a Representative Line Generator Program
Building the Rep Line Generator Program
On the Web Site
Writing the Collateral Selection Code
Overview
Deliverables
Under Construction
Building the Code
Financial and Demographic Selection Code
Financial Collateral Selection Process Reporting
Geographic Selection Code
Geographic Concentration Code
Introduction to Geographic Reporting
On the Web Site
Writing the Collateral Cash Flow Amortization Code
Overview
Deliverables
Under Construction
Quick Review of Existing CF Generation Code
Adding New Mortgage Types
New Prepayment and Default Methodologies
Uniform Methodology
Geographic Methodology
Demographic Methodology
Reporting the Results of the Cash Flow Calculations
Writing the Assumptions Report Package
On The Web Site
Writing the CCGF Reporting Capability
Overview
Deliverables
Under Construction
Recapping the CCFG Report Package
CCFG Activities and Reports
Eligible Collateral Assessment Process
Geographic/Demographic Reports
Presentation Reports
On The Web Site
Building the New Liabilities Model
Designing the Liabilities Waterfall Model
Overview
Deliverables
Under Construction
Structure of the Deal: Sources and Uses of Funds
Design Elements of the LWM
Main Menu
Report Package Menu User Form
Structure Inputs Menu
Liabilities Waterfall Worksheet
Performance Summary Page
VBA Code Modules
Report Template Files
On the Website
Writing the Liabilities Waterfall Model
Overview
Deliverables
Under Construction
Deal Structure Inputs
Building the Liabilities Waterfall Model
Period Factors Worksheet
Total Cashflows Sections
Results Page Worksheet
On the Web Site
Writing the VBA Code of the Liabilities Waterfall Model
Overview
Deliverables
Under Construction
VBA Requirements of the LWM
Menus of the LWM
Main Program
Error Checking the Main Menu Inputs
Importing the Liability Structure Inputs
Importing the Cash Flows of the CCFG
Loading and Running the LWM
Reporting the Results
Other Functionality
On The Web Site
Access, PowerPoint, and Outlook
Access: An Introduction
Overview
Deliverables
Under Construction
Basics of Access
Opening Access and Creating a Database
Access Tables
Getting the Data In: Entering Data
Getting the Data Back Out: Queries
Basics of Structured Query Language
Concluding Remarks
On The Web Site
Implementing Access in the CCFG and LWM
Overview
Deliverables
Under Construction
Access and the Collateral Cash Flow Generator
Collateral Portfolio Data
Geographic Selection Criteria
Geographic Concentration Criteria
Initial Data Screening Criteria
Custom Financial Selection Criteria
Run Options Elections
Generating the Loan Portfolio
Monthly Cash Flows Scenarios
Access in CCFG Reporting
Access and the Liabilities Waterfall Model
Connection Subroutines
Structure Worksheet Objects
Retrieving the Cash Flows
Storing LWM Runs in the Database
Linked Excel Tables
LWM Data to Access DB
Concluding Remarks
On The Web Site
(to come)
(to come)
(to come)
Exhibit Index
Index
Table of Contents provided by Publisher. All Rights Reserved.

WILLIAM PREINITZ has thirty years' experience in investment banking and structured finance analytics. A former managing director at Citigroup, highlights of his career include the creation and marketing of securitizations backed by 12b-1 mutual fund fees. With a team of ten people over a fifteen-year period, his group concurrently provided analytical structuring support for the over 100 different asset classes contained in the ABCP program of the bank. These included such varied items as movie revenues, fast-food franchise financing, and communication satellite leasing. In conjunction with the bank's corporate training program, he taught Excel/VBA modeling classes to over 300 people ranging from associates to managing directors.

MATTHEW NIEDERMAIER is currently a vice president in the Structured Finance Group at Markit, where he is a member of a pricing and valuation group focused on agency mortgages and CMOs. Within Markit he has also taught courses in Excel/VBA globally. Prior to Markit he modeled U.S. and Latin American ABS deals at Citigroup, including auto loans, credit cards, and timeshares.

RENT TEXTBOOKS

Intermediate Structured Finance Modeling, with Website : Leveraging Excel, VBA, Access, and Pow...

Why Rent from Knetbooks?

Top 5 reasons to order all your textbooks from Knetbooks: