{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Overview","Drop_Down_Options","Calculations","Original_Project_Order","Debt_Capacity","Asset_Management","Quick_Start & Getting_Started"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":4,"comments":2,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Overview","index":0,"n_rows":30,"n_cols":6,"rows_kept":30,"cut":false,"hidden":false,"merged":[[1,1,1,15],[3,5,4,15],[6,5,12,15],[6,1,12,3],[14,5,20,15],[14,1,20,3],[29,5,32,15],[22,5,27,15],[22,1,27,3],[29,1,32,3],[3,1,4,3]]}
[]
[null,"Welcome to EPA's Asset Management and Debt Capacity Tool, \ndeveloped with the assistance of The Cadmus Group, Inc."]
[]
[null,"What will I find \nin this tool?",null,null,null,"This tool has two sections: (1) an asset management section and (2) a debt capacity section."]
[]
[]
[null,"How can I\nuse this tool?",null,null,null,"The asset management section includes an inventory where you may list all of the assets owned or operated by your utility and flexible, easy-to-use features to help you prioritize repairs or replacements of the assets.\n\nThe debt capacity section includes a simple budget table to calculate your utility's costs and revenues. It also provides a debt service table, which, for simplicity, is based on loan amortization schedules. This section helps you consider the potential financial impacts of borrowing money and/or increasing customer rates."]
[]
[]
[]
[]
[]
[]
[]
[null,"How do I get started?",null,null,null,"We highly recommend reading the Getting Started document before using this tool for the first time. Getting Started walks you through each of the tool's features, explaining why it is included and how to use it. The document also includes screenshots, troubleshooting help, and important notes to save time and avoid errors.\n\nSome brief help and instructions are also provided within the two sections of the tool itself, but we recommend using this information as a reminder after having first read through the Getting Started document."]
[]
[]
[]
[]
[]
[]
[]
[null,"How often should I \nupdate this tool?",null,null,null,"In order to accurately track each asset's condition, the inventory should be updated every time that an employee goes into the field and inspects a particular asset. We recommend setting aside a certain time each week to update information in the inventory. Additionally, the entire tool should be updated annually, particularly in preparation for developing an annual budget and list of scheduled capital improvements."]
[]
[]
[]
[]
[]
[]
[null," Where can I find\nadditional information\non these topic?",null,null,null,"For information on asset management: http://water.epa.gov/infrastructure/sustain/asset_management.cfm\n\nFor information on debt capacity: http://water.epa.gov/infrastructure/sustain/financing_priceofwater.cfm"]

{"sheet":"Drop_Down_Options","index":1,"n_rows":10,"n_cols":3,"rows_kept":10,"cut":false,"hidden":true,"merged":[]}
["Condition","Criticality","Service Start Date"]
["5: Excellent","5: Insignificant",{"$f":"TODAY()","v":"2014-02-18"}]
[4.5,4.5]
["4: Good","4: Minor"]
[3.5,3.5]
["3: Fair","3: Moderate"]
[2.5,2.5]
["2: Poor","2: Major"]
[1.5,1.5]
["1: Very Poor","1: Catastrophic"]

{"sheet":"Calculations","index":2,"n_rows":78,"n_cols":9,"rows_kept":78,"cut":false,"hidden":true,"merged":[]}
["Service Start Date","Estimated Useful Life (yrs.)","Condition Score","Criticality Score","Remaining Useful Life","Years in Service","Condition #","Criticality #","Priority Score"]
["1965-06-30",75,"2: Poor","3: Moderate",{"$f":"B2-F2","v":26.328767123287669},{"$f":"(TODAY()-A2)/365","v":48.671232876712331},{"$f":"IF(LEN(C2)>3,LEFT(C2,1),C2)","v":"2"},{"$f":"IF(LEN(D2)>3,LEFT(D2,1),D2)","v":"3"},{"$f":"IF(B2=\"\",\"\",IF(C2=\"\",\"\",IF(D2=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E2/B2)<0,0,(E2/B2)))))+(Asset_Management!$H$15*EXP(1-(G2/5)))+(Asset_Management!$H$16*EXP(1-(H2/5)))))))","v":17.08283425180063}]
["1965-06-30",75,"2: Poor","3: Moderate",{"$f":"B3-F3","v":26.328767123287669},{"$f":"(TODAY()-A3)/365","v":48.671232876712331},{"$f":"IF(LEN(C3)>3,LEFT(C3,1),C3)","v":"2"},{"$f":"IF(LEN(D3)>3,LEFT(D3,1),D3)","v":"3"},{"$f":"IF(B3=\"\",\"\",IF(C3=\"\",\"\",IF(D3=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E3/B3)<0,0,(E3/B3)))))+(Asset_Management!$H$15*EXP(1-(G3/5)))+(Asset_Management!$H$16*EXP(1-(H3/5)))))))","v":17.08283425180063}]
["1965-06-30",75,"3: Fair","4: Minor",26.328767123287669,48.671232876712331,"3","4",{"$f":"IF(B4=\"\",\"\",IF(C4=\"\",\"\",IF(D4=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E4/B4)<0,0,(E4/B4)))))+(Asset_Management!$H$15*EXP(1-(G4/5)))+(Asset_Management!$H$16*EXP(1-(H4/5)))))))","v":14.679970082879274}]
["1965-06-30",75,"2: Poor","3: Moderate",26.328767123287669,48.671232876712331,"2","3",{"$f":"IF(B5=\"\",\"\",IF(C5=\"\",\"\",IF(D5=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E5/B5)<0,0,(E5/B5)))))+(Asset_Management!$H$15*EXP(1-(G5/5)))+(Asset_Management!$H$16*EXP(1-(H5/5)))))))","v":17.08283425180063}]
["1965-06-30",75,"1: Very Poor","3: Moderate",26.328767123287669,48.671232876712331,"1","3",{"$f":"IF(B6=\"\",\"\",IF(C6=\"\",\"\",IF(D6=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E6/B6)<0,0,(E6/B6)))))+(Asset_Management!$H$15*EXP(1-(G6/5)))+(Asset_Management!$H$16*EXP(1-(H6/5)))))))","v":18.696522764208467}]
["1965-06-30",75,"2: Poor","3: Moderate",26.328767123287669,48.671232876712331,"2","3",{"$f":"IF(B7=\"\",\"\",IF(C7=\"\",\"\",IF(D7=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E7/B7)<0,0,(E7/B7)))))+(Asset_Management!$H$15*EXP(1-(G7/5)))+(Asset_Management!$H$16*EXP(1-(H7/5)))))))","v":17.08283425180063}]
["1965-06-30",75,"2: Poor","2: Major",26.328767123287669,48.671232876712331,"2","2",{"$f":"IF(B8=\"\",\"\",IF(C8=\"\",\"\",IF(D8=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E8/B8)<0,0,(E8/B8)))))+(Asset_Management!$H$15*EXP(1-(G8/5)))+(Asset_Management!$H$16*EXP(1-(H8/5)))))))","v":18.404010662797585}]
["1981-06-01",75,"2: Poor","2: Major",42.260273972602739,32.739726027397261,"2","2",{"$f":"IF(B9=\"\",\"\",IF(C9=\"\",\"\",IF(D9=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E9/B9)<0,0,(E9/B9)))))+(Asset_Management!$H$15*EXP(1-(G9/5)))+(Asset_Management!$H$16*EXP(1-(H9/5)))))))","v":17.671606737276623}]
["1981-06-01",75,"3: Fair","4: Minor",42.260273972602739,32.739726027397261,"3","4",{"$f":"IF(B10=\"\",\"\",IF(C10=\"\",\"\",IF(D10=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E10/B10)<0,0,(E10/B10)))))+(Asset_Management!$H$15*EXP(1-(G10/5)))+(Asset_Management!$H$16*EXP(1-(H10/5)))))))","v":13.947566157358311}]
["1981-06-01",75,"3: Fair","4: Minor",42.260273972602739,32.739726027397261,"3","4",{"$f":"IF(B11=\"\",\"\",IF(C11=\"\",\"\",IF(D11=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E11/B11)<0,0,(E11/B11)))))+(Asset_Management!$H$15*EXP(1-(G11/5)))+(Asset_Management!$H$16*EXP(1-(H11/5)))))))","v":13.947566157358311}]
["1997-08-10",75,"3: Fair","1: Catastrophic",58.463013698630135,16.536986301369861,"3","1",{"$f":"IF(B12=\"\",\"\",IF(C12=\"\",\"\",IF(D12=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E12/B12)<0,0,(E12/B12)))))+(Asset_Management!$H$15*EXP(1-(G12/5)))+(Asset_Management!$H$16*EXP(1-(H12/5)))))))","v":17.36284527598874}]
["1997-08-10",75,"3: Fair","1: Catastrophic",58.463013698630135,16.536986301369861,"3","1",{"$f":"IF(B13=\"\",\"\",IF(C13=\"\",\"\",IF(D13=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E13/B13)<0,0,(E13/B13)))))+(Asset_Management!$H$15*EXP(1-(G13/5)))+(Asset_Management!$H$16*EXP(1-(H13/5)))))))","v":17.36284527598874}]
["1997-08-10",75,"2: Poor","1: Catastrophic",58.463013698630135,16.536986301369861,"2","1",{"$f":"IF(B14=\"\",\"\",IF(C14=\"\",\"\",IF(D14=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E14/B14)<0,0,(E14/B14)))))+(Asset_Management!$H$15*EXP(1-(G14/5)))+(Asset_Management!$H$16*EXP(1-(H14/5)))))))","v":18.684021686985691}]
["1997-08-10",75,"2: Poor","1: Catastrophic",58.463013698630135,16.536986301369861,"2","1",{"$f":"IF(B15=\"\",\"\",IF(C15=\"\",\"\",IF(D15=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E15/B15)<0,0,(E15/B15)))))+(Asset_Management!$H$15*EXP(1-(G15/5)))+(Asset_Management!$H$16*EXP(1-(H15/5)))))))","v":18.684021686985691}]
["1997-08-10",75,"3: Fair","4: Minor",58.463013698630135,16.536986301369861,"3","4",{"$f":"IF(B16=\"\",\"\",IF(C16=\"\",\"\",IF(D16=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E16/B16)<0,0,(E16/B16)))))+(Asset_Management!$H$15*EXP(1-(G16/5)))+(Asset_Management!$H$16*EXP(1-(H16/5)))))))","v":13.346292594659545}]
["1981-06-01",60,"3: Fair","4: Minor",27.260273972602739,32.739726027397261,"3","4",{"$f":"IF(B17=\"\",\"\",IF(C17=\"\",\"\",IF(D17=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E17/B17)<0,0,(E17/B17)))))+(Asset_Management!$H$15*EXP(1-(G17/5)))+(Asset_Management!$H$16*EXP(1-(H17/5)))))))","v":14.304411072149117}]
["1981-06-01",60,"1: Very Poor","4: Minor",27.260273972602739,32.739726027397261,"1","4",{"$f":"IF(B18=\"\",\"\",IF(C18=\"\",\"\",IF(D18=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E18/B18)<0,0,(E18/B18)))))+(Asset_Management!$H$15*EXP(1-(G18/5)))+(Asset_Management!$H$16*EXP(1-(H18/5)))))))","v":17.239275995553907}]
["1981-06-01",60,"3: Fair","4: Minor",27.260273972602739,32.739726027397261,"3","4",{"$f":"IF(B19=\"\",\"\",IF(C19=\"\",\"\",IF(D19=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E19/B19)<0,0,(E19/B19)))))+(Asset_Management!$H$15*EXP(1-(G19/5)))+(Asset_Management!$H$16*EXP(1-(H19/5)))))))","v":14.304411072149117}]
["1981-06-01",60,"3: Fair","4: Minor",27.260273972602739,32.739726027397261,"3","4",{"$f":"IF(B20=\"\",\"\",IF(C20=\"\",\"\",IF(D20=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E20/B20)<0,0,(E20/B20)))))+(Asset_Management!$H$15*EXP(1-(G20/5)))+(Asset_Management!$H$16*EXP(1-(H20/5)))))))","v":14.304411072149117}]
["1981-06-01",60,"3: Fair","4: Minor",27.260273972602739,32.739726027397261,"3","4",{"$f":"IF(B21=\"\",\"\",IF(C21=\"\",\"\",IF(D21=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E21/B21)<0,0,(E21/B21)))))+(Asset_Management!$H$15*EXP(1-(G21/5)))+(Asset_Management!$H$16*EXP(1-(H21/5)))))))","v":14.304411072149117}]
["1981-06-01",60,"3: Fair","4: Minor",27.260273972602739,32.739726027397261,"3","4",{"$f":"IF(B22=\"\",\"\",IF(C22=\"\",\"\",IF(D22=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E22/B22)<0,0,(E22/B22)))))+(Asset_Management!$H$15*EXP(1-(G22/5)))+(Asset_Management!$H$16*EXP(1-(H22/5)))))))","v":14.304411072149117}]
["1981-06-01",60,"3: Fair","4: Minor",27.260273972602739,32.739726027397261,"3","4",{"$f":"IF(B23=\"\",\"\",IF(C23=\"\",\"\",IF(D23=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E23/B23)<0,0,(E23/B23)))))+(Asset_Management!$H$15*EXP(1-(G23/5)))+(Asset_Management!$H$16*EXP(1-(H23/5)))))))","v":14.304411072149117}]
["1981-06-01",60,"3: Fair","4: Minor",27.260273972602739,32.739726027397261,"3","4",{"$f":"IF(B24=\"\",\"\",IF(C24=\"\",\"\",IF(D24=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E24/B24)<0,0,(E24/B24)))))+(Asset_Management!$H$15*EXP(1-(G24/5)))+(Asset_Management!$H$16*EXP(1-(H24/5)))))))","v":14.304411072149117}]
["1981-06-01",60,"3: Fair","4: Minor",27.260273972602739,32.739726027397261,"3","4",{"$f":"IF(B25=\"\",\"\",IF(C25=\"\",\"\",IF(D25=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E25/B25)<0,0,(E25/B25)))))+(Asset_Management!$H$15*EXP(1-(G25/5)))+(Asset_Management!$H$16*EXP(1-(H25/5)))))))","v":14.304411072149117}]
["1981-06-01",60,"3: Fair","4: Minor",27.260273972602739,32.739726027397261,"3","4",{"$f":"IF(B26=\"\",\"\",IF(C26=\"\",\"\",IF(D26=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E26/B26)<0,0,(E26/B26)))))+(Asset_Management!$H$15*EXP(1-(G26/5)))+(Asset_Management!$H$16*EXP(1-(H26/5)))))))","v":14.304411072149117}]
["1983-06-01",60,"3: Fair","3: Moderate",29.260273972602739,30.739726027397261,"3","3",{"$f":"IF(B27=\"\",\"\",IF(C27=\"\",\"\",IF(D27=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E27/B27)<0,0,(E27/B27)))))+(Asset_Management!$H$15*EXP(1-(G27/5)))+(Asset_Management!$H$16*EXP(1-(H27/5)))))))","v":15.27294515994193}]
["1983-06-01",60,"3: Fair","3: Moderate",29.260273972602739,30.739726027397261,"3","3",{"$f":"IF(B28=\"\",\"\",IF(C28=\"\",\"\",IF(D28=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E28/B28)<0,0,(E28/B28)))))+(Asset_Management!$H$15*EXP(1-(G28/5)))+(Asset_Management!$H$16*EXP(1-(H28/5)))))))","v":15.27294515994193}]
["1983-06-01",60,"3: Fair","3: Moderate",29.260273972602739,30.739726027397261,"3","3",{"$f":"IF(B29=\"\",\"\",IF(C29=\"\",\"\",IF(D29=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E29/B29)<0,0,(E29/B29)))))+(Asset_Management!$H$15*EXP(1-(G29/5)))+(Asset_Management!$H$16*EXP(1-(H29/5)))))))","v":15.27294515994193}]
["1983-06-01",60,"3: Fair","3: Moderate",29.260273972602739,30.739726027397261,"3","3",{"$f":"IF(B30=\"\",\"\",IF(C30=\"\",\"\",IF(D30=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E30/B30)<0,0,(E30/B30)))))+(Asset_Management!$H$15*EXP(1-(G30/5)))+(Asset_Management!$H$16*EXP(1-(H30/5)))))))","v":15.27294515994193}]
["1983-06-01",60,"3: Fair","2: Major",29.260273972602739,30.739726027397261,"3","2",{"$f":"IF(B31=\"\",\"\",IF(C31=\"\",\"\",IF(D31=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E31/B31)<0,0,(E31/B31)))))+(Asset_Management!$H$15*EXP(1-(G31/5)))+(Asset_Management!$H$16*EXP(1-(H31/5)))))))","v":16.594121570938881}]
["1983-06-01",60,"2: Poor","2: Major",29.260273972602739,30.739726027397261,"2","2",{"$f":"IF(B32=\"\",\"\",IF(C32=\"\",\"\",IF(D32=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E32/B32)<0,0,(E32/B32)))))+(Asset_Management!$H$15*EXP(1-(G32/5)))+(Asset_Management!$H$16*EXP(1-(H32/5)))))))","v":17.915297981935836}]
["1983-06-01",60,"2: Poor","2: Major",29.260273972602739,30.739726027397261,"2","2",{"$f":"IF(B33=\"\",\"\",IF(C33=\"\",\"\",IF(D33=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E33/B33)<0,0,(E33/B33)))))+(Asset_Management!$H$15*EXP(1-(G33/5)))+(Asset_Management!$H$16*EXP(1-(H33/5)))))))","v":17.915297981935836}]
["1997-08-10",60,"2: Poor","4: Minor",43.463013698630135,16.536986301369861,"2","4",{"$f":"IF(B34=\"\",\"\",IF(C34=\"\",\"\",IF(D34=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E34/B34)<0,0,(E34/B34)))))+(Asset_Management!$H$15*EXP(1-(G34/5)))+(Asset_Management!$H$16*EXP(1-(H34/5)))))))","v":14.808771204327599}]
["1997-08-10",60,"2: Poor","4: Minor",43.463013698630135,16.536986301369861,"2","4",{"$f":"IF(B35=\"\",\"\",IF(C35=\"\",\"\",IF(D35=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E35/B35)<0,0,(E35/B35)))))+(Asset_Management!$H$15*EXP(1-(G35/5)))+(Asset_Management!$H$16*EXP(1-(H35/5)))))))","v":14.808771204327599}]
["1997-08-10",60,"2: Poor","3: Moderate",43.463013698630135,16.536986301369861,"2","3",{"$f":"IF(B36=\"\",\"\",IF(C36=\"\",\"\",IF(D36=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E36/B36)<0,0,(E36/B36)))))+(Asset_Management!$H$15*EXP(1-(G36/5)))+(Asset_Management!$H$16*EXP(1-(H36/5)))))))","v":15.890458962251998}]
["1997-08-10",60,"4: Good","4: Minor",43.463013698630135,16.536986301369861,"4","4",{"$f":"IF(B37=\"\",\"\",IF(C37=\"\",\"\",IF(D37=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E37/B37)<0,0,(E37/B37)))))+(Asset_Management!$H$15*EXP(1-(G37/5)))+(Asset_Management!$H$16*EXP(1-(H37/5)))))))","v":12.405907035406242}]
["1997-08-10",60,"2: Poor","3: Moderate",43.463013698630135,16.536986301369861,"2","3",{"$f":"IF(B38=\"\",\"\",IF(C38=\"\",\"\",IF(D38=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E38/B38)<0,0,(E38/B38)))))+(Asset_Management!$H$15*EXP(1-(G38/5)))+(Asset_Management!$H$16*EXP(1-(H38/5)))))))","v":15.890458962251998}]
["1997-08-10",60,"2: Poor","3: Moderate",43.463013698630135,16.536986301369861,"2","3",{"$f":"IF(B39=\"\",\"\",IF(C39=\"\",\"\",IF(D39=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E39/B39)<0,0,(E39/B39)))))+(Asset_Management!$H$15*EXP(1-(G39/5)))+(Asset_Management!$H$16*EXP(1-(H39/5)))))))","v":15.890458962251998}]
["1997-08-10",60,"2: Poor","3: Moderate",43.463013698630135,16.536986301369861,"2","3",{"$f":"IF(B40=\"\",\"\",IF(C40=\"\",\"\",IF(D40=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E40/B40)<0,0,(E40/B40)))))+(Asset_Management!$H$15*EXP(1-(G40/5)))+(Asset_Management!$H$16*EXP(1-(H40/5)))))))","v":15.890458962251998}]
["1997-08-10",60,"4: Good","1: Catastrophic",43.463013698630135,16.536986301369861,"4","1",{"$f":"IF(B41=\"\",\"\",IF(C41=\"\",\"\",IF(D41=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E41/B41)<0,0,(E41/B41)))))+(Asset_Management!$H$15*EXP(1-(G41/5)))+(Asset_Management!$H$16*EXP(1-(H41/5)))))))","v":16.422459716735432}]
["1981-06-01",75,"3: Fair","3: Moderate",42.260273972602739,32.739726027397261,"3","3",{"$f":"IF(B42=\"\",\"\",IF(C42=\"\",\"\",IF(D42=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E42/B42)<0,0,(E42/B42)))))+(Asset_Management!$H$15*EXP(1-(G42/5)))+(Asset_Management!$H$16*EXP(1-(H42/5)))))))","v":15.029253915282714}]
["1981-06-01",75,"4: Good","1: Catastrophic",42.260273972602739,32.739726027397261,"4","1",{"$f":"IF(B43=\"\",\"\",IF(C43=\"\",\"\",IF(D43=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E43/B43)<0,0,(E43/B43)))))+(Asset_Management!$H$15*EXP(1-(G43/5)))+(Asset_Management!$H$16*EXP(1-(H43/5)))))))","v":16.882431080763102}]
["1981-06-01",75,"5: Excellent","1: Catastrophic",42.260273972602739,32.739726027397261,"5","1",{"$f":"IF(B44=\"\",\"\",IF(C44=\"\",\"\",IF(D44=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E44/B44)<0,0,(E44/B44)))))+(Asset_Management!$H$15*EXP(1-(G44/5)))+(Asset_Management!$H$16*EXP(1-(H44/5)))))))","v":15.996820048122423}]
["1997-08-10",25,"3: Fair","3: Moderate",8.4630136986301387,16.536986301369861,"3","3",{"$f":"IF(B45=\"\",\"\",IF(C45=\"\",\"\",IF(D45=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E45/B45)<0,0,(E45/B45)))))+(Asset_Management!$H$15*EXP(1-(G45/5)))+(Asset_Management!$H$16*EXP(1-(H45/5)))))))","v":15.809911351844509}]
["1997-08-10",25,"1: Very Poor","4: Minor",8.4630136986301387,16.536986301369861,"1","4",{"$f":"IF(B46=\"\",\"\",IF(C46=\"\",\"\",IF(D46=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E46/B46)<0,0,(E46/B46)))))+(Asset_Management!$H$15*EXP(1-(G46/5)))+(Asset_Management!$H$16*EXP(1-(H46/5)))))))","v":17.663088517324898}]
["1997-08-10",25,"3: Fair","4: Minor",8.4630136986301387,16.536986301369861,"3","4",{"$f":"IF(B47=\"\",\"\",IF(C47=\"\",\"\",IF(D47=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E47/B47)<0,0,(E47/B47)))))+(Asset_Management!$H$15*EXP(1-(G47/5)))+(Asset_Management!$H$16*EXP(1-(H47/5)))))))","v":14.728223593920106}]
["1997-08-10",25,"3: Fair","4: Minor",8.4630136986301387,16.536986301369861,"3","4",{"$f":"IF(B48=\"\",\"\",IF(C48=\"\",\"\",IF(D48=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E48/B48)<0,0,(E48/B48)))))+(Asset_Management!$H$15*EXP(1-(G48/5)))+(Asset_Management!$H$16*EXP(1-(H48/5)))))))","v":14.728223593920106}]
["1997-08-10",25,"3: Fair","4: Minor",8.4630136986301387,16.536986301369861,"3","4",{"$f":"IF(B49=\"\",\"\",IF(C49=\"\",\"\",IF(D49=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E49/B49)<0,0,(E49/B49)))))+(Asset_Management!$H$15*EXP(1-(G49/5)))+(Asset_Management!$H$16*EXP(1-(H49/5)))))))","v":14.728223593920106}]
["1997-08-10",25,"3: Fair","4: Minor",8.4630136986301387,16.536986301369861,"3","4",{"$f":"IF(B50=\"\",\"\",IF(C50=\"\",\"\",IF(D50=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E50/B50)<0,0,(E50/B50)))))+(Asset_Management!$H$15*EXP(1-(G50/5)))+(Asset_Management!$H$16*EXP(1-(H50/5)))))))","v":14.728223593920106}]
["1997-08-10",25,"3: Fair","4: Minor",8.4630136986301387,16.536986301369861,"3","4",{"$f":"IF(B51=\"\",\"\",IF(C51=\"\",\"\",IF(D51=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E51/B51)<0,0,(E51/B51)))))+(Asset_Management!$H$15*EXP(1-(G51/5)))+(Asset_Management!$H$16*EXP(1-(H51/5)))))))","v":14.728223593920106}]
["1997-08-10",25,"3: Fair","4: Minor",8.4630136986301387,16.536986301369861,"3","4",{"$f":"IF(B52=\"\",\"\",IF(C52=\"\",\"\",IF(D52=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E52/B52)<0,0,(E52/B52)))))+(Asset_Management!$H$15*EXP(1-(G52/5)))+(Asset_Management!$H$16*EXP(1-(H52/5)))))))","v":14.728223593920106}]
["1994-03-10",25,"3: Fair","4: Minor",5.0410958904109577,19.958904109589042,"3","4",{"$f":"IF(B53=\"\",\"\",IF(C53=\"\",\"\",IF(D53=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E53/B53)<0,0,(E53/B53)))))+(Asset_Management!$H$15*EXP(1-(G53/5)))+(Asset_Management!$H$16*EXP(1-(H53/5)))))))","v":15.296680843855357}]
["1983-06-01",40,"3: Fair","4: Minor",9.2602739726027394,30.739726027397261,"3","4",{"$f":"IF(B54=\"\",\"\",IF(C54=\"\",\"\",IF(D54=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E54/B54)<0,0,(E54/B54)))))+(Asset_Management!$H$15*EXP(1-(G54/5)))+(Asset_Management!$H$16*EXP(1-(H54/5)))))))","v":15.16593834766705}]
["1983-06-01",40,"3: Fair","3: Moderate",9.2602739726027394,30.739726027397261,"3","3",{"$f":"IF(B55=\"\",\"\",IF(C55=\"\",\"\",IF(D55=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E55/B55)<0,0,(E55/B55)))))+(Asset_Management!$H$15*EXP(1-(G55/5)))+(Asset_Management!$H$16*EXP(1-(H55/5)))))))","v":16.247626105591451}]
["2001-05-01",40,"3: Fair","3: Moderate",27.18904109589041,12.810958904109588,"3","3",{"$f":"IF(B56=\"\",\"\",IF(C56=\"\",\"\",IF(D56=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E56/B56)<0,0,(E56/B56)))))+(Asset_Management!$H$15*EXP(1-(G56/5)))+(Asset_Management!$H$16*EXP(1-(H56/5)))))))","v":14.689607803735889}]
["1981-06-01",50,"3: Fair","3: Moderate",17.260273972602739,32.739726027397261,"3","3",{"$f":"IF(B57=\"\",\"\",IF(C57=\"\",\"\",IF(D57=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E57/B57)<0,0,(E57/B57)))))+(Asset_Management!$H$15*EXP(1-(G57/5)))+(Asset_Management!$H$16*EXP(1-(H57/5)))))))","v":15.78409154270112}]
["1981-06-01",50,"3: Fair","3: Moderate",17.260273972602739,32.739726027397261,"3","3",{"$f":"IF(B58=\"\",\"\",IF(C58=\"\",\"\",IF(D58=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E58/B58)<0,0,(E58/B58)))))+(Asset_Management!$H$15*EXP(1-(G58/5)))+(Asset_Management!$H$16*EXP(1-(H58/5)))))))","v":15.78409154270112}]
["1981-06-01",50,"3: Fair","2: Major",17.260273972602739,32.739726027397261,"3","2",{"$f":"IF(B59=\"\",\"\",IF(C59=\"\",\"\",IF(D59=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E59/B59)<0,0,(E59/B59)))))+(Asset_Management!$H$15*EXP(1-(G59/5)))+(Asset_Management!$H$16*EXP(1-(H59/5)))))))","v":17.105267953698075}]
["2000-04-20",50,"2: Poor","2: Major",36.158904109589045,13.841095890410958,"2","2",{"$f":"IF(B60=\"\",\"\",IF(C60=\"\",\"\",IF(D60=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E60/B60)<0,0,(E60/B60)))))+(Asset_Management!$H$15*EXP(1-(G60/5)))+(Asset_Management!$H$16*EXP(1-(H60/5)))))))","v":17.214813346949228}]
["1994-03-10",20,"2: Poor","2: Major",4.1095890410957736E-2,19.958904109589042,"2","2",{"$f":"IF(B61=\"\",\"\",IF(C61=\"\",\"\",IF(D61=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E61/B61)<0,0,(E61/B61)))))+(Asset_Management!$H$15*EXP(1-(G61/5)))+(Asset_Management!$H$16*EXP(1-(H61/5)))))))","v":20.002354508048931}]
["1994-03-10",20,"1: Very Poor","3: Moderate",4.1095890410957736E-2,19.958904109589042,"1","3",{"$f":"IF(B62=\"\",\"\",IF(C62=\"\",\"\",IF(D62=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E62/B62)<0,0,(E62/B62)))))+(Asset_Management!$H$15*EXP(1-(G62/5)))+(Asset_Management!$H$16*EXP(1-(H62/5)))))))","v":20.294866609459813}]
["1994-03-10",30,"3: Fair","4: Minor",10.041095890410958,19.958904109589042,"3","4",{"$f":"IF(B63=\"\",\"\",IF(C63=\"\",\"\",IF(D63=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E63/B63)<0,0,(E63/B63)))))+(Asset_Management!$H$15*EXP(1-(G63/5)))+(Asset_Management!$H$16*EXP(1-(H63/5)))))))","v":14.743045300971014}]
["1983-06-01",35,"3: Fair","4: Minor",4.2602739726027394,30.739726027397261,"3","4",{"$f":"IF(B64=\"\",\"\",IF(C64=\"\",\"\",IF(D64=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E64/B64)<0,0,(E64/B64)))))+(Asset_Management!$H$15*EXP(1-(G64/5)))+(Asset_Management!$H$16*EXP(1-(H64/5)))))))","v":15.666412696005107}]
["1983-06-01",35,"3: Fair","4: Minor",4.2602739726027394,30.739726027397261,"3","4",{"$f":"IF(B65=\"\",\"\",IF(C65=\"\",\"\",IF(D65=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E65/B65)<0,0,(E65/B65)))))+(Asset_Management!$H$15*EXP(1-(G65/5)))+(Asset_Management!$H$16*EXP(1-(H65/5)))))))","v":15.666412696005107}]
["2008-11-15",15,"3: Fair","4: Minor",9.7369863013698641,5.2630136986301368,"3","4",{"$f":"IF(B66=\"\",\"\",IF(C66=\"\",\"\",IF(D66=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E66/B66)<0,0,(E66/B66)))))+(Asset_Management!$H$15*EXP(1-(G66/5)))+(Asset_Management!$H$16*EXP(1-(H66/5)))))))","v":13.693508297814567}]
["1983-06-01",30,"3: Fair","4: Minor",{"$f":"B67-F67","v":-0.73972602739726057},{"$f":"(TODAY()-A67)/365","v":30.739726027397261},{"$f":"IF(LEN(C67)>3,LEFT(C67,1),C67)","v":"3"},{"$f":"IF(LEN(D67)>3,LEFT(D67,1),D67)","v":"4"},{"$f":"IF(B67=\"\",\"\",IF(C67=\"\",\"\",IF(D67=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E67/B67)<0,0,(E67/B67)))))+(Asset_Management!$H$15*EXP(1-(G67/5)))+(Asset_Management!$H$16*EXP(1-(H67/5)))))))","v":16.289473480123853}]
["1983-06-01",30,"3: Fair","4: Minor",-0.73972602739726057,30.739726027397261,"3","4",{"$f":"IF(B68=\"\",\"\",IF(C68=\"\",\"\",IF(D68=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E68/B68)<0,0,(E68/B68)))))+(Asset_Management!$H$15*EXP(1-(G68/5)))+(Asset_Management!$H$16*EXP(1-(H68/5)))))))","v":16.289473480123853}]
["1983-06-01",30,"3: Fair","4: Minor",-0.73972602739726057,30.739726027397261,"3","4",{"$f":"IF(B69=\"\",\"\",IF(C69=\"\",\"\",IF(D69=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E69/B69)<0,0,(E69/B69)))))+(Asset_Management!$H$15*EXP(1-(G69/5)))+(Asset_Management!$H$16*EXP(1-(H69/5)))))))","v":16.289473480123853}]
["1983-06-01",30,"3: Fair","4: Minor",-0.73972602739726057,30.739726027397261,"3","4",{"$f":"IF(B70=\"\",\"\",IF(C70=\"\",\"\",IF(D70=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E70/B70)<0,0,(E70/B70)))))+(Asset_Management!$H$15*EXP(1-(G70/5)))+(Asset_Management!$H$16*EXP(1-(H70/5)))))))","v":16.289473480123853}]
["1983-06-01",30,"3: Fair","3: Moderate",-0.73972602739726057,30.739726027397261,"3","3",{"$f":"IF(B71=\"\",\"\",IF(C71=\"\",\"\",IF(D71=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E71/B71)<0,0,(E71/B71)))))+(Asset_Management!$H$15*EXP(1-(G71/5)))+(Asset_Management!$H$16*EXP(1-(H71/5)))))))","v":17.371161238048252}]
["1983-06-01",30,"3: Fair","3: Moderate",-0.73972602739726057,30.739726027397261,"3","3",{"$f":"IF(B72=\"\",\"\",IF(C72=\"\",\"\",IF(D72=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E72/B72)<0,0,(E72/B72)))))+(Asset_Management!$H$15*EXP(1-(G72/5)))+(Asset_Management!$H$16*EXP(1-(H72/5)))))))","v":17.371161238048252}]
["1994-03-10",35,"3: Fair","3: Moderate",15.041095890410958,19.958904109589042,"3","3",{"$f":"IF(B73=\"\",\"\",IF(C73=\"\",\"\",IF(D73=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E73/B73)<0,0,(E73/B73)))))+(Asset_Management!$H$15*EXP(1-(G73/5)))+(Asset_Management!$H$16*EXP(1-(H73/5)))))))","v":15.472031503800334}]
["1994-03-10",35,"3: Fair","1: Catastrophic",15.041095890410958,19.958904109589042,"3","1",{"$f":"IF(B74=\"\",\"\",IF(C74=\"\",\"\",IF(D74=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E74/B74)<0,0,(E74/B74)))))+(Asset_Management!$H$15*EXP(1-(G74/5)))+(Asset_Management!$H$16*EXP(1-(H74/5)))))))","v":18.406896427205126}]
["1994-03-10",35,"3: Fair","2: Major",15.041095890410958,19.958904109589042,"3","2",{"$f":"IF(B75=\"\",\"\",IF(C75=\"\",\"\",IF(D75=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E75/B75)<0,0,(E75/B75)))))+(Asset_Management!$H$15*EXP(1-(G75/5)))+(Asset_Management!$H$16*EXP(1-(H75/5)))))))","v":16.793207914797289}]
["2002-07-01",15,"2: Poor","5: Insignificant",3.3561643835616444,11.643835616438356,"2","5",{"$f":"IF(B76=\"\",\"\",IF(C76=\"\",\"\",IF(D76=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E76/B76)<0,0,(E76/B76)))))+(Asset_Management!$H$15*EXP(1-(G76/5)))+(Asset_Management!$H$16*EXP(1-(H76/5)))))))","v":15.635114138722837}]
["2002-07-01",15,"2: Poor","5: Insignificant",3.3561643835616444,11.643835616438356,"2","5",{"$f":"IF(B77=\"\",\"\",IF(C77=\"\",\"\",IF(D77=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E77/B77)<0,0,(E77/B77)))))+(Asset_Management!$H$15*EXP(1-(G77/5)))+(Asset_Management!$H$16*EXP(1-(H77/5)))))))","v":15.635114138722837}]
["2002-07-01",15,"4: Good","5: Insignificant",3.3561643835616444,11.643835616438356,"4","5",{"$f":"IF(B78=\"\",\"\",IF(C78=\"\",\"\",IF(D78=\"\",\"\",10*((Asset_Management!$H$14*EXP(1-(IF((E78/B78)<0,0,(E78/B78)))))+(Asset_Management!$H$15*EXP(1-(G78/5)))+(Asset_Management!$H$16*EXP(1-(H78/5)))))))","v":13.232249969801479}]

{"sheet":"Original_Project_Order","index":3,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":true,"merged":[]}

{"sheet":"Debt_Capacity","index":4,"n_rows":59,"n_cols":16,"rows_kept":59,"cut":false,"hidden":false,"merged":[[58,1,60,9],[10,6,11,6],[10,7,10,9],[11,7,11,9],[49,1,49,5],[55,1,55,5],[50,1,50,5],[52,1,52,5],[53,1,53,5],[40,1,40,5],[42,1,42,6],[34,1,34,5],[35,1,35,5],[37,1,37,5],[31,4,31,5],[32,4,32,5],[1,1,1,9],[4,7,4,9],[5,7,5,9],[6,7,6,9],[3,6,3,9],[7,6,9,6],[8,1,9,3],[10,1,10,3],[7,7,7,9],[4,1,6,3],[4,6,6,6],[8,7,8,9],[9,7,9,9],[26,1,28,3],[24,1,24,5],[30,4,30,5],[30,1,30,3],[36,1,36,5],[51,1,51,5],[33,4,33,5],[31,1,31,3],[32,1,32,3],[33,1,33,3],[38,1,38,5],[41,1,41,5],[45,1,45,5],[46,1,46,6],[43,1,43,5],[44,1,44,6],[47,1,47,5],[48,1,48,6]]}
[]
[null,"Utility Budget and Debt Service Schedule"]
[]
[null,null,null,null,null,null,"Color Key for Financial Information"]
[null,"Debt Service Schedule ",null,null,null,null,"User-Entered Values (blue/purple)","Estimated Dollar Amounts"]
[null,null,null,null,null,null,null,"Fiscal Year (\"FY####\"); Loan/Debt Period"]
[null,null,null,null,null,null,null,"Estimated Percent Amounts"]
[null,null,null,null,null,null,"Calculated Values\n(green/red)","Calculated Line Item Values"]
[null,"Current \nFiscal Year",null,null,null,null,null,"Calculated Total Values"]
[null,null,null,null,null,null,null,"Negative Total Values or Unallowed Values"]
[null,"FY2014",null,null,null,null,"Information\n(white/gray)","Line Items"]
[null,null,null,null,null,null,null,"Headings / Titles"]
[]
[null,"Anticipated Loans/Debts","Amount Borrowed (Principal only)","Starting \nFY of Payments","Period of Loan/Debt (yrs.)","Annual Interest Rate",{"$f":"$B$11","v":"FY2014"},{"$f":"CONCATENATE(\"FY\",RIGHT(G14,4)+1)","v":"FY2015"},"FY2016","FY2017","FY2018","FY2019","FY2020","FY2021","FY2022","FY2023"]
[null,"Loan 1",500000,"FY2005",20,4.4999999999999998E-2,{"$f":"IF($D15=\"\",0,IF(OR(RIGHT($D15,4)>RIGHT(G$14,4),(RIGHT(G$14,4)-RIGHT($D15,4))>$E15),0,IF($F15=0,$C15/$E15,$C15*(($F15*POWER(1+$F15,$E15))/(POWER(1+$F15,$E15)-1)))))","v":38438.072162024095},{"$f":"IF($D15=\"\",0,IF(OR(RIGHT($D15,4)>RIGHT(H$14,4),(RIGHT(H$14,4)-RIGHT($D15,4))>$E15),0,IF($F15=0,$C15/$E15,$C15*(($F15*POWER(1+$F15,$E15))/(POWER(1+$F15,$E15)-1)))))","v":38438.072162024095},38438.072162024095,38438.072162024095,38438.072162024095,38438.072162024095,38438.072162024095,38438.072162024095,38438.072162024095,38438.072162024095]
[null,"Loan 2",2000000,"FY2012",20,4.4999999999999998E-2,{"$f":"IF($D16=\"\",0,IF(OR(RIGHT($D16,4)>RIGHT(G$14,4),(RIGHT(G$14,4)-RIGHT($D16,4))>$E16),0,IF($F16=0,$C16/$E16,$C16*(($F16*POWER(1+$F16,$E16))/(POWER(1+$F16,$E16)-1)))))","v":153752.28864809638},153752.28864809638,153752.28864809638,153752.28864809638,153752.28864809638,153752.28864809638,153752.28864809638,153752.28864809638,153752.28864809638,153752.28864809638]
[null,"Loan 3",1000000,"FY2014",20,4.4999999999999998E-2,76876.144324048189,76876.144324048189,76876.144324048189,76876.144324048189,76876.144324048189,76876.144324048189,76876.144324048189,76876.144324048189,76876.144324048189,76876.144324048189]
[null,"Loan 4",1500000,"FY2018",20,4.4999999999999998E-2,0,0,0,0,115314.21648607227,115314.21648607227,115314.21648607227,115314.21648607227,115314.21648607227,115314.21648607227]
[null,"Loan 5",null,null,20,4.4999999999999998E-2,0,0,0,0,0,0,0,0,0,0]
[null,"Loan 6",null,null,20,4.4999999999999998E-2,0,0,0,0,0,0,0,0,0,0]
[null,"Loan 7",null,null,20,4.4999999999999998E-2,0,0,0,0,0,0,0,0,0,0]
[null,"Loan 8",null,null,20,4.4999999999999998E-2,0,0,0,0,0,0,0,0,0,0]
[null,"Loan 9",null,null,20,4.4999999999999998E-2,0,0,0,0,0,0,0,0,0,0]
[null,"Loan 10",null,null,20,4.4999999999999998E-2,0,0,0,0,0,0,0,0,0,0]
[null,"Total",null,null,null,null,{"$f":"SUM(G15:G24)","v":269066.50513416866},{"$f":"SUM(H15:H24)","v":269066.50513416866},269066.50513416866,269066.50513416866,384380.72162024095,384380.72162024095,384380.72162024095,384380.72162024095,384380.72162024095,384380.72162024095]
[]
[null,"Utility Budget"]
[]
[]
[]
[null,"COSTS",null,null,"Percent Annual Increase",null,{"$f":"$B$11","v":"FY2014"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+1,CHAR(10),\" (estimate)\")","v":"FY2015\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+2,CHAR(10),\" (estimate)\")","v":"FY2016\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+3,CHAR(10),\" (estimate)\")","v":"FY2017\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+4,CHAR(10),\" (estimate)\")","v":"FY2018\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+5,CHAR(10),\" (estimate)\")","v":"FY2019\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+6,CHAR(10),\" (estimate)\")","v":"FY2020\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+7,CHAR(10),\" (estimate)\")","v":"FY2021\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+8,CHAR(10),\" (estimate)\")","v":"FY2022\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+9,CHAR(10),\" (estimate)\")","v":"FY2023\n (estimate)"}]
[null,"Salaries and Wages                   (default % inc. = 3.00%)",null,null,0.03,null,3500000,{"$f":"G32*(1+$E$32)","v":3605000},3713150,3824544.5,3939280.835,4057459.2600500002,4179183.0378515003,4304558.5289870454,4433695.2848566566,4566706.1434023567]
[null,"Fringe Benefits                          (default % inc. = 5.00%)",null,null,0.05,null,{"$f":"0.3*G32","v":1050000},{"$f":"G33*(1+$E$33)","v":1102500},1157625,1215506.25,1276281.5625,1340095.640625,1407100.42265625,1477455.4437890626,1551328.2159785158,1628894.6267774417]
[null,"Non-personnel Costs              (default % inc. = 4.00%)",null,null,0.04,null,800000,{"$f":"G34*(1+$E$34)","v":832000},865280,899891.20000000007,935886.84800000011,973322.32192000013,1012255.2147968002,1052745.4233886723,1094855.2403242192,1138649.4499371881]
[null,"Debt Service",null,null,null,null,{"$f":"G25","v":269066.50513416866},269066.50513416866,269066.50513416866,269066.50513416866,384380.72162024095,384380.72162024095,384380.72162024095,384380.72162024095,384380.72162024095,384380.72162024095]
[null,"Capital Expenditures",null,null,null,null,500000,2500000,500000,1500000,250000,null,null,1500000]
[null,"Savings into Capital Expense Fund",null,null,null,null,50000,50000,50000,50000,50000,50000,50000,50000,50000,50000]
[null,"Other - Add additional categories here if needed",null,null,null,null,0,0,0,0,0,0,0,0,0,0]
[null,"Total",null,null,null,null,{"$f":"SUM(G32:G38)","v":6169066.505134169},{"$f":"SUM(H32:H38)","v":8358566.505134169},{"$f":"SUM(I32:I38)","v":6555121.505134169},{"$f":"SUM(J32:J38)","v":7759008.4551341692},{"$f":"SUM(K32:K38)","v":6835829.9671202414},{"$f":"SUM(L32:L38)","v":6805257.9442152418},7032919.3969247919,8769140.1177850217,7514259.4627796337,7768630.9417372281]
[]
[null,"REVENUES",null,null,null,null,{"$f":"$B$11","v":"FY2014"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+1,CHAR(10),\" (estimate)\")","v":"FY2015\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+2,CHAR(10),\" (estimate)\")","v":"FY2016\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+3,CHAR(10),\" (estimate)\")","v":"FY2017\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+4,CHAR(10),\" (estimate)\")","v":"FY2018\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+5,CHAR(10),\" (estimate)\")","v":"FY2019\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+6,CHAR(10),\" (estimate)\")","v":"FY2020\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+7,CHAR(10),\" (estimate)\")","v":"FY2021\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+8,CHAR(10),\" (estimate)\")","v":"FY2022\n (estimate)"},{"$f":"CONCATENATE(\"FY\",RIGHT($G$31,4)+9,CHAR(10),\" (estimate)\")","v":"FY2023\n (estimate)"}]
[null,"Residential Customer Fees - Usage Charges",null,null,null,null,1300000,{"$f":"G42*(1+H43)","v":1300000},1300000,1326000,1379040,1420411.2,1463023.5360000001,1506914.2420800002,1552121.6693424003,1598685.3194226723]
[null,"Percent Increase for Residential Customer Fees - Usage Charges",null,null,null,null,null,0,0,0.02,0.04,0.03,0.03,0.03,0.03,0.03]
[null,"Residential Customer Fees - Other Charges",null,null,null,null,500000,{"$f":"G44*(1+H45)","v":500000},{"$f":"H44*(1+I45)","v":500000},{"$f":"I44*(1+J45)","v":510000},{"$f":"J44*(1+K45)","v":530400},{"$f":"K44*(1+L45)","v":546312},{"$f":"L44*(1+M45)","v":562701.36},{"$f":"M44*(1+N45)","v":579582.40079999994},{"$f":"N44*(1+O45)","v":596969.87282399996},{"$f":"O44*(1+P45)","v":614878.96900872001}]
[null,"Percent Increase for Residential Customer Fees - Other Charges",null,null,null,null,null,0,0,0.02,0.04,0.03,0.03,0.03,0.03,0.03]
[null,"Commercial Customer Fees - Usage Charges",null,null,null,null,4200000,{"$f":"G46*(1+H47)","v":4200000},{"$f":"H46*(1+I47)","v":4200000},{"$f":"I46*(1+J47)","v":4200000},{"$f":"J46*(1+K47)","v":4410000},{"$f":"K46*(1+L47)","v":4542300},{"$f":"L46*(1+M47)","v":4678569},{"$f":"M46*(1+N47)","v":4865711.76},{"$f":"N46*(1+O47)","v":5011683.1128000002},{"$f":"O46*(1+P47)","v":5212150.4373120004}]
[null,"Percent Increase for Commercial Customer Fees - Usage Charges",null,null,null,null,null,0,0,0,0.05,0.03,0.03,0.04,0.03,0.04]
[null,"Commercial Customer Fees - Other Charges",null,null,null,null,650000,{"$f":"G48*(1+H49)","v":650000},{"$f":"H48*(1+I49)","v":650000},{"$f":"I48*(1+J49)","v":650000},{"$f":"J48*(1+K49)","v":682500},{"$f":"K48*(1+L49)","v":702975},{"$f":"L48*(1+M49)","v":724064.25},{"$f":"M48*(1+N49)","v":753026.82000000007},{"$f":"N48*(1+O49)","v":775617.6246000001},{"$f":"O48*(1+P49)","v":806642.32958400017}]
[null,"Percent Increase for Commercial Customer Fees - Other Charges",null,null,null,null,null,0,0,0,0.05,0.03,0.03,0.04,0.03,0.04]
[null,"Borrowing",null,null,null,null,0,2000000,0,1000000,0,0,0,1500000,0,0]
[null,"Use of Surplus ",null,null,null,null,0,0,500000,500000,0,0,0,0,0,0]
[null,"Use of Funds from Capital Expense Account",null,null,null,null,500000,0,0,0,250000,0,0,0,0,0]
[null,"Other - Add additional categories here if needed",null,null,null,null,0,0,0,0,0,0,0,0,0,0]
[null,"Total",null,null,null,null,{"$f":"SUM(G42:G50)","v":6650000},{"$f":"SUM(H42:H50)","v":8650000},{"$f":"SUM(I42:I51)","v":7150000},8186000.04,7001940.1799999997,7211998.3200000003,7428358.2660000008,9205235.3628800008,7936392.3995663999,8232357.1953273928]
[]
[null,"SURPLUS/DEFICIT\n(cumulative)",null,null,null,null,{"$f":"G54-G39","v":480933.49486583099},{"$f":"G56+(H54-H39)","v":772366.98973166198},{"$f":"H56+(I54-I39)","v":1367245.484597493},1794237.0694633238,1960347.2823430821,2367087.6581278406,2762526.5272030495,3198621.7722980287,3620754.7090847949,4084480.9626749596]
[]
[]
[null,"Now try testing out a few alternatives to maximize long-term suprlus and viability! \n\nFor example, compare your utility's surpuls/deficit using large, infreqent rate increase vs. using small yearly increases. You can also try adjusting Savings into Capital Expense Fund, Use of Surplus, and Starting FY for any loans/debts."]

{"sheet":"Asset_Management","index":5,"n_rows":105,"n_cols":15,"rows_kept":105,"cut":false,"hidden":false,"merged":[[26,9,26,14],[7,1,7,3],[26,2,26,7],[19,6,19,7],[7,5,7,7],[18,1,20,3],[22,1,24,3],[16,5,16,6],[19,5,21,5],[20,6,21,7],[22,5,24,5],[22,6,24,7],[1,1,1,7],[18,5,18,7],[14,5,14,6],[15,5,15,6],[13,1,16,3],[13,5,13,6],[8,5,9,7],[9,1,9,3],[10,5,11,7],[11,1,11,3],[3,1,5,7]]}
[]
[null,"Asset Inventory and Prioritization"]
[]
[null,"Start by listing your utility's assets in the inventory below.\nIf you do not currently have a complete list of all assets, start by gathering information on your most critical assets first. This will allow you to prioritize these important assets. Then, over time, you can add in less critical assets to arrive at a complete inventory.\n\nAfter listing your assets in the Inventory, follow steps 1 - 4 to prioritize your assets."]
[]
[]
[]
[null,"Step 0: Enable Macros (if not done already)",null,null,null,"Buttons and Instructions"]
[null,null,null,null,null,"Step-by-step instructions are denoted in the cells to \nthe left that are tan-green in color with dotted borders. \nSteps 0, 2, and 3 are to be completed manually by the user."]
[]
[null,null,null,null,null,"Steps 1 and 4 (as well as the \"Undo Step 4\" function) are special instructions that are completed using \"buttons.\" \nThe tool performs these steps automatically when \nthe user clicks on the button."]
[null,"Step 2: Hard-enter Any Desired \nPriority Numbers (e.g., 1, 2, 3) in Column B \nof the Asset Inventory Below"]
[]
[null,"Step 3: Enter Relative Importance \n(i.e., \"weights\") of the Three Decision Variables (applies to all inventory assets without a hard-entered priority number from Step 2) ",null,null,null,"Remaining Useful Life",null,0.2,null,"Default Starting Value = 20%"]
[null,null,null,null,null,"Condition",null,0.4,null,"Default Starting Value = 40%"]
[null,null,null,null,null,"Impact of Failure",null,0.4,null,"Default Starting Value = 40%"]
[null,null,null,null,null,"Total",null,{"$f":"SUM(H14:H16)","v":1}]
[]
[null,null,null,null,null,"Color Key for Asset Inventory"]
[null,null,null,null,null,"Required Fields","Not used in Calculations"]
[null,null,null,null,null,null,"Used in Calculations - Correspond \nto Decision Variables"]
[]
[null,null,null,null,null,"Optional Fields","Not used in Calculations - Additional Information of Interest \n(may be added as desired)"]
[]
[]
[]
[null,null,"Required Fields",null,null,null,null,null,null,"Optional Fields"]
[null,"Priority Number","Asset ID (unique)","Asset Name","Service Start Date (mm/dd/yyyy)","Manufacturer's Estimated Useful Life (yrs.)","Condition \nScore\n(1 through 5)","Impact of Failure Score\n(1 through 5)","Priority Score","Asset Location","Aquisition Cost","Replacement/Repair Cost","Purchase Date","Serial Number","Model"]
[null,null,1,"V C PIPE","1965-06-30",75,"2: Poor","3: Moderate",16.909633766084369,"MAIN STREET",561349,null,"1965-06-30"]
[null,null,2,"V C PIPE","1965-06-30",75,"2: Poor","3: Moderate"]
[null,null,3,"V C PIPE","1965-06-30",75,"3: Fair","4: Minor"]
[null,null,4,"V C PIPE","1965-06-30",75,"2: Poor","3: Moderate"]
[null,null,5,"V C PIPE","1965-06-30",75,"1: Very Poor","3: Moderate"]
[null,null,6,"V C PIPE","1965-06-30",75,"2: Poor","3: Moderate"]
[null,null,7,"V C PIPE","1965-06-30",75,"2: Poor","2: Major"]
[null,null,8,"V C PIPE","1981-06-01",75,"2: Poor","2: Major"]
[null,null,9,"V C PIPE","1981-06-01",75,"3: Fair","4: Minor"]
[null,null,10,"V C PIPE","1981-06-01",75,"3: Fair","4: Minor"]
[null,null,11,"V C PIPE","1997-08-10",75,"3: Fair","1: Catastrophic"]
[null,null,12,"V C PIPE","1997-08-10",75,"3: Fair","1: Catastrophic"]
[null,null,13,"V C PIPE","1997-08-10",75,"2: Poor","1: Catastrophic"]
[null,null,14,"V C PIPE","1997-08-10",75,"2: Poor","1: Catastrophic"]
[null,null,15,"V C PIPE","1997-08-10",75,"3: Fair","4: Minor"]
[null,null,16,"DI PIPE","1981-06-01",60,"3: Fair","4: Minor"]
[null,null,17,"DI PIPE","1981-06-01",60,"1: Very Poor","4: Minor"]
[null,null,18,"DI PIPE","1981-06-01",60,"3: Fair","4: Minor"]
[null,null,19,"DI PIPE","1981-06-01",60,"3: Fair","4: Minor"]
[null,null,20,"DI PIPE","1981-06-01",60,"3: Fair","4: Minor"]
[null,null,21,"DI PIPE","1981-06-01",60,"3: Fair","4: Minor"]
[null,null,22,"DI PIPE","1981-06-01",60,"3: Fair","4: Minor"]
[null,null,23,"DI PIPE","1981-06-01",60,"3: Fair","4: Minor"]
[null,null,24,"DI PIPE","1981-06-01",60,"3: Fair","4: Minor"]
[null,null,25,"DI PIPE","1981-06-01",60,"3: Fair","4: Minor"]
[null,null,26,"DI PIPE","1983-06-01",60,"3: Fair","3: Moderate"]
[null,null,27,"DI PIPE","1983-06-01",60,"3: Fair","3: Moderate"]
[null,null,28,"DI PIPE","1983-06-01",60,"3: Fair","3: Moderate"]
[null,null,29,"DI PIPE","1983-06-01",60,"3: Fair","3: Moderate"]
[null,null,30,"DI PIPE","1983-06-01",60,"3: Fair","2: Major"]
[null,null,31,"DI PIPE","1983-06-01",60,"2: Poor","2: Major"]
[null,null,32,"DI PIPE","1983-06-01",60,"2: Poor","2: Major"]
[null,null,33,"DI PIPE","1997-08-10",60,"2: Poor","4: Minor"]
[null,null,34,"DI PIPE","1997-08-10",60,"2: Poor","4: Minor"]
[null,null,35,"DI PIPE","1997-08-10",60,"2: Poor","3: Moderate"]
[null,null,36,"DI PIPE","1997-08-10",60,"4: Good","4: Minor"]
[null,null,37,"DI PIPE","1997-08-10",60,"2: Poor","3: Moderate"]
[null,null,38,"DI PIPE","1997-08-10",60,"2: Poor","3: Moderate"]
[null,null,39,"DI PIPE","1997-08-10",60,"2: Poor","3: Moderate"]
[null,null,40,"DI PIPE","1997-08-10",60,"4: Good","1: Catastrophic"]
[null,null,41,"Transmission - 1","1981-06-01",75,"3: Fair","3: Moderate"]
[null,null,42,"Transmission - 2","1981-06-01",75,"4: Good","1: Catastrophic"]
[null,null,43,"Transmission - 3","1981-06-01",75,"5: Excellent","1: Catastrophic"]
[null,null,44,"Pump - BPS A","1997-08-10",25,"3: Fair","3: Moderate"]
[null,null,45,"Pump - BPS A","1997-08-10",25,"1: Very Poor","4: Minor"]
[null,null,46,"Pump - BPS A","1997-08-10",25,"3: Fair","4: Minor"]
[null,null,47,"Pump - BPS B","1997-08-10",25,"3: Fair","4: Minor"]
[null,null,48,"Pump - BPS B","1997-08-10",25,"3: Fair","4: Minor"]
[null,null,49,"Pump - BPS C","1997-08-10",25,"3: Fair","4: Minor"]
[null,null,50,"Pump - BPS C","1997-08-10",25,"3: Fair","4: Minor"]
[null,null,51,"Pump - BPS C","1997-08-10",25,"3: Fair","4: Minor"]
[null,null,52,"Pump - BPS C","1994-03-10",25,"3: Fair","4: Minor"]
[null,null,53,"Clarifier","1983-06-01",40,"3: Fair","4: Minor"]
[null,null,54,"Clarifier","1983-06-01",40,"3: Fair","3: Moderate"]
[null,null,55,"Clarifier","2001-05-01",40,"3: Fair","3: Moderate"]
[null,null,56,"Storage Tank","1981-06-01",50,"3: Fair","3: Moderate"]
[null,null,57,"Storage Tank","1981-06-01",50,"3: Fair","3: Moderate"]
[null,null,58,"Storage Tank","1981-06-01",50,"3: Fair","2: Major"]
[null,null,59,"Storage Tank","2000-04-20",50,"2: Poor","2: Major"]
[null,null,60,"Slow Sand Tank","1994-03-10",20,"2: Poor","2: Major"]
[null,null,61,"Slow Sand Tank","1994-03-10",20,"1: Very Poor","3: Moderate"]
[null,null,62,"Flocculation Tank","1994-03-10",30,"3: Fair","4: Minor"]
[null,null,63,"Clearwell #1","1983-06-01",35,"3: Fair","4: Minor"]
[null,null,64,"Clearwell #2","1983-06-01",35,"3: Fair","4: Minor"]
[null,null,65,"Back-up Generator","2008-11-15",15,"3: Fair","4: Minor"]
[null,null,66,"Valves - North","1983-06-01",30,"3: Fair","4: Minor"]
[null,null,67,"Valves - South","1983-06-01",30,"3: Fair","4: Minor"]
[null,null,68,"Valves - Central","1983-06-01",30,"3: Fair","4: Minor"]
[null,null,69,"Hydrants - North","1983-06-01",30,"3: Fair","4: Minor"]
[null,null,70,"Hydrants - South","1983-06-01",30,"3: Fair","3: Moderate"]
[null,null,71,"Hydrants - Central","1983-06-01",30,"3: Fair","3: Moderate"]
[null,null,72,"Raw Water Pump","1994-03-10",35,"3: Fair","3: Moderate"]
[null,null,73,"Raw Water Pump","1994-03-10",35,"3: Fair","1: Catastrophic"]
[null,null,74,"Raw Water Pump","1994-03-10",35,"3: Fair","2: Major"]
[null,null,75,"Meters - North","2002-07-01",15,"2: Poor","5: Insignificant"]
[null,null,76,"Meters - South","2002-07-01",15,"2: Poor","5: Insignificant"]
[null,null,77,"Meters - Central","2002-07-01",15,"4: Good","5: Insignificant"]

{"sheet":"Quick_Start & Getting_Started","index":6,"n_rows":2,"n_cols":1,"rows_kept":2,"cut":false,"hidden":false,"merged":[[1,0,2,5]]}
[]
["Double-click on the icons below to open the\nQuick Start Guide and Getting Started documents."]