case study project finance

Edward Bodmer – Project and Corporate Finance

Resolving BS in Finance

Project Finance Model Case Study of Solar

This page includes a couple of case studies on creating a solar project finance model, about the simplest model structure you can imagine.  I have put together a file with titles that illustrate how you can start from the fundamental operations and end up with investor returns and credit risk measures.  I have also put some other simple solar models on this page. My hope is that this exercise will not take too long.  I have also included a case write-up that is used in testing people. Below the simple solar project model I have put in a more complex case for a thermal construction project.

A-Z Exercise with Selected Existing Titles for Solar Model

I was asked to prepare an exercise where people could quickly make a model that evaluates bid prices and other model aspects and can get you most of the result without spending too much time on some of the horrible details that can make project finance modelling so difficult.  I was told that making a relatively simple project finance model could not be taught in a single day.  This is not true.  You can see the essentials of making a model including describing inputs, establishing operating cash flow, computing depreciation and project IRR, incorporating debt, making a cash flow waterfall and computing some key financial ratios in a morning.

The file available for download does this for a simple solar case.  To make it really simple I have not even included a construction period and made the model and annual model.  For some items I have not included titles so that you can do the really important part of a model which is to structure the model.  For other parts, I have included titles so that you do not have to waste a lot of time typing.  The exercise hopefully applies some of the fast modelling religion, meaning that it is flexible (except for the one period construction), it is accurate — the balance sheet balances and the debt is paid off; it is structured, where you start with physical operations and then move to revenues, expenses and capital expenditures and then to depreciation which allows you to compute operating taxes and project cash flow.  After project cash flow you incorporate debt with a sources and uses of funds statement, and a debt schedule.  Only after the debt schedule do you create the financial statements.  To download the file, press on the button below.

Excel File with Exercise to Create Basic Solar Project Finance Model Demonstrating Structuring Issues

Part 1: Timing

In the second part make a timing switch and use the EDATE function (FETCHA.MOIS) to compute the annual dates.  I have put some summary outputs at the top of the page.  Use the ALT and –> to group the rows and choose to not show the outputs.  In English you can use ALT, E, I, S to get the model started for 40 years.  With the GENERIC MACROS open, you can use the ALT, S combination.  The timing parts of the model are shown on the screenshot below (note that the latest version of the model has some different line items — I moved some of the stuff to the summary page.)

Part 2: Define Inputs

In the first part, you go to the operating data.  You use the INDEX function so you can select one of the scenarios.  Also put the developer tab in your excel and make a spinner box with the windscreen wiper method to use the form in other sheets.

After setting up the time line, pull the relevant inputs from the model from the operating page and from the debt page. The inputs should be discussed and you should see how to change the scenarios with your spinner box.  You should understand that the inputs are arranged in a proper order that separates operation from financing; that begins with physical operations; that includes ways to back into the contract price and that includes logical differentiation of the debt parts of the model.  When you have linked the outputs, use the CNTL, ALT, C sequence from the GENERIC MACROS to show the location of the inputs and to illustrate the structure of the model. The input section is shown on the screenshot below.

Part 3: Construct the Operations Section of the Model

In the first part of the model I have not provided the titles.  You are supposed to get to the volumes produced in MWH which is the basis for revenues.  To do this you need to know how much the solar capacity factor or yield will be.  I suggest you enter the driver of the formula in a left hand column, that you put in the units in a left hand column and that you use SHIFT, CNTL, R to quickly copy things to the right. You should also compute an index for degradation that begins in year 1 by taking the prior period and multiplying it by (1+degradation). Then you can divide the capacity by degradation and compute the capacity after degradation.  You can use whatever method you want to insert the rows.  You can create your own short-cut key for the underline.

Part 4: Revenues, Expenses, Capital Expenditures and Free Cash Flow

In any corporate finance model, M&A model, real estate model or project finance model, you will need revenues, expenses and capital expenditures.  The assumptions that create these three items (along with working capital) will drive all of the rest of the analysis.  In the next section you are to compute revenues, expenses and capital expenditures from the inputs.  I think it is a really good idea to put the drivers of each formula in a left hand column so you can be transparent (I hate looking around for where the numbers came from).  In this part of the analysis I have given you the titles as shown on the screenshot below. Once you have computed the project IRR you can use a goal seek and a macro to evaluate the required price.

Part 5: Depreciation, Operating Taxes and After Tax Free Cash Flow

If there were no taxes and you did not want to show a profit and loss statement you could eliminate this part of the model. With taxes, the shield from depreciation is an important item. Compute the depreciation by setting up an account with the balance of plant as shown below.  When you compute depreciation you can use the MIN function to make sure the depreciation does not exceed the net plant balance. Once you have the depreciation you can compute the EBIT, the taxes on EBIT, the after tax project cash flow and the project IRR.  The screenshot below illustrates this part of the model.

Part 6: Sources and Uses Prior to COD

Once you have completed the operating cash flow and operating taxes you can move to incorporating debt in the model.  In every project finance model there should be a sources and uses of funds for evaluating cash flows during the construction period.  You can think of this as a way to evaluate debt sizing.  In the example, you can fill in items on the sources and uses of funds from the inputs and items above the sources and uses.  Note that this is not realistic as items such as interest during construction and fees come from below not above and you get a circular reference.

Part 7: Debt Schedule

The debt schedule must have calculations for debt repayment, interest expenses and fees.  Repayment is often the most difficult aspect of a model.  There are alternative methods demonstrated to compute the repayment including flat repayment, mortgage repayment and inflated repayment that matches the cash flow when inflation is applied in the price.  Use the INDEX Function to select one of the three methods of repayment.  A screenshot is shown below.

Part 8: Profit and Loss Statement and Cash Flow Statement

Now you are to the easy part.  The only reason that an income statement is necessary in a project finance model is to compute taxes and to compute net income for purposes of balancing the balance sheet.  The cash flow statement can be more complex if there are cash sweeps, covenants and other items.

Part 9: Balance Sheet and Financial Ratios

The final part of the exercise is to put together a balance sheet and compute a couple of financial ratios. The balance sheet components should all come from items in the model and the equity balance should be computed.  Ratios like IRR, DSCR and LLCR come from the cash flow statement.

Exercise that Includes Construction Period

I was asked to prepare an exercise where people could quickly make a model that evaluates bid prices and other model aspects and can get you most of the result without spending too much time on some of the horrible details that can make project finance modelling so difficult.  I was told that making a relatively simple project finance model could not be taught in a single day.  This is not true.  You can see

Excel File with Exercise for Creating Combined Cycle Model with Timing and Construction Issues

Case Study Exercise on How You Can Evaluate and Existing Model

Some unlucky people have to create models and deal with circular references and many horrible details like withholding taxes on four tranches of debt.  More people have to interpret and use models that is arguably even more disagreeable because of the way models have become cumbersome and overly detailed.

This case study and exercise is designed so that you can more efficiently evaluate models created by other people including dissecting cash flow in the models, computing alternative financial ratios, adding your own scenario analysis, dissecting the model with sensitivity analysis, creating an summary page the conveys the transaction, making effective graphs the illustrate risks and finally formatting the model with nice colours and titles. My hope is that this exercise will be practical for you and that you can use some of the ideas in your current job immediately.

The file that I have used for this case is the completed case from the above model.  But it could be just about any project finance model, corporate model, M&A model or other financial analysis that has a few inputs and outputs.  The base model that I have chosen for this exercise is available for download by clicking on the button below.

Introduction to the Model Review Assignment

I have structured this assignment by attempting to explain the details of how to complete the excel stuff.  Then I have included questions that should be completed with only one or two sentences where you tell me the implications of the modelling tasks.  I have also provided titles for many of the items so that you do not have to waste a lot of time typing stuff and the exercise will not take too much time.  I strongly suggest that you do not do this exercise on an Apple computer; that you open and use the GENERIC MACRO file, the LOOKUP INTERPOLATE file and the SCENARIO REPORTER file to make the case go faster.  These three files are available for download below.

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

Download Excel File with the Function for Interpolation Using Either Compound Growth or Linear Interpolation

Scenario Reporter File - With this File, you Open the File, then Copy the Scenario Sheet to Your File and Use the Buttons

Part 1: Dissecting Cash flow

Use the SUMIF function to get the data to annual amounts. When you use the SUMIF, click on the entire row.  Question – how does the IRR on the project and equity compare to overall stock market returns. Do you think this is realistic.

Part 2: Computing Alternative Financial Ratios

I hope that you learn to look at ratios other than the DSCR.  In this case you can examine the LLCR and the PLCR.  The only calculation you have to make for this is to use the SUMPRODUCT function below the cash flow statement with the interest rate index and to find the debt at COD from the sources and uses. Explain how to interpret the DSCR, LLCR and PLCR.

Part 3: Adding your Own Scenario Analysis

Use the scenario manager to add a scenario. In this case could you invent a few cases with different inputs.  To do this, you should first copy the scenario reporter from the scenario reporter file to the file used for the analysis. Then, you can add different scenarios with the following: the project cost, the life of the plant, the availability.  Then open the scenario reporter and create a scenario report.

Part 4: Dissecting the Model with Sensitivity Analysis

I have provided a scenario analysis with a base case, a downside case and an upside case.  I have also used the INDEX function to link the cases to the model inputs. Thirdly, I have used the TRANSPOSE function to isolate different variables so that you can compute the effect on the DSCR, LLCR and different IRR’s.  In this task you are to finish the sensitivity analysis and create a tornado diagram.  To finish the data table, you should select the area of the data table as illustrates on the screenshot below.

Part 5: Making Effective Graphs that Illustrate Cash Flow and Risks

In this part of the assignment I would like you to create a flexible graph that you can use to look at different cash flows and combinations of cash flow. Use the INDEX Function to make select different cash flows.

Part 6: Creating an Summary Page the Conveys Key Aspects of the Transaction

In this part of the assignment I would like you to create a summary page that can be used to illustrate the risks and the structure of the project finance transaction.  Unlike other parts of the assignment I have left this one open ended.

Part 7: Formatting the Model with Nice Colours and Titles

Review concepts.

In this final section I have included a couple of ideas that I think are important in financial modelling for finance analysis.  I have listed a couple of bullet points and some ways that you may think about the issues.  I have also included references to my set of power point slides that describe project finance theory.

• Essence of Project Finance Model versus Corporate Finance Model, see slides 36 to 43 in the presentation below.
• The philosophy of FAST. The philosophy of FAST is discusses on slides 8-23 in the slides below.
• Structure of Models. Explanation of the structure of a project finance model is in slides 68 to 72
• Scenario Analysis.  Explanation of scenario analysis can be found on a few slides in the file below: Slides 73 to 78

Power point slides where you can find discussion of these items is included in the file that you can download below.

Power Point Slides that Accompany Project Finance Modelling A-Z Analysis and On-line Course

Example of Actual Model for Solar Project Demonstrating Complexity and Size of Real Models

• ATM locations
• ATM locator

Estás ingresando al nuevo sitio web de U.S. Bank en español.

Easing complex transactions: project finance case studies, project finance is complex, which is a why a corporate trust partner with comprehensive capabilities is a critical piece of the puzzle..

Improving the country’s aging infrastructure is a top priority, and the $1 trillion Congress recently committed to infrastructure spending will likely kickstart a host of new building projects. At the same time, the American Society of Civil Engineers estimates that the United States needs to spend $4.5 trillion by 2025 to “fix” the country’s infrastructure.

What that means for stakeholders across the infrastructure industry is a growing pipeline of projects, along with the need for project finance expertise to help move projects forward. Bringing those projects to a successful close requires proven expertise, experience and strong communication processes, as well as an ability to work seamlessly with a number of parties and an ability to understand and navigate project finance risks.

As a leading global corporate trust provider , U.S. Bank has experience working on many complex transactions.

“We’ve seen many different approaches to these financings, and we have the ability to come to the table, apply our expertise from prior transactions in the documentation process, and help our clients reach the best outcome on how they’re going to put these complex financing packages together,” says Bob Kocher, managing director, U.S. Bank Global Corporate Trust.

That expertise was recently highlighted in two major project finance projects, where U.S. Bank served as a trustee in bond issuances in the capital stack of the Red River Diversion Project at the Minnesota/North Dakota border and the Central 70 Project in Colorado.

Red River Diversion project

In an infrastructure industry that is no stranger to large, complex projects, the Red River Diversion Project is a notable standout. The $3 billion project is more than a decade in the making, with numerous stakeholders and a mix of funding sources. It’s also a landmark public-private partnership (P3) project in the water infrastructure industry.  

The Red River Diversion project represents the first use of the U.S. Army Corps of Engineers’ P3 Pilot Program to reach financial close. The aim of the program is to improve collaboration between the public and private sectors, as well as develop a more efficient alternative financing model for future Corps infrastructure projects.  

The Red River Diversion project intends to provide permanent, reliable flood protection to the Fargo-Moorhead metropolitan area. The Red River serves as the state border for much of Minnesota and North Dakota and cuts through the center of Moorhead, Minnesota, and Fargo, North Dakota. Spring flooding in the Fargo-Moorhead metro has been a chronic problem for decades.  

The solution is the development of a 30-mile diversion channel. The Army Corps of Engineers is overseeing design and construction, with completion scheduled in 2027.  

The Red River Diversion project involved a number of intricate financing sources that were woven together, including developer equity, federal and state funding and $1.1 billion from local tax levies. The Metro Flood Diversion Authority worked with the U.S. Environmental Protection Agency to obtain one of the largest Water Infrastructure Finance Innovation Act (WIFIA) loans in the program’s history, at $569 million. In addition, project financing included $273 million in tax-exempt senior bonds issued through the Wisconsin-based Public Finance Authority.  

According to the Corps , the Red River Diversion P3 was an “innovative approach leading to significant gains in efficiency, productivity and resiliency” that saved the federal government $277 million and shortened the construction time by 10 years.  

As part of a competitive bid process, U.S. Bank was selected in April 2021 to serve as the bond trustee on the bonds issued by the Wisconsin Public Finance Authority, as well as filling additional roles as the account bank, collateral agent and dissemination agent.  

Once U.S. Bank was selected as trustee, it needed to get up to speed quickly with all documents, provide comments regarding duties and liability and communicate to all parties its views on how the transaction should work as it related to the daily activities of the trustee.  

“This trustee deal had a tremendous amount of document turnarounds as a result of the complexity of the transaction. It required attention to detail to ensure changes were consistent throughout all the documents,” says Angela Davis, relationship manager, Global Corporate Trust at U.S. Bank.  

Keeping communication and workflow on track is a testament to the U.S. Bank team’s diligence in tracking documents, as well as its proactive approach to the collection and distribution of project information and covenants.  

“Through our hands-on partnership and ability to work efficiently with other business lines inside of U.S. Bank, our client received everything they needed to keep this project moving forward,” says Davis.  

Collectively, the U.S. Bank team will serve as the operational and administrative end of the financing, following the documents, administering the movement of funds and making sure the money is moved from account to account properly. U.S. Bank will be responsible for the billing and collecting funds to pay holders of the bonds and senior notes through 2056.  

“P3 projects are the wave of the future, and U.S. Bank is at the forefront of that shift in how infrastructure projects are financed,” says Kocher.

“We can come to the table, apply our expertise from prior transactions in the documentation process, and help our clients reach the best outcome.” Bob Kocher, managing director, U.S. Bank

Central 70 project

Interstate 70, between I-25 and Chambers Road in Denver, is a key corridor that services nearly 1,200 businesses and provides an important regional connection to Denver International Airport. The  Central 70 Project  will reconstruct a 10-mile stretch of I-70, add one new Express Lane in each direction, remove the aging viaduct and create a four-acre park over a portion of the lowered interstate between Brighton and Colorado Boulevards.  

The Central 70 Project involves the refinancing of a 2017 Transportation Infrastructure Finance and Innovation Act (TIFIA) loan, along with the financing of additional costs. As part of the refinancing, the U.S. Department of Transportation’s (DOT) Build America Bureau provided a new TIFIA direct loan with a reduced interest rate, allowing for additional loan principal increase to facilitate project completion.  

Besides TlFIA, the project is backed by the proceeds of tax-exempt private activity bond and taxable bond issuances, as well as contributions from the state DOT, the Colorado Bridge and Tunnel Enterprise, the High Performance Transportation Enterprise, and local and state entities .  The Series A bond issuance totaled $51,670,000 and the Series B bond issuance totaled $464,955,000.  

U.S. Bank served as bond trustee and acted in ancillary roles as the collateral agent and intercreditor agent, paying agent, registrar, transfer agent and dissemination agent. In addition, because bondholder approval was needed to issue the new debt in 2021, U.S. Bank stepped in and served as the tabulation agent for the existing investors.  

Key to a successful project finance deal is finding a partner with the expertise, resources and systems to streamline the process, such as tracking necessary compliance requirements and providing online reporting for the client. In addition, these complex deals often require a higher level of client relationship management.  

“There is a lot more client interaction than a typical municipal financing, because there is always something going on, whether it is requisitions being paid or the sponsor needing to post financials or updates that need to be disseminated to the market,” says Gretchen Middents, relationship manager, Global Corporate Trust at U.S. Bank. “So, it is a much more hands-on relationship as compared with other assignments that don’t have the same scope of documents and requirements.”  

In addition, working with a third-party trustee and agent to perform all project finance roles can produce numerous efficiencies, such as streamlining operations, coordinating workstreams from various parties and providing assistance for investors at every stage of the project lifecycle. Finding a partner with extensive experience servicing all debt vehicles can help guide decision-making with strategic insights and proactive solutions.  

“These projects are more of a team effort because of the complexity,” adds Middents, “and being able to rely on others within our organization is key to our success.”

U.S. Bank administers a variety of infrastructure asset types and has the dedicated expertise to assist investors at every stage of the project finance lifecycle. See our extensive suite of services for debt financing  here  or contact Lars Anderson at  lars.anderson@usbank.com  or Alejandro Hoyos at  [email protected] .

Learn about U.S. Bank

Related content.

High-yield bond issuance: 5 traits lawyers should look for in a service provider

Emerging trends in Europe: An outlook from multiple perspectives

Case study: U.S. asset manager expands to Europe

Top 3 considerations when selecting an IPA partner

The ongoing evolution of custody: Tips for renewing your custody contract

Depositary bank and collateral agent

3 innovative approaches to ESG investing in Europe

Employee benefit plan management: trustee vs. custodian

Digital processes streamline M&A transactions

European outlook: Trustee experience more important than ever

Managing complex transactions: what your corporate trustee should be doing

How to maximise your infrastructure finance project

High-yield bond issuance: how to avoid 5 common pain points

4 benefits of independent loan agents

An asset manager’s secret to saving time and money

Middle-market direct lending: Obstacles and opportunities

Programme debt clients want reliable service – no matter where they’re based

Maximizing your infrastructure finance project with a full suite trustee and agent

At your service: outsourcing loan agency work

Disclosures.

Loan approval is subject to credit approval and program guidelines. Not all loan programs are available in all states for all loan amounts. Interest rate and program terms are subject to change without notice. Mortgage, home equity and credit products are offered by U.S. Bank National Association. Deposit products are offered by U.S. Bank National Association. Member FDIC.

• Browse All Articles
• Newsletter Sign-Up

ProjectFinance →

No results found in working knowledge.

• Were any results found in one of the other content buckets on the left?
• Try removing some search filters.
• Use different search filters.

• Unsecured Business Loan
• Trade Finance Solutions
• Bill Discounting Facility
• Factoring Services
• LC Discounting
• Bank Guarantee
• Unsecured Working Capital Loan
• CGTMSE Loan
• Loan Against Shares
• Loan Against Property
• Lease Rental Discounting
• Machinery Loan
• Project Funding
• Debt Syndication
• Builder Finance
• Working Capital Finance
• Funding for Schools
• Foreign Currency Funding
• Sugar Pledge Funding
• Finance for Doctors
• Medical Equipment Finance
• Loan for CA / CS
• Loan for Architect Professional
• Corporate Finance
• Structured Debt Financing
• Private Equity
• Private Debt
• Project Report for Funding
• View All Products

Project Finance Case Study

• Debt Syndication Process
• The Process of Trade Financing
• Process of Bill Discounting Facility
• Foreign Currency Funding Process
• Factoring Funding Execution Process
• Sugar Pledge Loan Case Study
• LC Discounting Case Study
• Bank Guarantee Process
• Unsecured Funding Process
• The process to Avail of a Machinery Loan
• CGTMSE Loan Process
• Working Capital Financing Solution
• Lease Rental Discounting Funding Process
• Loan against Property Process
• Company Profile

Project Finance case study

Robert owns an automotive manufacturing company known as ‘RD Manufacturing Private Limited’. He started the company 10 years ago. The company is doing very well with a turnover of 75 crores this year. The company is into manufacturing automobile parts and other essential components. Some of the best automotive brands in India are clients of ‘RD manufacturing’.

Till now, Robert’s company had an office in Mumbai and a manufacturing plant in Pune. Now, due to the rapid growth of the business, Robert’s clients have spoken to him about opening another plant in Jamshedpur. Therefore, now Robert is looking forward to expanding his business by opening another manufacturing unit at Jamshedpur. But he is worried about the funds for his new project. Taking the funds out of working capital will create a challenge for the existing customer and will hamper the working capital cycle.

As a good businessman, this is not a good course of action. So he started to explore the options of raising the funds from the debt market, without disturbing the existing fund flow. He has good credit and can repay the loan from the revenue he gets from his project after completion. He foresees very good profit and rapid growth if he expands his company now. Thus, Robert’s rating as a business person and his extensive experience in the manufacturing industry is well known.

Read more about effective management of the working capital cycle here.

Financial Snapshots

Analysis from inputs.

Robert is especially looking for Non-recourse funding as a feature of the loan. Non-recourse funding means that he and the other shareholders of the borrower will have no personal liability in case of monetary default. Any recourse the lender may have will be limited primarily or entirely to the project assets if the project company defaults on the debt.

He didn’t know which loan to apply for or which financial institution to contact. While discussing his problem with a business friend, the name Terkar Capital came across. Thus, Robert approached Terkar Capital and we set up a meeting with him to understand the process of project financing.

Financial Analysis

Our executives met Robert and understood his requirements for the funding and his business. So, after a detailed discussion, we suggested that he should take Project Finance . This type of funding would be a perfect fit for him considering the viability of his project, payback period, and his personal experience and profile. 

The financial institution has to be shown that the project is viable in order to receive the loan. Therefore, we helped Robert prepare a report explaining his project and the financial position of the company. And that he is capable of paying off the loan from the revenue, that project generates. He also would not require any separate mortgage for the funding as the project itself will act as a mortgage for the loan. In the case of project funding , the funds can be generated as per the requirement, be it periodically or all at the same time. 

Robert got his project funding sanctioned in just 20 working days after approaching us. Robert’s new manufacturing plant has been doing very well now and he has almost repaid the entire loan amount. So, now, he is one of our loyal customers at Terkar Capital.

Project Financing at Terkar Capital

Terkar Capital understands the needs of entrepreneurs like Robert, who are seeking project funding services. With a personalized and human approach, our executives took the time to listen to Robert’s requirements and thoroughly assessed his business. Recognizing the potential for growth and Robert’s credibility, we recommended Project Finance as the ideal solution. This allowed Robert to secure the necessary funds without disrupting his existing operations. Today, Robert’s manufacturing plant thrives, and he stands as a testament to our commitment to our loyal customers at Terkar Capital.

REACH OUT FOR THE HASSLE FREE EXECUTION PROCESS

Do reach out to us with any questions or doubts. To ensure that you get the best service, please contact us before visiting.

• Consult with Expert
• Follow us on Linkedin