Building on the prior post, Problems with Excel Business Applications, we want to further articulate the approach to building modular financial models, which we call “Schedule Based Modeling” or “SBM.” A key concept behind this approach is to set up certain constraints so that financial models can be easily compared, audited and understood easily.
The specific application is to, as much as possible, employ a technique from computer science called decoupling. The idea to isolate business objects so that they do not depend on each other and avoid creating spaghetti-like relationships Here are the key tenets of SBM:
- Schedule Definition: A schedule is a table of data where there is a header row, item rows, and in some cases a summary row.
- Header Row: The “Header Row” has a label which identifies a unique name for the schedule. There may also be additional labels used in the Header Row that are placed directly above the Schedule Item Rows, described below. Though for time-series data the implied header is the month (or other collapsed period), and so the Header Row values above the Schedule Item Rows is omitted.
- Schedule Item Rows: Each item row below the Header Row is a “Schedule Item Row.” These rows do the core calculations and must have a unique label.
- Summary Row: The last row is an optional row called the Summary Row. This row will generally be a total row for the cells above, but may take other summary type calculations.
- Input Isolation: All inputs should have a dedicated Schedule, which should not be used by other Schedules. This may seem incredibly limiting, but the result is a powerful constraint that keeps models organized. Users should also not create relationship formulas between inputs, however tempting and expedient.
- Linked Schedules and Schedule Items: To build the dependencies between tables, there should be free reign to link to the cells or rows of other Schedules, Schedule Item, or Schedule Summaries. For example, instead of linking a cashflow start date to some global date or a date input used for a milestone schedule, users would use the row of a milestone schedule to identify the period where a particular milestone is a “1.”
These are the main ideas that, when followed, help to create very robust and clean models. If you can decouple your schedules, there is not limit to the complexity you can manage in a large model.