Modeling Structured Finance Cash Flows with Microsoft® Excel®: A Step-by-Step Guide

CHAPTER 8

Analytics and Output Reporting

Sophisticated financial models are virtually useless if the results are inaccurate or difficult to understand. The best financial models have internal tests that track computational and conceptual accuracy. While the purpose of these tests is to make sure that the model calculation is valid, the interpretation of the calculations needs to be created and presented in a comprehensive and simple-to-understand format. The goal is to have a single output sheet that summarizes the model calculations and provides metrics for financial performance.

INTERNAL TESTING

Internal tests are the first step in reporting results. They make sure that as a model is modified and changed, the core logic still functions as originally intended. The tests also allow a user to troubleshoot problems faster if the need arises. Tests mainly focus on cash flow calculations, but allow a user to interpret conceptual validity based on the test results. This will become clearer as internal tests are implemented in Project Model Builder.

A number of tests will be set up in Project Model Builder to make sure that the model is functioning correctly. Each one is discussed as it is implemented.

Cash In versus Cash Out

One of the most fundamental tests is to make sure that whatever cash has gone into the deal has come out. This can be reworded in a more important manner: to make sure that whatever cash is used in the deal was funded by cash coming into the transaction. ...