How to Create a Project Finance Model in Excel

This tutorial will show you how to create a Project Finance Model and forecast Cash Flow in Excel with step-by-step instructions, practical examples to download and the calculation of Equity Investment Returns.

First, I will build a simple cash flow forecast for a generic project. Then, I will use that spreadsheet to create a consolidated cash flow financial model. Lastly, I will show how to use those cash flows to calculate return on investment and then see how changes in assumptions impact your overall equity returns.

Download my Project Finance Model Template in Excel to follow the examples from this tutorial.

You can also watch the video version of the tutorial at the end of this post.

Step 1. Start with a Cash Flow Template for One Project

To create a Project Finance Model in Excel, start with an empty spreadsheet and type in month numbers in one of the first rows. I will build a monthly cash flow model spread over ten years, so my model will have 120 columns, with one for each month.

Then, below the month rows, add the main drivers of the model, for example, the number of units added or built. For simplicity, I will assume that each unit represents a connected home (for example, a house connected to a fibre or broadband internet network). You could also easily define the driver as a unit of length (for example, the length of the electricity grid) or any other meaningful metric.

Lastly, to make your project finance model user-friendly, use Excel’s freeze panes functions to see what period you are looking at as you scroll through the spreadsheet.

Step 2. Forecast Direct Cost and Revenues in Project Finance

Connect the drivers with the model’s revenue and cost assumptions to calculate Direct Revenues in a Project Finance Model.

For example, if the model assumes 100 new units connected in month one and the upfront fee is $100, the total upfront revenue would equal $10,000 (i.e. 100 new units x $100).

Similarly, link a unit cost assumption to the drivers to calculate Direct Costs. For example, my Project Finance Model Template takes the number of new units and multiplies it by the cost per acquisition assumption to calculate marketing cost.

Notice that I have listed the primary revenue and cost assumptions monthly. Listing the monthly inputs will allow the spreadsheet’s user to see what assumption a calculation uses in a given period. It will also allow you to adjust the pricing or costs to inflation or retail price increases. I have also clearly divided cash inflow into Upfront and Recurring Revenues.

Step 3. Calculate Project’s Operating Expenditures

To calculate operating costs in a project finance model, split the cost into variable and fixed parts. For example, look at how my Project Finance Model calculates the salary costs. Notice that the project’s cash flow forecast divides the employee numbers into two parts:

• Direct Headcount: driven by the number of units added in your model; for example, you can assume one additional employee per a fixed number of new units,
• Fixed Headcount: includes central staffing, such as CEO, finance, and other expenses, which will be less sensitive to changes in the number of units.

Step 4. Calculate Capital Expenditure in Your Financial Model

To calculate Capital Expenditure, multiply the number of newly connected units by the per-unit build cost. Split your cash flow projections into phases. For example, divide the timeline into the following periods:

• Setup period: the time needed to prepare the company for growth. In this period, your model will incur costs with no generated revenues,
• Growth period: the period in which you accelerate growth and gradually add customers over time.

My Project Finance Model Template assumes a fixed number of new connections over time, but you could also flex it to fit your growth projections.

Notice that the model forecasts capital expenditure differently depending on the period. In the setup period, it takes a one-off cost, for example, R&D or software development, and spreads it over time. In the growth period, it takes the number of newly connected units and multiples by assuming costs.

Step 5. Add Debt to Your Project Finance Model

Adding debt to your project finance model will lower your equity requirement and often increase your overall returns on investment. My Project Finance Excel Template assumes that the main driver of the potential debt level will be an EBITDA.

To calculate the level of annual debt for your project finance:

1. Set up your Excel template to look back at the previous twelve months of EBITDA.
2. Use that figure as your basis and multiply it by your project finance model’s Debt-to-EBITDA assumption; that is the maximum debt level you can raise.
3. Add additional debt to top up your current debt balance to that maximum level.

For example, if in a Cash Flow Forecast, the previous year’s EBITDA was $1.0m and you use a Debt-to-EBITDA multiplier of 5, you can raise debt to a maximum of $5.0m. Hence, if we assume that the Current Debt Closing Balance is $3.5m, you can top it up by $1.5m. Note that using this method, you can only draw debt if EBITDA is positive.

The debt calculation uses assumptions for the number of future repayments. I used Excel’s PMT formula to calculate the total debt payment and then allocated it between the interest and the principal.

As a result of adding debt financing, you will notice that my Project Finance Model now has two kinds of cash flows: unlevered and levered. The difference between those two cash flows is the amount of bank lending invested.

Read my tutorial on How to Create an Excel Financial Model with Debt Financing to learn more about other methods of using cash flow forecast to calculate debt.

Step 6. Build a Consolidated Project Finance Cash Flow Statement

When you have modelled a Cash Flow Forecast for one project, consolidate it into a series of projects. Combining project cash flows will allow you to see how to use Investment Returns from one project to fund another.

In my example, I assumed a set number of projects with their starting date separated by a fixed period. My Project Finance Model Template uses a dynamic phasing assumption, so you can always change it and see its effect on your overall returns. For example, you can set the template to five projects, with each project starting six months apart.

Lastly, to make the consolidation and flexibility possible, create a ‘Consolidated Cash Flow’ tab and then use Excel’s INDEX formula to aggregate each project’s revenues and costs in one place.

Step 7. Summarise Model’s Cash Flows and Assumptions

Aggregate the Project Finance’s Cash Flow into an annual view. Summarising the monthly output will allow you to build a clear and easily digestible statement of your cash flows and test the impact of your assumption.

Then, use the output to calculate the level of Equity Investment Required. To create the summaries, use Excel’s SUMIFS formula, allowing you to quickly aggregate the main numbers by year.

Lastly, list the main assumptions and metrics next to the summary for ease of presentation and usability. Having the inputs next to the model’s output allows you to test how a change in one assumption affects the overall picture. Try changing the value of one input and see how it affects the cash flow statement.

Step 8. Calculate Equity Returns in a Project Finance Model

The negative cash flows, in summary, represent the level of external equity needed to fund your project finance model. My Project Finance Model assumes three sources of investment:

1. Revenues: excess cash flow returns from existing projects, decreasing the equity investment.
2. Debt: loans or other bank funding, usually secured by the historical cash flows generated from projects.
3. Equity: external investment, filling the cash requirement between the cash required and the funding from existing cash flows and debt.

Make sure to add Return on Investment Calculation to your Project Finance Model. Use Excel’s IRR and NPV formulas to calculate the cash flow’s Return on Equity.

For example, in my Project Finance Model Template, I calculated the annual cash flow to equity and valued the business at the end of ten years. The exit value or any positive cash flow represents the distribution of proceeds or dividends to the equity investors. To calculate the exit or terminal value, take the EBITDA and apply a multiplier relevant to your industry.

You can read more about cash flow forecasting, valuation using EBITDA and return calculations in my How to Create a Cash Flow Forecast Tutorial.

Step 9. Stress Test Project Cash Flows and Equity Returns

Download my Project Finance Model Template and change its assumptions to see how they impact your cash flows and the calculation of Investment Returns. A few financial metrics can help you analyse the results. Specifically, pay attention to the return on investment metrics such as IRR or NPV, the level of Equity Required or the Payback Period.

Stress test the model or build scenarios that will break and become unsustainable. For example, you may find that accelerating your build positively affects revenues, but it also increases the overall equity investment required to fund the model.

Similarly, spreading the projects apart may lower your overall returns, but it may decrease your equity requirement as you will have more access to cash from earlier projects.

Lastly, use Excel’s data table to summarise the sensitivity and outputs of the model. In the examples below, I have tested how to change both units built per month and project phasing affects the model, but you can also try different inputs.

Summary: How to Build a Project Finance Model in Excel

Here is the summary of the steps required to create a Project Finance Model in Excel covered in this tutorial:

1. Start with a Project Cash Flow Forecast Template: This will allow you to phase and consolidate individual projects into one view.
2. Calculate Direct Revenues and Costs: Use model drivers, such as the number of units connected, and link them to the revenue and cost assumptions.
3. Forecast Operating Costs: Estimate a project’s costs for salaries, office, admin and other recurring expenses.
4. Estimate Project’s Capital Expenditure: Connect the model’s drivers to per-unit costs to estimate overall build spend.
5. Add Debt to the Project Finance Model: Improve your Equity Investment Requirement by including a bank loan in the cash flow.
6. Consolidate Individual Project Cash Flows: An annual summary will show how each project contributes to the overall output.
7. List the Main Assumptions: Placing the inputs next to the Project Finance Model’s summary will allow you to check their impact on overall cash flows quickly.
8. Calculate Equity Returns: Use metrics such as IRR and NPV to evaluate the investment return of the Project Finance Model.
9. Stress Test the Project Finance Model: Create scenarios and modify a set of assumptions to measure the sensitivity of the project cash flows.

Download: Project Finance Model Template in Excel

Click on the button below to download my Project Finance Model Template in Excel. The spreadsheet allows you to build a cash flow forecast for one generic project and then aggregate the projections into one consolidated view. The file contains the following sheets:

• Project Cash Flow: A monthly Cash Flow Forecast for a generic project. It calculates Direct Revenues and Costs, Operating Expenditures and CAPEX based on the growth assumptions and units added.
• Project Summary: An annual view of a generic project’s cash flow with a simple valuation and a Return on Invested Equity calculation.
• Consolidated Cash Flow: The sheet takes the output of the Project Cash Flows, applies timing assumptions and consolidates multiple projects into one view.
• Consolidated Summary: Aggregates the Project Finance’s consolidated cash flow model into an annual view and an editable list of assumptions. It also contains a calculation of return on invested equity and scenario analysis.
• Graph: A visual representation of the source of capital and Equity Investment needed to fund the Project Finance Plan.

 

Video: How to Use the Project Finance Model Template

For more details and a step-by-step explanation of using and creating the Project Finance Model Template in Excel, watch my video tutorial below:

 

Get in Touch

Hi, my name is Jacek, and I love Excel. I hope you’ve enjoyed reading this tutorial as much as I enjoyed writing it! If you have any questions about the project finance models in particular or financial analysis in general, don’t hesitate to get in touch.

Click here if you would like to book my One-to-One Financial Modelling Crash Course. For more Financial Modelling Tutorials, visit this page. If you need help with financial modelling or cash flow forecasting, see my Financial Modelling and Forecasting Consulting services here.

Read More:

How to Create a Cash Flow Forecast in Excel – learn more about creating a cash flow forecast in Excel, focusing on building a scenario analysis, return on equity calculator, and user growth assumptions.

Learn More about How to Build a Financial Model with Debt Financing in Excel – in this tutorial, I will take you through building a debt model in more debt, and you will learn how to calculate debt in Excel using cash flow’s projected revenues.

Merger and Acquisition Model in Excel – this tutorial is another example of consolidating two companies into one cash flow forecast. It includes an example of using debt to finance the transaction.

How to Create a Capital Investment Plan in Excel – learn more about converting a capital investment forecast into cash flow, revenue/sales and variable and fixed costs calculations.

How to Create a Real Estate Investment Model in Excel – this post will teach you how to create a cash flow forecast for a real estate business and show you examples of adding debt to a financial model.

Tutorial on How to Create a Telecom Investment Model in Excel – learn more about forecasting revenues and network build costs for another capital-intensive infrastructure project.

How to Create a Subscription Model in Excel – this tutorial explores how to build a model for businesses relying on revenues generated from subscription services.

Your First Steps in Excel – Beginner’s Tutorial – if you are new to Excel, this teaches you the basic formulas and functionalities to set up your first spreadsheet.