Start Up Hydro Dam Excel Model and Valuation (Imperial Units)
Important Note: Imperial Metrics are used.
Start Up Hydro Dam Excel Model presents the business case of an investment in the construction of a dam and the sale of the hydroelectric energy generated from it. The model generates the three financial statements as well as the cash flows and calculates the relevant metrics (cash on cash, Internal Rate of Return, payback period, shareholder value, Debt Service Coverage Ratio, Loan Life Coverage Ratio). The financing options for the project include a construction loan, a Value Added Tax facility as well as an overdraft facility and of course equity funding from investors.
So a quick overview of the model, in the contents tab you can see the structure of the model and by clicking on any of the headlines to be redirected to the relevant worksheet.
On the manual tab you are able to feed the general information for the model such as: model name, responsible, timeline of the model and date and currency conventions.
Additionally there is a description of the color coding of the model in the same tab. Inputs are always depicted with a yellow fill and blue letters, call ups (that is direct links from other cells) are filled in light blue with blue letters while calculations are depicted with white fill and black characters.
There is also a color coding for the various tabs of the model. Yellow tabs are mostly assumptions tabs, grey tabs are calculations tabs, blue tabs are outputs tabs (that is effectively results or graphs) and finally light blue tabs are admin tabs (for example: the cover page, contents and checks).
Guide Tab: this tab helps you with some benchmarks in order for you to use in the model as inputs.
Moving on to the Inputs (in yellow whatever can be amended as an assumption): detailed inputs for revenues (tariffs, gross capacity assumptions, and other capacity utilization parameters such as derating factors and other losses ), costs (operations & maintenance, insurance, land leasing, staff costs, spare parts, utilities, regulatory fees etc...), working capital (receivables, payables, cash and overdraft), fixed assets and capex (including a construction schedule, Value Added Tax & Depreciation applicability), financing (long term debt and related Value Added Tax facility), equity financing as well as valuation assumptions (such as discount rates used).
Summary: Since the model does not use any macros, the summary page (one page ready to be printed) is instantly updated with the main output metrics of the model such as Internal Rate of Return, Shareholder Value, Cash on Cash Multiple, Sources and Uses, General Assumptions regarding the power generation, Debt Service Coverage Ratio, Loan Life Coverage Ratio, and other financing assumptions.
Calculations: this were all calculations are performed. The energy production is calculated and by applying the relevant tariffs adjusted for inflation and deducting the operating costs adjusted for inflation the operating profit is resulting. Based on the assets financed and the gearing of the financing the interest and depreciation are occurring. By using the working capital assumptions the impact of the business cycle is presented. Finally the sources and uses of funds are presented and the relevant debt financing is calculated (construction loan, Value Added Tax Facility and overdraft)
Outputs: everything is aggregated here into the relevant statements: profit and loss, balance sheet and cash flow.
Graphs: Various graphs present the investment & operating costs as well as the energy generation potential. Then multiple charts present the performance of the project from revenues to bottom line along with debt, assets, working capital and cash flows which results in a valuation on a project basis as well as on an equity basis together with the internal rate of return of the project and payback period metrics.
Investment Metrics: in order to see the detailed calculations for the above metrics (Internal Rate of Return, Debt Service Coverage Ratio, Loan Life Coverage Ratio, Valuations, Payback Period) you should consult this page for more information.
Checks: A dedicated worksheet that makes sure that everything is working as it should!
Important Notice: Yellow indicates inputs and assumptions that the user is able to change, blue cells are used for called up cells, and white cells with black characters indicates calculation cells. The model does not use a macro (all calculations are performed on the spot).