Calculations Worksheet

This Calculations worksheet contains the core functionality of the Abacus financial model. The calculations are grouped into collapsible sections according to their function, and the dependencies (generally) flow down the worksheet. Each section is designed to be modifiable by a user, without negatively affecting the rest of the model. For example, in the base model the only revenue is the sale of energy at a defined rate. A user could easily add additional revenue sources, as long as they were included in the Total Revenues line. Similar functionality exists for the other sections.

  1. Flags and Timing – These important flags (operations, construction, principal payment date) and timing factors (number of days in the period, year, quarter number, etc.) are referenced, directly or indirectly, by every calculation in the model. All calculations such as when payments are made, when to calculate interest and fees, and when the various phases begin and end (construction period, operations period, grace period) are calculated in this section, and not in their individual calculation in the sections below. Using flags and timing sections, as well as indexation factors (below), are financial modeling best practices and will significantly simplify formulas and reduce spreadsheet complexity.
  2. Indexation Factors – This section includes all indexation such as escalation rates under the offtake or PPA contract (calculated as a percentage of the previous year’s amount), or escalation of operating expenses over time. Importantly, annual panel degradation (specific to solar PV projects) is a percentage of the original output, not of the prior year’s figure, as this is consistent with most technical guidance for solar projects. Be sure to confirm degradation factors and their calculation with technical advisors as this may differ by technology or project. Additional indexation factors, such as additional operating costs or currency exchange rates can be added in this section.
  3. Uses of Funds – This section includes all Uses of funds for the development, construction, and initial operations of the project. The Total Uses of Funds is then used to allocate the sources of capital available to the project in the Sources section below. Uses are divided into two main categories: Capital Expenditures (development costs, advisors, construction, etc.) and Financing Costs (financing fees, interest during construction, reserve accounts, initial working capital, etc.). It is assumed that depreciation (book and tax) assumptions will be different for these two major categories, though it is possible that there may need to be more than two categories for the purposes of depreciation.
  4. Sources of Funds – This section reflects the sources of funds, by month, to fund the Total Uses of Funds from the section below. The actual calculations are performed in the subsequent sections, according to an assumption that equity will be funded first, then shareholder (or subordinated loans), and then senior debt. These calculations can be updated to reflect pari passu (equity and debt funded pro rata) by making adjustments in the Equity, Shareholder Loan, and Senior Debt sections below. Two error checks are included to confirm 1) that the total project leverage does not exceed the maximum included in the Financing section of the Inputs, and 2) to confirm the total Sources and Uses of funds are equal.
  5. Equity – This section, assuming equity funds will be drawn down first, evaluates the total Uses of funds, and if there is additional equity available, it will draw down the equity funds needed. Any shortfall is reflected in the Additional Funding Required from Shareholder Loan. If you have chosen 0% of equity to be contributed as a shareholder loan under the Equity subsection of the Financial section of the Inputs worksheet, then this amount would flow directly to the Senior Debt section below.
  6. Shareholder Loan – This section calculates both the amount of the shareholder loan to be drawn down to fund the total Uses of funds and calculates the principal and interest payments to be made on the loan. Using a shareholder way is a common way to increase the IRR of a project by 1) providing a tax deduction from the interest payments due on the shareholder loan and 2) by getting cash out of the project sooner than would otherwise be possible with dividend restrictions. However, shareholder loans are often governed by thin capitalization rules to prevent the abuse of these deductions, so be sure to understand these rules as they relate to loans from affiliates.  For the base model, it is assumed that shareholder loans cannot be paid until Senior Debt payments are made, as they are typically subordinate to the Senior Debt. In addition, it is assumed there will be no fixed payment schedule, and that 100% of cash available to pay the shareholder loan will be used to pay back the loan until it is paid off in full. These assumptions can be easily reworked in this Section. An error check is included to confirm the shareholder loan is paid off in full.
  7. Senior Debt – This section calculates how much Senior Debt is drawn down to fund the Uses of funds, and calculates the principal and interest payments to be made. Debt payments are, by default, to be sculpted based on the Cash Flow Available for Debt Service (CFADS) divided by a target DSCR, which is determined by the macro-pasted calculations from the Financing section of the Inputs worksheet. This section also calculates all debt-related fees such as commitment fees, interest during construction, and the initial funding of the Debt Service Reserve Account (DSRA). The Macros (see the separate section regarding Macros for more information) iteratively calculate the debt, interest, and fees to arrive at an optimized debt payment schedule, accounting for the circular logic involved in calculating fees, taxes, and interest payments which include themselves as a basis for calculation. Annual banking fees are included here, not under operating expenses, because they are not used in the net working capital calculations (below), and are deducted separately on the Income Statement (also below).
  8. Reserve Accounts – This section calculates the typical reserve accounts to include the Major Maintenance Reserve Account, which in this model is only a placeholder, and the Debt Service Reserve Account. The Monthly DSRA Requirement is based on the assumed number of months to look forward, and as a result, the Monthly DSRA Requirement can be highly volatile (for projects with grace periods or low cash flows due to a project ramp-up) and cause a demand for cash to fill the reserve in excess of the cash available. To solve this problem, the First Year Override function gives the user the ability to set a higher Year One DSRA requirement so that a higher amount is funded up-front. Abacus will use the greater of the Monthly DSRA Requirement or the First Year Override. This section also calculates interest earned on Reserve Accounts, based on the total balance of reserve accounts, assuming the same interest rate for both accounts. This is set to zero by default. An error check is included to confirm the initial DSRA funding matches the Uses of funds above.
  9. Revenues – Total project revenues, derived from monthly electricity generation multiplied by a contractual PPA rate. Monthly generation is derived from the monthly schedule on the Inputs worksheet, and can be adjusted to reflect the various scenarios (e.g., P50, P99, etc.).
  10. Operations – All operating expenses, as provided in the Operations section of the Inputs worksheet, multiplied by their indexation factor to account for inflation or contractual escalation rates. Episodic costs are also provided for anticipated one-off expenses that will be anticipated in the future.
  11. Working Capital – Accounts Receivable is calculated as the balance of Revenues that are outstanding, based on the Accounts Receivable Period (number of months) as provided in the Operations section of the Inputs worksheet. The number of months is defined as the number of months after the generation of revenue, not from the date of the invoice. Therefore, a minimum value of “2” is recommended. Accounts Payable is calculated as the balance of Operating Expenses that are outstanding, based on the Accounts Payable period, similarly calculated as the number of months after costs were incurred, not the date of invoice. Similarly, a minimum value of “2” is recommended. The Net Working Capital is calculated as Accounts Receivable less Accounts Payable. The monthly change in Net Working Capital is considered for the purposes of calculating Cash Flows from Operations in the Cash Flow Statement (below), to account for the delays in payment.
  12. Depreciation – This section calculates the depreciable basis for the two cost categories: Capital Expenditures and Financing Costs, as defined in the Uses of Funds (above). It is assumed that all CapEx are depreciable, but be aware that land acquisition costs cannot typically be depreciated.  The Financing Cost depreciable basis is reduced by the initial reserve account balances which cannot be depreciated. The depreciation schedule is then calculated based on the book (accounting) schedule, and by the tax schedule, which is often different. The difference between the two is carried as a Deferred Tax Asset in the Taxes section (below). Abacus, by default, assumes straight-line depreciation for both schedules, but this section can easily be modified to accommodate other depreciation schedules such as double-declining balance, MACRS, etc.
  13. Taxes – Calculation of Corporate Income Tax, with adjustments to Earnings Before Tax to remove book depreciation, and add tax depreciation. Due to the accelerated depreciation or early tax losses generated by many project finance transactions, Abacus also includes the ability to have a tax loss carryforward, which is carried as a Deferred Tax Asset on the Balance Sheet (below). The exact calculation of corporate income tax and the rules regarding tax loss carryforwards vary by jurisdiction and should be discussed with appropriate tax counsel.
  14. Income Statement – This income statement (or statement of profit and loss), includes key metrics to include EBITDA (not part of official accounting rules), EBIT, EBT, and net income. Net income is then used on the Cash Flow Statement as a basis for calculating Cash Flow from Operations, as well as Retained Earnings which is held on the balance sheet. Further, EBITDA is used to calculate Cash Flows Available for Debt Service (CFADS) under the Cash Waterfall (below). Given the three accounting statements are linked, any changes to one may affect the other two, and changing these calculations can be a key source of errors. This income statement assumes IFRS rules but can be adjusted to reflect US GAAP, or other, accounting rules.
  15. Balance Sheet – Presents the financial position (assets, liabilities, and equity position) of the project at the end of each monthly period. This section references many calculations from the sections above, to include cash balances, depreciation, working capital calculations, debt, and equity capital. An error check is included to ensure the balance sheet balances each period.
  16. Cash Flow Statement – Displays how changes in the income statement and balance sheet affect the cash position of the project, broken down by operating, investing, and financing activities. Two error checks are included in this section: 1) to confirm the ending cash balance for each period is greater than or equal to zero, and 2) to confirm the cash amount meets the required minimum cash balance as defined in the Operations section of the Inputs worksheet.
  17. Cash Waterfall – Calculates the inflows and outflows of cash, based on their hierarchy and seniority. Cash inflows are calculated based on EBITDA from the income statement, with adjustments for changes in net working capital, taxes, and interest on reserve accounts (typically included in cash flows from investing and therefore not as revenue). Senior debt is the most senior outflow of cash, followed by the filling of reserve accounts (often required by senior lenders), followed by the use of cash to pay shareholder loans, and finally for dividend distributions. An error check is included to confirm the closing balance is greater than or equal to zero.
  18. Retained Earnings – Calculates the accumulated net income of the project that have not bee distributed to shareholders as dividends. Retained Earnings are displayed on the balance sheet, and combined with the original shareholder capital, represent the net equity position.
  19. Dividends – Dividends are typically restricted payments made to the shareholders under defined circumstances. Under the Abacus model, four typical tests are included before a distribution can be made: 1) If the quarterly DSCR exceeds the covenant (this can often be a four-quarter rolling DSCR), 2) if the shareholder loan has been fully repaid, 3) if retained earnings are positive (some corporations or jurisdictions may not permit dividends to be paid if a company has a negative retained earnings, therefore this test is made as a Yes/No toggle under the Equity subsection of the Financing section of the Inputs worksheet), and 4) if the period follows a debt principal repayment date. Uses will need to configure their own tests to meet their anticipated project requirements.  The maximum dividend distribution is calculated as the period starting cash, plus the Cash Flow Available for Dividends (from the Cash Waterfall), less the minimum cash balance. A second calculation is included to reflect the maximum distribution available if a positive retained earnings is required, with the ultimate Dividend Paid chosen between these two values.
  20. Internal Rate of Return (IRR) – Presentation of three IRR Calculations: 1) Equity IRR (to include shareholder loans as part of the equity return), 2) Pre-Tax Project IRR which is useful for investors who may have different tax rates and structures, and 3) After-Tax Project IRR.
  21. Debt Covenants – Presentation of key debt covenants: 1) Debt Service Coverage Ratio (DSCR) – calculated as Cash Flow Available for Debt Service (from the Cash Waterfall) divided by Senior Debt Service, Abacus automatically accounts for the payment frequency and reports the DSCR at the same frequency as senior debt payments (quarterly/semiannually). Lenders can often require a Historical (rolling average) DSCR or a Prospective DSCR which can be easily calculated from what has been already provided. Logic to identify the minimum DSCR (and its date) and the average DSCR are also provided. 2) Loan Life Coverage Ratio (LLCR) which is the NPV of CFADS divided by the outstanding debt. This calculation is also used by the Macros as a proxy for the target DSCR that would fully maximize the tenor of the loan, and 3) Senior Debt Weighted Average Life (WAL), which is the average amount of time until a dollar of principal is repaid. The WAL can be used for loan pricing or as a point of comparison against other debt amortization schedules.