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

multiple time series
6 posts / 0 new
Last post
Will Harrison-Cripps A 0
WH
Monthly model during investment phase, switching to semi-annual during long payback phase

Hi,

Is there a way of having two separate time series within the same model, so that some inputs and outputs are determined on a monthly time series basis, while others are on a semi-annual basis.

For example, the first couple of years of the model period would be defined on a monthly basis, with all underlying calcs on this basis. The following periods would be modelled on a semi-annual basis, with inputs defined on SA periodicity too. To get to overall financial statements etc., the monthly outputs would be aggregated onto the SA time series sheets and combined with semi-annual inputs.

We use this approach in our freeform models, where construction and detailed finance during construction is modelled on a monthly basis. Once the asset has completed operations and the monthly time series is at an end of a semi-annual period, we move onto less granular inputs through a long operating life.

Thanks!

Jun Yan A+ 124

Hi Will,

There certainly is and I'm sure Mike and the team will be able to provide better solutions. I think Mike does answer Hanz in this post - https://www.modano.com/forum/two_time_series_project_finance_model

Up late playing around, so I thought I'd give it a crack as well. One option might be to use the Project Appraisal content, which has some of this functionality built into it already, but the alternative below is possibly along the lines of what you're after:

This one will require a bit of customising a Time Series, so please make sure you're familiar with creating/amending time series https://www.modano.com/resources/user_guide/automation/time_series_analysis/managing_period_titles_sets will give you some initial guidance.

With the split you're after, and to maximise use of the content library, I'd start with a SA Time Series as a base, given your long range forecasting is on this basis. You can then customise your time series by adding a monthly period title set construction time series into your model. Additionally, you'd also want to include a transition Time Series that collates any of the monthly commissioning rev / costs up into your operations time series.

I would strongly suggest you build your operations up as much as possible using your SA Time Series first, ie. bring in as much of the SA content that you think you'd likely utilise as possible before you start customising the Time Series. Adapting the Time Series will mean using content from the library will require customising the Time Series in those modules that you want to insert. Suggest a fair amount of planning before you get into the build...

The screencaps below provide an example of something we pulled together a while ago, which slots in a monthly => quarterly time series => annual roll-up. We used quarterly as a base in this example on a CoA6.

Screencap 1 is the core Time Series assumptions sheet with adapted formulas to drive the monthly / quarterly time series. This also controls the monthly into quarterly construction piece.

Screencap 2 is of the Period Titles. We kept the base (albeit renamed), but we added a monthly & quarterly period title set in, which is controlled by the customised Time Series assumptions.

Screencap 3a is an example EPC module that is using the newly created monthly period title set. This is handy as we can then quickly duplicate / scale up or down the project capex.

Screencap 3b is the same EPC module, but showing the comms period, which is again controlled in the Time Series assumption set

Screencap 4 is an example of the quarterly time series that we use to transition between construction and operations.

Screencap 5 is an example of an annual time series roll-up.

Screencap 1

Screencap 2

Screencap 3a

Screencap 3b

Screencap 4

Screencap 5

Hope the above is helpful, I'm sure there are plenty on here (+ the Modano Support Team) that have experience pulling something similar together. I know Hanz (other post) was sharing an example of customising time series as well.

Jun

Will Harrison-Cripps A 0
WH

Thanks Jun, really helpful.

I had missed the post from Hanz, so thanks for flagging that too.

I was thinking about having the construction phase (monthly) on one worksheet, with the semi-annual assumptions on another (with clear worksheet names to identify MTH versus SA, e.g. Rev_Exp_Mth and Rev_Exp_SA). Rev_Exp_Out_SA would combine data from the Rev_Exp_Out_Mth, along with costs from the Rev_Exp_SA. (A bit like the annual cash flow module, when added into the monthly model, in terms of optics at least.)

I was trying to avoid having a worksheet that includes a changing time series, though at least the method you set out has it all in the header rows, rather than my pet bug bear of a new time series being introduced half way down the sheet!

Michael has previously hinted at having assumptions worksheets with different periodicity, or at least allowing for it - in post https://www.modano.com/forum/combine_time_series

I'd be interested to know how Michael and the team are proposing to handle this in the Modano built project finance modules...

Jun Yan A+ 124

Hi Will,

I think the screencaps may not be clear and it probably doesn't help that I've only shown example input sections and I've whited out a lot of the assumptions as it contains data. The layout you're noting is actually how we've built it In the example I showed. There aren't any sheets with changing time series in the model.

Screencaps 3a and 3b are on one time series (monthly), including assumptions. Once Commissioning ends, the time series just nulls out (we've got it labelled as Spare).

Screencap 4 is on a separate time series (quarterly in this case).

Having said the above, I did miss a couple steps in describing what we've done. We built out a monthly set of financial statements for the construction period, which gives us monthly flow amounts for the quarterly transition as well as an opening balance to carry into the quarterly operations. The quarterly operations are forecast separately in isolation, and don't carry any construction / comms data.

The all periods quarterly financials then stitch the two together, and the annual roll-ups are just a roll up of the all periods quarterly financial statements.

Hope that provides a bit more clarity.

Jun

Michael Hutchens A+ 188

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.

jessegraham98 X 0
CG

Thanks for answering, you made my day. I also would like to help you by sharing the https://letsgradeit.com/all-categories/ website link with you where you can find writing services for all categories and also you can read reviews of them easily. I am also using that website link whenever I want to hire an essay writer.