Financial Impact Assessment Tool for Water Service Providers: COVID Response

WELCOME COVID Financial Impact Assessment Tool for WSPs Virtual Training May 20, 2019 www.wsp.org | www.worldbank.org/water | www.blogs.worldbank.org/water | @WorldBankWater

Agenda Context Objective Principles applied Process for using the tool Structure of the tool Steps for completing the assessment Visual snapshots Next steps 1

Context: COVID has posed additional pressure on water service providers to ensure continuity of services Increased costs (e.g. overtime, safe work environment, higher chemical use) Lower revenues (e.g. from inability for households to pay, suspension of disconnection) Demand increase in under-served communities (e.g. use of tankers) Debt service pressures (IFI financing, PPPs) Cashflow problems 2

Objective: Demonstrate the financial gap associated with COVID in justifying claim for resources to serve customers Simple, for wider use, only need access to Microsoft Excel Regular Financial Model and User Guide in English Short-term (12 months) to assess impact of COVID In French, Portuguese (ongoing) In Spanish (done) Not the only tool, and is protected Lite Financial Model and User Guide in English Color Codes For Numbers and Cells: Light Blue Cells = Input Areas Black Nos. = Formula Cells. Do not input Yellow Cells = Formula Cells of Focus. Do not Input 3

Principles applied to the tool Revenues are sourced from water tariffs, sewerage, and other fees Working capital requirements and debt service obligations are fulfilled from internally generated funds before new investments or loan obligations are considered. Accrual accounting, and includes the income statement, balance sheet, and cash flow statement. 4

Process of using the Tool Step 1 Step 2 Develop a baseline projection assuming normal operational and financial circumstances without the COVID contagion. Step 3 Develop a financial impact assessment assuming the likelihood of COVID affecting certain operations and finances. Use the results to develop an emergency response plan designed to minimize the cash flow gap. 5

Structure of the Regular Version Five Worksheets each for (i) Baseline and (ii) COVID Financial Impact 1. Dashboard: opening data, key financial indicators 2. Assumptions: opening balance sheet position and financial management parameters 3. Connections and operating expenses: key operating indicators for revenue and expense projection 4. Project and Financing: new investment program, financing for new loan, amortization of outstanding loans, and depreciation of existing and new assets 5. Output Statements: three financial statements, investments, and borrowing 1. Baseline Projection 1-4 are input sheets 2. COVID Financial Impact Summary Sheet: Results of COVID financial impact assessment 6

Structure of the Lite Version Revenue and Cost Build Up Worksheet 1 Production & Connection Program for Households and Commercial Customers Non-revenue water Tariff increase New connections Production capacity 2 Sewerage Treatment, Connection Program & Other Revenue Sewerage tariff New connections 3 Water Supply Service Operating Costs Water extraction costs, production, chemical, maintenance, repair, salaries, and pension costs 4 Wastewater Service Operating Costs Salaries, electricity, maintenance, repair costs COVID Impact Assessment Worksheet 1 Revenue Section of the Income Statement Starting month is actual average Billed and collected revenue Local government transfers 2 Cost Section of the Income Statement Starting month is actual average Water and wastewater operating costs Depreciation and interest 3 Cash Flow Statement Profit after taxes Operating cash flow, debt payments COVID related capex 4 COVID CAPEX Response Program Additional capex requirements 7

Snapshot of the Results: Regular Version Sample WSP COVID Cash Flow Impact From Baseline Millions Peso Projection Month Baseline Ending Cash Flow Covid Financial Impact Ending Cash Flow Difference Feb Mar Apr May Jun Jul Aug Sept 0.053 (0.806) (0.859) Oct Nov Dec Jan Cumulative 0.228 (7.695) (7.923) (0.348) (0.358) (0.010) 0.016 (0.438) (0.454) 0.047 (0.584) (0.630) 0.066 (0.796) (0.861) 0.085 (0.795) (0.880) 0.052 (0.812) (0.864) 0.053 (0.812) (0.865) 0.052 (0.828) (0.880) 0.051 (0.482) (0.534) 0.051 (0.490) (0.541) 0.051 (0.495) (0.545) Baseline Pre Covid Post Covid Financial Baseline Pre Covid Post Covid Financial INCOME STATEMENT CASH FLOW STATEMENT Impact Impact Total Total Total Total Total Total REVENUES Water Billed Revenue Sewerage Revenue Other Revenue OPERATING REVENUES OTHER INCOME Interest on Time Deposits Installation Charges Non Operating Income TOTAL INCOME WATER OPERATING COSTS Electricity Water Extraction Costs Commercial Operations Repairs & Maintenace Salaries & Wages Allowances and Overtime Expense Total WASTEWATER OPERATING COSTS Salaries & Wages, Allowances and Overtime Wastewater Electricity Costs Maintenance & Repairs Domestic Average Factor Total Wastewater Costs Total Operating Costs NET OPERATING PROFIT Administration Pension Expense Depreciation Interest Total INCOME BEFORE TAXES Income Taxes NET PROFIT/(LOSS) INTERNAL CASH GENERATION Net Income Add:Interest Expense Add: Depreciation Exp Operating Cash Flow Add: Beginning Cash Position Working Cap Inc/(Dec) CASH BEFORE DEBT SERVICE 10.910 9.934 1.673 20.844 0.000 0.046 0.300 0.346 21.190 0.000 0.337 0.250 0.357 4.213 2.469 1.110 8.735 0.000 1.627 0.362 1.020 1.324 4.333 13.068 8.121 4.172 0.000 1.205 0.196 5.572 2.549 0.765 1.784 11.169 10.498 1.673 21.667 0.000 0.008 0.228 0.236 21.903 0.000 0.345 0.256 0.365 4.213 2.469 1.110 8.757 0.000 1.627 0.383 1.020 1.324 4.354 13.111 8.792 4.172 0.000 1.205 0.196 5.572 3.219 0.966 2.254 1.784 0.196 1.205 3.185 5.092 3.888 4.389 2.254 0.196 1.205 3.654 5.107 12.282 3.522 0.469 0.000 0.000 0.469 0.015 8.394 7.910 0.259 0.564 0.000 0.823 0.000 0.038 0.072 0.110 0.713 0.000 0.008 0.006 0.008 0.000 0.000 0.000 0.022 0.000 0.000 0.021 0.000 0.000 0.021 0.043 0.670 0.000 0.000 0.000 0.000 0.000 0.670 0.201 0.469 0.000 0.196 0.144 0.340 4.049 0.000 0.196 0.144 0.340 3.861 0.000 0.000 0.000 0.000 7.910 DEBT SERVICE Net Interest Payments Principal Payments Total Debt Service CASH AFTER DEBT SERVICE 0.000 0.300 3.920 0.172 0.000 0.300 3.933 8.095 0.000 0.000 0.013 7.923 CAPITAL INVESTMENT REQUIREMENTS Annual Capital Investment Add: Cash Ending Balance FINANCING REQUIREMENTS 0.000 0.600 0.300 0.000 0.900 0.000 0.600 0.300 0.000 0.900 0.000 0.000 0.000 0.000 0.000 FUNDS FROM LOANS & GRANTS Grants and Other Funds Loan 1 Outstanding Loans Funds From Loans & Grants Funds Distributed to City Government CASH SURPLUS/(DEFECIT) 0.500 0.228 0.500 7.695 0.000 7.923 If Cash Surplus: Add to Savings Account 0.576 0.000 0.576 0.000 0.348 0.000 1.000 0.000 0.652 If Cash Deficit: Use Savings Account 8

Snapshot of the Results: Lite version Pre Covid Post Covid Financial Total Total CASH FLOW STATEMENT Total Jun 1.774 Jul 0.808 Aug 0.519 Sept 0.593 Oct 0.337 Nov 0.336 Dec 0.583 Jan 0.122 Feb 0.159 Mar 0.355 Apr 0.355 May 0.355 Jun 0.355 PROFIT AFTER TAXES (assumes cash collections) Add: Depreciation Expense Operating Cash Flow Principal Repayments Cash Flow After Debt Service COVID-19 Related CAPEX Costs Programmed Funding For Capex Month End Cash Flow 21.288 2.035 23.323 0.144 0.144 0.000 0.012 0.012 0.012 0.012 0.012 0.012 0.012 0.012 0.012 0.012 0.012 0.012 0.012 21.432 1.891 23.323 1.786 0.796 0.507 0.581 0.325 0.324 0.571 0.110 0.147 0.367 0.367 0.367 0.367 1.200 1.200 0.000 0.100 0.100 0.100 0.100 0.100 0.100 0.100 0.100 0.100 0.100 0.100 0.100 0.100 20.232 3.091 23.323 1.686 0.896 0.607 0.681 0.425 0.424 0.671 0.210 0.247 0.267 0.267 0.267 0.267 0.000 0.550 0.550 0.000 0.050 0.200 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.300 0.000 0.000 0.000 0.000 0.000 0.000 20.232 3.641 23.873 1.686 0.946 0.807 0.681 0.425 0.424 0.671 0.210 0.247 0.267 0.033 0.267 0.267 COVID Income and Cash Flow Financial Impact (Increase = Blue Decrease = Red) 9

The Way Forward The Way Forward 1. 2. 3. Populate the model Provide feedback on the model: glitch? clarification? comment? Share results: Total Revenues (% increase/decrease) Total Expenses (% increase/decrease) Estimated Financing Requirement (in USD) Send to: Email: Aldo Baietti - baietti.aldo@gmail.com cc: Aileen Castro acastro2@worldbank.org

Thank you www.wsp.org | www.worldbank.org/water | www.blogs.worldbank.org/water | @WorldBankWater