WSP Financial Projections Period 2021 Name of WSP Sample WSP Name of Contact Person Finance Contact Tel and Email Address Opening month 6 Enter month number Currency IDR Please enter data in blue cells in Millions Actual Projection Financial INCOME STATEMENT Average Pre Covid Post Covid Impact Remarks Enter Average Revenue Over Last 12 Months Jun Jul Aug Sept Oct Nov Dec Jan Feb Mar Apr May Jun Total Total Total REVENUES Billed Revenue from Water Sales (million) 2.000 1.277 1.108 1.190 1.201 1.209 1.217 1.216 1.206 1.817 1.817 1.817 1.817 24.000 16.892 7.108 Billed Revenue From Sewerage Sales (million) 0.730 1.205 1.022 1.088 1.096 1.097 1.097 1.098 1.112 1.781 1.781 1.781 1.781 8.760 15.939 7.179 Revenue collection efficiency (%) 90% 40% 35% 30% 30% 30% 30% 50% 50% 50% 50% 50% 50% Actual revenue collected (million) 2.500 0.993 0.745 0.683 0.689 0.692 0.694 1.157 1.159 1.799 1.799 1.799 1.799 30.000 14.009 15.991 Connection Revenue 0.060 0.084 0.084 0.036 0.036 0.036 0.036 0.000 0.000 0.000 0.000 0.000 0.000 0.372 0.372 Other revenue (please specify, if applicable ) 0.200 0.139 0.139 0.139 0.139 0.139 0.139 0.139 0.139 0.139 0.139 0.139 0.139 2.400 1.668 0.732 TOTAL REVENUES 2.700 1.192 0.968 0.906 0.864 0.867 0.869 1.332 1.298 1.938 1.938 1.938 1.938 32.400 16.049 16.351 Local Government Transfers (Plus/Minus) 0.200 0.200 0.200 0.200 0.500 0.500 0.200 0.200 0.200 0.200 0.200 0.200 0.200 2.400 2.600 0.200 Total Revenue (Plus/Minus) Transfers 2.900 0.992 1.168 1.106 1.364 1.367 1.069 1.532 1.498 2.138 2.138 2.138 2.138 34.800 18.649 16.151 Financial INCOME STATEMENT Pre Covid Post Covid Impact Remarks OPERATING COSTS Jun Jul Aug Sept Oct Nov Dec Jan Feb Mar Apr May Jun Total Total Total Water Operating Costs Water Operating Salaries 0.300 0.297 0.349 0.349 0.349 0.349 0.297 0.297 0.297 0.297 0.297 0.297 0.297 3.600 3.774 0.174 Water Extraction Cost 0.030 0.089 0.076 0.081 0.082 0.082 0.083 0.083 0.085 0.136 0.136 0.136 0.136 0.360 1.203 0.843 Water Production Electricity Cost 0.020 0.174 0.037 0.039 0.040 0.040 0.040 0.040 0.041 0.066 0.066 0.066 0.066 0.240 0.714 0.474 Chemical Treatment 0.020 0.046 0.039 0.042 0.042 0.043 0.043 0.043 0.044 0.070 0.070 0.070 0.070 0.240 0.623 0.383 Maintenance & Repairs 0.035 0.350 0.350 0.350 0.350 0.350 0.350 0.350 0.350 0.350 0.350 0.350 0.350 0.420 4.200 3.780 Pension Expense 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 Adminstration 0.135 0.153 0.153 0.153 0.153 0.153 0.153 0.153 0.153 0.153 0.153 0.153 0.153 1.620 1.836 0.216 Other Water Operating Costs 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 Water Supply Operating Costs 0.540 1.110 1.004 1.014 1.015 1.016 0.966 0.967 0.970 1.072 1.072 1.072 1.072 6.480 12.351 5.871 Waster Water Operating Costs Wastewater Employee Salaries 0.135 0.135 0.135 0.135 0.135 0.135 0.135 0.135 0.135 0.135 0.135 0.135 0.135 1.620 1.622 0.002 Electricity Costs 0.020 0.044 0.037 0.040 0.040 0.040 0.040 0.040 0.040 0.065 0.065 0.065 0.065 0.240 0.580 0.340 Maintenance & Repairs 0.065 0.085 0.085 0.085 0.085 0.085 0.085 0.085 0.085 0.085 0.085 0.085 0.085 0.780 1.020 0.240 Administrative Expenses 0.184 0.194 0.194 0.194 0.194 0.194 0.194 0.194 0.194 0.194 0.194 0.194 0.194 2.208 2.328 0.120 Other Wastewater Costs 0.110 0.110 0.110 0.110 0.110 0.110 0.110 0.110 0.110 0.110 0.110 0.110 0.110 1.320 1.320 0.000 Total Wastewater Costs 0.514 0.568 0.561 0.564 0.564 0.564 0.564 0.564 0.565 0.589 0.589 0.589 0.589 6.168 Other Direct Costs 0.010 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.120 1.200 1.080 Total Operating Costs 1.06 1.778 1.665 1.678 1.679 1.681 1.630 1.631 1.634 1.761 1.761 1.761 1.761 12.768 20.420 7.652 Interest Expense (if applicable) 0.010 0.010 0.010 0.010 0.010 0.010 0.010 0.010 0.010 0.010 0.010 0.010 0.010 0.120 0.120 0.000 Depreciation Expense 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 0.144 0.144 0.000 TOTAL COSTS 1.09 1.800 1.687 1.700 1.701 1.703 1.652 1.653 1.656 1.783 1.783 1.783 1.783 12.888 20.540 7.652 PROFIT BEFORE TAXES 1.814 0.808 0.519 0.593 0.337 0.336 0.583 0.122 0.159 0.355 0.355 0.355 0.355 21.912 1.891 23.803 Income Taxes (if applicable) 0.040 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.480 0.000 0.480 PROFIT AFTER TAXES (assumes cash collections) 1.774 0.808 0.519 0.593 0.337 0.336 0.583 0.122 0.159 0.355 0.355 0.355 0.355 21.288 2.035 23.323 Financial CASH FLOW STATEMENT Pre Covid Post Covid Impact Remarks Jun Jul Aug Sept Oct Nov Dec Jan Feb Mar Apr May Jun Total Total Total PROFIT AFTER TAXES (assumes cash collections) 1.774 0.808 0.519 0.593 0.337 0.336 0.583 0.122 0.159 0.355 0.355 0.355 0.355 21.288 2.035 23.323 Add: Depreciation Expense 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 0.144 0.144 0.000 Operating Cash Flow 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 21.432 1.891 23.323 Principal Repayments 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 1.200 1.200 0.000 Cash Flow After Debt Service 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 20.232 3.091 23.323 COVID-19 Related CAPEX Costs 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.550 0.550 Programmed Funding For Capex 0.000 0.000 0.000 0.000 Month End Cash Flow 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 20.232 3.641 23.873 Itemized COVID-19 CAPEX Please provide planned capex that can be completed within 3 months, e.g. tanker services, hand washing stations, dealing with backlog of connections, providing free Financial connections (where feasible), installing public standpoints etc). If no interventions planned , please leave blank Remarks Pre Covid Post Covid Impact Jun Jul Aug Sept Oct Nov Dec Jan Feb Mar Apr May Jun Total Total Total existing work in process 0.300 0.000 0.300 0.300 Kiosks & Hand Washing Stations 0.200 0.000 0.200 0.200 Cleaning and Sanitation Equipment 0.050 0.000 0.050 0.050 CAPEX intervention 4 0.000 0.000 0.000 CAPEX intervention 5 0.000 0.000 0.000 Total capital costs 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.550 0.550 Samp Water Capacity, Production & Connection Program Month-End Opening Household Indicators Month Ending House Connections Please enter data in blue cells Jun 29,782 Jul Aug Unaccounted For Water (NRW) 35.0% 35.0% Households Operations Average Tariff (Currency/m3) 0.75 Connection Charge (Currency/Connection) 120 Tariff Rate increases/(decreases) 0% 0% Domestic Month-End Factor: 1.00 1.00 Domestic Average Factor 1.00 1.00 Effective Tariff 0.75 0.75 New Connections 300 500 Cumulative Water Connections (Month End) 30,082 30,582 Cumulative Water Connections (Ave.) 29,932 30,332 Percent Growth in Connections 1.0% 0.8% Average Usage/Connection (m3/day) 1.100 1.100 Month-End Opening Commercial Indicators Month Ending Commercial Connections Jun 19,850 Jul Aug Commercial Operations Average Tariff (Currency/m3) 0.60 Connection Charge (Currency/Connection) 1,000 Tariff Rate increases/(decreases) 0% 0% Domestic Month-End Factor: 1.00 1.00 Domestic Average Factor 1.00 1.00 Effective Tariff 0.60 0.60 New Connections Cumulative Water Connections (Month End) 19,850 19,850 Cumulative Water Connections (Ave.) 19,850 19,850 Percent Growth in Connections 0.0% 0.0% Average Usage/Connection (m3/day) 1.500 1.000 Water Monthly Production Capacity (m3) 4,500,000 4,500,000 Total Potential Monthly Demand 1,881,006 1,596,456 Daily Demand (m3) 62,700 53,215 Daily Revenue 42,559 36,934 Total Production Requirement (m3) 2,893,855 2,456,086 Total Actual Production (m3) 2,893,855 2,456,086 Total Water Consumed (m3) 1,881,006 1,596,456 Water (Shortage)/Surplus (m3) 1,606,145 2,043,914 Total Water Connections 30,082 30,582 New Water Connection Revenue (mllion) 0.0360 0.0600 Total Water Revenue (million) 1.277 1.108 Opening Sewerage Connections Jun 29,000 0 Jul Aug Sewerage tariff/m3 1.10 Connection Charge (currency/connection) 120 Tariff Rate increases 0% 0% Domestic Year-End Factor: 1.00 1.00 Domestic Average Factor 1.00 1.00 Effective Tariff 1.10 1.10 Opening Sewerage Connections 29,000 29,200 New connections 200 200 Monthly Wastewater Capacity (m3) 5,000,000 5,000,000 Sewerage Processed (m3) 1,095,761 928,949 Total Sewerage Connections 29,200 29,400 Sewerage Billed (million) 1.2053 1.0218 Sewerage Connection Revenue (million) 0.024 0.024 Other Revenue (million) 0.139 0.139 Water Supply Operating & Admin Costs Jul Aug Water Extraction Cost NRW @ Extraction 3% 3% Monthly Water Extraction 2,980,671 2,529,769 Cost/M3 in Currency 0.0300 0.0300 Water Extraction Costs (million) 0.089 0.076 Water Production Electricity Cost Monthly Water Production 2,893,855 2,456,086 Water Pumping Cost/m3 0.060 0.015 Electricity Costs (million) 0.174 0.037 Chemical Treatment Chemical Costs (currency/m3) 0.016 0.016 Chemical Cost (million) 0.0463 0.0393 Maintenance & Repairs Maintenance (million) 0.350 0.350 Water Operating Salaries No. of Employees 55 55 Cost/Employee (currency/month) 3,729.00 3,729.00 Monthly Direct Salaries Costs 0.205 0.205 Allowances and Bonus as % of Direct 10% 35% Allowances and Bonuses (million) 0.021 0.072 Overtime as % of direct costs 35% 35% Overtime costs (million) 0.072 0.072 Pension Expense - - Administration (million) 0.153 0.153 Other Water Operating Costs - - Wastewater Service Operating & Admin Costs Jul Aug Wastewater Employee Salaries No. of Employees 25 25 Cost/Employee (currency/month) 3,729.00 3,729.00 Direct Salaries Costs 0.093 0.093 Allowances and Bonus as % of Direct 10% 10% Allowances and Bonuses (million) 0.009 0.009 Overtime as % of direct costs 35% 35% Overtime costs (million) 0.033 0.033 Energy Cost (unit cost/m3) 0.040 0.040 Electricity Costs 0.044 0.037 Maintenance & Repairs 0.085 0.085 Administrative Expenses 0.194 0.194 Other Wastewater Costs 0.110 0.110 Monthly Cost 0.526 0.519 Wastewater Costs/ (m3) 0.48 0.56 Other Direct Costs 0.100 0.100 Color Codes For Numbers and Cells: Light Blue Cells and Numbers = Input Areas Black Nos. = Formula Cells. Do not input Sample WSP Weighted NRW Average Tariff 0.75 35.0% Sept Oct Nov Dec Jan Feb Mar 35.0% 35.0% 35.0% 35.0% 35.0% 35.0% 35.0% 0% 0% 0% 0% 0% 0% 0% 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 0.75 0.75 0.75 0.75 0.75 0.75 0.75 500 300 300 300 300 - - 31,082 31,382 31,682 31,982 32,282 32,282 32,282 30,832 31,232 31,532 31,832 32,132 32,282 32,282 0.8% 0.5% 0.5% 0.5% 0.5% 0.0% 0.0% 1.200 1.200 1.200 1.200 1.200 1.100 1.100 Weighted Average Tariff 0.60 Sept Oct Nov Dec Jan Feb Mar 0% 0% 0% 0% -5% 0% 0% 1.00 1.00 1.00 1.00 0.95 0.95 0.95 1.00 1.00 1.00 1.00 0.98 0.95 0.95 0.60 0.60 0.60 0.60 0.59 0.57 0.57 - - - - 19,850 19,850 19,850 19,850 19,850 19,850 19,850 19,850 19,850 19,850 19,850 19,850 19,850 19,850 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 1.000 1.000 1.000 1.000 1.000 1.200 3.000 4,500,000 4,500,000 4,500,000 4,500,000 4,500,000 4,500,000 4,500,000 1,705,452 1,719,852 1,730,652 1,741,452 1,752,252 1,779,906 2,851,806 56,848 57,328 57,688 58,048 58,408 59,330 95,060 39,659 40,019 40,289 40,559 40,531 40,210 60,576 2,623,772 2,645,926 2,662,542 2,679,157 2,695,772 2,738,317 4,387,394 2,623,772 2,645,926 2,662,542 2,679,157 2,695,772 2,738,317 4,387,394 1,705,452 1,719,852 1,730,652 1,741,452 1,752,252 1,779,906 2,851,806 1,876,228 1,854,074 1,837,458 1,820,843 1,804,228 1,761,683 112,606 31,082 31,382 31,682 31,982 32,282 32,282 32,282 0.0600 0.0360 0.0360 0.0360 0.0360 - - 1.190 1.201 1.209 1.217 1.216 1.206 1.817 Opening Connections Sept Oct Nov Dec Jan Feb Mar 0% 0% 0% 0% 0% 0% 0% 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.10 1.10 1.10 1.10 1.10 1.10 1.10 29,400 29,600 29,600 29,600 29,600 29,600 29,600 200 - - - - - - 5,000,000 5,000,000 5,000,000 5,000,000 5,000,000 5,000,000 5,000,000 989,312 996,586 996,989 997,387 997,781 1,010,612 1,619,225 29,600 29,600 29,600 29,600 29,600 29,600 29,600 1.0882 1.0962 1.0967 1.0971 1.0976 1.1117 1.7811 0.024 - - - - - - 0.139 0.139 0.139 0.139 0.139 0.139 0.139 Sept Oct Nov Dec Jan Feb Mar 3% 3% 3% 3% 3% 3% 3% 2,702,485 2,725,304 2,742,418 2,759,532 2,776,645 2,820,466 4,519,016 0.0300 0.0300 0.0300 0.0300 0.0300 0.0300 0.0300 0.081 0.082 0.082 0.083 0.083 0.085 0.136 2,623,772 2,645,926 2,662,542 2,679,157 2,695,772 2,738,317 4,387,394 0.015 0.015 0.015 0.015 0.015 0.015 0.015 0.039 0.040 0.040 0.040 0.040 0.041 0.066 0.016 0.016 0.016 0.016 0.016 0.016 0.016 0.0420 0.0423 0.0426 0.0429 0.0431 0.0438 0.0702 0.350 0.350 0.350 0.350 0.350 0.350 0.350 55 55 55 55 55 55 55 3,729.00 3,729.00 3,729.00 3,729.00 3,729.00 3,729.00 3,729.00 0.205 0.205 0.205 0.205 0.205 0.205 0.205 35% 35% 35% 10% 10% 10% 10% 0.072 0.072 0.072 0.021 0.021 0.021 0.021 35% 35% 35% 35% 35% 35% 35% 0.072 0.072 0.072 0.072 0.072 0.072 0.072 - - - - - - - 0.153 0.153 0.153 0.153 0.153 0.153 0.153 - - - - - - - Sept Oct Nov Dec Jan Feb Mar 25 25 25 25 25 25 25 3,729.00 3,729.00 3,729.00 3,729.00 3,729.00 3,729.00 3,729.00 0.093 0.093 0.093 0.093 0.093 0.093 0.093 10% 10% 10% 10% 10% 10% 10% 0.009 0.009 0.009 0.009 0.009 0.009 0.009 35% 35% 35% 35% 35% 35% 35% 0.033 0.033 0.033 0.033 0.033 0.033 0.033 0.040 0.040 0.040 0.040 0.040 0.040 0.040 0.040 0.040 0.040 0.040 0.040 0.040 0.065 0.085 0.085 0.085 0.085 0.085 0.085 0.085 0.194 0.194 0.194 0.194 0.194 0.194 0.194 0.110 0.110 0.110 0.110 0.110 0.110 0.110 0.522 0.522 0.522 0.522 0.522 0.523 0.547 0.53 0.52 0.52 0.52 0.52 0.52 0.34 0.100 0.100 0.100 0.100 0.100 0.100 0.100 Apr May Jun 35.0% 35.0% 35.0% 0% 0% 0% 1.00 1.00 1.00 1.00 1.00 1.00 0.75 0.75 0.75 - - - 32,282 32,282 32,282 32,282 32,282 32,282 0.0% 0.0% 0.0% 1.100 1.100 1.100 Apr May Jun 0% 0% 0% 0.95 0.95 0.95 0.95 0.95 0.95 0.57 0.57 0.57 - - - 19,850 19,850 19,850 19,850 19,850 19,850 0.0% 0.0% 0.0% 3.000 3.000 3.000 4,500,000 4,500,000 4,500,000 2,851,806 2,851,806 2,851,806 95,060 95,060 95,060 60,576 60,576 60,576 4,387,394 4,387,394 4,387,394 4,387,394 4,387,394 4,387,394 2,851,806 2,851,806 2,851,806 112,606 112,606 112,606 32,282 32,282 32,282 - - - 1.817 1.817 1.817 Apr May Jun 0% 0% 0% 1.00 1.00 1.00 1.00 1.00 1.00 1.10 1.10 1.10 29,600 29,600 29,600 - - - 5,000,000 5,000,000 5,000,000 1,619,225 1,619,225 1,619,225 29,600 29,600 29,600 1.7811 1.7811 1.7811 - - - 0.139 0.139 0.139 Apr May Jun 3% 3% 3% 4,519,016 4,519,016 4,519,016 0.0300 0.0300 0.0300 0.136 0.136 0.136 4,387,394 4,387,394 4,387,394 0.015 0.015 0.015 0.066 0.066 0.066 0.016 0.016 0.016 0.0702 0.0702 0.0702 0.350 0.350 0.350 55 55 55 3,729.00 3,729.00 3,729.00 0.205 0.205 0.205 10% 10% 10% 0.021 0.021 0.021 35% 35% 35% 0.072 0.072 0.072 - - - 0.153 0.153 0.153 - - - Apr May Jun 25 25 25 3,729.00 3,729.00 3,729.00 0.093 0.093 0.093 10% 10% 10% 0.009 0.009 0.009 35% 35% 35% 0.033 0.033 0.033 0.040 0.040 0.040 0.065 0.065 0.065 0.085 0.085 0.085 0.194 0.194 0.194 0.110 0.110 0.110 0.547 0.547 0.547 0.34 0.34 0.34 0.100 0.100 0.100 1 Jan 2 Feb 3 Mar 4 Apr 5 May 6 Jun 7 Jul 8 Aug 9 Sept 10 Oct 11 Nov 12 Dec 13 Jan 14 Feb 15 Mar 16 Apr 17 May 18 Jun 19 Jul 20 Aug 21 Sept 22 Oct 23 Nov 24 Dec 25 Jan 26 Feb 27 Mar 28 Apr 29 May 30 Jun