Modelling pre-operational cashflows
Lets get real and stop modelling discrete ‘construction phases’ in real world transactions.
Traditionally financial models used in a project finance transaction have discrete phases, such as construction and operations, this makes the analysis easier to conceptualise, model and update as the transaction evolves. That’s nice….but in project finance transactions it’s often not good enough! This is regularly ruled out by financial modellers and managers as too complicated, this posting shows that it is relatively straight forward, how to do it and why it is important to properly analyse a project transaction.
The situation
Unfortunately for financial model authors, model auditors and the often neglected end users this simplification isn’t good enough when borrowers, project finance lenders or advisors are trying to maximize or just properly understand the economics of a project. The traditional approach needs to be overhauled for when transactions just don’t fit this approach. This is especially true in competitive situations when the sponsors are hunting for every last drop of value to provide a lower tariff or the highest government incentive payment to win a tender. The rights and wrongs of this approach are not being looked at here, merely the reality that I would expect a professional financial modeller to handle competently.
The screenshot is lifted from the headline section of a worksheet in a Navigator Project Finance project model and shows clearly the “Construction Phase” in yellow on a monthly basis transitioning to the Operational Phase in blue. The transition is soft coded and is handled by the end date of each column being ‘smart’ using the EOMONTH function a test to see if the step size should be one month or three. I am assuming in this posting that you have got this part nailed.
Re-label the Construction phase in project finance models?
In these situations the concepts of ‘construction’ versus ‘operations’ blurs so much they are no longer appropriate labels. As a suggestion new labels might be
- Construction = “Development and Funding”
- Operations =”Fully Operational”
There is a definite increase in projects where this is the case, partly due to the credit crunch, resulting in low capital availability, but also the sophistication of the project finance market over the years. This maturity has resulted in a market that now accommodates portfolios of smaller projects into one that is at least big enough to finance and at the other extreme a huge collection of assets in one financing! The general financial modeller in the trenches hasn’t caught up yet.
A small sample from a long list of examples where this overlap is important to capture are
- Wind farms, first turbines are commissioned before later ones are built
- Toll roads, a section and off-ramp are completed before the Project is Completed
- Mining, substantial valuable excavation before the main ore body is accessible
The above screenshot illustrates a simple calculation of revenue for a power project. It is important to realize that as always there is only one formula on each row, each one multiplied by the “Operational” flag which is live (i.e: =1 rather than 0) before the end of the construction period. Pre-operational cashflows can be readily computed when they can be tied to factors such as days in the period which are very date flexible.
Pre-operational overlap in a project finance model
Some common questions and considerations need to be made when there is an ‘overlap’ between modelling construction versus operations.
- Construction is traditionally modelled monthly Operations quarterly or semi-annual, how are the operational calculations back-casted into the Construction phase?
- Construction and construction funding are often kept separate from operational calculations, how is this handled if the ‘Operations’ start after Construction is complete?
- Do early positive operational cashflows reduce the required project finance funding
- What happens when there is a negative pre-operational cashflow? Is more funding required?
- Can the project be completed without these pre-operational cashflows?
- Can these pre-operational cashflows be distributed as equity before the project is Complete, external funding is still being drawn upon?
- If there are positive cashflows, does that mean that tax payments are also due?
- Are assets that are completed early depreciated before the remaining assets are completed?
- If a monthly Development and Funding phase doesn’t naturally end on a calendar quarter due to base case or a scenario settings what do you do to line Operations up with the quarter ends?
Project finance lenders perspective
An experienced project financier will have executed enough transactions to know that these funds either don’t occur of fall short in timing or amount, or combinations thereof – leading to funding gaps and at the very least stress on the transaction and relationships.
Project developers / project finance advisors
Want every last dollar and their risk-return function will naturally expect this to be a base case event, why on earth wouldn’t we receive these funds? Advisors are generally paid based on the funds raised so will go along with this approach.
Project finance transaction modeller
Regardless of the final outcome it is important to at least understand the impact of this internally generated funding source – so be ready to model it. A good transaction modeller will have this element well under control and will ask the questions that this approach requires answers to because at the very least you can expect that borrowers will want to see the impact of realizing those cashflows even if a lender won’t credit them for it , in full or partially.
How did I solve this issue?
Every now and then the challenges of a project finance transaction, not to mention a demanding client, need an approach that you can only arrive at my pressing the pause button and taking the problem back to first principles. After years of modelling construction cashflows on a separate worksheet and producing estimates of pre-operational cashflows on that sheet, or worst still, saying it would over complicate the model if we modelled them I decided a seamless solution was needed.
In speaking with other respected points of reference around the world I did not come up with a satisfactory approach, time to get the pencil and paper out! I asked myself the question, what’s so special about Construction and can the financial model, on all time based worksheets, start from Financial Close (or “Date_ModelStart”)? The answer, somewhat annoyingly, was yes. The benefits of this simple approach to note isolate Construction on one worksheet was that all Operational, tax, depreciation , return calculations started from the same evaluation date (the first live column in each worksheet). These calculations are of course zeroed with a binary flag governed by the dates of the respective phases.
For Cashflows (and other line items such as operational interest margins) we can manage them by setting the “Date_OpsStart” < “Date_ConEnd” so they are ‘live’ during a monthly period. This is demonstrated in screenshot 2.
But how does a quarterly calculation work in a monthly period? Well the trick is to make sure that all calculations are one way or the other driven off the number of days in the period which is automatically generated by deducting the end date from the start (+1) in each column.
The other thing feature that may be needed is if there are inputs that are based on Operational Quarter then you may want to have a “Cons” assumption which is the value picked up if it is not in full operations. In the same way we would sometimes for debt margin.
Is it that easy?
Well almost. There are some big questions that still need to be answered hinted at earlier. The biggest one is ‘what do we do with these cashflows’. Well for that we need to think about what stage is the project / transaction at?
If we are at an early stage evaluation stage then we might just net this off of funding requirements and funnel the net free cash back into the construction costs. As the transaction matures and before lenders start to be involved a savvy financial advisor would build in the optionality to apply pre-operational, post tax, cashflows to reduce external funding and also to investigate what happens if a lender would discount these cashflows (potentially by 100%, especially in a downside case).
What happens to pre-operational cashflows?
There are three immediate ways to treat this
- Net them off in full (or discounted), post tax, against external funding requirements
- External funding ignores them and they travel down the waterfall to equity
- External funding assumes they are generated, captured in reserves and can be used at the end of the Funding period as a contingency which could then be released or used to repay the total debt funding that has accumulated.
When I was a project finance lender, there were two projects in particular which hit a stalemate because our credit risk executives would not credit pre-operational cashflows as a ‘source of funding’. From a borrower’s perspective these cashflows are ‘almost certain’ but for a lender ‘they are far from certain’ and so entering a transaction where the total funding required to be physically completed is very risky.
In these instances, accurate and flexible modelling kept the ball rolling and of course an agreement was reached one way or the other but only after several late nights and heated debates.
In the screenshot above you can see that Pre-Operational Revenue is a ‘source’ of funding and pre-operational costs are considered a cost to be funded. This wont always be the way, sometimes the MAX(0, Net Cashflow post tax) will just be applied to the capital and financing costs and hence reducing the external funding that is required (in theory). This is covered in the Navigator Tutorial on pre-operating cashflow utilisation for funding.
Navigator Tutorial: Pre-operating cashflow utilisation for funding
Aligning monthly periods with calendar quarter ends.
Ok so here is a situation where there is an easy way and a very hard way. Clients on their first projects or working with an overly demanding bank group focus on getting the schedule accurate to the nearest month. An elementary error of judgement.
Experienced project financiers acknowledge that projects are unlikely to finish on track with the base case and that if a construction end falls on the end of Feb then the expedient and practical (and conservative) approach is to assume that the Construction period ends on March which will automatically align the model quarter ends with calendar quarters. The alternative is the ‘hard way’ which is to have a stub period, that is the first x columns of the ‘quarterly’ phase are monthly. Yes it sounds complicated and it is. It is one of those modelling approaches that doesn’t actually take too long to incorporate or build but its effects are felt every time any change is made to the model – best avoided and adds very little accuracy.
In this screenshot you can that the first month of the quarterly phase (Full Operations) has been forced to the monthly period. This works in the same way, based on days in the period. There are quite a few other considerations to be mindful of if you take this approach however this particular part is readily achieved.
Conclusion
Modelling pre-operational cashflows is relatively straight forward if you:
- Ensure all worksheets start from Date_ModelStart rather than having a worksheet with dedicated Construction timing.
- Use binary flags to govern when calculations are allowed to be ‘switched on’.
- Ensure calculations are set up to accommodate this by having a Construction version of all important inputs (these can even be time based).
Because you are probably financing a project worth hundreds of millions of dollars, it is very unlikely to be finished on time (sorry but that’s the facts!) so live with timing that marries up with calendar quarter ends.
Retro-fitting this is still relatively easy but like most aspects of transaction modelling, thinking about it on day 1, will add no extra work. If you happy with this then the same principles can be applied to a Planing phase that extends into Construction before financial close has been reached and all manner of fancy footwork.
I hope this was useful, it’s a tricky area the first time you encounter it and new challenges do occur from time to time so even more reason for transparency and lots of sense checking and asking the transaction questions as you.
Nick, as expected a very good article that shows your experience and expertise in the area (not that they were ever in doubt!).
As usual, of a financial model, the cardinal rule of GIGO applies. Given a transactional model involves a lot of stakeholders and each has a hand in providing the input and assumption, maybe in your next article you should share some of your experience in dealing with the parties providing the inputs (and quite often the inputs are of varying level of granularity and quality).
Not sure if its appropriate for a financial modelling website to deal with project management but you have to admit, as a transactional modeller, project management is just as an important skill set as Excel.
Excellent summary of the complex issue of financial modelling of pre-operational cashflows! Your screenshots also looks absolutely fantastic – I wish our project finance models looked that great!
Similar to other articles by Nick, when reading this article I felt: “This guy is reading my mind”.
I have been building and reviewing financial models for 10 years, the last 2 of which has been in the PFI/PPP arena. Traditionally in this area there was a clear Construction phase followed by an Operations phase. As Nick pointed out in his article, on more and more projects this simplistic approach is no longer appropriate.
My initial experience of modelling did not require such phases, so when I saw models with a Construction calculation sheet and then an Operations calculation sheet, it looked odd to me. Yes, individually several formulae were simpler, but overall I found this approach far too restrictive to cater for the more complex project requirements as described by Nick.
From a financial modelling point of view, splitting a model into phases (such as Construction and Operations) should simply be a presentational thing in the final outputs, not an issue in a model’s structural design.
In some cases, it will be necessary to have more complex formulae to seamlessly ‘transition’ from monthly to semi-annual periods, but that’s the sort of detailed technical challenge that good modellers thrive on (or should, in my view).