I have a profound dislike of models with excessive tabs (or sheets). I believe this makes it difficult to trace the flow of logic in a model. Consequently, I believe the model should have only three main tabs, with ancillary presentation sheets referencing those sheets, but not containing calculations themselves.
My preferred sheets are:
- Assumptions sheet: This contains assumptions which are not series in time. The assumptions sheet should contain a Case selector at the top, the name of the assumptions down the left column, the active case assumptions next to this column, and then different scenario cases off to the right. Examples of different cases are:
- The base case
- Increased opex case
- Increased construction price case
- etc
The active case assumptions should reference the selected case through the use of the index function, or similar
- A Time-based Assumptions sheet, which specifies assumptions which change in time, such as fx rates, interest rates, inflation, etc. These should generally be specified with the same periodicity as the model itself
- A Working sheet, which contains all of the calculations relating to the model. Generally, the Working sheet contains the following sections:
- Dates, period numbers, etc
- Indicators (construction period, operations period, repayment period, etc)
- Construction (funding requirements, VAT, etc)
- Technical calculations (how much the plant produces, what its technical assumptions are)
- Revenue calculations (dependent upon the technical performance of the plant)
- Opex (dependent on, inter alia, the technical performance of the plant)
- Equity and shareholder loan draw downs
- Tax and depreciation calculations
- Calculation of Cash flow available for debt service (CFADS) per the facility documents’ priority of payments (also called the cash flow waterfall)
- Debt (draw downs over the construction period, repayments over the operating period. Possibly more than one facility, either ranking pari passu (repayments and draws occur proportionally) or not (one facility drawn or repaid after the other)
- Debt service reserve account (DSRA) funding and draws and Maintenance Reserve Account (MRA or MMRA) funding and draws
- Covenant ratio calculations (DSCR, LLCR and PLCR)
- Distributions to shareholders
- Financial statements
As mentioned above, other sheets may be required by lenders or project sponsors. These usually include presentation sheets for Annual Financial Statements and a Summary sheet.