Building an Operating Model in Excel
- 04:27
Building an operating model in Excel in renewable energy project finance.
Downloads
No associated resources to download.
Glossary
Project finance Renewable EnergyTranscript
What are the required sheets that we will need in the model that we need to build? Well, that's going to depend on the objectives that we're trying to set. Typically for a renewable power project model, we're looking at assessing the amount of the cash flow. So we need to be able to work out what the total cash flows are going to be, and especially how much cashflow is available to service debt. Cashflow available for debt service, commonly abbreviated to CFADs and what money is left over to be able to provide some sort of return to the equity shareholders.
If our focus is on cashflow, then we need to calculate how much cashflow this project will generate. We will need to know the revenue and the costs during the operating period. We'll need to have an estimate of both the amount and the timing of the capital expenditure, how much working capital will be needed. Typically, this will be things like accounts receivable.
How much leverage is this project going to have? How much debt is going to be present in the project relative to the amount of equity that has been invested and for that debt we'll need to work out the costs. What interest is going to be payable, what fees need to be paid and over what time period, and what amounts as the debt going to get repaid period by period until we reach the end of the loan life and all the debt gets repaid.
So what sheets will be required? We will need an input sheet where we gather together all of the assumptions about the project. Remember of course, we are modeling a future forecast project. So all of the assumptions are just that assumptions as, and we need to put them into the model in a separate sheet on separate cells for each assumption so that we can quickly and easily change them. If we want to test the assumption or we get an update as to what the assumption should be. We will need an operating cash flow calculation. How much revenue and operating cost will be incurred by the project? We'll need a calculation of capital expenditure. How much and when? How will that capital expenditure be funded? What sources of funding will be used? Equity and debt, how much of each and when do those funds get injected into the project company? How much cashflow will be available to service debt and how much will be left after that to give a return to the equity investors.
We'll pull together some ratios and covenants, things like a debt service cover ratio, for example. So this will provide some comfort to the lenders about what proportion of cash is available relative to the amount of debt payments, interest fees, and capital repayments that need to be made in any one period. We could also produce an errors sheet, a check sheet that makes sure that the model has integrity and is robust, nice to haves, but not essential, would be an income statement in the conventional financial statements type of buildup of an income statement showing sales costs, interest tax, dividends, and retained earnings. A balance sheet showing all of the assets, liabilities, and equity year by year until the completion of the project. And if the model gets to be quite large and has multiple sheets, it's a good idea to have a model summary sheet that presents some of the highlights of the model, maybe some of the key inputs and key outputs so that people don't need to review the model by going through all the detail, but they can just look at a single sheet and get an overall impression about the project.