Project Financing Tutorial

[Scrap #1] This tutoral will talk about financial modelling for an infrastructure plan with project financing. The project financing is one of the method of securing funds for your projects. It is especially applicable to infsatrucutre project dealing with substatantional investment in capex such as electricity, bridge, fiber optics, telecoms and housing but with a steady and predictable revenue stream which can be used to pay off the debt. The aim of the model is to estimate the level of debt financing an infrastructure project can raise against the future revenue. This tutorial will show how to model each project as a separate ring-fenced entity with their own financing and then consolidate them into one company view. That way you will be able to see how each venture contribute to lowering the equity financing. The premise would be to calcute the level of required debt and then fill the gap with equity. Finally, with the detailed calculations dowe, the spreadsheet will summarise the funding and calculate overall and per project equity requirement / peak and debt raised. The model will use a modular approach i.e. take one standard project and then multiply them across the timeline. We will use ten projects.

[Scrap #2] We will first start with building a cash flow for just one project. Bear in mind that the revenue and cost will have to be specific for a particular entity so we will exclude any general overheads like headquarter salaries and only add anything that can be directly attribute to running the project. Think of it a self contained module. The example fo such project could be infastucuture venture where you choose one area of town where you need to develop building or lay tv or fibre cable. This area will then become a separate entity and your overall plan will consoldiate them into one. We will starting with the main drivers of cost and revenues. Let's say that for an infstucture project this will be spending money on a building. Fist we need to assume how many building we are planning to complete and the amount of money this will require. Let's say that we will build 100 units over a period of 24 months. You will see that the model has a build timeline and "flags" with 1 standing for build in process. We also want to specify the completion rate. Let's assume consistent build over time.

[Scrap #3] Now when we have a builidngs capex in place, let's turn to the revenue side and see how quickly we can start selling the properties. For the purpose of this model we will assume that there are certain customers that have paid for the building in advance and we will assume that they have prepaid for the build with a x % of discount (flexible). The rest of the properties will be sell to the customers within 3 months of releasing the building but we won't be able to start selling the building at least until 12 months of when the build start (assuming that you have certain work to complete before the buildings can become habitable). The rest of the building will sell within 18 months over a straight line. Each of the transactions will carry a cost which you will use to pay off the debt. There will be a portion of building which the housing association will decide to rent. Assume that the rental revenue will equal to x % of the capital spend. In other words, your investment will generate a steady return. We will assume that the value of the house increases by x.

[Scrap #4] Every transaction, revenue, will carry a cost which can be expressed as a % i.e. commission or duty and one-off fees such as adding cost, surveys, etc. The cost will increase in line with cost inflation. Then we will assume other direct costs just assigned to that project. There will be a portion of costs that will be applicable only during build period, such as build supervisor, which we will put in the capex section, but that will be steady costs associated with customer service or managements which we will put in our headcount lines and then calculate them off the salary assumptions (adjusted for cost inflation). There will be other pay employee costs. Other direct costs will include proportional marketing associated with selling the house and some other contingency line. Also cost associated with the rental portion. The result of revenues and costs will give you your operational margin, which we can use to calculate future financing.  On the capex side we will take the numbers of homes built and multiply them unit costs assumptions, which we will increase in line in inflation. For example 10 x 200k. We will also include build headcount assuming accounting rules allow it.

[Scrap #5] Now with the revenue, opex and the capex / investing, (and working capital) lines we can forecast the level of cash flow required for our infrastructure project. We can see the level of cash required so let's move onto cash from financing. Our projects will have three line of financing cash flow - pre-paid made by customers, debt financing against the future revenues, and equity required. The first financing will be calculated as the percentage of % of upartmerns paid upfront minus a rebate and the amount. The debt calculation is the core of the project financing. We want to raise as much debt as possible assuming that it will be paid for the people buying the property (either through their personal mortgage or cash) and then by the rental parts. Let's divide those cash flow streams in two and then calcualte a pv. Bear in mind that you will need to keep a certain DSCR and other covenants for the projects (as there is a risk with the paid/retnal assumptions) so we will apply a "haircut" and see what happens to our covenants and schedule. Take the streams and apply PV formula to calculate the possible project financing.

[Scrap #6] With the possible level of financing, you can now start looking into principal repayments, interests and amortisation. The model will give you two options - straight line amortisation over a period of time (let's say 30 but flexible) or more flexbile approach where you can set the amortisation schedule. This will allow you to set a bullet payment (i.e. no repayment and then fully repaid balance) or a hybrid of the approach where you pay some portion of the debt and then higher portion at the end. With all the approaches watch out for the debt. You also need to calculate the itnerestest. Here again we will have two options. First calculate the lever of interests payment required, which will be the % assumption x the outstanding debt. You can then deiced if you want to repaid full interest (most cases), accrue the debt through an interest free period, or paid a portion of the interest due and then repay them later. The combination of the principal repayments and debt drawn (based off the cost spend) will total to the total debt service. Contrast them against gross available cash for repayment. The resulting cashflow will give peak debt and equity.

[Scrap #7] One you can calculate level of equity returns. We will take our post financing debt to see whatS the level of equity required and then take an exit assumptions to calculate the level of returns the project financing will generate. Our example deals with property so we will use the percentage of property value as an exit value but there is also a second option where you take your EBITDA or gross margin and exit x for the valuation. This can be used for projects that don't have readily available benchmark. For example telecoms project that require investing in fibre or cable and that can generate a steady flow of cash making it effectivelly in a similar way a graded bond would(?). We will use the IRR and NPV formulas to judge the attractiveness of the project which will return you all the project financing main metrics and output. This is the first part of the project financing. The second part of this tutorial will show how to combine our projects if need to consolidate them into one view. We will add corpororate overheads to give you an idea of what accounts of the entire holding company will look like.

[Scrap #8] We will treat each project as a sepeate module, so I will simply combine them, stack them against a timeline and apply some adjustment to allow for different sizes of the project. For simplicity it will be all done on proportional basis. First, you need to create a sheet listing the main projects and the timing, sizinh assumptions. Then you will see I added the main sections as outputs with buildings metrics, revenues, main direct costs and capex. Then we need to combine the project financing and prepayments as again they are done on a project by project basis so we won't be raising debt on consolidated view. For the timing assumptions I am using offset formula and then multiple the output of the model by the sizing assumptions. That will give you an entire view which for simplicity I will put together into easier to visualise summary. I will now add some corporate overheads to complete the consoiidated picture and then add an equity line. This will give me my IRR and NPV and equity requirement which may differ due to timing and those overheads. I now have a consolidate model that I can with a project financing view.

[Scrap #9] I now have to link the resulting sheets to P&L, add depreciation assumptions, tax implication and then link it to cash flow statement and cash flow sheet. How exactly treat the model and the accounting is up to particular lines so you may need to model those lines on a project by project basis. With the equity returns and consolidated project financing numbers I will also now create balance sheet. I have also added some graphs to better visualise how the progress in your build increases the capital need and how your financing develop in terms of your equity to debt financing ratios. To play with the model sensitivities you can go to one of the assumptions in the summary sheet and then see how they influence the rest of the model. Pay attention to the covenants and make sure you stay within financing limits. In our case the financing model only applies to a terroritoal project but you could adopt in a way so you can see financing of your different revenue streams treating each of the stream as a separate project. For example, you may be running a subscription service with steady revenues and ad-ons with less predictability.

[Scrap #10] In summary,