Flexible Timelines in Project Finance Models

This page addresses the question of timelines in project finance models.  I argue for a consistent and standard time line with flexible dates across pages of a project finance model and that the practice of using many different time lines and different formulas for alternative parts of a project finance model is disgusting. Developing dates and timelines and putting the timelines at the top of each sheet is a standard part of financial modelling and included in all of the regular old blah blah blah classes that you can get elsewhere.  Formulas for establishing dates and switches (or flags or masks or whatever you want to call them) is very standard.  But, very surprisingly, the use of a master time line where you work through a simple formula for establishing dates is not generally done these days. In addition to complaining about multiple time lines I address a couple of other issues on this page. One issue that is a bit tricky is being careful about developing annual analysis when moving from monthly to semi-annual cash flows. Another issue that can be tricky is accumulating monthly data into semi-annual data or consolidating quarterly data.

Make a Master Time Line

At some point you will want to compute an IRR that includes monthly periods and semi-annual periods.  This means a master time line must be part of the model.  But it is shocking to see people develop different pages with different time lines.  This leads to painful SUMIF’s; too many lines in a model and difficulty in auditing. A really horrible thing is to see all of the accounts put on a monthly basis in some sheets for the pre-cod periods and then put in a post-cod with different time lines.  Why in the hell would you not just make one master time line that is flexible enough to account for different situations.  You can make two master time lines if you want — one for operations and another for financing.

To make a master time line that has different months in a period for pre-commmercial operation and post-commercial operation, I find the easiest thing to do is to follow the following steps:

  1. Make a period counter with ALT, E, I, S (on English keyboard)
  2. Compute the months from the start of a model until COD (can use DAYS360)
  3. Compute flag for pre-COD
  4. Use the flag to compute the months in period
  5. Compute the start and end of period with EDATE
  6. Compute a flag for post-COD operations

This process is illustrated in the screenshots below.  The first screenshot demonstrates how you can set-up the dates in the input sheet.  The second screenshot illustrates the completed time line.

 

 

Timeline Examples – Keeping Useful Flags on the Top of the Sheet

In the next few paragraphs, I put some examples of what to do and what not to do when you are setting-up your time lines.  In the first screenshot below I suggest that you should fine the time line switches like and operating flag or a flag that is date when the availability period of a loan turns into a term loan.  You also often want to see the number of days for computing periodic interest rates. So, put key timelines that you will use over and over again at the top of the page as in the example below.

 

 

In the screenshot below I show a counter example where there is no time line at the top of the page and no useful switches.  You can see that the timeline is in line 21 and there are no 1/0 or TRUE/FALSE switches that you can use over again at the top.  For each page, it is basic modelling concept that a time line is at the top.  Further, the formulas should be simple and not like the formula shown in the screenshot.

 

 

The next example of time lines is even worse.  Here the year is a fixed number as shown in the formula box.  This means that the date of the financial close or the commercial operation cannot be changed.  This is a simple and dramatic error rendering the model very inflexible (counter to the F in FAST modelling). Please do not ever do this.

 

 

Fiscal Year End and Accumulation of Periodic Data into Fiscal Years

For the fiscal year used in the annual page, you want to find the end of the month before the COD.  You can just use EOMONTH function and enter -1 for the month  — EOMONTH(COD,-1).  Then find the month of this date using the MONTH function.  Once you have the month end for the fiscal year, enter a switch that is true whenever the end month in the main time line is equal to that month.  You should create the year of the date with the formula that the year changes when the fiscal month is reached. To do this use an IF formula — IF(MONTH(end date in time line) = Month End defined above, Year = year +1, prior year).  This makes a year counter that increments whenever the fiscal year end is true.

The process is shown in the two screenshots below.  The first screen shot is really the key.  Note that the commercial operation date is 1 August 2023.  You need to find the prior month (including if the month happens to be December).  This month can be used to drive the SUMIF function.  Note that instead of using the function MONTH(COD) – 1 which would not work for January or December, you can use the EDATE with -1 for the month and then put the month around the EDATE.  The formula is:

Month of Fiscal Year End = MONTH(EDATE(COD,-1)).

 

 

The second part of this method is shown in the screenshot below.  In this example, the COD date using the standard monthly analysis is 1-May-2018.  This means the fiscal year end will be April.  But when the model switches to semi-annual mode, the month of April is in the ending date and not the beginning date.  But you want the switch for a new year to start after the closing date.  So you should take the PRIOR closing month and use that in the test.  Once you have done all of this you can use the computed fiscal year end rather than the calendar year computed from the start or end date.  The fiscal year is computed from the prior fiscal year and changing the year when the switch is TRUE. This sheet uses the generic macro program to point out when a flag is on or off.  Go to the generic macro discussion to see how to do this automatically. In this example again, the numbers in the left (the dates) that come from another sheets are coloured in red (you could also colour the cells according to the sheet name).

 

 

User Defined Functions for Quarterly and Semi-Annual Time Lines

Most of the time, creating a timeline is pretty straightforward.  You can count the months pre-COD and create a timing switch that is 1 for the monthly periods and 6 for the semi-annual periods (or 3 for quarterly periods).  Then you can use the EOMONTH or EDATE function to count the months (if you use the EDATE function, subtract 1 for the end of the month.  If you use EOMONTH, subtract 1 inside the formula.  Sometimes when you use a half-year model, you may need to aggregate months into half years.  You can use the user defined functions below to compute the appropriate half year or quarterly date from the monthly date.  If you use quarterly time periods or monthly periods that will be converted to semi-annual flows, the following user-defined functions may be useful.  The first function returns the end of the half year period given the date.  The second function returns the end of the quarter.  These functions should be part of excel.

Function eohy(date1) 
month_of_date = Month(date1) 
If month_of_date = 1 Or month_of_date = 7 _ 
     Then eohy = WorksheetFunction.EoMonth(date1, 5) 

If month_of_date = 2 Or month_of_date = 8 _ 
     Then eohy = WorksheetFunction.EoMonth(date1, 4) 

If month_of_date = 3 Or month_of_date = 9 _ 
      Then eohy = WorksheetFunction.EoMonth(date1, 3) 

If month_of_date = 4 Or month_of_date = 10 _ 
    Then eohy = WorksheetFunction.EoMonth(date1, 2) 

If month_of_date = 5 Or month_of_date = 11 _ 
     Then eohy = WorksheetFunction.EoMonth(date1, 1) 

If month_of_date = 6 Or month_of_date = 12 _ 
     Then eohy = WorksheetFunction.EoMonth(date1, 0) 

End Function 



Function eoqtr(date1)

month_of_date = Month(date1)

If month_of_date = 1 Or month_of_date = 4 Or month_of_date = 7 Or month_of_date = 10 _
Then eoqtr = WorksheetFunction.EoMonth(date1, 2)

If month_of_date = 2 Or month_of_date = 5 Or month_of_date = 8 Or month_of_date = 11 _
Then eoqtr = WorksheetFunction.EoMonth(date1, 1)

If month_of_date = 3 Or month_of_date = 6 Or month_of_date = 9 Or month_of_date = 12 _
Then eoqtr = WorksheetFunction.EoMonth(date1, 0)


End Function

 

A final issue is what happens when you have a model that does not end in months like December that is the fiscal year of the company.  In this case you may want to pro-rate the data and put some of the amounts in a prior period or a future period for annualisation.  I address this issue at the bottom of this webpage.

 

Generic Macro File that Allows you to Copy to the Right (Shift, CNTL, R) and to Colour and Format Sheets (CNTL, ALT, C)