Home

ASTEC MODEL. User's Guide to ASTEC 2.0.

1. With this information along with some other technical and financial data e g aggregate water production and wastewater treatment accounts in arrears etc the ASTEC user can estimate the current budget balance of the water system In other words under current conditions including tariff levels does this system have the income necessary to meet it immediate bills Or does it have to depend on some transfers from outside the water system or revenues from non core operations to keep itself afloat Most water systems we encountered were in current account balance Even so it was important to our work and that of water system managers to have the fresh and systematic look at the water system s short term financial condition that ASTEC provides and to identify those systems with current budget deficits even if such systems are in the minority 2 2 Is Your Water System Financially Sustainable A water system may not be broke but can it reasonably expect to be a self sustaining entity for years into the future This question is particularly important for water systems that like many of the systems in the DRB operated until recently in economic systems where infrastructure and equipment were financed externally at no direct cost to the water system and its customers ASTEC examines this question by enlarging the baseline set of accounts for a given water system to include capital elements especially depreciation of infrastructure a
2. 00 Small enterprises OOo o e S MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 15 The next section asks for information on differences between billed and discharged wastewater for each SU category The wastewater discharge of large industrial customers is sometimes metered but for most service users invoiced wastewater volumes are determined as a percent of water consumption This is usually 100 but it is sometimes less e g 9590 Invoiced and discharged volumes however are not always the same If this is the case then average annual discharged volumes should be separately entered This is important since the variable costs of the wastewater treatment services estimated below are based on estimates of the actual volumes discharged as opposed to billed or invoiced volumes In cells 012 P26 you can indicate if invoiced and discharged volumes are different and supply discharged volumes as a percent of water use Figure 8 Invoiced and Discharged Wastewater Quantities Are invoiced and actual Service user category wastewater discharge the If not actual discharge percent of water use 4 2 4 Own Price Elasticity of Demand The price elasticity of demand is the percentage change in consumption as a result of a one percent increase in the variable tariff of the corresponding service For instance if the elasticity of demand for water consumption is 0 2 then one percent increase in the var
3. Fixed Costs and One Time Grants for Wastewater Service Fixed cost and grant items for wastewater service are supplied by the user in Section 4 1 of ASTEC scenario spreadsheets 1 S10 starting at row 86 The structure of this section is the same as that of Section 3 1 for water The main difference is that while in section 3 1 costs can be assigned to the unbilled water category here there is an unbilled but treated wastewater category referring to collected and treated storm water and infiltrating ground water We offer a figure showing an example of this data entry block below Figure 19 Providing and Allocating Fixed Wastewater Service Costs 4 Costs of providing wastewater service 4 1 Fixed costs and one time grants for wastewater service 1 2 3 4 5 Costor grant of eo J st o Ost Select the basis for annual cost Unbilled but Name of the item wastewater HUF Number of years YY ost of capital sd Annualized value HUF year 90000000 45 000 000 20 000 000 100 000 000 Distribution of the annual value among service user accounts Households apartments Individual houses w amp ww arge enterprises mall enterprises gt wastewater Total SR a a a UNDP GEF DANUBE REGIONAL PROJECT 4 Entering Data page 26 4 4 4 Variable Costs and Subsidies for Wastewater Service Variable cost and subsidy items for wastewater service can be supplied in Section 4 2 starting at row 115 of
4. document states how much service has been provided and how much the customer owes the MWWU for this service The invoice or bill may also contain information on how the amount owed has been calculated and other information related to the service provided The amount of money actually collected from the customer may differ from the amount invoiced for a variety of reasons including most particularly non payment This may be either a periodic e g monthly or one time charge assessed on a customer to recover the cost of materials such as pipes and meters machinery and labor used to connect that customer to the water or wastewater system See Fixed Water Tariff The elasticity of demand is a measure of the responsiveness of consumption to a change in the price of a good or service It is usually measured as the ratio of the percentage change in consumption to percentage change in price In this case it is an estimate of the percentage change in wastewater service consumption measured in m per period divided by the percentage change in price measured in monetary units per m3 Price elasticity of demand refers to the average service user within a group including also those which do not react to changes in tariffs The higher the share of non paying customers the lower the response in consumption to changes in price will be Those costs the value of which do not change with changes in the volume of production or consumption Depending
5. 2 ASTEC expects you to specify water use and tariff features of the SU categories for the MWWU being modeled The columns and sample entries with light blue background are as follows Invoiced water use per account This is the average annual water consumption within the service user category denominated in m year Fixed water tariff per account Fixed tariff or service charge excluding VAT that the individual account will have to pay regardless of water consumption specified in the selected local currency Variable water tariff The price of one cubic meter of water excluding VAT specified in the selected local currency per m of water billed Water use to which the customer is entitled for paying the fixed tariff Under some tariff regimes including a fixed tariff component the first few cubic meters of water use and frequently also the equivalent wastewater discharge are not subject to a variable water tariff The units of this entry are in m per year Figure 6 Data on Water Use and Tariffs Water use to i which the user Invoiced water Fixed Water Variable water tariff per tariff is entitled for ari account paying the HUF year C HUF M3 fixed tariff Service user category use per account m year m year UNDP GEF DANUBE REGIONAL PROJECT 4 Entering Data page 14 Having this latter option also provides the opportunity to represent block tariff systems In the case of a block ta
6. ASTEC spreadsheets are laid out in what we think is a fairly orderly and transparent way They are also laden with headings comments and alerts that help guide the user s understanding of the significance of an entry and to protect the user from making a mistake For example if entries in several fields are in logical conflict ASTEC will alert the user to this problem Similarly when one changes a scenario ASTEC asks if the user also wants to change the description that accompanies the scenario before allowing the user to save that version of ASTEC Despite these user friendly features the enormous flexibility and range of ASTEC has also meant that the software is best described as complicated and demanding of the user s care and attention To implement use and interpret ASTEC correctly the user or user group needs a good understanding of how the software is structured and an appreciation for the basic mathematical economics engineering and accounting principles represented in the functions and operations of the model While it is possible for a naive user to quickly get a feel for ASTEC and to run ASTEC scenarios we do not recommend such use of ASTEC unless it is done under the tutelage of an experienced user Of course having made the investment in learning about ASTEC and implementing it for a particular water system the user is in a position of explore update and re examine most any policy change or investment program
7. but to an estimate of the annual value of system depreciation In another water system we worked with the development fund was not used for development but for maintenance In that same water system household customers were identified as metered when the meter in question covered an entire apartment block In yet another instance of confusing terminology an accounting item labeled as profit was actually a rental payment on capital infrastructure used by the water system but owned by the city Economic technical or finance terminology compounded by language differences can often foil identification and treatment of serious financial difficulties We all too commonly mis understood or mis interpreted data provided by the water systems At the same time water system staff sometimes misunderstood the organizing principles used in setting up ASTEC accounts principles that we thought were self evident but which in retrospect needed to be more clearly and effectively conveyed The simple process of entering data in proper locations and in the proper way in the ASTEC spreadsheets often revealed a wide variety of such problems and helped us identify confront and correct them 2 5 Can the Water System and Its Customers Support Expansion and Upgrades For many water systems of the DRB the current challenge goes beyond sustaining current services to up grading or expanding them to meet the needs of their customers or new health safe
8. for water service ndividual houses water only ndividual houses w amp ww 70 000 80 000 mall enterprises 4 3 Scenario Sheets Unbilled Water and Wastewater 4 3 1 Unbilled water Unbilled water is produced water which is not billed to any of the service users It may consist of leakage which is the actual loss of water from the distribution network technological use such as regular flushing of the system and produced and used but not billed water e g illegal consumption free use of water for the fire department or the city park service etc The volume of unbilled water in m year is entered in cells E27 Figure 13 Unbilled Water and Wastewater Unbilled water and wastewater Unbilled water 300 000 Baseline water production 1 848 018 Unbilled water is important for modeling purposes since it contributes to costs while it does not generate revenues It is applied in the model in this spirit there are certain cost items that can be associated with unbilled water e g production and pumping of water through the network and as shown later in section 4 4 they can actually be assigned to the Unbilled water sub account If some of the costs are applied to unbilled water while at the same time the user requires ASTEC to calculate cost recovering tariffs on the basis of each SUs particular costs then the costs associated with producing and distributing unbilled water may not be covered by any of the service users To av
9. of the service users are also business entities VAT should not have a fundamental role in ASTEC s examination of investment and tariff modeling Households however cannot get a VAT refund The gross water and wastewater tariff inclusive of VAT will all appear as a real expense In order to account for the different perspectives of households and business entities the water bill calculations in the output sheets will include the non refundable VAT paid by the household SU categories To support this feature you need to supply ASTEC with the VAT rates and declare whether VAT is refundable for a given SU category This information is to be entered into range T14 V22 and is illustrated in the figure below Please remember that all monetary values should be entered in the model as net values without VAT There may be cases when VAT does have a role in business decisions subject to e g the VAT refund regulations bookkeeping rules on the VAT part of long term investments or expected changes in the level of VAT In the majority of cases however VAT is not one of the major factors shaping business decisions MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 17 Figure 10 Setting VAT Information Rate of the VAT for wastewater service Rate of the VAT for water service Is the VAT Service user category deductible 4 2 6 Unpaid Bills The user next sets the percentage
10. on the length of the examined period a certain cost item may be fixed or variable cost On the short run the majority of the costs of water systems are usually fixed costs UNDP GEF DANUBE REGIONAL PROJECT Annex 1 page 40 Term Fixed Water Tariff Explanation The fixed water tariff is charged to customers as a fixed amount per unit of time For example a 0 5 Euro per month per account The fixed water tariff is sometimes called a fixed charge and it does not vary with the amount of water consumed The customer pays the fixed water tariff just for the privilege of accessing the water and or wastewater system In some cases it may entitle the customer to use up to a fixed amount of water per unit of time e g 0 2 m per month at no additional charge Future Value Payment This feature is used to set aside funds today to finance some future Independent goods MWWU Present Value Payment Real Interest Rate investment such as replacement of a part of the water network or pump station The future value payment is the expected amount of the future investment The future value payment ASTEC computes is a series of annual payments to be made over some planning period and that as part of the planning process should be covered by tariffs on the annual provision of water and wastewater services The usual assumption is that the annual payments are deposited in some form of bank account and accrue annual interest The an
11. system these values should add up to 1 The sum of the supplied values is displayed in row 57 Selecting Water used from the drop down menu will result in distribution of costs in proportion with consumed water quantities Not all service user categories have to carry a proportion of the annualized cost grant You can select any service user group but at least one and the full value of the annualized cost grant will be distributed among selected service users in proportion to their consumption of water Selection of a group takes place by inserting 1 into the corresponding cell in the service user category s row Otherwise 0 needs to be inserted or the cell has to be left blank While the model is running consumption of water within a service user group may change due to changes in tariffs ASTEC will automatically make corresponding changes in cost distribution The names of service user categories are displayed in range B41 B55 Only those service users receiving water service are displayed MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 23 Figure 16 Allocating Fixed Water Service Costs 3 Costs of providing water service 3 1 Fixed costs and one time grants for water service 1 2 3 Cost or grant PO Cost Cost Select the basis for annual cost recovery Amount of the cost or grant HUF 230 000 Number of years YY Cost of capital YY Annualized value HUF year 230000000 75 000 00
12. the ASTEC scenario spreadsheets Section 4 2 is also set up similarly to Section 3 2 for water services as seen in the figure below Figure 20 Providing and Allocating Variable Wastewater Service Costs 4 2 Variable costs and subsidies for wastewater service 1 2 3 Cost or subsidy oo Unbilled but Name of the item wastewater Chemicals Electricity Value HUF m3 Application of the cost or subsidy to service user accounts Households apartments 1 1 Individual houses w amp ww arge enterprises Small enterprises O 1 1 ooo Unbilled but treated wastewater me _ __ 4 4 5 Effluent Charges and Fines Section 4 3 starting at row 139 of the ASTEC scenario spreadsheets offers you the option to enter wastewater related costs that aren t a function of the volume of wastewater alone This section was designed specifically to address effluent charges and fines on wastewater discharges These are entered separately from the rest of the wastewater costs An effluent charge is assessed on the full amount of effluent discharge of a pollutant subject to the charge while an effluent fine is imposed only on above limit discharges A portion of Section 4 3 is displayed below If effluent charge and fine costs are entered in Section 4 3 then they should not be inserted in Sections 4 1 and 4 2 otherwise ASTEC would account for these costs twice and would overestimate costs The computation of the annual effluent charge p
13. the upper right corner By moving the cursor to the cell the comment will pop up These comments usually include guidance on data entry You can enter your own comments by selecting the cell and then pressing Ctrl Shift i and then filling in the text of the comment If you want to delete a comment you should press Ctrl Shift d 4 1 Control Sheet Before you start filling in the Scenario sheets there are some cells to be filled in on the Control sheet You should enter the number of service user categories in Cell C5 You can use up to 15 different service user categories within ASTEC 2 0 If you however organize MWWU customers into fewer MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 11 than 15 categories then the model will carry out computations in less time In addition scenario and output sheets will also be easier to use when there are fewer SU categories since smaller tables will be created and displayed After you have entered the number of SU categories you should also press the button right of the cell called Set number so that the SU category number entry will take effect Figure 2 Setting the Number of Service User Categories Number of service user groups Lose Set number In Cells C21 C23 you should fill in the code for the local currency like RON for New Romanian Lei or HRK for Croatian Kuna Also fill in the exchange rate between the local currency and the EUR and the USD t
14. 0 Distribution of the annual value among service user accounts Households apartments Individual houses water only arge enterprises mall enterprises foo TCS Error display IX One additional service user category is Unbilled water As discussed before costs can be assigned not only to service users but also to the category of unbilled water For instance you may choose to assign some of the cost of chemicals or electricity used to treat and pump water to this category This feature of ASTEC is especially useful if you would like to track the costs associated with leakage The costs related to unbilled water are computed by ASTEC Since one of the purposes of using ASTEC is estimation of cost recovering tariffs costs of unbilled water should further be distributed to tariff paying entities i e the service user categories This can be done in the cells of spreadsheet range C40 C55 in the same fashion as distribution of any of other cost item When you are filling in the cells distributing unbilled costs to SU categories the exact value of the costs associated with unbilled water may not be known ASTEC will compute unbilled costs while running using the data for SU categories and the method of distribution provided by the modeler The method of cost redistribution is illustrated by the figure below 11 If you invest into leakage reduction you need to know your costs before and after the investment in order to be ab
15. 5 Microsoft Forms 2 0 Object Library or a different version number If any of the above are unmarked you need to mark them If you do not find Solver xls or xla in the list which is a reference sometimes missing then you need to hit Browse and go to the project library where solver is located Most often this library is ProgramFiles Microsoft Office Office Makro Solver Solver If you do not have such a library then you need to find the location of Solver from Windows Explorer with the Find tool Sometimes Solver is installed in your version of Excel but you still receive an error message when you try to run the model As a remedy to this problem try the following gt Go to any of the spreadsheets and open the Solver window Tools Solver and then close it For some reason this action sometimes wakes up Solver gt Unmark and then mark again Solver within the Visual Basic Editor Tools References UNDP GEF DANUBE REGIONAL PROJECT 9 Troubleshooting page 38 9 2 Data Problems We have tried to assure that ASTEC accepts only proper data types in any given cell Data incompatibility should not be a cause for problems An order of magnitude mistake in entry may create problems however If for instance costs are provided in thousands of units of local currencies while tariffs are provided in simple currency units then the large gap between costs and revenues will be difficult if not impossible for ASTEC to f
16. C in a similar fashion as unbilled water Costs can be assigned to the collection and treatment of this type of wastewater and the assigned costs can then be further distributed to SU categories if the option of setting tariffs to fully recovery costs is later selected by the user 4 4 Scenario Sheets Cost Data ASTEC asks the user to distinguish between fixed and variable costs This distinction is important to computing costs and evaluating different investment and policy proposals The variable cost is so called because it varies with the amount of service usually calculated as m of water or wastewater provided This is a marginal concept in that we ask how many more units of various inputs will be needed to increase output by one unit For practical reasons however these costs are usually computed as the average cost of those inputs whose use varies directly with the production of each unit of output Defined this way the short run marginal and average variable costs are roughly the same For larger changes or changes that occur at the very limit of some capacity constraint the variable cost may include the cost of adding some inputs that have an economic life longer than a year The level of fixed costs on the other hand is distinguished by being independent of output The majority of the costs of most MWWUs are made up of fixed costs as opposed to variable costs This is certainly the case in short and intermediate time frame the t
17. KKR KKR 33 Selected Comparative Output TableS ssssssrssersrrrserrrsrrrrerrsrrrrrrrrr e estes KRA RK KAR RK KAR KKR 34 Selected Output Tables from the Output By Scenario WOorksheet rsssess 34 MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 5 1 INTRODUCTION The regulatory economic and institutional environments of municipal water and wastewater utilities MWWUs in Central and Eastern Europe CEE are undergoing substantial change Assets and service responsibilities have been or are being transferred from the central government to local levels in most instances and private participation in operation or ownership of MWWUs is an option in many countries Meanwhile operating and investment subsidies provided by the central government are reduced eliminated and or subject to new conditions while new sources of financing from external donors or development or commercial banks are more readily available MWWUs also have to adapt to new or redesigned regulations pertaining to water supply conditions water quality and effluent standards and charges fees and fines they must comply with revised and extended oversight of tariff setting tariff design and cost recovery and they now serve a market that is more uncertain and volatile than it used to be In short MWWUs are currently beset by a variety of interconnected technical economic and organizational challenges MWWUs respond to these new challenge
18. OOTING 9 1 Settings within Excel ASTEC was prepared in Excel 2000 and tested both in Excel 2000 subsequent versions of Microsoft Office In all of these softwares the model worked without problems In earlier versions of Excel however the model may not work properly If ASTEC does not run properly or does not run at all then try the following remedies gt Excel should be enabled to run macros You can do this at Tools Macro Safety choosing mid level safety Any time when the model is opened you will be asked if you would like to open the macros and you can say yes The changed macro safety setting will only take effect when ASTEC is opened up the next time gt Solver should be installed in Excel You can check this on any of the sheets at Tools Solver If the Solver window pops up then Solver is installed If it is not installed you can do it at Tools Add ins You may need the Excel installation disk for this gt A number of items need to be installed within the Visual Basic Editor of Excel Visual Basic is the programming language in which the ASTEC macros were written You can reach the Visual Basic Editor by hitting Alt F11 on any of the worksheets Then go to Tools References and check if the following items are marked or not t Visual Basic for Applications 2 Microsoft Excel 9 0 Object Library a different version number may appear for different versions of Excel 3 OLE Automation 4 Solver xls or Solver xla
19. UNDP I GEF 09000 DANUBE REGIONAL PROJECT May 2007 ACCOUNT SIMULATION FOR TARIFFS AND EFFLUENT CHARGES ASTEC MODEL oa B WORKING FOR THE DANUBE AND ITS PEOPLE Ji GEF AUTHORS PREPARED BY MAKK AUTHORS Glenn Morris Andras Kis UNDP I GEF e DANUBE REGIONAL PROJECT User s Guide to the ASTEC Model page 3 TABLE OF CONTENTS 1 TIVERO CHA GENOM rensar dnr rare barer o TN RE dan ENE RR RA TRE EA NR AK B R BARN RR RENA RNA 5 2 Technical REQUirSmMeNES s sm sissi sie ss ia ses Ne kk EAA RN DR KAR AR EK KR SN RANKAR eres 7 3 The Structure and Operation of the Model ssrssssserssseraserrsrrrrrrrrrrerrrnrrr rr rr RK KAR KRA RK K RR KKR RKA 8 3 1 The Logiccof the MOCCI snsissmes ansann inna REA ro or an TE a dented ARNE 8 3 2 Structure of the MO GI l 2 24s 5 esse ss se ora br r kastal E ARA fal Ro a RR SR FETA RA ER 8 3 3 Operation Of CHE Models ierann annain aiaa aA a a dentate eden Er TR 9 A JENLGRING Data nncciritrtsnmsceidtraeedunndrdadeecanecaaniaancie RNE TN NR ANTENNER RN NER 10 4 1 Controll SNC CE anestesi r atin nccsse ethan tussitess homie Ge SAS Sr ARS SES AR INS SAR 10 4 2 Scenario Sheets Definition of Service User Categories s ssssssssrssrrsrsrrrrrrrrrrrrrrrrrr rr na 11 4 2 1 Service User ACCOUNT Data issia seems esse ka oe ee Kra re Ko bab KORR ER aie 11 4 2 2 Water Use and Tariffs ob osssssiisssdssssa tiniani t a Sr te EE i NR RER 13 4 2 3 Wastewater Discharge and Tarif
20. Water and wastewater elasticity for the composite goods WSc service range S14 S22 The price elasticity of demand for composite water and wastewater services is the percentage change in the demand for a unit of this service as a result of a one percent increase in the sum of the variable water tariff and the corresponding variable wastewater tariff together For instance if consumption of a cubic meter of water results in 0 9 cubic meter of wastewater discharge then the composite service comprises of a cubic meter of water consumption and 0 9 cubic meter of wastewater discharge If the variable tariff of the water service is 1 EUR m and that of the wastewater service is also 1 EUR m then an increase of 0 1 EUR m of water service will result in 5 increase of the variable tariff of the composite service 0 1EUR 2EUR If the price elasticity of demand for the composite service is 0 2 then 5 increase in the variable tariff will result in 1 decline in consumption of water and a corresponding 1 decline in the discharge of wastewater 4 2 5 Value Added Tax VAT Under most VAT regimes the level of VAT will not enter business decisions The VAT component paid on business expenditures will simply offset the corresponding portion of VAT received from sales and any VAT balance paid to the central government will be just offset by the remaining VAT revenues received from sales Since water utilities in general are business entities and some
21. an be automatically displayed in EUR and USD The exchange rates supplied in Control range C22 C23 will be used to convert the results to these two currencies Please note that the monetary data supplied by the user to the scenario sheets should always be in local currency Scenario sheet data will not be converted to other currencies by ASTEC The user can however choose at the outset to make either the Euro or any other currency the local currency MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 33 Figure 27 Setting Exchange Rates and Choosing the Currency in which Results are Displayed Local currency xchange rate HUF EUR EUR USD xchange rate HUF USD Control spreadsheet range B28 F37 provides historic information about the scenarios As illustrated below these cells display the date and time of the last run of each scenario spreadsheet the time needed to run the scenario and whether satisfactory results were produced Results are usually identified as unsatisfactory when the set of equations describing the scenario cannot be solved Such a situation may occur when initial conditions are so disparate that Excel software cannot find an equilibrium In our case studies we have never encountered this problem However during experiments with ASTEC it was possible to create artificial data and equation sets which were impossible to solve Figure 28 Summary Table on Scenario Runs Details
22. are not even cost effective your system s particular circumstances 2 7 Fine Tuning Investment Programs and Tariff Policies One water system we working with is committed to building a new wastewater treatment plant WWTP but the best combination of design choices and complementary investments is not clear The cost and revenues of the WWTP depend on the capacity and extent of treatment of the plant other investments in the wastewater network and storm water holding basins as well as anticipated industrial pre treatment and industrial use of the WWTP While external financial assistance is available in a fixed amount the water system if responsible for covering the residual capital and operating costs We have helped this water system implement ASTEC and are assisting the water system in examining strategic choices regarding plant design complementary investments and tariff designs and levels 2 8 Choosing an Investment Strategy Some water systems aren t sure what their broad investment strategy should be because they have lots of options and no way to systematically evaluate them Implementing ASTEC can help these water systems not only choose among investments but assess whether the increased costs are commensurate with the improvement in services This later assessment cannot be done directly by ASTEC it does not evaluate improvements in service in a monetary measure that may be compared directly with the increase in cost Given water
23. ated in the SideCalc sheet and referenced from there to the scenario sheet Figure 21 Allocating Effluent Charge and Fine Related Costs 4 3 Effluent charges and fines paid to state regulatory authorities Name of pollutant mm Annual payment HUF year 12000000 lt Distribution of the payments among service user accounts Proportion Households apartments m iw Large enterprises Op Small enterprises 08f Tow E momy d The effluent charge and fine payments can be distributed among service user categories just as fixed wastewater costs You can select to use either proportion values that you provide exogenously or let the model determine distribution of the costs in proportion to wastewater discharge by selected groups of SUs The effluent charges and fines appear as costs to the water company The effluent charge component of tariffs can be linked to actual discharges by SUs to the sewer In this case the effluent charge costs would differ among SU categories and this can be reflected by adjusting the variable tariffs in each SU category 4 5 Scenario Sheets Modeling Tariff and Cost Recovery Options One of the useful features of ASTEC is that it can be instructed to calculate cost recovering tariffs In fact ASTEC offers a multitude of choices for achieving cost recovery gt Those service user categories for which cost recovery must be achieved can be selected individually Not all service us
24. ay be especially useful if you are not familiar with the English financial engineering and economic terms used in ASTEC The Control sheet is used for initiating commands such as running scenarios copying one scenario into another one and deleting scenario data Scenario sheets S1 to S10 contain scenario specific input data These sheets are to be filled in by the user While running a scenario ASTEC makes use of the data in these sheets After a scenario has been run results are displayed in two output sheets The Output Comparative sheet contains thematic tables with results from all 10 scenarios thereby making it easy to compare the results of different scenarios The Output By Scenario sheet contains most of the output of ASTEC one scenario at a time The output data for each scenario is presented via a set of tables In addition to the worksheets there is a Visual Basic program code attached to ASTEC and some of the computing algorithms are stored as instructions within the Solver tool of Excel 3 3 Operation of the Model After you have filled in a scenario sheet any of sheets S1 S10 then you initiate tasks from the Control sheet When you have clicked on any of the command buttons in the Control sheet the program code behind ASTEC will start running and the model will start working with data While running ASTEC makes use of some of the otherwise hidden worksheets It can only do so if these sheets are unhid temp
25. ayment varies with regulatory regimes In some countries the algorithm is very straightforward e g x EUR kg of BOD release In other locations complicated formulae are used taking into account a wide range of features like the absorbing capacity of the receiving water body or the available wastewater treatment technology ASTEC can be used to keep track of effluent charge or fine payments by major groups of pollutants but ASTEC is not designed to accommodate all the different possible charge and fine regimes The user will often have to take into account the particular structure of effluent charges and fines outside of ASTEC and develop an algorithm for transforming them into the simplified design provided in Section 4 3 Such computations can be done outside of ASTEC or on the SideCalc sheet of ASTEC Only the annual payment for each of the key pollutants should be entered in Section 4 3 MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 27 In this spirit in the hypothetical example in Figure 21 the annual payment after BOD releases is set at HUF 12 million This figure is calculated based on actual BOD effluent discharges and an effluent charge or fine regime If there is a change in either the regulations or the discharges then very likely a different annual payment would need to be inserted in the same cell The new figure would not be calculated by ASTEC automatically it would have to be entered manually or calcul
26. ce using ASTEC and the DRP Tariffs and Charges related reports and materials for additional descriptions and explanation These latter materials can be downloaded from the DRP website http www undp drp org drp activities_ 1 6 7 tariffs and _charges html http www undp drp org drp themes_municipal ws ww html Further inquiries or comments about ASTEC may be addressed to the developers Andras Kis at kis andras makk zpok hu or Glenn Morris at glennmorris bellsouth net MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 7 2 TECHNICAL REQUIREMENTS ASTEC 2 0 requires Microsoft Excel 2000 or a more recent version of Excel with Solver installed and Excel enabled to run macros Solver is installed if you can see it under the Tools menu of Excel If you cannot see it there then you need to install it by clicking on Tools Add ins and then selecting the Solver Add in In some versions of Excel you will need the original Microsoft Office installation disk to complete this process Macros can be enabled under Tools Macro Security Here you have three choices of security levels high medium and low High security will not let you run macros at any time therefore you will be unable to use ASTEC If you select medium security then when you open the ASTEC file you will always be prompted with the question if you would like to enable macros and you should select yes If you choose low security th
27. costs is a little simpler than the structure for fixed costs since variable costs do not need to be annualized The value of variable costs should be supplied as monetary unit m e g HRK m in Croatia The m refers to invoiced quantities since the annual variable costs are computed by ASTEC as the product of the unit cost you enter monetary unit m and the consumed quantities If a given cost item applies to a group of SUs then this has to be indicated by inserting 1 into the intersection of the service user group row and the cost column Just as for fixed costs any of the variable cost items can also be allocated to unbilled water In this section however the cost of unbilled water cannot be further allocated to service user categories because all fixed and variable costs associated with unbilled water are redistributed together in section 3 1 In other words the unbilled water fixed and variable costs are added together and get allocated based on the instructions provided in Section 3 1 MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 25 Figure 18 Variable Costs of Water Service 3 2 Variable costs and subsidies for water service 1 2 3 Cost or subsidy po deost Cost Value HUF m3 Application of the cost or subsidy to service user accounts Households apartments Individual houses water omly Individual houses www Large enteprises o Small enterprises Unbiled water my CC 4 4 3
28. different format The spreadsheet has 10 sections one for each scenario and thematic tables which usually include several variables for all the SU categories Below some of the thematic tables are shown for illustration Figure 30 Selected Output Tables from the Output By Scenario Worksheet Invoiced Actual Invoiced water wastewater wastewater Total invoiced Service user category use per account discharge per discharge per water use m year account account m year Invoiced Total actual wastewater wastewater discharge discharge 3 3 m year m year m year m year Individual houses water only f 97 00 0 00 oa y 0 Individual houses w amp ww 8 Large enterprises Unbilled water wastewater 30000000 Too 000 00 sooo 100000 roa 3 EE ras MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 35 Service user accounts and tariffs Number of i Fixed water accounts in the Variable water Service user category The service tariffper account service user tariff HUF m3 HUF year category ifffouseholds apartments J 2ssfwse ooo 25000 Individual houses water only 1121 Iw 250 00 Indivi 1 ndividual houses w amp ww 56 WSc 0 00 250 00 Large enterprises 14 WSi 0 00 450 00 Ss l Small enterprises E E 000 45000 Financial information on the two output spreadsheets excludes the Value Added Tax with two notable exceptions monthly water bills and monthly was
29. e 22 Figure 23 Figure 24 Figure 25 Figure 26 Figure 27 Figure 28 Figure 29 Figure 30 Error STON IM ASTEGC s d 6n enes son ses ss er nell daia anA Wanna she AINA i eid sete Eadan A AE RA je AN 10 Setting the Number of Service User Categories ssisssosssserrsssrrsrrrrrrrrrrr rss nets rk rr rr sR 11 Choosing the Currency Of Results ssssossssssserssrrrersrserrrerrserr renen eee tena eee KKR KRKA RK 11 Naming and Describing SCENarOS sssesssssserrrsrrrrrrrrrrrrrrrrrrkrrr KRA ARKA ARK KAR KKR tena enti 11 Specification Of Service USC ssssssssss isdans sssmesdsss sas see eka ere rr kr KR SERA beasts 13 Data on Water Use and Tariffs s ssssss sssssessmes ses v beses eee eee neta eee eats eae 13 Data on Wastewater Discharge and TariffS ssssssssssssrrsrrrrrrrarrrrrnrrrr rr ee rk rr RK KRKA na 14 Invoiced and Discharged Wastewater Quantities ssmolosrssssrssrrrsrrrrrrrrrrrrrrrrrrr ner rn r ra 15 Own Price Elasticity Of Demand sss sisereaossnsan sedns snonsnnessnanensenen eee daadaa 15 Setting VAT Information essens ss caste aaraa SMA m r let ee b aja je Ae TA ARTE a annie 17 Providing Information on Unpaid BillS ssssseesssrssserrrsrrrrrrrrrrrrrr nera rr teeta eaten teens 17 Connect Charges is iaiscisiecsine Saadicedd salad fed craic SAN EN O A Ra RE f BANER ER REA 18 Unbilled Water and WaSteWatCl sssissssessssrssesdsesseserenn nsnssrnssentene seen dana nianiar Sa 18 Unbilled but Treated WaSteWwatCri ssssserrrssesserrrrrrsnerrrrrrr
30. e a sample MWWU application such as ASTEC 2 0 Demo xls while reading the descriptions in this Users Guide The cost data within the model are organized around the service water or wastewater as well as the nature of the cost fixed cost or variable cost 2 The design and level of effluent charges 2 ASTEC 2 0 Demo xls is a simple demonstration version of ASTEC 2 0 This file contains data for a hypothetical water utility and four simple scenarios that illustrate use of the model for several common applications Specific ranges of the ASTEC 2 0 Demo xls are displayed at relevant sections of the User s Guide 3 The allocation of costs into fixed or variable components may be varied by user On logical grounds this determination should reflect a time frame and planning horizon consistent with the policies and investment program to be tested by the model UNDP GEF DANUBE REGIONAL PROJECT 1 Introduction page 6 assessed on the MWWU is another type of cost that may be included in the model ASTEC offers several methods for allocating each and every cost item among the SUs making it possible to estimate the cost of service provision for each customer group These may then be compared with the revenues to see if revenues are proportionate to costs for that SU While water and wastewater tariffs can be supplied to the model exogenously ASTEC can also be asked to compute tariffs that will just cover all costs This can be d
31. ear cash flows There are two broad strategies for financing large scale investments by 1 saving for a future expenditure or 2 taking out a loan and repaying the debt over multiple years The method of financing has an implication on the annual financing need If a company saves for a future investment then interest will accrue on its savings while taking out a debt will require the company to pay interest Assuming the same financing horizon the annual burden in the first case where a payment is made in anticipation of a future investment is lower In the latter case taking a loan in the present and paying principal and interest in the future the annual payment is higher You can specify the method of financing in row 34 by selecting future value payment for the former strategy or present value payment for the later strategy involving taking a loan Next the name of the cost item should be supplied in row 35 This should concisely describe the most important feature s of the fixed cost investment cost or grant The name can refer to the technology the geographical location or the service users served by the item For example Water treatment at well A or Pipeline to town B The amount of the cost or grant is to be entered in row 36 In case of annual expenditures like annual cost of labor the annual value should be entered If there is an investment financed through multi year cash flows then the full value o
32. ear rel B bn etic eevee lanes bn Ar BT Ae ER AEA Ae Rs Kr 28 4 5 2 Wastewater Tariffs cccccccecee cece eee eee nee a a ne ene nS EEO SESE EEE ES CESSES ESSE REESE 29 4 5 3 Full Cost RECOV GI arsina a ee Ae ESA KATA conan Eaa 30 5 Protection of the User Surface ccccccccecce cece eee eee ee eee RR KARA RR RR KKR RAR RR KKR RARE RR K SEES ESE EE SHEE ES 31 GO Operating ASteC ieia a adie bias whines lg bre re RR br EE a wha N Aa PESE ere melded EAA er Apa ARE dean 32 7 Output Table Ssss eaaa vie S ja era ar a ne bj f eli be kar B sana neha Qe So a AST Aa Ho SR ARA te 34 8 TipS on Effective USE sussdans anina N see Ane KR EN AR DAR ETE 36 9 TFOUBISSKHOOUNG wissiscccsene sar ae ade a stale er RARE BR BARE BR EE EE AE EE Har 37 9 1 Settings Within EXCe litsen svea gr tensive a arken karens ladd KA Sn n lar NR ASA AR BE SEA 37 9 2 Data Problems 00 cana sand nit rea fe Fer f aa ge tine die week swine diane aa S KORA E R r R 38 Annex 1s Glossary asiaani ee fa Hafa fare Kb Ef etei r r HAF r VE Ha blb HR EAA AN BFA EA EA EES k r a refer ou 39 Annex 2 Experience USING ASTIEC 2 as 04asaysnessnse due aia E a bra N AE 42 UNDP GEF DANUBE REGIONAL PROJECT page 4 LIST OF PICTURES AND GRAPHS Figure 1 Figure 2 Figure 3 Figure 4 Figure 5 Figure 6 Figure 7 Figure 8 Figure 9 Figure 10 Figure 11 Figure 12 Figure 13 Figure 14 Figure 15 Figure 16 Figure 17 Figure 18 Figure 19 Figure 20 Figure 21 Figur
33. eets starting at row 162 Section 5 1 sets cost recovery and tariff conditions for water service Section 5 2 sets cost recovery and tariff conditions for wastewater service and section 5 3 can be used to sets cost recovery and tariff conditions for the whole MWWU 4 5 1 Water Tariffs All of the service users that receive water service are listed in range B165 B179 As a first step each SU category needs to be assigned into exactly one tariff cluster by entering 1 at the cross section of the column of the cluster and the service user row If a SU category is not assigned to any cluster or it is assigned to more than one cluster an ERROR message will appear in column D The model will not run until this logical error is corrected All service user categories which belong to the same tariff cluster are subject to the same tariff design and tariff level A cluster may contain one or more SU categories but may also be left empty For instance if the first three SU categories should have the same tariff designs and tariff levels then they can all be assigned to Cluster 1 in column E by entering 1 into each of the cells in range E165 E167 while leaving range E168 E179 empty In an extreme example all SU categories can be assigned to one cluster and all other clusters are left empty At the other extreme each service user category may be assigned to a different cluster After SU categories have been assigned to clusters you can set the
34. either be left out of the description of the system entirely Grants and subsidies are handled by the cost entry sections Grants and subsidies are from a numerical point of view negative costs For each and every item in cost Sections 3 and 4 you need to indicate whether it is cost or grant subsidy except in Section 4 3 In section 4 3 covering effluent related expenditures only costs can be entered The user does this by moving the cursor to the head of each cost column selecting the toggle box and then designating the entry information as a cost or grant subsidy Grants or subsidies are monetary transfers to the MWWU that does not have to be repaid A grant is usually meant to support some investment project If the transfers need to be repaid then it is not a grant any more but a loan The loan should be designated as such i e as a cost item When MWWU costs are offset with a grant or a subsidy then both the costs and grants subsidies should be entered They both need to appear as cash flows of the MWWU the cost or expenditure as a negative cash flow the grant subsidy as a positive one In the instances when a cost or expenditure is financed through a loan then it is preferable that only the amortization of the loan appears as cost to the company in order to avoid double counting of expenditures Please note that all cost grant and subsidy data should be entered without VAT 4 4 1 Fixed Costs and One Time Grants for Water Serv
35. elling of multi year periods is also feasible in this case each year should be handled as a separate scenario UNDP GEF DANUBE REGIONAL PROJECT 4 Entering Data page 22 Figure 15 Fixed Water Service Costs 3 Costs of providing water service 3 1 Fixed costs and one time grants for water service Cate Hm Sis the basis for annual cost recovery Amount of the cost or grant HUF Number of years Cost of capital Annualized value HUF year 230 000 000 75000000 66 531 543 200 000 4 4 1 2 Distributing Costs Among Service User Categories In order to link the costs associated with provision of services to particular service user categories costs and also grants need to be distributed among the service user categories This takes place in the data columns of Section 3 1 right below the annualized cost grant values The figure below illustrates this portion of S 1 Section 3 1 from the sample version of ASTEC For each cost item in row 40 you first need to specify the method for distributing the costs ASTEC gives you two standard options gt Selecting Proportion from the drop down menu will result in a proportionate distribution of costs based on the ratios supplied in the cells directly below row 40 In these cells you determine what fraction of the total value should be allocated to any particular service user group Figures between 0 and 1 should be entered into the cells In a closed
36. en macros will automatically be enabled without asking you each time when you open up ASTEC However under this regime you may also be more vulnerable to a special breed of viruses which make use of macros We advise that you choose medium security and you enable macros when prompted after you open the ASTEC file The user surface of the ASTEC model is presently in English but ASTEC itself functions independently of the language of the Excel software Implementing ASTEC using older versions of Excel or on obsolete computers may result in long run times or it may not be possible at all If you are experiencing problems running the model please consult the section on troubleshooting at the end of this User s Guide The distributed model ASTEC 2 0 xIs does not contain any data and is a read only version When you enter data into it you need to save it under a different name This way you will always have a back up copy of the original data free model This protects the user who inadvertently modifies the model in the process of entering data UNDP GEF DANUBE REGIONAL PROJECT 3 The Structure and Operation of the Model page 8 3 THE STRUCTURE AND OPERATION OF THE MODEL 3 1 The Logic of the Model As introduced above the main organizing principles of ASTEC are groups of SUs annual revenue accounts and annual cost accounts of the MWWU You can define up to 15 SU categories These categories are selected on the basis of charac
37. er categories need to be selected for cost recovery so the MWWU as a whole may not recover all its costs or may have net revenues gt SU categories can be grouped to achieve cost recovery together i e all service users within such a cluster will have the same tariff design and tariff level and together they produce enough revenues to just cover the costs of providing service to them In this case also the MWWU as a whole may not recover all its costs or may have net revenues gt Water and wastewater services can be handled separately for cost recovery purposes gt Several tariff designs are available including the use of fixed tariffs UNDP GEF DANUBE REGIONAL PROJECT 4 Entering Data page 28 gt You can individually determine the tariff design and cost recovery method for each service user group cluster of service users including the option to have paying customers cover the costs associated with those customer water and wastewater accounts that go unpaid gt And lastly there are several choices to achieve cost recovery for the MWWU as a whole This feature is useful when cost recovery is not assured for all of the service user categories but the MWWU still needs to generate sufficient revenues to pay for all its costs Since the above features can also be combined with each other it is possible to test virtually hundreds of cost recovery designs Cost recovery options can be set in Section 5 of the scenario sh
38. es for the utility as a whole and for each service user category As a variable is changed that will have an impact on either revenues or costs and sometimes both of them ASTEC can also compute cost recovering tariffs under any particular cost or policy scenario 3 2 Structure of the Model When you open up the model you will see that you can select up to fifteen worksheets These are itemized and available for use on the worksheet toolbar on the bottom of the Excel screen The model also includes a number of hidden worksheets containing ASTEC programming and side calculations Starting from the left side of the worksheets toolbar on the bottom of the Excel page we have the following sheets The SideCalc sheet is an empty sheet in which you can carry out side calculations You can use it as a notepad and you can store information there Data from this worksheet does not Each version of ASTEC has several spreadsheets as described below One group of spreadsheets holds scenario data and these are labeled S1 S2 etc MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 9 automatically get used for actual model calculations but data in Sidecalc can be referenced from cells on scenarios worksheets sheets S1 through S10 The Glossary sheet is also a worksheet which is not directly used by the model It has a list of the various terms used in conjunction with the model and offers explanations for each one It m
39. f the investment is to be entered and ASTEC will annualize the cost based on the term of the loan or saving horizon and interest rate The term of the transaction in years is entered in row 37 In the instance of annual cost items the user should enter 1 in this row When a one time cost is spread through several years then the duration of financing is to be supplied e g an investment is financed through a 5 year loan then 5 is entered The cost of capital or interest rate should be supplied in row 38 when a cost item is financed through a multi year loan or multi year savings Otherwise the cell can be left empty Depending on the method of financing either the real interest rate paid on savings or the real interest rate on the loan should be supplied The real interest rate r is defined as the nominal rate of return n corrected for the rate of inflation i Because of compounding the real rate should be calculated as r n i 1 i where the terms are the decimal equivalents of the percentage rates Using the above described data ASTEC will calculate an annualized value of the savings or the debt which appears in row 39 These figures will then be further by ASTEC to compute annual expenditures revenues and financial balances As a check on data entry and data interpretation the user should examine row 39 after entering the fixed cost data to see that this value makes sense given the nature of the fixed cost item 10 Mod
40. fS sssssssesrsserssrrrsrrrrrrrrrrrrrrrrrrr nera nr KRK KKR KKR KRKA 14 4 2 4 Own Price Elasticity of Demand sssmssvssssseessss beses see rar a RER 15 4 2 5 Value Added Tax VAT siiicctesncnttcctascent seeiisbea cidatieeleazanis catiees an 16 4 2 6 Unpaid Bills rescon re bag Ne NG frans dead Sa KS SR j ts a santana IR JEN es 17 4 2 7 Connect Chargesins rsrsr beta Eee SR Te ERE f ARA ERK AT BIRTE a AE Ar Ar RAR 17 4 3 Scenario Sheets Unbilled Water and WaSteWwatCri sssssererrrssrsserrrrrrsrerrrrrr rss rr rna 18 4 3 1 Unbilled Water f dda een ser rna l r NR ae AA EA AE AR Ae ARE RR tumuli EA REAR EA 18 4 3 2 Unbilled but Treated WaSteWwatCr sssssssrerrsrsserrrerrrsrrrerrrrrrr nns tee ene rss rr rr RR Krk AR 18 4 4 Scenario Sheets Cost Data iuzm ssss issues sees ust a atten Meet ete oneal AKTRA 19 4 4 1 Fixed Costs and One Time Grants for Water Service sssisseserrrsrrererrrrrnerrrrr eee 20 4 4 2 Variable Costs and Subsidies for Water Service ssmessssssresrrsssrrrrrrrrrrr rr rrrr rn nera 24 4 4 3 Fixed Costs and One Time Grants for Wastewater ServiCe cccceeeeeeeee teenies 25 4 4 4 Variable Costs and Subsidies for Wastewater ServiCC ssssssererrrssrsrerrrrrrrnnrrrr 26 4 4 5 Effluent Charges and Fine S cc ccscescseesseeeceeeeeeteaeeesneeneceseeeusseetaeneneensaenens 26 4 5 Scenario Sheets Modeling Tariff and Cost Recovery Options s ssssersrserssorrsrrrrrrrrra 27 4 5 1 Water Tarif sii tsicsaciusanesl D
41. ffs Error display WSc W WSc arge enterprises WSi mall enterprises WSc Select tariff design New fixed tariff HUF year New variable tariff HUF m3 Recovery of the costs of non payers Error display po T S O T fk 4 5 2 Wastewater Tariffs In section 5 2 starting in row 187 wastewater tariff designs and tariff levels can be specified in the exact same way as in section 5 1 for water Figure 23 Setting Water Tariff Designs and Levels 5 2 Wastewater tariffs Individual houses w amp ww Large enterprises Small enterprises Select tariff design New fixed tariff year New variable tariff m3 Recovery of the costs of non payers Error display Ee E e a e UNDP GEF DANUBE REGIONAL PROJECT 4 Entering Data page 30 4 5 3 Full Cost Recovery If you use original or newly supplied tariffs for any of the clusters without requiring ASTEC to compute cost recovering tariffs then full cost recovery for the whole utility is not assured despite requiring cost recovery for some tariff clusters of service users Even in cases when full cost recovery is required for all clusters but the costs of non paying customers are excluded the system as a whole may not break even financially Therefore section 5 3 offers an option to achieve system wide full cost recovery by uniformly adjusting tariffs until all annual costs are recovered by annual revenues Algorithmically ASTEC will first compu
42. fs should recover variable costs but full cost recovery of both fixed and variable costs is not required This scenario may be useful for experimentation with tariff designs but the chances for its actual adoption are low given political constraints In row 183 you can require that the costs of non paying customers be recovered by the revenues obtained from those customers within the same tariff cluster who do pay This feature only applies to tariff designs C D and E since in tariff designs A and B tariffs are set exogenously by the user If a mistake has been made during the specification of tariff designs and levels then an ERROR sign will appear for the cluster in row 184 Any of three typical mistakes will prompt the error message 1 If new fixed and variable tariffs are supplied when they should not be since they are going to be computed by ASTEC or if they are not supplied when they should be 2 When A Original tariffs is selected for tariff design but more than one service user category appears in the tariff cluster In this case the set tariffs of the service user categories may be different but the tariff cluster assignment says that the user wants them to have the same tariffs 3 When at least one service user group is part of a tariff cluster but no tariff design has been selected for the cluster Figure 22 Setting Water Tariff Designs and Levels 5 Modelling tariff and cost recovery options 5 1 Water tari
43. hange in classification had an immediate impact on system revenues and the tariff scenarios developed as part of the ASTEC based analysis Once again the process of using ASTEC in this case to explore system up grades produced information that was news to policy makers and of direct value to the water system 3 WHAT DOES ASTEC COST The direct but misleading answer is nothing The ASTEC software is as an application of the Microsoft Excel spreadsheet system and associated macro tools If one has a computer with a recent version of Excel installed with the Solver macro one can download and use ASTEC and its multiple spreadsheets free of charge On the other hand it is expensive to introduce a new water system in ASTEC The ASTEC model requires substantial data input familiarity with the way excel software works and training in some economics and finance principles to assure proper implementation of scenarios and interpretation of results The data inputs as described above are rarely available without a coordinated and concerted effort on the part of the accounting engineering and finance sections of a water system While some data may be right at hand other data must be gathered developed or estimated While as noted above this process often has benefits in its own right it is often arduous demanding reconciliation of conflicting information as well as development of new information for use in ASTEC The
44. hat you want to use These entries will make sure that the proper currency units are displayed in the scenario and output sheets Then select the currency in which you want to show results in ASTEC you can chose to show results in EUR and USD as well as in the local currency A sample of these entries and toggle buttons is shown below Figure 3 Choosing the Currency of Results Local currency xchange rate HUF EUR EUR USD xchange rate HUF USD 200 4 2 Scenario Sheets Definition of Service User Categories The Scenario sheers are the sheets named S1 to S10 Before supplying detailed data on service users costs and tariff designs you should fill in the name of the scenario and a short description in Cells C5 and C6 of the scenario sheet you are working in A sample entry is shown below Figure 4 Naming and Describing Scenarios 1 Scenario Identification Scenario name Baseline Hypothetical case Loss making water company Scenario description 4 2 1 Service User Account Data Once the number of service user categories has been set in the Control sheet Section 2 of all ten Scenario sheets will include a matrix with this number of rows You as user can fill in this matrix gt What actually happens is that the rows of unused service user categories are hidden ASTEC will assume that all raw monetary entries are in the local currency The user can of course choose to make the Euro or US Dollar
45. iable water tariff triggers a 0 2 percent reduction in water consumption relative to the initial level Service users will alter their consumption according to the price elasticity of demand as a result of a marginal change in price only usually a change in the variable tariff of the last unit of water purchased A change in the fixed tariff will not usually trigger the same kind of behavioral response since changing consumption will not alter the payment of the fixed tariff A change in the fixed tariff will result in an income effect only which may eventually have minor implications on water consumption but this effect is not modeled Figure 9 Own Price Elasticity of Demand Elasticity of Elasticity of Elasticity of Service user category demand for demand for demand for water wastewater both services UNDP GEF DANUBE REGIONAL PROJECT 4 Entering Data page 16 The actual price elasticity values entered and the location of entry in ASTEC should vary with the services and their pricing The entry options are summarized in the following table Type of service Elasticity to be supplied Water service only W Water elasticity range Q14 Q22 Wastewater service only S Wastewater elasticity range R14 R22 Water and wastewater services provided independently Water elasticity wastewater elasticity ranges from each other WSi Q14 Q22 and R14 R22 Water and wastewater services provided as composite
46. ice 4 4 1 1 Entering Fixed Cost Items Up to 100 cost items identified by the user as fixed costs can be entered in Section 3 1 This section is illustrated below The first column provides a heading description for each of the row entries The next column is reserved for adding information on distributing the costs assigned to unbilled water among service user categories This is discussed in more detail in 0 The rest of the columns of the Section can be filled in by the user with data pertaining to actual cost and grant items Accounting systems sometimes contain hundreds of cost headings For use in ASTEC some aggregation is needed For each cost item the information detailed below needs to be supplied In row 33 you need to specify as discussed above the nature of the item whether it is cost or grant Grants will be handled by the model as negative costs They should be entered however as positive values just like costs MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 21 The user enters the basis for annualization in row 34 The time period represented by ASTEC scenarios is one financial year which for most MWWUs is equivalent to one calendar year The natural period for some of the fixed costs is also one year e g labor costs rental of office space outsourced services However there are some major cost items which should be spread over several years especially investments financed through multi y
47. ill MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 39 ANNEX 1 GLOSSARY One of the ASTEC spreadsheets is the Glossary The glossary provides definitions descriptions and background on many of the specialized technical or financial terms used in ASTEC or in supporting documentation like this Users Guide The glossary can be accessed when ASTEC is open but here we provide another source for the ASTEC Glossary Term Composite goods Invoice Connect Charge Fixed Charge Elasticity of Demand for Water and Wastewater Service Fixed Costs Explanation The term composite good as used here refers to the situation when one good or service is billed on the basis of the amount of another good or service consumed In the most common case encountered the amount of wastewater service provided to an individual customer is estimated based on the amount of domestic water consumed The two goods may or may not have different prices This method of billing the non measured good or service is imperfect but may be economically efficient if 1 the consumption of one good is highly correlated with the consumption of the other and 2 measuring the consumption of the individual goods is relatively expensive Usually water and wastewater service satisfy these two conditions An invoice sometimes also called a bill is a document sent from the municipal water and wastewater utility MWWU to each customer This
48. ime horizon within which most analysis carried out with ASTEC is undertaken ASTEC also handles water and wastewater related costs separately in order to track the consequences of changes in the structure and level of costs and also to be able to compute different cost recovering tariffs for each of the two services In line with this discussion ASTEC costs are to be entered by you into four separate sections within Sections 3 and 4 of the S1 to S10 scenario spreadsheets of ASTEC gt 3 1 fixed water costs gt 3 2 variable water costs gt 4 1 fixed wastewater costs and gt 4 2 variable wastewater costs UNDP GEF DANUBE REGIONAL PROJECT 4 Entering Data page 20 In addition to the four listed items there is another section of the ASTEC scenario spreadsheet 4 3 Effluent charges and fines paid to state regulatory authorities that addresses costs related to variations in discharged wastewater quality Essentially this is a wastewater cost category but a separate section was needed because these costs vary with the quality and amount of wastewater discharged based on regulatory mandates not the cost of treatment All costs associated with provision of water and wastewater service need to be entered somewhere in ASTEC These entries can be in any of the five sections just described Costs related to other services such as external construction projects maintenance of assets which are not part of the water system etc can
49. in that we ask how many more units of various inputs will be needed to increase output by one unit This is usually computed for small changes in output in the vicinity of the current level of aggregate output For larger changes or changes that occur at the very limit of some capacity constraint the variable cost may include the cost of adding some inputs that have an economic life longer than a year The variable tariff results in an invoice that varies with the level or estimated or measured consumption of water or wastewater service It is the charge per unit of consumption and is therefore denominated in monetary units per physical unit e g m The variable water tariff as set by the MWWU sometimes varies by the type of customer the season of the year or some other criterion selected by the MWWU UNDP GEF DANUBE REGIONAL PROJECT Annex 2 page 42 ANNEX 2 EXPERIENCE USING ASTEC This annex is a copy of a project information document that briefly describes the history of ASTEC and describes some of our experiences using the model In the process we introduce the reader to the various uses of ASTEC some of the most valuable of which are incidental to model application rather than the model results per se 1 THE ORIGINS OF ASTEC As we met with country experts and municipal water officials during our study of tariff and effluent charge reforms these experts recounted many common problems high levels of system water los
50. incipal and interest that must be paid in order to retire the loan over the planning period The annual payment is higher the shorter the planning period the larger the loan and the higher the interest rate of charge to finance the loan and is treated by the model as a cost of operating the water or wastewater system The real interest rate r is defined as the nominal rate of return n corrected for the rate of inflation i Because of compounding the real rate should be calculated as r n i 1 i where the terms are the decimal equivalents of the percentage rates MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model Term Tariff Service User Unbilled Water Variable Costs Variable Tariff page 41 Explanation Tariff is the price of water and wastewater services A category of service users defined for modeling purposes Service users belonging to one service user group are not identical but they have a lot in common e g they consists of only household apartment accounts with approximately the same level of water use The difference between produced and billed water Unbilled water may take a variety of forms such as leaked water from water pipelines consumed but not invoiced water own water use of the MWWU for instance to flood the pipelines in order to keep them clean stolen water The variable cost is that cost which varies with the amount of service provided Itis a marginal concept
51. is to be supplied in the next column cells J12 326 gt The actual invoiced discharge whether supplied by you or computed by ASTEC will appear in cells K12 K26 gt Fixed wastewater tariff per account Fixed wastewater tariff or service charge excluding VAT that the individual account will have to pay regardless of the volume of wastewater discharged gt Variable wastewater tariff The price imposed on the discharge of 1 m of wastewater excluding VAT gt Wastewater use to which the customer is entitled in for paying the fixed tariff This is the first few cubic meters of discharged wastewater for which the service user does not have to pay variable wastewater tariff This entitlement is related to either the fixed water or the fixed wastewater tariff In the first case only the fixed wastewater tariff variable does not have to be filled in Figure 7 Data on Wastewater Discharge and Tariffs A i Wastewater use Invoiced Invoiced Fixed i Invoiced Variable to which the wastewater wastewater wastewater 5 5 discharge per wastewater user is entitled Service user category discharge per discharge as tariff per ccou t enh account cette tariff for paying the p HUF m3 fixed tariff m year water use m year HUF year m3 year Houscholds apartments OO o o gaf Tao Individual houses water omly do ooo of _ Individual houses w amp eww __ on o 140 Large enterprises no f 12 000
52. le to make a reasonable decision on the economic merit of that investment One can also compare for instance the relative merit of leakage reduction in two settings e g a suburb with many individual houses spread over a large area and a more densely populated area with large apartment blocks UNDP GEF DANUBE REGIONAL PROJECT 4 Entering Data page 24 Figure 17 Redistribution of Costs Related to Unbilled Water Cost item 1 Service user category 1 Cost item 2 Service user category 2 Cost item 3 Service user category 3 Cost item n Service user category n E Unbilled water 4 4 2 Variable Costs and Subsidies for Water Service Variable costs are again those costs that vary directly with the output of the MWWU where output is usually defined in the m of water produced or treated The user supplies variable costs to ASTEC in Section 3 2 of scenario sheet S1 starting at row 61 An example is provided in the figure below These variable water cost data blocks are similar in design to fixed water costs data entry Up to 100 cost subsidy items can be entered As in the case of fixed costs the user first defines the nature of the item cost or subsidy then describes its name and value and lastly determines which SUs use is responsible for imposing this cost item on the MWWU At the same time the structure for entering variable
53. nd investment programs to maintain repair and replace plant and equipment Given these baseline data ASTEC can be used to evaluate a system s financial sustainability Moreover if the water system does not appear to be sustainable ASTEC can also be used to examine what tariff and related policy reforms can assist in transforming that water system into one that is ASTEC can approach achieving financial sustainable from several perspectives individually or in combination gt What tariff levels will balance system costs and revenues at current service levels gt What invoice recovery strategies will increase revenues gt Are there cost saving investments that can be made In the water system In wastewater system gt Can operating costs be reduced without impairing services These questions suggest some of the capabilities of ASTEC It can take account of simultaneous changes in the water tariffs and water consumption or of improved collection rates and an investment in leakage reduction in the water network or of an investment activity that is also labor saving Whatever the policies under consideration if they can be described by changes in system tariffs and costs and in the case of investment costs linked to the physical performance of the system ASTEC will compute a new system financial balance and in so doing estimate how close to sustainability the system will be with the new program and policies 2 3 We Just Lost Hu
54. ndreds of Customers Translating existing system data billing data accounting data and technical data into an ASTEC spreadsheet has invariably provided a useful cross check on these data One water system that UNDP GEF DANUBE REGIONAL PROJECT Annex 2 page 44 implemented ASTEC discovered that they had hundreds fewer customers than they thought It tuned out that customer account data were not regularly purged when customers moved or dropped off the water system so hundreds of ghost customers remained in the billing data of the water system This discrepancy was discovered because ASTEC s method of independently computing baseline conditions showed higher consumption and revenue than in the water system s annual financial report Our experience is that ASTEC implementation often leads to the discovery of a wide variety of data and policy anomalies in a water system Problems of cost allocation customer billing tax payments and payment avoidance that weren t known or full appreciated before hand by the system operators and managers or by consultants like us have been identified or highlighted simply as a result of efforts to implement a current account baseline using ASTEC 2 4 That s Not What We Mean by Amortization In several water systems we have worked with the amortization charge in their accounts was not linked to repayment of the principle and interest on a commercial loan the common usage in English
55. nother by inserting the number of the source scenario spreadsheet into cell C10 of the Control worksheet and the target scenario spreadsheet number into cell D10 and hitting the Copy button This feature is useful when you would like to create a new scenario by modifying an existing scenario Fragment of the Control spreadsheet including this feature and other ASTEC features are shown below Figure 24 Copying Data from One Scenario to the Other From To Copy scenario data scenario scenario 3 Likewise you can delete scenario data by inserting its number into cell C13 of the Control spreadsheet and hitting the Delete button This is quicker and less troublesome than manually deleting the content of the cells on a given scenario sheet Be aware however that the results of previous runs of this scenario will also be deleted from the output sheets Figure 25 Deleting Data from a Scenario Delete scenario data J Delete If you wish to run a scenario insert its number into cell C17 of the Control spreadsheet and hit the Run button ASTEC will only be run if proper data and instructions have been supplied on the scenario sheet If there is an ERROR sign in Cell D217 of the scenario sheet then ASTEC will give you a signal and refuse running since erroneous data will produce misleading outputs or it may cause a break down in ASTEC Figure 26 Running a Scenario In addition to the local currency results of the scenarios c
56. nrrrrrrr rr rr rss rr RR KRKA RR RR Rea 19 Fixed Water Service COS US sssasssnnipmraues ses canine eetietiens eaptieleniais eder ANNE 22 Allocating Fixed Water Service COStS sssssrsserssrrsrsrrrrrsrrrrnrrrrerrr nera rr rr rn KKR RK KRKA NA 23 Redistribution of Costs Related to Unbilled WatCr sssssrsssersssrrsserrsrrrrrrrrrrrrrr arr rna 24 Variable Costs of Water ServiCe cccscccssccsesetanecseceseaeeceeeaeeceacastacerseeetaeeeseeane 25 Providing and Allocating Fixed Wastewater Service COSts cceseeseeeeeeeeeeeeenees 25 Providing and Allocating Variable Wastewater Service COStS srrsssrserrrsrrsnrrsr 26 Allocating Effluent Charge and Fine Related COStS ssssssssssersssrrsserrsrrrrrrrrrrrrrr rr rna 27 Setting Water Tariff Designs and LevelS c eceeeee eee ee tees eee rss rr rr nr rr RK KAKA 29 Setting Water Tariff Designs and LeVClS ssissssrrssrrsserrasrerrsrrrrrr rer rr rrr rr rr rr rr RK KAKA 29 Copying Data from One Scenario to the Other sssessrrsserssrrrrrrrrrrrrrrr rss nr rr rn kr rn rss a 32 Deleting Data from a Scenari sss sasss ters ssd ai send bref as BA da Ffa Rn NR 32 RUNNING a SCENANMO sacrae ana RINNA ao ir ER bn JE BR Daia Kaa ja KR Kon rn MARE NR tense 32 Setting Exchange Rates and Choosing the Currency in which Results are Displayed esmai a ra R AE AA KR BYE AE AVE SR deeded day RE f AE AN RER RR AR AS 33 Summary Table on Scenario RUNS ssssstssssrrssrrsrrrerrrsrrrrrrrrnrrrr rr rr rr KR RR KKR RR KRA RK
57. nual payment is higher the shorter the planning period the larger the future investment and the lower the interest rate paid on the amounts deposited in the bank account As used here the term independent goods is used in contrast to composite goods The measurement of consumption and payment for these goods is done completely independently of one another In this context this may be the case for water and wastewater services when the wastewater produced by the user is sufficiently large and or of different enough quality to justify direct metering and or monitoring Municipal Water and Wastewater Utility This may also be a regional water system that serves several communities The MWWU is responsible for providing treated water to customers in municipalities or communities for domestic commercial and some industrial purposes It is frequently also responsible for collecting and treating sewerage produced by its water customers This feature is used to set aside funds to cover the principal and interest on a loan that has been taken out in the past or is expected to be taken out during the planning period of this scenario to make in investment in a water or wastewater system Such an investment might be replacement of part of the water network or new wastewater pumps The present value payment is the initial amount of the loan usually at the beginning of a planning period Fora present value payment ASTEC computes the annual pr
58. of the last run a ae eS Senne pa ine on a results S1 S2 S3 S4 S5 S6 S7 S8 S9 S10 UNDP GEF DANUBE REGIONAL PROJECT 7 Output Tables page 34 7 OUTPUT TABLES ASTEC displays the same output information in two ways in two separate spreadsheets the Output Comparative and Output by Scenario spreadsheets The Output Comparative worksheet has 33 tables each containing the values of one variable for all service user categories and all scenarios This sheet makes comparison of results from different scenario spreadsheets much easier Below are two tables as examples Figure 29 Selected Comparative Output Tables Invoiced water use and unbilled water m year Service user category St S23 I 2 Individual houses water only 3 Individual houses w amp ww Total billed water 1548 1s 1476230 _1455642 1 459200 Umbilied water 300000 300000 300 000 300000 roat water 1 sas ors 1776230 1755642 1759200 Financial balance both services together HUF year Service usercategory 5 e s s s TjHouscholds apartments 284036400 saoo TAB 0 Pfindividual houses water only 1894221 f of 0 3 individual houses w amp ww 20587914 874003 ranas of Large enterprises 12077 570 6266 131 ssor oA S Small enterprises 13155092 6266131 eorn of __ FR Ci sss CSCC The other worksheet Output By Scenario provides roughly the same information organized in a
59. of unpaid water and wastewater bills for each SU category If bills are fully paid then these cells should be left empty or zero should be entered These values will be used by the model when revenue calculation takes place Figure 11 Providing Information on Unpaid Bills Percentage of unpaid wastewater bills Percentage of Service user category unpaid water bills 4 2 7 Connect Charges Many of the water utilities require payment of a connect charge after connecting customers to the water and wastewater networks The connect charge is a one time payment and related revenues usually do not modify the financial balance substantially However there may be years when lots of new connections are created and it makes sense to keep track of revenues from connection charges as a separate item In range Y12 AB26 the user can enter the number of accounts paying connect charges for each service user category and the average value of the charge per account All connect charges are assumed by ASTEC to be paid in full even if there are unpaid bills within the SU category VAT should be excluded when these values are entered UNDP GEF DANUBE REGIONAL PROJECT 4 Entering Data page 18 Figure 12 Connect Charges Number of Average accounts Average connect charge paying connect connect charge per account charge for per account HUF year wastewater HUF year service Number of accounts Service user category paying connect charge
60. oid this ASTEC asks the user to allocate the costs associated with unbilled water among service user accounts as described in section 4 4 below 4 3 2 Unbilled but Treated Wastewater In a similar fashion to unbilled water in most systems there is unbilled but collected and treated wastewater water that infiltrates the sewers and storm water may fall in this category Infiltration is underground water that enters through cracks or gaps in the wastewater pipelines Storm water is rain snow melt or other surface water runoff that is collected by the sewer network when the sewer system also serves as the urban water runoff collection system Such combined storm and wastewater systems are the norm Within the Danube River Basin there are only a handful of MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 19 communities with extended separated storm water collection systems Some of the unbilled but treated wastewater may be the result of illegal or undocumented connections Figure 14 Unbilled but Treated Wastewater Unbilled but treated wastewater 100 000 Baseline wastewater collected 1 491 681 Sometimes the municipality pays for some of the storm water collection and treatment other times there is a special storm water charge paid by service users but quite often storm water collection does not generate additional revenues for the utility Unbilled but treated wastewater is handled by ASTE
61. om the SU category residential apartment blocks to individually metered apartments in residential apartment blocks It might also result in a reduction in the estimated average use of the residential apartment block category The service column allows the user to specify whether a particular SU category uses only water service W only wastewater Sewage service S or both services in a composite way i e they cannot be decoupled from each other WSc and both services independently from each other WSi In the latter case the two services are not linked wastewater discharge is determined independently of metered water use and the service user can choose to abandon one service and switch to an alternative provision e g self supply without having to give up the other service 7 In case of 15 service user categories For a lower number of groups a smaller matrix will appear There are some commonly applied methods for sharing the water bill such as based on the number of residents in the apartments or the size of the apartments m or number of rooms MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 13 Figure 5 Specification of Service Use 2 Specification of service users 2 1 Service User Account Data Number of accounts in the Service user category A The service service user category 4 2 2 Water Use and Tariffs In the next group of columns of Section
62. one for customers as a whole or for each service user group Furthermore the tariffs computed can reflect a variety of designs e g either a simple variable tariff or commodity charge or as multipart tariff with a fixed charge component While computing new tariff levels ASTEC simultaneously calculates new levels of consumption as customers react to tariff changes These features make it possible to investigate the consequences of different operating and development strategies on tariffs and physical and financial accounts It is possible for instance to investigate what happens if cross financing between industrial and household consumers is ended to estimate the tariff consequences of a new investment with and without supporting grants or to identify the most cost effective strategy for dealing with a newly introduced effluent charge regulation Experience shows that ASTEC is a powerful tool for MWWU managers municipal decision makers and policy makers alike Between 2004 and 2006 it has been applied in seven MWWUs in seven countries of the Danube River Basin In two demonstration projects one in Karlovac Croatia and the other in Pitesti Romania ASTEC was used as a planning tool for examination of new investments cost saving measures and a variety of tariff and tariff related policy reforms In the first phase of the DRP the original ASTEC model was developed Working with actual water systems as part of Phase 1 case studies pr
63. ooks or billing database of the company In case of household customers if households are individually billed then the number of accounts will usually be equal to the number of households In other cases big apartment buildings with multiple households appear as one account for the company This is usually the situation when there is only one meter for the whole building and residents share payment of the bill based on some predetermined formula You should be aware of situations when there are multiple apartments behind one account and accordingly adjust any calculations of the burden of the water and wastewater bills falling on an individual household In still other cases an apartment building has only one common water meter consumption is broken down to apartments already within the water company and bills are issued for each apartment individually according to some rough allocation criteria for water use e g the number of residents in each apartment The number of accounts may change with scenarios For instance if you introduce metering for individual households of big apartment buildings then the total number of your accounts will increase while average consumption per account will decrease In this case you might consider adding another SU category e g individually metered apartments in big apartment blocks In this case the policy of metering individual apartments in apartment blocks will result in shifts in the number of accounts fr
64. orarily If operation of the model is interrupted by the user or by an error then the otherwise hidden sheets are also displayed They will however be hidden again after the next successful run of the model After the model successfully finishes running the results of the scenario will be written in the output sheets and can be examined there Some of the scenarios will take only a minute or two to run Other more complex scenarios may require a lot of computing time depending also on the speed of the processor of the computer The most complicated scenarios on a slower computer may take 15 20 minutes to run UNDP GEF DANUBE REGIONAL PROJECT 4 Entering Data page 10 4 ENTERING DATA You can enter data into the Control sheet and the Scenario sheets In these sheets data for use by the model should be entered into colored cells Some of the non colored white cells are protected from modification by the user In others you can enter information but they will have no impact on the operation of the model The user must be careful to enter the correct data as well as to enter data correctly This discussion describes the assumptions ASTEC makes about the basis and units of the data entered Obtaining the correct data to enter any cost entry may require some calculation adjustment or calibration on the part of the user For this purpose and to keep a record of the data used and its modification ASTEC also includes an extra spreadshee
65. ovided many concrete tests of the model and insight into how the model could be further developed to address a greater range of policies become more user friendly and less prone to user software and data errors During Phase 2 of the DRP we kept developing and applying ASTEC The latest version is called ASTEC 2 0 and the present User s Guide describes this version of ASTEC ASTEC was developed by Glenn Morris and Andras Kis within the DRP sponsored by the Global Environmental Facility GEF and the United Nations Development Program UNDP ASTEC is public domain software and DRP or the developers do not charge for its use At the same time however its developers and sponsors do not warrantee the software or promise to support the software beyond Project related applications in which they are involved While ASTEC is relatively easy for someone with spreadsheet skills to understand and run experience has shown that it is easy to make mistakes in an ASTEC application if the user does not entirely understand the operational and economic concepts built into ASTEC It is for this reason that we urge training and supervision in its initial use The ASTEC model builds on economic concepts and adopts terminology described in this User s Guide and in the spreadsheet comments included in the model itself These descriptions however are meant as reminders and to refresh memory We encourage prospective users to both consult those who have experien
66. riff system consumption is broken down to blocks and each block has a separate variable price per cubic meter Block tariffs can be represented in ASTEC if service users are categorized according to their present blocks and all consumption preceding the present block is considered as part of the fixed tariff As an illustration assume a tariff design in which there is a fixed tariff of 10 EUR year plus a variable tariff of 1 EUR m for the first 20 m in a given year then 1 2 EUR m for all subsequent consumption In this case service users who consume more than 20 m in a year will fall in the 1 2 EUR m variable tariff category and their fixed tariff in ASTEC should be 10 20 1 30 EUR year in exchange for which they are entitled for 20 m of water consumption All additional water consumption will be computed with a price of 1 2 EUR m by the model 4 2 3 Wastewater Discharge and Tariffs In the next group of columns of Section 2 ASTEC asks you to specify wastewater use and tariff features of the SU categories for the MWWU being modeled The columns and sample entries with light blue background are as follows gt Invoiced wastewater discharge per account Values are in m year This variable should only be filled in if water and wastewater services are defined as independent from each other see 4 2 1 above gt In all other cases invoiced wastewater discharge will be computed by the model as a percentage of invoiced water use which
67. rrected before the model will be ready to run MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 31 5 PROTECTION OF THE USER SURFACE ASTEC is an application using Microsoft Excel Excel offers many user options a wide variety data types can be entered into cells rows columns and worksheets can be inserted and deleted and commands can be initiated from the menu bar or with specific combinations of keys etc While all this is very handy for the use of Excel it is also a potential source of unintended problems for use of ASTEC For example entering the wrong data type in a cell e g alphabetical text instead of a number may disrupt ASTEC computations In order to avoid such mistakes the type of data that can be entered is restricted for all cells Furthermore some cells containing formulae are protected altogether you cannot change them Rows and columns are also protected they cannot be inserted or deleted on a worksheet In addition there are several hidden worksheets on which calculations are carried out but which are not displayed for reasons of security as well as to avoid overwhelming the user with unnecessary or intermediate information Some of these worksheets are unhidden during the run of ASTEC because certain tasks cannot be carried out on hidden Excel spreadsheets but they are hidden again once ASTEC is finished running If running of ASTEC is interrupted then these sheets will be on di
68. s out of date equipment and technology difficulty of raising capital to support investment changing or uncertain ownership shifting economic regulation At the same time however there were substantial differences from place to place and country to country Perhaps most importantly we commonly encountered different contentions regarding prevalent financial and operating conditions water systems had an operating surplus or were insolvent operators were being squeezed by municipal owners or taking advantage of the new owners owners were approving tariffs that were too high or were not approving the tariffs increases needed to put the water system on solid long term footing Of course all this may be true depending on ones perspective as well as on the particular water system or institutional setting What was clear is that both our Project and the policy makers in the region needed some systematic way of evaluating the current status of individual water systems one that could also accommodate the many differences in national settings and local circumstances To meet this need we developed a spreadsheet model Accounts Simulation for Tariff and Effluent Charge Modeling or ASTEC 2 WHAT IS ASTEC GOOD FOR 2 1 Is Your Water System Broke As the ASTEC name suggests the model is built around water system accounts What does the current account look like What are the systems current revenues What are the systems immediate costs Ins
69. s and opportunities by adopting a variety of new policies and strategies Unfortunately many MWWUs currently lack the data and tools to properly assess the consequences of the planned measures especially those measures related to tariffs and financing in the new environment This limitation is one of the biggest barriers to adoption of effective responses and an important reason why the UNDP GEF Danube Regional Project DRP developed the Accounts Simulation for Tariffs and Effluent Charges ASTEC Model ASTEC is an Excel based model capable of broadly examining the interaction of a MWWU s tariffs and effluent charges with investment strategies cost structures customer behavior regulatory changes and physical conditions The model is organized around groups of customers or service user SU categories The main features of each service user group are characterized by baseline data such as number of user accounts average annual consumption of water and average annual discharge of wastewater along with the present tariff structure applicable to an SU and the SU s elasticity of demand for services Annual revenues from water and wastewater services provided to each SU are computed based on these data Additional revenues e g revenue from non core services grants subsidies can also be supplied as inputs to the ASTEC model In order to bring this organization of data more concretely to mind we strongly recommend that the reader examin
70. sorts of calculations and actually arrive at data used for modelling Instead of copying data from the SideCalc to the scenario sheets 1 10 you can actually reference them in the scenario spreadsheets If you do this changes you make to your original data in SideCalc will automatically appear in the relevant scenario spreadsheets You can insert comments to cells so it will be easier to remember your assumptions source of data etc This also helps to convey this information to new users of your MWWU specific ASTEC files Be sure to name your scenarios and explain the underlying assumptions at the top of the S1 S10 scenario sheets And when you change a scenario be sure to remember to change this scenario name and description Service user categories should be uniform across scenarios otherwise scenarios results will not be comparable Keep a backup copy of both the original ASTEC file without data in it and your versions of ASTEC While working with ASTEC regularly save the files If you d like to understand the interrelations in your system change one variable at a time to see what impact that has on revenues costs production volumes etc Be patient while the model is running If you start hitting the keyboard in hope of some response the response you may get is that ASTEC will terminate the run before it finishes computations MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 37 9 TROUBLESH
71. splay until an ASTEC run is completed and it hides them again You can insert new worksheets into ASTEC in order to carry out calculations or make notes but This is only possible when the structure of the workbook the ASTEC file is not protected Un protecting the workbook allows one to delete or move around worksheets Moving around worksheets does not threaten the integrity of the model Deleting worksheets however may cause malfunctions Therefore we warn you not to delete any of the original ASTEC worksheets One side effect of protecting the content of the worksheets is that comments to cells cannot be entered or deleted because this task is also subject to the general protection used by Excel To circumvent this problem we made it possible to enter and delete comments through specific key combinations To enter a comment to a cell first you need to select the cell and the press Ctrl Shift i A window will appear in which a comment can be entered and the comment will then be attached to the cell To delete an existing comment select the cell then press Ctrl Shift d UNDP GEF DANUBE REGIONAL PROJECT 6 Operating ASTEC page 32 6 OPERATING ASTEC Operation of ASTEC is initiated on the Control spreadsheet As already discussed in Section 4 1 above the number of service user categories needs to be supplied in Cell C5 before data are entered in the scenario sheets 1 S10 You can copy data from one scenario into a
72. system tariff policy however ASTEC can compute the tariff levels required to cover the investment option Water system policy makers can then assess whether the service improvements associated with the investment justify the estimated tariff increases Many water systems in the DRB are obliged by national policy to meet certain service targets Commonly however local resources are not sufficient to meet these targets in full or all at once The water systems must consider a phased investment strategy and ASTEC scenarios can be used to both select a cost effective sequence of investments and to demonstrate the burden of a more aggressive strategy to national policy makers Aside from the tariff levels per se ASTEC also computes the average or typical expenditures of various service user categories These data together with household or firm budget data can be use to produce various the burden indices that highlight the limits of local resources for the consideration of policy makers locally and at national and international institutions UNDP GEF DANUBE REGIONAL PROJECT Annex 2 page 46 2 9 Who Knew In one water system we worked with households had substantially lower tariffs than commercial industrial customers While using ASTEC to examine new of tariff policies to support system upgrade it was discovered that over eighty public but commercial customers had been classified as households This discovery and the subsequent c
73. t for side calculations called Sidecalc The data that you can enter into some of the colored cells of the sheets is often restricted For instance you may enter integers into some cells or only certain alphabetical text into others This is important for the safe running of the model if improper data types are entered then ASTEC will not know how to handle them and running the ASTEC program code will be halted The ASTEC software has a particular logic or structure that has implications for data entry If entered data is not compatible with the logic of ASTEC then a red ERROR sign shows up in a cell close to the entered data e g in the last row of a table In the bottom part of the scenario sheets in cell D217 the ERROR sign shows up if there is any other ERROR sign anywhere in the sheet This is illustrated below and is ASTEC s way reminding you that at least one entry should be changed somewhere before the model can be run Figure 1 Error Sign in ASTEC Final error display ERROR Cells columns and rows should not be deleted or inserted because that will change the reference numbers of cells thereby confusing the model In fact as a general principle ASTEC will not let you carry out such deletion or insertion tasks Information can be copied between colored cells but it should never be cut and pasted because cutting cells will modify the cell references Some cells have comments that are indicated by a little red triangle in
74. tariff design tariff level and cost recovery specifications of the SUs included in each cluster You do this in rows 180 183 First in row 180 one of five tariff designs needs to be selected for each cluster A Original tariffs The tariffs that have been supplied in Section 2 will be used by ASTEC as the MWWUs tariffs B New tariffs set by the user You should supply new fixed and variable tariffs in rows 181 and 182 to be used by ASTEC as the MWWUs tariffs C Cost recovering tariffs with original fixed tariffs During optimization ASTEC will calculate a new variable tariff which together with the original fixed tariffs should generate enough revenue to recover the costs for all the service users within the cluster but necessarily for the MWWU as a whole D Cost recovering tariffs with marginal cost pricing ASTEC will calculate new fixed and variable tariffs Fixed and variable tariffs should generate enough revenue to recover fixed and variable costs respectively for all the service users within the given cluster While this tariff design has merit on economic efficiency grounds it is rarely applied in pure form due to the high proportion of fixed costs in most MWWUs but it is sometimes useful to know how closely actual tariffs approximate this design MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 29 E Simple marginal cost pricing Revenue from variable tarif
75. te individual tariffs for each cluster and both services and then changes those tariffs which are not exogenously set with the same percentage value in order to achieve full cost recovery for all service users together excluding those SUs with exogenous tariffs Four methods of system wide full cost recovery are offered by ASTEC A System wide full cost recovery separately for water and wastewater services by changing those fixed and variable tariffs that have been derived by the model B System wide full cost recovery separately for water and wastewater services by changing only those variable tariffs that have been derived by the model C System wide full cost recovery together for water and wastewater services by changing those fixed and variable tariffs that have been derived by the model D System wide full cost recovery together for water and wastewater services by changing only those variable tariffs that have been derived by the model If all tariffs are determined by the user i e either tariff design A Original tariffs or B New tariffs set by the user are selected then the model cannot attain system wide full cost recovery If the user chooses system wide full cost recovery then an error sign will be displayed in cell D214 Finally if anywhere in the scenario spreadsheet there is an ERROR message cell D217 will show ERROR This further alerts the user that there is an error that needs to be located and co
76. tead of simply looking at account totals however ASTEC asks the user to dig a little deeper It asks the user to distinguish the current account for water service from the current account for wastewater service and to leave out account items that don t relate to its core business of providing these water and wastewater services to customers One the revenue side of the account ASTEC asks the user to estimate the sources of revenue by each significant customer or service user category How much does this service user group e g households in multiunit apartment blocks use per account What is the tariff applied to this service user group What is the expected revenue from this service user group On the cost side of the current account ASTEC asks for the costs of providing current levels of service It asks the user to provide separate information on operating and capital costs in the current period It also asks the user to 12 Under new municipal or private ownership in CEE the trend has been to break up integrated municipal service companies and create separate water utilities This makes the job of creating separate water system accounts more tractable for ASTEC users MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 43 assign these costs to particular service users e g what fraction of operating costs is due to provision of water service to the large industrial users on the northwest side of the city
77. teristics that in the modelers view distinguish in important ways customer behavior or service Examples of such distinctions include services received water and or wastewater consumption levels water and wastewater tariffs legal status households public entities commercial enterprises reaction to changes in price as expressed by the elasticity of demand technical features such as individually metered apartments vs centrally metered block of house differences in the cost of supplying customers geographical locations or any other characteristic deemed important from the perspective of analysis Each defined service user category is supplied with key variable values such as consumption levels tariffs elasticities and nonpayment of bills for service SUs and associated data headings are included in Section 2 of the scenario spreadsheets of ASTEC 2 0 Demo xls Annual revenues from tariffs are computed by the model for each scenario based on tariffs and average consumption levels then adjusted for non payment The revenue calculations are based on the data of Section 2 of the scenario spreadsheets Annual costs are supplied either as fixed costs or variable costs and entered in Sections 3 and 4 of the scenario spreadsheets A particular category of costs can be allocated among service users automatically based on two methods proportionate with service use or as a percentage of the cost The model keeps track of both costs and revenu
78. tewater bills for SUs that are households Households are final users of the services and cannot get the VAT reimbursed Therefore the VAT is an actual budgetary burden for them The information displayed on the output sheets is protected from modification in order to keep the integrity of the output tables The information however can be copied to another Excel file where it can be used for further computations Output information as indicated before can be displayed in local currency EUR and USD by clicking on the appropriate button on the Control sheet UNDP GEF DANUBE REGIONAL PROJECT 8 Tips on Effective Use page 36 8 TIPS ON EFFECTIVE USE Based on our own experience as well as feedback from ASTEC users at our case study sites we assembled a few tips on how to best operate ASTEC Some of the advice below will seem overly obvious to experienced modelers but may be helpful to beginners gt Keep good records of your data Data comes from many different sources some data are actually measured some are computed others are estimated ASTEC can use vast amounts of data and it is easy to lose track of the source meaning and reference year of some of this data Thorough and structured record keeping of input data will also help to uncover data inconsistencies that when corrected help improve the reliability of modeling results Make use of the SideCalc sheet within ASTEC This is a sheet where you can do all
79. that comes to mind This provides a wonderful feeling of competence and power We hope that many water system staff get to experience this feeling while using ASTEC in the near future MAKK Glenn Morris Andras Kis WORKING FOR THE DANUBE AND ITS PEOPLE www undp drp org
80. the local currency UNDP GEF DANUBE REGIONAL PROJECT 4 Entering Data page 12 with the name and data for each service user category It is important that each scenario has the same number and structure of service user categories This helps assure that it is proper to compare scenario results The main characteristics of service user should be supplied in the range B12 AB26 of scenario sheet S1 The first three columns of this light blue data entry range are shown below This area of S1 stores information on initial SU conditions i e before any changes take place Such information may be different in other scenarios sheets S2 S10 but any modeling requirements such as introducing cost recovering tariffs will take place in scenarios run after establishing this initial or baseline scenario in sheet S1 The service user category names should concisely summarize the most important features of consumers in that SU category This will help you keep track of the features of each SU category when comparing results Through the name you can differentiate for instance between user types e g households industrial users you can provide a reference for the type of service water wastewater or both the location of the service area town village and any other distinctive feature of the SU category The next column headed Number of Accounts in Section 2 of S1 refers to the actual number of customers as they appear in the b
81. ty or environmental regulations Since providing these additional services requires additional resources the water systems find themselves trying to balance the various demands for up grades against the burdens these up grades place on its finances and customers ASTEC can examine the impact of service provision and service costs for a wide variety tariff administrative and investment policies both individually and in combination It allows the user to examine the different impacts of different programs not simply to cost out a given program MAKK Glenn Morris Andras Kis User s Guide to the ASTEC Model page 45 2 6 What Investments are Cost Effective One water system we are familiar with was taken to task for having water losses that were outside the range of a well managed system In this case nearly 50 of the water produced by the system did not reach its customers An investment program was proposed to reduce the water system leakage to a target level of 2590 When this investment program was examined with ASTEC however it was discovered that many of the investment components actually increase the net cost of providing water service This locality is blessed with very productive high quality water resources Water production costs are so low that it simply would not pay to aggressively replace parts of the water system to reduce leakage ASTEC can be used to screen out upgrades that while advisable in some settings

ASTEC MODEL. User's Guide to ASTEC 2.0.

Contents

Download Pdf Manuals

Related Search

Related Contents