This is a guest post written by Paramdeep from Pristine. Chandoo.org runs Financial Modeling School program in partnership with Pristine Careers. Visit Financial Modeling School to join our training program.
Greetings!
It’s been long time since we interacted on Chandoo.org. Actually I was very busy teaching the 105 awesome students for financial modeling in Excel. We all worked together to create some easy and some complex financial models. I found the journey to be quite exciting and enriching (From the feedback that I got, my students too didn’t find it bad either 😉 )
During the interaction, I found that a lot of students were looking for financial modeling around the project financing as well. So we thought why not introduce financial modeling for project finance.
In this post I will speak about some of the key aspects of a project finance model and why it can be different from modeling a normal company.
What is so Special About Project Finance?
An organization is an on-going entity – the basic assumption being that it would continue business for time immemorial. Project finance is different. It exists for a limited duration (though that duration is usually long – 20 to 30 Years!) and the project is structured in a special purpose vehicle (SPV). The SPV exists for a specific period of time and after the purpose of the project is solved, the SPV is dissolved. That means that the horizon of analysis is NOT time immemorial but a known time frame for which the SPV is formed.
The other typical difference is that these are long gestation projects, with almost no cash flow in the beginning and a lot of parties involved. This basic difference in the structure creates a lot of issues, Principal – Agency problem because of large number of parties, everybody trying to maximize their returns in a short time, propensity to wait decreases significantly and the initial project gestation period is critical to success of project.
Summarizing, Project Finance is the financing of
- Often long-term, industrial projects
- Increasingly those which provide public services or infrastructure
- Based upon complex financial and contractual structures commonly involving many legal entities
Two main types of Project Financing
- Greenfield – a fresh start
- Brownfield – expansion of an existing project
Key Aspects of Project Finance
I will highlight some of the other key aspects of Project finance.
- Separate Entity (Parent) and SPV Status – Risk of the transaction is generally measured by the creditworthiness of the project itself rather than that of its owners (Sponsors).
- Project Finance debt is often termed as “non-recourse”– That means the financial institutions cannot go to the parent level to get their money back. The money has to be generated at the SPV level. Typically these loans are secured by the project assets and the core project contracts.
- Timing of Cash Flow: The cash flows from the project comes only after the project is fully complete (takes more than a single financial year for completion) and are usually the sole means of repayment of the borrowed funds
- Multiple parties involved
- Sponsors
- Contractors
- Suppliers
- Governments
- Global financiers
- Long Gestation: From inception of an idea to Financial Close, a Project Finance deal can take years to negotiate
- Identifying Risks: The success of the project depends a lot on identifying risks, allocating them appropriately and ensuring that the responsible parties are adequately incentivized to manage their risks efficiently
- Construction time, costs & specification
- Operational cost, reliability
- Supply reliability, quality, cost
- Off-take volume, price
- Political environment, war, local hostility, currency in-convertibility
- Socio-environmental responsibilities
Modeling a Key Risk – Delay in Project Implementation
Because of the structure of the project one of the key risks in the project is the risk of delay. Delay in project can significantly reduce the IRR (what is it?) of the project and completely take it off track! There are multiple ways in which projects can be delayed and each would have its own repercussions on the return. These can be (The list is not exhaustive):
- There is an implementation risk (Delay because you could not execute the project in time)
- Delay in start of project (Could be because of regulatory approvals – Regulatory Risk)
- Delay in project because no funds were available (or delay in arranging the funds)
- Delay in collection of revenues (You built at the right time, but could not sell – Selling risk)
Typically each of these delays would have different affect on the cash flows. For example, if you are implementing a real estate residential project and you are not able to sell the homes – you have incurred all your cash outflows and your inflows are delayed. This would spell doom for the returns. On the other hand, if there is a delay in staring the implementation because of non acquisition of land, typically your cash outflow is not there so the affect would not be that large.
Why are delays more relevant for certain kind of investments?
Typically for long gestation projects – Roads, Real Estate, Power, Telecom, etc. there is a huge upfront investment. Even if there is a small delay in getting back the cash flows, it makes your project financials look very bad.
The Case of ABC Housing Co.
ABC housing company is planning to start the project on 1 Jan 2002 and is expecting to complete the construction over a period of 3 years. The construction cost is expected to be USD 1000 Mn. It is expecting to hold the property for a period of 5 years, in which it would get a lease rental of USD 100 Mn each year. After 5 years, it would sell the property at a value of USD 2000 Mn.
Modeling delays in Excel – Playing around with the date function
If you are planning to incorporate delays in your model, date function comes in very handy. For example, if you are expecting the construction period of 1 year, you can just put this as a parameter in your date (for years) and change his to see, if the construction period increases, what would be the effect on the project IRR.
The simple concept I have used here is to make the counter as a counter from the start date. The EOMONTH function calculates the end of month date of the particular month for which the date is given.
Now, I want to see if this is a construction year of not, I can just compare the number of years!
Some assumptions to start with
I am assuming that once the project starts, the cost incurred during the construction period would be uniform. So if the construction is completed over a period of 3 years, each year 33% of the cost is incurred and if it is completed over 5 years, then 20% of the cost is incurred each year.
To model this in excel, I compare the current year with the time period when the construction started and allocate the cost.
Similarly for Leasing I assume that it gets leased out one year after the construction period and after the leasing is complete, ABC is able to sell the property at the desired price
Calculating the Cash
There are two ways in which you generate cash – by lease and sale of property. The cash is consumed by your Capital expenditure. Assuming there are no taxes, cash generated is just a combination of all these.
Calculating the Return
As discussed last time, Internal Rate of Return (IRR) is an important aspect that all investors look at. Once we have the cash, we can simply ask excel to calculate the IRR for us. But in this case, since we have the exact year of cash with us, we will not use simple IRR function, but the XIRR function in Excel. This would take into account that all cash flows might not be at the year end.
Scenario Analysis
Now that we have the model with us, we can change the construction period and see its effect on the IRR. If we want to do a complete scenario analysis, we can used Data Tables in Excel to generate a complete scenario analaysis.
Related Tip: Learn how to work with Data Tables & Monte-carlo Simulations in Excel
To create the data table, the structure of the table should look like:
Where 1, 2, 3 .. etc are the years of construction and the cell is linked to the IRR that we had calculated earlier.
Then select the data tables option within the data tab > What if Analaysis
Select the changing cell as the years of construction (In this case the change is in a column)
You can see that there is a significant change in the IRR of the project with a change in construction period.
So if you construction period is delayed from 3 years to 5 years, your IRR would change from 15.6% to 13.1% (and hence the investor interest would simply die out!!).
Templates to download
I have created a template for you, where the subheadings are given and you have to link the model to get the cash numbers!
You can download the same from here [mirror].
You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).
Also you can download this filled template [mirror] and check, if the information you recorded, matches mine or not! 🙂
I am just doing that for the single sheet model and recommend that you do the same for multi-sheet model as a homework problem. If you face any issue, post your excel with the exact problem and we can discuss the way to move forward.
Next Steps
This series gives you a flavor of how financial modeling is done and an idea about specific nuances in modeling for long gestation projects. In the next part of this, we will learn how the interest payments of a project should be modeled.
Join our Financial Modeling & Project Finance Classes
We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.
Please click here to learn more about the program & sign-up.
For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com
15 Responses to “A Gantt Chart Alternative – Gantt Box Chart”
That's a great idea.
Maybe the planned End Date should be highlight more.
I don't know how it would look like (nor how to do it yet), but what if instead of finishing the bold line to the best case End Date, it finishes to the realistic End Date?
The idea is ok, I think other project management tools have this, already? Maybe not.
Gantt charts in my view are about the signal most unless thing in the world, theres no way you can look at one thats more that a little complex and understand what it's telling you. I'm going to write a diatribe on project management at some point, its one of my pet areas I think!! 😉
The issue I have with this chart Chandoo, is that Tasks need to be linked to each other, so they should inherit the uncertainty, which would mean the as you moved down chart the lines would be miles apart for later tasks, and you might have to add lots of lines for subsequent tasks to cover the various outcome of it's parents.
Having said that, for the high level board summary, it's a nice way to go, it it appeals to the management 😉
thanks Chandoo, great post.
Ross
Whoooa !!! That's a very clever idea Chandoo. I really love it.
I think i'll update my gantt project sheet with that idea soon (remember my template ?)
@ross : you can link start date to the end date of the previous task in your data. The only problem I still se is to which end date (real ? planned ? best ?) in order to have average amount of information.
If best end date, you'll tend to increase uncertainty at the end of chain, although if you link to real end date, uncertainty will be decreased too much, leading in both cases to wrong management direction.
Maybe planned till the task is finished then real will do the job ?
Hey chandoo, this looks good and this would definite add value in production planning / scheduling. Uncertainity in finishing a task is very high in production scheduling and this could give an insight or a bird eye view of possible shipments we can have....
I've always been frustrated by the limitations of gantt charts. Will definitely use this, I've always struggled with how to succinctly communicate the uncertainty of certain tasks without confusing stakeholders.
I like this, I think it's a very effective way of showing how a timeline can change and which parts of a project need close attention.
@Cyril / @Ross: I would intially link the the start date to the planned end date of the previous task, with the chart updating when a task has been completed to reflect the true end date.
Or what about giving a drop-down selection box to allow the user to see the chart based on planned/best-case/worst-case end dates?
Like the idea. Have found that Excel is more flexible than MS Project for graphical solutions. The "Best Case"\"Worst Case" metrics are theoretically appealing but once the project and\or phase commences their reliability diminishes. A chart like the above that showed Planned Start, Planned End, Replan End Start, Replan End Date, Number of Replans the Start and End Dates, and Actual would provide an active, actionable view of each task\phase. It would also highlight the areas which are riskiest.
It is always amazing how flexible excel can be.
My question is how would the chart show a scenario where the date moved up? If a task is dropped or the duration of the task is significantly reduced by applying more people or machinery to the task, the dates will move up.
The gantt chart has been around for a long time, but it is still quite useful to show progress.
Cheers,
B
I like the idea but seems bit complicated in case of long projects involving numerous activity.
Also, reading and explaining is required hence not feasible where plans are just send to audience for approval.
Cheers
SY
Great idea Chandoo,
When I was reading this idea regarding delivery dates, another thought popped into my mind, how can you show the uncertainty with MONEY!!
In this case, applies to cost management or even a normal budget, you think?
Would Box Chart and Gannt Chart help to understand the best case, middle case and worst case when money is spend or planned with these three risks are involved?
I imagine that this chart could help people who write their budgets get a better understanding of risks affecting their spending.
Peter
Chandoo,
I like it. How would you display an entry once it has been completed (actual)?
Thank you,
Matt
From what you have shown so far I think that this box Gantt chart is awesome! I think that this could be an extremely useful tool.
I can't wait to learn how to make my own charts in Excel.
Will the methods that you are going to teach us work in 2003 as well?
[...] Firday, we proposed a new chart for showing project plans. I chose an ugly name for it and called it Gantt Box [...]
You need to read Eli Goldratt's Critical Chain. The uncertainty you are looking for should be accounted for in a project buffer. Not at each task level.
Further you should spend time understanding Agile Development. This would have you plan only in 1-3week iterations. This allows you to embrace changes to work not yet started, and for your customer to re-direct your course at regular intervals (after each iteration) throughout your project. keyword search: Agile Scrum
These items will show you that you are solving a tracking problem for something that you can entirely avoid!
[…] Chandoo.org’s Gantt Box Chart. […]