How to Create a Project Finance Model

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

First, it builds a simple cash flow forecast for a generic project. Then, it uses that spreadsheet to create a consolidated cash flow financial model. Lastly, it shows 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 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, start with an empty Excel spreadsheet and type in month numbers in one of the first rows. For example, my Project Finance Model Template spreads over ten years, so it has 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, the template assumes that each unit represents one customer. You could also easily define the driver as a unit of the length of a network or any other meaningful metric.

Lastly, to make your project finance model user-friendly, use Microsoft 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 allows the spreadsheet’s user to see what assumption a calculation uses in a given period. Also, it helps adjust the pricing or costs for inflation or retail price increases.

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, my Project Finance Model calculates the salary costs.

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, an additional employee per a fixed number of new units,
• Fixed Headcount: includes central staffing, such as CEO, finance, and other expenses, which is 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. Also, 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, a project incurs costs with no generated revenues,
• Growth or Build period: the phase with accelerated growth and customer increase.

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 also 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. However, in the growth period, it takes the number of newly connected units and multiples them by the cost assumption.

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 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 sum up 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, which is the maximum debt level.
3. Add additional debt to top up the current balance to that maximum level.

For example, if the previous year’s EBITDA was $1.0m, and you use a Debt-to-EBITDA multiplier of 5, the result will be a maximum of $5.0m of debt. Hence, assuming a 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.

As a result, you will notice that my Project Finance Model Template now has two kinds of cash flows: unlevered and levered. The difference between those two cash flows is the net debt funding.

Learn More: Read my tutorial on How to Create a Financial Model with Debt Financing to learn more about other methods of calculating debt.

Step 6. Build a Consolidated Project Finance Cash Flow Statement

Consider adding and consolidating projects into one forecast. Combining project cash flows will allow you to see how to use investment returns from one project to fund another.

For 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. In short, 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 use Microsoft 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 Microsoft Excel’s SUMIFS formula 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

Use the financial model’s output to estimate the required investment. The annual negative cash flows represent the level of external equity needed to fund your project finance model.

My Project Finance Model Template 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 Microsoft Excel’s IRR and NPV formulas to calculate the cash flow’s Return on Equity.

The exit value or any positive cash flow represents the distribution of proceeds or dividends to the equity investors. For example, my spreadsheet takes the EBITDA and applies a multiplier to calculate the terminal value.

Learn More: You can discover more about cash flow forecasting, valuation using EBITDA and return calculations in my How to Build a Cash Flow Forecast in Microsoft Excel.

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, Equity Required or Payback Period.

Stress test the model or build scenarios to find weak spots in the financial plan. 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 Microsoft 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 how project phasing affects the model.

Summary: How to Build a Project Finance Model

Here is the summary of the steps required to create a Project Finance Model in Microsoft 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.
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

Click the button below to download my Project Finance Model Template. The Excel 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.

The file is in Excel format, but you can convert it to Google Sheets using the instructions here [external link].

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, watch my video tutorial below:

 

Get in Touch

Hi, my name is Jacek, and I love spreadsheets! I hope you’ve enjoyed reading this tutorial as much as I did writing it. If you have any questions about creating a Project Finance Model in particular or financial analysis in general, don’t hesitate to get in touch.

Explore my other tutorials to learn more about financial modelling or data analysis. If you need further support, find out about my One-to-One Training and Financial Modelling Services.

Please note that this tutorial is provided for general informational and educational purposes only and is not a substitute for professional advice.

Learn More

How to Build a Cash Flow Forecast in Microsoft Excel – Build a cash flow forecast with scenario analysis, return on equity calculator, and user growth assumptions.

Financial Model with Debt Financing – Explore creating a cash flow model with debt using projected revenues.

Monthly Budget Forecast – Create long-term projections based on actual and near-term estimates.

Merger and Acquisition Model – Consolidate two companies into one cash flow forecast using debt to finance the transaction.

Capital Investment Plan – Convert a capital investment into revenue, sales and cost calculations.

Real Estate Investment Model – An example of forecasting building cost with debt financing.

Telecom Investment Model – Forecast revenues and network build costs for a capital-intensive infrastructure project.

Subscription Model with Churn Calculation – Explore creating a financial model for businesses relying on revenues generated from subscription services.

Your First Steps in Microsoft Excel – Beginner’s Tutorial – Learn basic formulas and functionalities to set up your first spreadsheet.