Excel Sheet Download 30806 | Annex Item Download 2022-08-08 03-54-12

Partial file snippet.

Sheet 1: Covid Impact Assessment

	
	
	
	
	
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		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
		

		

		

		

		

		

	
	
		

		INCOME STATEMENT
		Actual Average
		Projection
		Pre Covid
		Post Covid
		Financial 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
		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		INCOME STATEMENT
		

		

		

		

		

		

		

		

		

		

		

		

		

		Pre Covid
		Post Covid
		Financial 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
		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		CASH FLOW STATEMENT
		

		

		

		

		

		

		

		

		

		

		

		

		

		Pre Covid
		Post Covid
		Financial 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 connections (where feasible), installing public standpoints etc).  If no interventions planned , please leave blank
		

		

		

		Pre Covid
		Post Covid
		Financial Impact
		Remarks
	
	
		

		

		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
		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		Color Codes For Numbers and Cells:
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		Light Blue Cells and Nos. = Input Areas
		

		

		

		

		

		COVID Income and Cash Flow Financial Impact
		

	
	
		

		Black Nos. = Formula Cells. Do not input
		

		

		

		

		

		(Increase = Blue   Decrease = Red) 
		

	
	
		
 
		
		
 
		
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	



Sheet 2: Revenue and Cost Build-Up

	
	
	
		Sample WSP
		
	
		Water Capacity, Production & Connection Program
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Opening  Household Indicators
		Month Ending
		Month-End House Connections
		Weighted Average Tariff
		NRW
		

		

		

		

		

		

		

		

	
	
		Please enter data in blue cells
		Jun
		 29,782 
		 0.75 
		35.0%
		

		

		

		

		

		

		

		

	
	
		

		Jul
		Aug
		Sept
		Oct
		Nov
		Dec
		Jan
		Feb
		Mar
		Apr
		May
		Jun
	
	
		Unaccounted For Water (NRW)
		35.0%
		35.0%
		35.0%
		35.0%
		35.0%
		35.0%
		35.0%
		35.0%
		35.0%
		35.0%
		35.0%
		35.0%
	
	
		Households Operations
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		 Average Tariff (Currency/m3) 
		 0.75 
		

		

		

		

		

		

		

		

		

		

		

	
	
		 Connection Charge (Currency/Connection) 
		 120 
		

		

		

		

		

		

		

		

		

		

		

	
	
		Tariff Rate increases/(decreases)
		0%
		0%
		0%
		0%
		0%
		0%
		0%
		0%
		0%
		0%
		0%
		0%
	
	
		 Domestic Month-End Factor: 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
	
	
		 Domestic Average Factor 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
	
	
		 Effective Tariff 
		 0.75 
		 0.75 
		 0.75 
		 0.75 
		 0.75 
		 0.75 
		 0.75 
		 0.75 
		 0.75 
		 0.75 
		 0.75 
		 0.75 
	
	
		New Connections
		 300 
		 500 
		 500 
		 300 
		 300 
		 300 
		 300 
		 -   
		 -   
		 -   
		 -   
		 -   
	
	
		 Cumulative Water Connections (Month End) 
		 30,082 
		 30,582 
		 31,082 
		 31,382 
		 31,682 
		 31,982 
		 32,282 
		 32,282 
		 32,282 
		 32,282 
		 32,282 
		 32,282 
	
	
		 Cumulative Water Connections (Ave.) 
		 29,932 
		 30,332 
		 30,832 
		 31,232 
		 31,532 
		 31,832 
		 32,132 
		 32,282 
		 32,282 
		 32,282 
		 32,282 
		 32,282 
	
	
		 Percent Growth in Connections 
		1.0%
		0.8%
		0.8%
		0.5%
		0.5%
		0.5%
		0.5%
		0.0%
		0.0%
		0.0%
		0.0%
		0.0%
	
	
		 Average Usage/Connection (m3/day) 
		 1.100 
		 1.100 
		 1.200 
		 1.200 
		 1.200 
		 1.200 
		 1.200 
		 1.100 
		 1.100 
		 1.100 
		 1.100 
		 1.100 
	
	
		Opening  Commercial Indicators
		Month Ending
		Month-End Commercial Connections
		Weighted Average Tariff
		

		

		

		

		

		

		

		

		

	
	
		

		Jun
		 19,850 
		 0.60 
		

		

		

		

		

		

		

		

		

	
	
		

		Jul
		Aug
		Sept
		Oct
		Nov
		Dec
		Jan
		Feb
		Mar
		Apr
		May
		Jun
	
	
		Commercial  Operations
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		 Average Tariff (Currency/m3) 
		 0.60 
		

		

		

		

		

		

		

		

		

		

		

	
	
		 Connection Charge (Currency/Connection) 
		 1,000 
		

		

		

		

		

		

		

		

		

		

		

	
	
		Tariff Rate increases/(decreases)
		0%
		0%
		0%
		0%
		0%
		0%
		-5%
		0%
		0%
		0%
		0%
		0%
	
	
		 Domestic Month-End Factor: 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 0.95 
		 0.95 
		 0.95 
		 0.95 
		 0.95 
		 0.95 
	
	
		 Domestic Average Factor 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 0.98 
		 0.95 
		 0.95 
		 0.95 
		 0.95 
		 0.95 
	
	
		 Effective Tariff 
		 0.60 
		 0.60 
		 0.60 
		 0.60 
		 0.60 
		 0.60 
		 0.59 
		 0.57 
		 0.57 
		 0.57 
		 0.57 
		 0.57 
	
	
		New Connections
		

		

		

		

		

		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
	
	
		 Cumulative Water Connections (Month End) 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
	
	
		 Cumulative Water Connections (Ave.) 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
		 19,850 
	
	
		 Percent Growth in Connections 
		0.0%
		0.0%
		0.0%
		0.0%
		0.0%
		0.0%
		0.0%
		0.0%
		0.0%
		0.0%
		0.0%
		0.0%
	
	
		 Average Usage/Connection (m3/day) 
		 1.500 
		 1.000 
		 1.000 
		 1.000 
		 1.000 
		 1.000 
		 1.000 
		 1.200 
		 3.000 
		 3.000 
		 3.000 
		 3.000 
	
	
		Water Monthly Production Capacity (m3)
		 4,500,000 
		 4,500,000 
		 4,500,000 
		 4,500,000 
		 4,500,000 
		 4,500,000 
		 4,500,000 
		 4,500,000 
		 4,500,000 
		 4,500,000 
		 4,500,000 
		 4,500,000 
	
	
		 Total Potential Monthly Demand 
		 1,881,006 
		 1,596,456 
		 1,705,452 
		 1,719,852 
		 1,730,652 
		 1,741,452 
		 1,752,252 
		 1,779,906 
		 2,851,806 
		 2,851,806 
		 2,851,806 
		 2,851,806 
	
	
		Daily Demand (m3)
		 62,700 
		 53,215 
		 56,848 
		 57,328 
		 57,688 
		 58,048 
		 58,408 
		 59,330 
		 95,060 
		 95,060 
		 95,060 
		 95,060 
	
	
		Daily Revenue
		 42,559 
		 36,934 
		 39,659 
		 40,019 
		 40,289 
		 40,559 
		 40,531 
		 40,210 
		 60,576 
		 60,576 
		 60,576 
		 60,576 
	
	
		 Total Production Requirement (m3) 
		 2,893,855 
		 2,456,086 
		 2,623,772 
		 2,645,926 
		 2,662,542 
		 2,679,157 
		 2,695,772 
		 2,738,317 
		 4,387,394 
		 4,387,394 
		 4,387,394 
		 4,387,394 
	
	
		 Total Actual Production (m3) 
		 2,893,855 
		 2,456,086 
		 2,623,772 
		 2,645,926 
		 2,662,542 
		 2,679,157 
		 2,695,772 
		 2,738,317 
		 4,387,394 
		 4,387,394 
		 4,387,394 
		 4,387,394 
	
	
		Total Water Consumed (m3)
		 1,881,006 
		 1,596,456 
		 1,705,452 
		 1,719,852 
		 1,730,652 
		 1,741,452 
		 1,752,252 
		 1,779,906 
		 2,851,806 
		 2,851,806 
		 2,851,806 
		 2,851,806 
	
	
		 Water (Shortage)/Surplus (m3) 
		 1,606,145 
		 2,043,914 
		 1,876,228 
		 1,854,074 
		 1,837,458 
		 1,820,843 
		 1,804,228 
		 1,761,683 
		 112,606 
		 112,606 
		 112,606 
		 112,606 
	
	
		 Total Water Connections 
		 30,082 
		 30,582 
		 31,082 
		 31,382 
		 31,682 
		 31,982 
		 32,282 
		 32,282 
		 32,282 
		 32,282 
		 32,282 
		 32,282 
	
	
		 New Water Connection Revenue (mllion) 
		 0.0360 
		 0.0600 
		 0.0600 
		 0.0360 
		 0.0360 
		 0.0360 
		 0.0360 
		 -   
		 -   
		 -   
		 -   
		 -   
	
	
		 Total Water Revenue (million) 
		 1.277 
		 1.108 
		 1.190 
		 1.201 
		 1.209 
		 1.217 
		 1.216 
		 1.206 
		 1.817 
		 1.817 
		 1.817 
		 1.817 
	
	
		Opening Sewerage Connections
		Jun
		 29,000 
		 Opening Connections 
		

		

		

		

		

		

		

		

		

	
	
		0
		Jul
		Aug
		Sept
		Oct
		Nov
		Dec
		Jan
		Feb
		Mar
		Apr
		May
		Jun
	
	
		 Sewerage tariff/m3 
		 1.10 
		

		

		

		

		

		

		

		

		

		

		

	
	
		 Connection Charge (currency/connection) 
		 120 
		

		

		

		

		

		

		

		

		

		

		

	
	
		 Tariff Rate increases 
		0%
		0%
		0%
		0%
		0%
		0%
		0%
		0%
		0%
		0%
		0%
		0%
	
	
		 Domestic Year-End Factor: 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
	
	
		 Domestic Average Factor 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
		 1.00 
	
	
		 Effective Tariff 
		 1.10 
		 1.10 
		 1.10 
		 1.10 
		 1.10 
		 1.10 
		 1.10 
		 1.10 
		 1.10 
		 1.10 
		 1.10 
		 1.10 
	
	
		 Opening Sewerage Connections 
		 29,000 
		 29,200 
		 29,400 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
	
	
		 New connections 
		 200 
		 200 
		 200 
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
	
	
		 Monthly Wastewater Capacity (m3) 
		 5,000,000 
		 5,000,000 
		 5,000,000 
		 5,000,000 
		 5,000,000 
		 5,000,000 
		 5,000,000 
		 5,000,000 
		 5,000,000 
		 5,000,000 
		 5,000,000 
		 5,000,000 
	
	
		 Sewerage Processed (m3) 
		 1,095,761 
		 928,949 
		 989,312 
		 996,586 
		 996,989 
		 997,387 
		 997,781 
		 1,010,612 
		 1,619,225 
		 1,619,225 
		 1,619,225 
		 1,619,225 
	
	
		 Total Sewerage Connections 
		 29,200 
		 29,400 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
		 29,600 
	
	
		 Sewerage Billed (million) 
		 1.2053 
		 1.0218 
		 1.0882 
		 1.0962 
		 1.0967 
		 1.0971 
		 1.0976 
		 1.1117 
		 1.7811 
		 1.7811 
		 1.7811 
		 1.7811 
	
	
		 Sewerage Connection Revenue (million) 
		 0.024 
		 0.024 
		 0.024 
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
	
	
		Other Revenue (million)
		 0.139 
		 0.139 
		 0.139 
		 0.139 
		 0.139 
		 0.139 
		 0.139 
		 0.139 
		 0.139 
		 0.139 
		 0.139 
		 0.139 
	
	
		Water Supply Operating & Admin Costs
		Jul
		Aug
		Sept
		Oct
		Nov
		Dec
		Jan
		Feb
		Mar
		Apr
		May
		Jun
	
	
		Water Extraction Cost
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		 NRW @ Extraction 
		3%
		3%
		3%
		3%
		3%
		3%
		3%
		3%
		3%
		3%
		3%
		3%
	
	
		 Monthly Water Extraction 
		 2,980,671 
		 2,529,769 
		 2,702,485 
		 2,725,304 
		 2,742,418 
		 2,759,532 
		 2,776,645 
		 2,820,466 
		 4,519,016 
		 4,519,016 
		 4,519,016 
		 4,519,016 
	
	
		 Cost/M3 in Currency 
		 0.0300 
		 0.0300 
		 0.0300 
		 0.0300 
		 0.0300 
		 0.0300 
		 0.0300 
		 0.0300 
		 0.0300 
		 0.0300 
		 0.0300 
		 0.0300 
	
	
		 Water Extraction Costs (million) 
		 0.089 
		 0.076 
		 0.081 
		 0.082 
		 0.082 
		 0.083 
		 0.083 
		 0.085 
		 0.136 
		 0.136 
		 0.136 
		 0.136 
	
	
		Water Production Electricity Cost
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		 Monthly Water Production 
		 2,893,855 
		 2,456,086 
		 2,623,772 
		 2,645,926 
		 2,662,542 
		 2,679,157 
		 2,695,772 
		 2,738,317 
		 4,387,394 
		 4,387,394 
		 4,387,394 
		 4,387,394 
	
	
		 Water Pumping Cost/m3 
		 0.060 
		 0.015 
		 0.015 
		 0.015 
		 0.015 
		 0.015 
		 0.015 
		 0.015 
		 0.015 
		 0.015 
		 0.015 
		 0.015 
	
	
		 Electricity Costs (million) 
		 0.174 
		 0.037 
		 0.039 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
		 0.041 
		 0.066 
		 0.066 
		 0.066 
		 0.066 
	
	
		Chemical Treatment
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		 Chemical Costs (currency/m3) 
		 0.016 
		 0.016 
		 0.016 
		 0.016 
		 0.016 
		 0.016 
		 0.016 
		 0.016 
		 0.016 
		 0.016 
		 0.016 
		 0.016 
	
	
		 Chemical Cost (million) 
		 0.0463 
		 0.0393 
		 0.0420 
		 0.0423 
		 0.0426 
		 0.0429 
		 0.0431 
		 0.0438 
		 0.0702 
		 0.0702 
		 0.0702 
		 0.0702 
	
	
		Maintenance & Repairs
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		 Maintenance (million) 
		 0.350 
		 0.350 
		 0.350 
		 0.350 
		 0.350 
		 0.350 
		 0.350 
		 0.350 
		 0.350 
		 0.350 
		 0.350 
		 0.350 
	
	
		Water Operating Salaries
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		 No. of Employees 
		 55 
		 55 
		 55 
		 55 
		 55 
		 55 
		 55 
		 55 
		 55 
		 55 
		 55 
		 55 
	
	
		 Cost/Employee (currency/month) 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
	
	
		 Monthly Direct Salaries Costs 
		 0.205 
		 0.205 
		 0.205 
		 0.205 
		 0.205 
		 0.205 
		 0.205 
		 0.205 
		 0.205 
		 0.205 
		 0.205 
		 0.205 
	
	
		 Allowances and Bonus as % of Direct 
		10%
		35%
		35%
		35%
		35%
		10%
		10%
		10%
		10%
		10%
		10%
		10%
	
	
		 Allowances and Bonuses (million) 
		 0.021 
		 0.072 
		 0.072 
		 0.072 
		 0.072 
		 0.021 
		 0.021 
		 0.021 
		 0.021 
		 0.021 
		 0.021 
		 0.021 
	
	
		 Overtime as % of direct costs 
		35%
		35%
		35%
		35%
		35%
		35%
		35%
		35%
		35%
		35%
		35%
		35%
	
	
		 Overtime costs (million) 
		 0.072 
		 0.072 
		 0.072 
		 0.072 
		 0.072 
		 0.072 
		 0.072 
		 0.072 
		 0.072 
		 0.072 
		 0.072 
		 0.072 
	
	
		Pension Expense
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
	
	
		Administration (million)
		 0.153 
		 0.153 
		 0.153 
		 0.153 
		 0.153 
		 0.153 
		 0.153 
		 0.153 
		 0.153 
		 0.153 
		 0.153 
		 0.153 
	
	
		Other Water Operating Costs
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
		 -   
	
	
		Wastewater Service Operating & Admin Costs
		 Jul 
		 Aug 
		 Sept 
		 Oct 
		 Nov 
		 Dec 
		 Jan 
		 Feb 
		 Mar 
		 Apr 
		 May 
		 Jun 
	
	
		Wastewater Employee Salaries
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		 No. of Employees 
		 25 
		 25 
		 25 
		 25 
		 25 
		 25 
		 25 
		 25 
		 25 
		 25 
		 25 
		 25 
	
	
		 Cost/Employee (currency/month) 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
		 3,729.00 
	
	
		 Direct Salaries Costs 
		 0.093 
		 0.093 
		 0.093 
		 0.093 
		 0.093 
		 0.093 
		 0.093 
		 0.093 
		 0.093 
		 0.093 
		 0.093 
		 0.093 
	
	
		 Allowances and Bonus as % of Direct 
		10%
		10%
		10%
		10%
		10%
		10%
		10%
		10%
		10%
		10%
		10%
		10%
	
	
		 Allowances and Bonuses (million) 
		 0.009 
		 0.009 
		 0.009 
		 0.009 
		 0.009 
		 0.009 
		 0.009 
		 0.009 
		 0.009 
		 0.009 
		 0.009 
		 0.009 
	
	
		 Overtime as % of direct costs 
		35%
		35%
		35%
		35%
		35%
		35%
		35%
		35%
		35%
		35%
		35%
		35%
	
	
		 Overtime costs (million) 
		 0.033 
		 0.033 
		 0.033 
		 0.033 
		 0.033 
		 0.033 
		 0.033 
		 0.033 
		 0.033 
		 0.033 
		 0.033 
		 0.033 
	
	
		 Energy Cost (unit cost/m3) 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
	
	
		Electricity Costs
		 0.044 
		 0.037 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
		 0.040 
		 0.065 
		 0.065 
		 0.065 
		 0.065 
	
	
		Maintenance & Repairs
		 0.085 
		 0.085 
		 0.085 
		 0.085 
		 0.085 
		 0.085 
		 0.085 
		 0.085 
		 0.085 
		 0.085 
		 0.085 
		 0.085 
	
	
		Administrative Expenses
		 0.194 
		 0.194 
		 0.194 
		 0.194 
		 0.194 
		 0.194 
		 0.194 
		 0.194 
		 0.194 
		 0.194 
		 0.194 
		 0.194 
	
	
		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 
	
	
		 Monthly Cost  
		 0.526 
		 0.519 
		 0.522 
		 0.522 
		 0.522 
		 0.522 
		 0.522 
		 0.523 
		 0.547 
		 0.547 
		 0.547 
		 0.547 
	
	
		 Wastewater Costs/ (m3) 
		 0.48 
		 0.56 
		 0.53 
		 0.52 
		 0.52 
		 0.52 
		 0.52 
		 0.52 
		 0.34 
		 0.34 
		 0.34 
		 0.34 
	
	
		Other Direct Costs
		0.100
		0.100
		0.100
		0.100
		0.100
		0.100
		0.100
		0.100
		0.100
		0.100
		0.100
		0.100
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Color Codes For Numbers and Cells:
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Light Blue Cells and Numbers = Input Areas
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Black Nos. = Formula Cells. Do not input

The words contained in this file might help you see if this file matches what you are looking for:

...Sheet covid impact assessment wsp financial projections period name of sample contact person finance tel and email address opening month enter number currency idr please data in blue cells millions income statement actual average projection pre post remarks revenue over last months jun jul aug sept oct nov dec jan feb mar apr may total revenues billed from water sales million sewerage collection efficiency collected connection other specify if applicable local government transfers plusminus operating costs salaries extraction cost production electricity chemical treatment maintenance amp repairs pension expense adminstration supply waster wastewater employee administrative expenses direct interest depreciation profit before taxes after assumes cash collections flow add principal repayments debt service related capex programmed funding for end itemized provide planned that can be completed within eg tanker services hand washing stations dealing with backlog connections providing free wh...

Related files

Share

Help


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
INCOME STATEMENT	Actual Average	Projection												Pre Covid	Post Covid	Financial 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

INCOME STATEMENT														Pre Covid	Post Covid	Financial 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

CASH FLOW STATEMENT														Pre Covid	Post Covid	Financial 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 connections (where feasible), installing public standpoints etc). If no interventions planned , please leave blank														Pre Covid	Post Covid	Financial Impact	Remarks
	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

Color Codes For Numbers and Cells:
Light Blue Cells and Nos. = Input Areas						COVID Income and Cash Flow Financial Impact
Black Nos. = Formula Cells. Do not input						(Increase = Blue Decrease = Red)

Sample WSP
Water Capacity, Production & Connection Program
Opening Household Indicators	Month Ending	Month-End House Connections	Weighted Average Tariff	NRW
*Please enter data in blue cells*	Jun	29,782	0.75	35.0%
	Jul	Aug	Sept	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Unaccounted For Water (NRW)	35.0%	35.0%	35.0%	35.0%	35.0%	35.0%	35.0%	35.0%	35.0%	35.0%	35.0%	35.0%
Households Operations
Average Tariff (Currency/m3)	0.75
Connection Charge (Currency/Connection)	120
Tariff Rate increases/(decreases)	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
Domestic Month-End Factor:	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00
Domestic Average Factor	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00
Effective Tariff	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75	0.75
New Connections	300	500	500	300	300	300	300	-	-	-	-	-
Cumulative Water Connections (Month End)	30,082	30,582	31,082	31,382	31,682	31,982	32,282	32,282	32,282	32,282	32,282	32,282
Cumulative Water Connections (Ave.)	29,932	30,332	30,832	31,232	31,532	31,832	32,132	32,282	32,282	32,282	32,282	32,282
Percent Growth in Connections	1.0%	0.8%	0.8%	0.5%	0.5%	0.5%	0.5%	0.0%	0.0%	0.0%	0.0%	0.0%
Average Usage/Connection (m3/day)	1.100	1.100	1.200	1.200	1.200	1.200	1.200	1.100	1.100	1.100	1.100	1.100
Opening Commercial Indicators	Month Ending	Month-End Commercial Connections	Weighted Average Tariff
	Jun	19,850	0.60
	Jul	Aug	Sept	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Commercial Operations
Average Tariff (Currency/m3)	0.60
Connection Charge (Currency/Connection)	1,000
Tariff Rate increases/(decreases)	0%	0%	0%	0%	0%	0%	-5%	0%	0%	0%	0%	0%
Domestic Month-End Factor:	1.00	1.00	1.00	1.00	1.00	1.00	0.95	0.95	0.95	0.95	0.95	0.95
Domestic Average Factor	1.00	1.00	1.00	1.00	1.00	1.00	0.98	0.95	0.95	0.95	0.95	0.95
Effective Tariff	0.60	0.60	0.60	0.60	0.60	0.60	0.59	0.57	0.57	0.57	0.57	0.57
New Connections						-	-	-	-	-	-	-
Cumulative Water Connections (Month End)	19,850	19,850	19,850	19,850	19,850	19,850	19,850	19,850	19,850	19,850	19,850	19,850
Cumulative Water Connections (Ave.)	19,850	19,850	19,850	19,850	19,850	19,850	19,850	19,850	19,850	19,850	19,850	19,850
Percent Growth in Connections	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
Average Usage/Connection (m3/day)	1.500	1.000	1.000	1.000	1.000	1.000	1.000	1.200	3.000	3.000	3.000	3.000
Water Monthly Production Capacity (m3)	4,500,000	4,500,000	4,500,000	4,500,000	4,500,000	4,500,000	4,500,000	4,500,000	4,500,000	4,500,000	4,500,000	4,500,000
Total Potential Monthly Demand	1,881,006	1,596,456	1,705,452	1,719,852	1,730,652	1,741,452	1,752,252	1,779,906	2,851,806	2,851,806	2,851,806	2,851,806
Daily Demand (m3)	62,700	53,215	56,848	57,328	57,688	58,048	58,408	59,330	95,060	95,060	95,060	95,060
Daily Revenue	42,559	36,934	39,659	40,019	40,289	40,559	40,531	40,210	60,576	60,576	60,576	60,576
Total Production Requirement (m3)	2,893,855	2,456,086	2,623,772	2,645,926	2,662,542	2,679,157	2,695,772	2,738,317	4,387,394	4,387,394	4,387,394	4,387,394
Total Actual Production (m3)	2,893,855	2,456,086	2,623,772	2,645,926	2,662,542	2,679,157	2,695,772	2,738,317	4,387,394	4,387,394	4,387,394	4,387,394
Total Water Consumed (m3)	1,881,006	1,596,456	1,705,452	1,719,852	1,730,652	1,741,452	1,752,252	1,779,906	2,851,806	2,851,806	2,851,806	2,851,806
Water (Shortage)/Surplus (m3)	1,606,145	2,043,914	1,876,228	1,854,074	1,837,458	1,820,843	1,804,228	1,761,683	112,606	112,606	112,606	112,606
Total Water Connections	30,082	30,582	31,082	31,382	31,682	31,982	32,282	32,282	32,282	32,282	32,282	32,282
New Water Connection Revenue (mllion)	0.0360	0.0600	0.0600	0.0360	0.0360	0.0360	0.0360	-	-	-	-	-
Total Water Revenue (million)	1.277	1.108	1.190	1.201	1.209	1.217	1.216	1.206	1.817	1.817	1.817	1.817
Opening Sewerage Connections	Jun	29,000	Opening Connections
0	Jul	Aug	Sept	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Sewerage tariff/m3	1.10
Connection Charge (currency/connection)	120
Tariff Rate increases	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
Domestic Year-End Factor:	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00
Domestic Average Factor	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00
Effective Tariff	1.10	1.10	1.10	1.10	1.10	1.10	1.10	1.10	1.10	1.10	1.10	1.10
Opening Sewerage Connections	29,000	29,200	29,400	29,600	29,600	29,600	29,600	29,600	29,600	29,600	29,600	29,600
New connections	200	200	200	-	-	-	-	-	-	-	-	-
Monthly Wastewater Capacity (m3)	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000	5,000,000
Sewerage Processed (m3)	1,095,761	928,949	989,312	996,586	996,989	997,387	997,781	1,010,612	1,619,225	1,619,225	1,619,225	1,619,225
Total Sewerage Connections	29,200	29,400	29,600	29,600	29,600	29,600	29,600	29,600	29,600	29,600	29,600	29,600
Sewerage Billed (million)	1.2053	1.0218	1.0882	1.0962	1.0967	1.0971	1.0976	1.1117	1.7811	1.7811	1.7811	1.7811
Sewerage Connection Revenue (million)	0.024	0.024	0.024	-	-	-	-	-	-	-	-	-
Other Revenue (million)	0.139	0.139	0.139	0.139	0.139	0.139	0.139	0.139	0.139	0.139	0.139	0.139
Water Supply Operating & Admin Costs	Jul	Aug	Sept	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Water Extraction Cost
NRW @ Extraction	3%	3%	3%	3%	3%	3%	3%	3%	3%	3%	3%	3%
Monthly Water Extraction	2,980,671	2,529,769	2,702,485	2,725,304	2,742,418	2,759,532	2,776,645	2,820,466	4,519,016	4,519,016	4,519,016	4,519,016
Cost/M3 in Currency	0.0300	0.0300	0.0300	0.0300	0.0300	0.0300	0.0300	0.0300	0.0300	0.0300	0.0300	0.0300
Water Extraction Costs (million)	0.089	0.076	0.081	0.082	0.082	0.083	0.083	0.085	0.136	0.136	0.136	0.136
Water Production Electricity Cost
Monthly Water Production	2,893,855	2,456,086	2,623,772	2,645,926	2,662,542	2,679,157	2,695,772	2,738,317	4,387,394	4,387,394	4,387,394	4,387,394
Water Pumping Cost/m3	0.060	0.015	0.015	0.015	0.015	0.015	0.015	0.015	0.015	0.015	0.015	0.015
Electricity Costs (million)	0.174	0.037	0.039	0.040	0.040	0.040	0.040	0.041	0.066	0.066	0.066	0.066
Chemical Treatment
Chemical Costs (currency/m3)	0.016	0.016	0.016	0.016	0.016	0.016	0.016	0.016	0.016	0.016	0.016	0.016
Chemical Cost (million)	0.0463	0.0393	0.0420	0.0423	0.0426	0.0429	0.0431	0.0438	0.0702	0.0702	0.0702	0.0702
Maintenance & Repairs
Maintenance (million)	0.350	0.350	0.350	0.350	0.350	0.350	0.350	0.350	0.350	0.350	0.350	0.350
Water Operating Salaries
No. of Employees	55	55	55	55	55	55	55	55	55	55	55	55
Cost/Employee (currency/month)	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00
Monthly Direct Salaries Costs	0.205	0.205	0.205	0.205	0.205	0.205	0.205	0.205	0.205	0.205	0.205	0.205
Allowances and Bonus as % of Direct	10%	35%	35%	35%	35%	10%	10%	10%	10%	10%	10%	10%
Allowances and Bonuses (million)	0.021	0.072	0.072	0.072	0.072	0.021	0.021	0.021	0.021	0.021	0.021	0.021
Overtime as % of direct costs	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%
Overtime costs (million)	0.072	0.072	0.072	0.072	0.072	0.072	0.072	0.072	0.072	0.072	0.072	0.072
Pension Expense	-	-	-	-	-	-	-	-	-	-	-	-
Administration (million)	0.153	0.153	0.153	0.153	0.153	0.153	0.153	0.153	0.153	0.153	0.153	0.153
Other Water Operating Costs	-	-	-	-	-	-	-	-	-	-	-	-
Wastewater Service Operating & Admin Costs	Jul	Aug	Sept	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Wastewater Employee Salaries
No. of Employees	25	25	25	25	25	25	25	25	25	25	25	25
Cost/Employee (currency/month)	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00	3,729.00
Direct Salaries Costs	0.093	0.093	0.093	0.093	0.093	0.093	0.093	0.093	0.093	0.093	0.093	0.093
Allowances and Bonus as % of Direct	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%	10%
Allowances and Bonuses (million)	0.009	0.009	0.009	0.009	0.009	0.009	0.009	0.009	0.009	0.009	0.009	0.009
Overtime as % of direct costs	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%	35%
Overtime costs (million)	0.033	0.033	0.033	0.033	0.033	0.033	0.033	0.033	0.033	0.033	0.033	0.033
Energy Cost (unit cost/m3)	0.040	0.040	0.040	0.040	0.040	0.040	0.040	0.040	0.040	0.040	0.040	0.040
Electricity Costs	0.044	0.037	0.040	0.040	0.040	0.040	0.040	0.040	0.065	0.065	0.065	0.065
Maintenance & Repairs	0.085	0.085	0.085	0.085	0.085	0.085	0.085	0.085	0.085	0.085	0.085	0.085
Administrative Expenses	0.194	0.194	0.194	0.194	0.194	0.194	0.194	0.194	0.194	0.194	0.194	0.194
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
Monthly Cost	0.526	0.519	0.522	0.522	0.522	0.522	0.522	0.523	0.547	0.547	0.547	0.547
Wastewater Costs/ (m3)	0.48	0.56	0.53	0.52	0.52	0.52	0.52	0.52	0.34	0.34	0.34	0.34
Other Direct Costs	0.100	0.100	0.100	0.100	0.100	0.100	0.100	0.100	0.100	0.100	0.100	0.100

Color Codes For Numbers and Cells:
Light Blue Cells and Numbers = Input Areas
Black Nos. = Formula Cells. Do not input

Related files

Share

Share to social media

Help

Login Area