Spreadsheets for fun and profit — what makes them complete?
Most experienced investors will tell you “we’ll know it when we see it,” as there is an art to presenting numbers in a way that enables the reader to accurately pinpoint risk versus reward, but here are some of the main considerations for building and validating complete and investible pro forma financial projections.
Jargon alert: some of the words and phrases used in this brief article (like “pro forma“) are specialized terms used by finance professionals. For non-finance managers, look up anything new or seemingly ambiguous at glossary.
In3’s senior personnel are formally trained (such as by Moodys Analytics) and specialists in this area. That doesn’t make us “right” but does inform the following guidelines and criteria for evaluating financial projection quality and efficacy, while giving the project’s proposal for funding the best possible odds of gaining support — either from our partners, or others you may be able to access. See also Project Financeability Checklist.
A separate matter is how to summarize and present financial modeling work to show the key facts that are of greatest interest to investors. For a few hints on this, consider our guide to Sources & Uses Statements (with numerous examples on page 3) a solid starting point.
A complete financial model has the following characteristics:
- Complete accounting of costs, where possible, aided by third party validation of estimated costs, actual bids or experience-based costing by a competent estimator, such as an EPC firm or General Contractor (more). Why not DIY? Because history shows that we humans tend to underestimate or overlook certain cost categories, due in part to the bias inherent with showing a project’s financial feasibility. Although we do not require feasibility studies for projects our partners would finance, such precise estimating can be extremely valuable.
- Transparent assumptions. Instead of just hard-coding numbers that involved calculated estimates, where possible (even if seemingly impractical to do this), show the formula as well as the calculated basis and describe any underlying methods, metrics, market, and/or benchmark comparison that were used in constructing the estimate or resultant forecast. Extra points (faster due diligence, fewer questions, more of a self-guided tour):
- Put all assumptions in one place. User-friendly models that group together what are assumptions (inputs) as distinct from estimated performance, metrics (such as results indicators, performance ratios, summarizing statements, etc.), or other “outputs” are best presented on different sheets.
- Put controlling variables in a visible cell — which also makes it easier to update the model due to fluctuations in currency exchange rates, prevailing rates of interest, presumptions about how inflation would affect inputs or output, tax venue-specific factors, in a cell that is visible and labeled
- Show your work — using sensitivity analysis, provide additional footnotes or other documentation (even if separate from the live spreadsheet / workbook / model) to enable self-guided access to the key assumptions. Do not just present your assumptions in the model as part of calculations, as “hard coded” numbers, but take note of their basis and explain it somewhere that the funder can easily access. Trace values used back to their source(s), and disclose your methodology, or at least explain the logic employed to support any critical inputs and anticipated performance.
For example, if modeling utility-scale solar/PV, provide the source of the estimated GWH/year solar irradiance.
- Provide annual financial statements — projected annual income statement, balance sheet and cash flow statement. Properly organize and present a Sources & Uses statement as part of the project’s executive summary. Include any prior year actuals adjacent to the projections.
- Anticipated Results and Social/Environmental Benefits: Include industry-standard ratios and metrics such as Internal Rate of Return (the project’s overall, unlevered IRR), Debt Service Coverage Ratio (DSCR) or Cash Available for Debt Service (CADS) and net operating margins for the life of the forecast. Indicate the number of years used for each metric. Points to any industry standard ratios (such as Price-Earnings or PE) for equity investor-related valuations, although this is option with most project financiers, a cornerstone of venture finance more than greenfield project finance.
- IRRs are particularly important to investors as a performance benchmark. The number of years used is an important disclosure — typically 10-25 years would mirror the useful life of the project’s assets or life of the loan. If an IRR is not at least on par with standards for a given project’s industry (more), or if well above or below those standards, expect questions. Better, proactively explain and show why the IRR is either materially above or below the industry standard.
- Equity IRR (eIRR) is more subjective, and not widely respected for this reason. If you show eIRR, be sure to disclose the basis. Do not include any “terminal value” (selling price) in overall, unlevered IRR calculations.
- DSCR should be at least 1.25 for the life of most long-term loans. Document how many years were used to calculate the DSCR average.
Upholding IFRS or US GAAP accounting standards
Use as a basis for these projections a spreadsheet model template or similar tool that has been rigorously tested and proven to uphold international accounting rules such as IFRS. If yours is not based on such a model, consider getting it audited by a certified/accredited financial services firm or accountancy that specializes in project finance.
Impact Investing Metrics
Although not essential to show incentives like Investment Tax Credits, carbon credits, or non-financial impacts such as how people benefit, the indirect value of restoring or conserving ecosystem services, eco-diversity, or numerous other measures of sustainability, it is nonetheless important to use established tools like Lifecycle Analysis to properly account for such benefits as part of the so-called Triple Bottom Line.
Each industry has its unique and often standardized metrics. For example, for power generation, investors may want to understand the Levelized Cost of Energy (more at glossary), thus showing your proficiency is a value add, even if it says more about you and your team than (by itself) conveying the true investability of your project.
Annual trended surveys of The GIIN’s impact investment community shows that 90%+ of all impact investors are obsessed with measuring and monitoring the social and environmental impacts of their investments. More at Triple Bottom Line