Monthly model during investment phase, switching to semi-annual during long payback phase

Hi Will (& Jun),

As per my comments in other posts in this forum, we are planning to release project finance content libraries in the near future, but due to the layered approach we take to developing new content libraries (i.e. each library builds on prior libraries) we don't anticipate completing and releasing these content libraries until after we've completed consolidations and a couple of deals modeling libraries such as LBO and potentially M&A. We have, as you've probably noticed, started including functionalities within existing libraries that will be used in the project finance content libraries - such as scenario analysis capabilities - but we don't expect to complete the project finance libraries until late this year, all things going well.

We did a large survey of project finance modeling requirements last year, and over 50% of the hundreds of submissions we received indicated that they use multiple periodicities in their project finance models, usually a lower periodicity (usually monthly) for construction followed by a higher periodicity (such as quarterly, semi-annual or annual) for operations. So what you've asked for here Will is completely expected.

Hence, while this is unfortunately not a current focus of our team, I will make the following comments based on the R&D we've done so far:

1. You can obviously build any time series construct you like by customizing an existing time series module or creating your own from scratch.
2. You can choose to include multiple periodicities on the same sheet, but we generally avoid doing so as it rarely works well and is usually quite messy.
3. One thing we're experimenting with is including higher periodicity period titles within module components on lower periodicity time series sheets - e.g. allowing annual indexation assumptions to be entered on a monthly time series sheet. This must be done clearly distinguishing the periodicity of the assumptions within each component within assumptions sheets, but we've found some clear ways of doing this using conditional formatting, etc., which work very well.
4. Our R&D has demonstrated that, albeit hard to see looking at most messy non-modular freeform project finance models, a project finance model is simply 2 financial models in series - i.e. a construction financial model focusing on funding followed by an operational financial model focusing on returns. When you look at a project finance model this way, you can effectively use existing lower periodicity modules to build the construction model and higher periodicity modules to build the operating model, and simply connect them at the change of periodicity as though the 1st model is simply providing opening balance sheet data to the second model. This is obviously an over-simplification of a very complex structure, but the fact that most project finance models don't reflect this dynamic/structure explains why they're a nightmare to work with and often baffling to all but the person who has built them.

We have mocked up quite a few example project finance models based on these comments and discoveries and found that treating them as 2 models in series has allowed us to build them using predominantly existing modules - which Jun alludes to in his posts above. The trick to this is taking the 2 separate financial models in 2 separate workbooks - e.g. a monthly model and a semi-annual model in your case - and then amending their time series modules so that they have identical time series assumptions and lookups components. You can then insert all the models from each into the same workbook, then edit the semi-annual period titles set to start after the monthly period titles set ends, and you've got yourself the core multi-periodicity project finance model structure you're after along with a full range of existing modules.

Having said this, none of this stuff is simple, and we're intentionally not publicly releasing project finance model examples as we want to be 100% sure that the core time series infrastructure we decided upon works before doing so because we won't easily be able to change it once it's in the wild.

This is a frustrating area for us, as we know how badly many of our users want/need this libraries, but once we release project finance content libraries we expect them to become an industry standard so we're building them carefully and correctly, and as fast as we can given the complexity involved and resources required.

Watch this space guys, and in the interim our support team are always around to help you build specific models for projects. We recently did a small (~$100m asset) project finance model in less than 10 days - when the client had been given a quote of 6 weeks from a traditional / freeform project finance modeling organization - so I can assure you that project finance modeling is incredibly amenable to modularization. It just takes time to develop and package the foundational libraries...

M.