{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Title Page","Inputs","Revenue Proportions","Comparison of smoothing","Price Path Calc","PTRM output","Demand Forecast","Distribution of Revenue","Standard Estimates"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":7,"defined_names":125,"comments":3,"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":"Title Page","index":0,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Inputs","index":1,"n_rows":8,"n_cols":8,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"Headings","2014/15","2015/16","2016/17","2017/18","2018/19","2019/20"]
[]
[null,"WACC",5.8500000000000003E-2]
[null,"Forecast Inflation",2.3800000000000002E-2]
[]
[null,"Last Updated","2014-10-19"]

{"sheet":"Revenue Proportions","index":2,"n_rows":20,"n_cols":8,"rows_kept":20,"cut":false,"hidden":false,"merged":[[2,1,3,1],[2,2,3,2],[4,1,5,1],[4,2,5,2],[8,1,9,1],[10,1,11,1],[12,1,12,7],[15,1,16,1],[17,1,18,1],[19,1,19,7]]}
[]
[null,"Street Lighting Service","Revenue Proportion","Tariff","Revenue Proportion"]
[null,"Major",{"$f":"'Price Path Calc'!L49","v":0.45257854616371906},"Non-contributed",{"$f":"'Price Path Calc'!J102","v":0.43273917757785252}]
[null,null,null,"Contributed",{"$f":"'Price Path Calc'!J103","v":0.56726082242214748}]
[null,"Major",{"$f":"'Price Path Calc'!L50","v":0.54742145383628105},"Non-contributed",{"$f":"'Price Path Calc'!J104","v":0.47018645907667611}]
[null,null,null,"Contributed",{"$f":"'Price Path Calc'!J105","v":0.529813540923324}]
[]
[null,"$/day","Category","2015-16","2016-17","2017-18","2018-19","2019-20"]
[null,"Major Street Lights","non-contributed",{"$f":"'Price Path Calc'!D62","v":0.77873267507473232},{"$f":"'Price Path Calc'!E62","v":0.79667608817935043},{"$f":"'Price Path Calc'!F62","v":0.81503295006318177},{"$f":"'Price Path Calc'!G62","v":0.83381278733590936},{"$f":"'Price Path Calc'!H62","v":0.85302534611758052}]
[null,null,"contributed",{"$f":"'Price Path Calc'!D69","v":0.27082275153301538},{"$f":"'Price Path Calc'!E69","v":0.27706299887902525},{"$f":"'Price Path Calc'!F69","v":0.28344703284088979},{"$f":"'Price Path Calc'!G69","v":0.28997816652300251},{"$f":"'Price Path Calc'!H69","v":0.29665978936969073}]
[null,"Minor Street Lights","non-contributed",{"$f":"'Price Path Calc'!D65","v":0.35686598366752131},{"$f":"'Price Path Calc'!E65","v":0.36508882312564184},{"$f":"'Price Path Calc'!F65","v":0.37350113171741062},{"$f":"'Price Path Calc'!G65","v":0.38210727515527876},{"$f":"'Price Path Calc'!H65","v":0.39091171974562955}]
[null,null,"contributed",{"$f":"'Price Path Calc'!D71","v":0.13077766408899619},{"$f":"'Price Path Calc'!E71","v":0.13379101864148193},{"$f":"'Price Path Calc'!F71","v":0.13687380634772708},{"$f":"'Price Path Calc'!G71","v":0.14002762707351477},{"$f":"'Price Path Calc'!H71","v":0.1432541175484372}]
[null,"All prices are exclusive of GST"]
[]
[null,"Forecast Luminaries\n(avg pa)","Category","2015-16","2016-17","2017-18","2018-19","2019-20"]
[null,"Major Street Lights","non-contributed",{"$f":"'Price Path Calc'!D12","v":39907},{"$f":"'Price Path Calc'!E12","v":39903},{"$f":"'Price Path Calc'!F12","v":39899},{"$f":"'Price Path Calc'!G12","v":39895},{"$f":"'Price Path Calc'!H12","v":39891}]
[null,null,"contributed",{"$f":"'Price Path Calc'!D14","v":49203},{"$f":"'Price Path Calc'!E14","v":50781},{"$f":"'Price Path Calc'!F14","v":52385},{"$f":"'Price Path Calc'!G14","v":54017},{"$f":"'Price Path Calc'!H14","v":55677}]
[null,"Minor Street Lights","non-contributed",{"$f":"'Price Path Calc'!D13","v":108439},{"$f":"'Price Path Calc'!E13","v":108430},{"$f":"'Price Path Calc'!F13","v":108421},{"$f":"'Price Path Calc'!G13","v":108411},{"$f":"'Price Path Calc'!H13","v":108401}]
[null,null,"contributed",{"$f":"'Price Path Calc'!D15","v":114767},{"$f":"'Price Path Calc'!E15","v":118536},{"$f":"'Price Path Calc'!F15","v":122369},{"$f":"'Price Path Calc'!G15","v":126266},{"$f":"'Price Path Calc'!H15","v":130231}]
[null,"All prices are exclusive of GST"]

{"sheet":"Comparison of smoothing","index":3,"n_rows":52,"n_cols":12,"rows_kept":52,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Base without smoothing"]
[]
[null,"$ per luminaire per day",{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"}]
[null,"Rate 1 - Major (base)",{"$f":"'Price Path Calc'!C31","v":1.2},{"$f":"'Price Path Calc'!D31","v":0.77873267507473232},{"$f":"'Price Path Calc'!E31","v":0.80270084091603811},{"$f":"'Price Path Calc'!F31","v":0.83752964136469754},{"$f":"'Price Path Calc'!G31","v":0.87308712550119727},{"$f":"'Price Path Calc'!H31","v":0.90391407916603217}]
[null,"      ROA & Depn",{"$f":"'Price Path Calc'!C32","v":0.87372998616327335},{"$f":"'Price Path Calc'!D32","v":0.50790992354171693},{"$f":"'Price Path Calc'!E32","v":0.53629645555824257},{"$f":"'Price Path Calc'!F32","v":0.56766511703750389},{"$f":"'Price Path Calc'!G32","v":0.59817737874986499},{"$f":"'Price Path Calc'!H32","v":0.63133210446156662}]
[null,"      Opex",{"$f":"'Price Path Calc'!C33","v":0.3262700138367266},{"$f":"'Price Path Calc'!D33","v":0.27082275153301538},{"$f":"'Price Path Calc'!E33","v":0.26640438535779559},{"$f":"'Price Path Calc'!F33","v":0.2698645243271936},{"$f":"'Price Path Calc'!G33","v":0.27490974675133228},{"$f":"'Price Path Calc'!H33","v":0.27258197470446555}]
[null,"Rate 1 - Minor (base)",{"$f":"'Price Path Calc'!C34","v":0.48},{"$f":"'Price Path Calc'!D34","v":0.35686598366752131},{"$f":"'Price Path Calc'!E34","v":0.36746761710222953},{"$f":"'Price Path Calc'!F34","v":0.38320052072146987},{"$f":"'Price Path Calc'!G34","v":0.39932462123893947},{"$f":"'Price Path Calc'!H34","v":0.41305461264349175}]
[null,"      ROA & Depn",{"$f":"'Price Path Calc'!C35","v":0.34699507389162559},{"$f":"'Price Path Calc'!D35","v":0.2260883195785251},{"$f":"'Price Path Calc'!E35","v":0.23872003408182579},{"$f":"'Price Path Calc'!F35","v":0.25267871568401162},{"$f":"'Price Path Calc'!G35","v":0.26625817749987463},{"$f":"'Price Path Calc'!H35","v":0.28101361396113683}]
[null,"      Opex",{"$f":"'Price Path Calc'!C36","v":0.13300492610837439},{"$f":"'Price Path Calc'!D36","v":0.13077766408899619},{"$f":"'Price Path Calc'!E36","v":0.12874758302040371},{"$f":"'Price Path Calc'!F36","v":0.13052180503745825},{"$f":"'Price Path Calc'!G36","v":0.13306644373906484},{"$f":"'Price Path Calc'!H36","v":0.13204099868235492}]
[null,"Rate 2 - Major (base)"]
[null,"      Opex",{"$f":"'Price Path Calc'!C38","v":0.33},{"$f":"'Price Path Calc'!D38","v":0.27082275153301538},{"$f":"'Price Path Calc'!E38","v":0.26640438535779559},{"$f":"'Price Path Calc'!F38","v":0.26986452432719354},{"$f":"'Price Path Calc'!G38","v":0.27490974675133234},{"$f":"'Price Path Calc'!H38","v":0.27258197470446555}]
[null,"Rate 2 - Minor (base)"]
[null,"      Opex",{"$f":"'Price Path Calc'!C40","v":0.13},{"$f":"'Price Path Calc'!D40","v":0.13077766408899619},{"$f":"'Price Path Calc'!E40","v":0.12874758302040371},{"$f":"'Price Path Calc'!F40","v":0.13052180503745825},{"$f":"'Price Path Calc'!G40","v":0.13306644373906484},{"$f":"'Price Path Calc'!H40","v":0.13204099868235494}]
[]
[null,"Total Revenue",{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"},null,"NPV"]
[null,"Non-contributed - Major",null,{"$f":"'Price Path Calc'!D49","v":11343062.97543568},{"$f":"'Price Path Calc'!E49","v":11691012.654101523},{"$f":"'Price Path Calc'!F49","v":12197057.233695675},{"$f":"'Price Path Calc'!G49","v":12713610.968232647},{"$f":"'Price Path Calc'!H49","v":13161183.334184451},null,{"$f":"NPV(WACC,D17:H17)","v":51467379.774937905},{"$f":"J17/$J$21","v":0.31774749862826862},{"$f":"K17+K19","v":0.45257854616371906}]
[null,"Non-contributed - Minor",null,{"$f":"'Price Path Calc'!D50","v":14124839.497066656},{"$f":"'Price Path Calc'!E50","v":14543247.508674081},{"$f":"'Price Path Calc'!F50","v":15164649.034857007},{"$f":"'Price Path Calc'!G50","v":15801281.252294153},{"$f":"'Price Path Calc'!H50","v":16343069.568786008},null,{"$f":"NPV(WACC,D18:H18)","v":63997552.076753184},{"$f":"J18/$J$21","v":0.39510583557282353},{"$f":"K18+K20","v":0.54742145383628105}]
[null,"Contributed - Major",null,{"$f":"'Price Path Calc'!D51","v":4863731.5229428187},{"$f":"'Price Path Calc'!E51","v":4937822.59889179},{"$f":"'Price Path Calc'!F51","v":5159951.3840112118},{"$f":"'Price Path Calc'!G51","v":5420176.9234473528},{"$f":"'Price Path Calc'!H51","v":5539439.5110514928},null,{"$f":"NPV(WACC,D19:H19)","v":21839355.963201806},{"$f":"J19/$J$21","v":0.13483104753545047}]
[null,"Contributed - Minor",null,{"$f":"'Price Path Calc'!D52","v":5478270.4636931662},{"$f":"'Price Path Calc'!E52","v":5570346.5778308995},{"$f":"'Price Path Calc'!F52","v":5829715.3076294865},{"$f":"'Price Path Calc'!G52","v":6132645.1685822178},{"$f":"'Price Path Calc'!H52","v":6276478.4242816446},null,{"$f":"NPV(WACC,D20:H20)","v":24671431.890612558},{"$f":"J20/$J$21","v":0.15231561826345752}]
[null,null,null,{"$f":"SUM(D17:D20)","v":35809904.459138319},{"$f":"SUM(E17:E20)","v":36742429.339498296},38351372.960193381,40067714.312556371,41320170.838303596,null,{"$f":"SUM(J17:J20)","v":161975719.70550543}]
[]
[null,"Pricing based on 2015/16 Base smoothed"]
[]
[null,null,"CPI",null,{"$f":"'Price Path Calc'!E58","v":2.3800000000000002E-2},{"$f":"'Price Path Calc'!F58","v":2.3800000000000002E-2},{"$f":"'Price Path Calc'!G58","v":2.3800000000000002E-2},{"$f":"'Price Path Calc'!H58","v":2.3800000000000002E-2}]
[null,null,"X",null,{"$f":"'Price Path Calc'!E59","v":7.4056109559923983E-4},{"$f":"'Price Path Calc'!F59","v":7.4056109559923983E-4},{"$f":"'Price Path Calc'!G59","v":7.4056109559923983E-4},{"$f":"'Price Path Calc'!H59","v":7.4056109559923983E-4}]
[]
[null,"$ per luminaire per day",{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"}]
[null,"Rate 1 - Major (15/16)",{"$f":"'Price Path Calc'!C62","v":1.2},{"$f":"'Price Path Calc'!D62","v":0.77873267507473232},{"$f":"'Price Path Calc'!E62","v":0.79667608817935043},{"$f":"'Price Path Calc'!F62","v":0.81503295006318177},{"$f":"'Price Path Calc'!G62","v":0.83381278733590936},{"$f":"'Price Path Calc'!H62","v":0.85302534611758052}]
[null,"      ROA & Depn",null,{"$f":"'Price Path Calc'!D63","v":0.50790992354171693},{"$f":"'Price Path Calc'!E63","v":0.51961308930032524},{"$f":"'Price Path Calc'!F63","v":0.53158591722229198},{"$f":"'Price Path Calc'!G63","v":0.5438346208129069},{"$f":"'Price Path Calc'!H63","v":0.55636555674788979}]
[null,"      Opex",null,{"$f":"'Price Path Calc'!D64","v":0.27082275153301538},{"$f":"'Price Path Calc'!E64","v":0.27706299887902525},{"$f":"'Price Path Calc'!F64","v":0.28344703284088979},{"$f":"'Price Path Calc'!G64","v":0.28997816652300251},{"$f":"'Price Path Calc'!H64","v":0.29665978936969073}]
[null,"Rate 1 - Minor (15/16)",{"$f":"'Price Path Calc'!C65","v":0.48},{"$f":"'Price Path Calc'!D65","v":0.35686598366752131},{"$f":"'Price Path Calc'!E65","v":0.36508882312564184},{"$f":"'Price Path Calc'!F65","v":0.37350113171741062},{"$f":"'Price Path Calc'!G65","v":0.38210727515527876},{"$f":"'Price Path Calc'!H65","v":0.39091171974562955}]
[null,"      ROA & Depn",null,{"$f":"'Price Path Calc'!D66","v":0.2260883195785251},{"$f":"'Price Path Calc'!E66","v":0.23129780448415987},{"$f":"'Price Path Calc'!F66","v":0.23662732536968351},{"$f":"'Price Path Calc'!G66","v":0.24207964808176397},{"$f":"'Price Path Calc'!H66","v":0.24765760219719238}]
[null,"      Opex",null,{"$f":"'Price Path Calc'!D67","v":0.13077766408899619},{"$f":"'Price Path Calc'!E67","v":0.13379101864148193},{"$f":"'Price Path Calc'!F67","v":0.13687380634772708},{"$f":"'Price Path Calc'!G67","v":0.14002762707351477},{"$f":"'Price Path Calc'!H67","v":0.1432541175484372}]
[null,"Rate 2 - Major (15/16)"]
[null,"      Opex",{"$f":"'Price Path Calc'!C69","v":0.33},{"$f":"'Price Path Calc'!D69","v":0.27082275153301538},{"$f":"'Price Path Calc'!E69","v":0.27706299887902525},{"$f":"'Price Path Calc'!F69","v":0.28344703284088979},{"$f":"'Price Path Calc'!G69","v":0.28997816652300251},{"$f":"'Price Path Calc'!H69","v":0.29665978936969073}]
[null,"Rate 2 - Minor (15/16)"]
[null,"      Opex",{"$f":"'Price Path Calc'!C71","v":0.13},{"$f":"'Price Path Calc'!D71","v":0.13077766408899619},{"$f":"'Price Path Calc'!E71","v":0.13379101864148193},{"$f":"'Price Path Calc'!F71","v":0.13687380634772708},{"$f":"'Price Path Calc'!G71","v":0.14002762707351477},{"$f":"'Price Path Calc'!H71","v":0.1432541175484372}]
[]
[null,"Total Revenue",{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"},null,"NPV"]
[null,"Non-contributed - Major",null,{"$f":"'Price Path Calc'!D80","v":11343062.97543568},{"$f":"'Price Path Calc'!E80","v":11603264.570516527},{"$f":"'Price Path Calc'!F80","v":11869434.881218374},{"$f":"'Price Path Calc'!G80","v":12141710.820029628},{"$f":"'Price Path Calc'!H80","v":12420232.439921387},null,{"$f":"NPV(WACC,D41:H41)","v":50099626.077263705},{"$f":"J41/$J$21","v":0.30930330896724428},{"$f":"K41+K43","v":0.45030940526555285}]
[null,"Non-contributed - Minor",null,{"$f":"'Price Path Calc'!D81","v":14124839.497066656},{"$f":"'Price Path Calc'!E81","v":14449102.098402372},{"$f":"'Price Path Calc'!F81","v":14780808.663705682},{"$f":"'Price Path Calc'!G81","v":15119990.609503508},{"$f":"'Price Path Calc'!H81","v":15466955.786233285},null,{"$f":"NPV(WACC,D42:H42)","v":62387826.981321424},{"$f":"J42/$J$21","v":0.38516777140889535},{"$f":"K42+K44","v":0.54421690201965256}]
[null,"Contributed - Major",null,{"$f":"'Price Path Calc'!D82","v":4863731.5229428187},{"$f":"'Price Path Calc'!E82","v":5135380.6933176601},{"$f":"'Price Path Calc'!F82","v":5419656.0776100541},{"$f":"'Price Path Calc'!G82","v":5717268.9766916549},{"$f":"'Price Path Calc'!H82","v":6028751.3888487387},null,{"$f":"NPV(WACC,D43:H43)","v":22839563.930782329},{"$f":"J43/$J$21","v":0.14100609629830854}]
[null,"Contributed - Minor",null,{"$f":"'Price Path Calc'!D83","v":5478270.4636931662},{"$f":"'Price Path Calc'!E83","v":5788554.0477756457},{"$f":"'Price Path Calc'!F83","v":6113425.4452722305},{"$f":"'Price Path Calc'!G83","v":6453465.8514235113},{"$f":"'Price Path Calc'!H83","v":6809486.348594442},null,{"$f":"NPV(WACC,D44:H44)","v":25762097.399212338},{"$f":"J44/$J$21","v":0.15904913061075723}]
[null,null,null,{"$f":"SUM(D41:D44)","v":35809904.459138319},{"$f":"SUM(E41:E44)","v":36976301.410012208},38183325.067806341,39432436.257648304,40725425.963597849,null,{"$f":"SUM(J41:J44)","v":161089114.38857979}]
[]
[null,null,null,{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"},null,"NPV"]
[null,"Non-contributed - Major",null,{"$f":"D41-$D$17","v":0},{"$f":"E41-$E$17","v":-87748.08358499594},{"$f":"F41-$F$17","v":-327622.35247730091},{"$f":"G41-$G$17","v":-571900.14820301905},{"$f":"H41-$H$17","v":-740950.89426306449},null,{"$f":"NPV(WACC,D48:H48)","v":-1367753.6976742032}]
[null,"Non-contributed - Minor",null,{"$f":"D42-$D$18","v":0},{"$f":"E42-$E$18","v":-94145.410271709785},{"$f":"F42-$F$18","v":-383840.37115132436},{"$f":"G42-$G$18","v":-681290.64279064536},{"$f":"H42-$H$18","v":-876113.78255272284},null,{"$f":"NPV(WACC,D49:H49)","v":-1609725.095431763}]
[null,"Contributed - Major",null,{"$f":"D43-$D$19","v":0},{"$f":"E43-$E$19","v":197558.09442587011},{"$f":"F43-$F$19","v":259704.69359884225},{"$f":"G43-$G$19","v":297092.05324430205},{"$f":"H43-$H$19","v":489311.87779724598},null,{"$f":"NPV(WACC,D50:H50)","v":1000207.9675805253}]
[null,"Contributed - Minor",null,{"$f":"D44-$D$20","v":0},{"$f":"E44-$E$20","v":218207.46994474623},{"$f":"F44-$F$20","v":283710.137642744},{"$f":"G44-$G$20","v":320820.68284129351},{"$f":"H44-$H$20","v":533007.92431279738},null,{"$f":"NPV(WACC,D51:H51)","v":1090665.50859978}]
[null,null,null,{"$f":"SUM(D48:D51)","v":0},{"$f":"SUM(E48:E51)","v":233872.07051391061},-168047.89238703903,-635278.05490806885,-594744.87470574398,null,{"$f":"SUM(J48:J51)","v":-886605.31692566117}]

{"sheet":"Price Path Calc","index":4,"n_rows":105,"n_cols":13,"rows_kept":105,"cut":false,"hidden":false,"merged":[]}
[]
["PTRM Output - pre smoothed"]
[]
[null,null,{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"},null,"NPV"]
[null,"ROA & Depn (incl Tax)",null,{"$f":"'PTRM output'!D36","v":16346872.701027568},{"$f":"'PTRM output'!E36","v":17258751.527996019},{"$f":"'PTRM output'!F36","v":18266406.581317291},{"$f":"'PTRM output'!G36","v":19246304.65925014},{"$f":"'PTRM output'!H36","v":20311018.397318304}]
[null,"O&M (incl Tax)",null,{"$f":"'PTRM output'!D37","v":19463031.758110754},{"$f":"'PTRM output'!E37","v":19483677.811502282},{"$f":"'PTRM output'!F37","v":20084966.37887609},{"$f":"'PTRM output'!G37","v":20821409.653306231},{"$f":"'PTRM output'!H37","v":21009152.440985292}]
[null,null,44390000,{"$f":"SUM(D5:D6)","v":35809904.459138319},{"$f":"SUM(E5:E6)","v":36742429.339498296},38351372.960193381,40067714.312556371,41320170.838303596,null,{"$f":"NPV(WACC,'Price Path Calc'!D7:H7)","v":161975719.70550549}]
[null,null,null,{"$f":"(D7/C7)-1","v":-0.19328892860693137},{"$f":"(E7/D7)-1","v":2.6040976496434221E-2},4.3789799684406328E-2,4.4753061491291657E-2,3.1258496952862869E-2]
["Forecast Luminaires",null,null,39400000]
[null,null,null,{"$f":"(D9/C7)-1","v":-0.11241270556431626}]
[null,"Luminaires for each Streetlight Tariff Class (avg for year)",{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"}]
[null,"Non-contributed - Major",{"$f":"ROUND('Demand Forecast'!$B33,0)","v":39911},{"$f":"ROUND('Demand Forecast'!$B34,0)","v":39907},{"$f":"ROUND('Demand Forecast'!$B35,0)","v":39903},{"$f":"ROUND('Demand Forecast'!$B36,0)","v":39899},{"$f":"ROUND('Demand Forecast'!$B37,0)","v":39895},{"$f":"ROUND('Demand Forecast'!$B38,0)","v":39891}]
[null,"Non-contributed - Minor",{"$f":"ROUND('Demand Forecast'!$D33,0)","v":108449},{"$f":"ROUND('Demand Forecast'!$D34,0)","v":108439},{"$f":"ROUND('Demand Forecast'!$D35,0)","v":108430},{"$f":"ROUND('Demand Forecast'!$D36,0)","v":108421},{"$f":"ROUND('Demand Forecast'!$D37,0)","v":108411},{"$f":"ROUND('Demand Forecast'!$D38,0)","v":108401}]
[null,"Contributed - Major",{"$f":"ROUND('Demand Forecast'!$I33,0)","v":47652},{"$f":"ROUND('Demand Forecast'!$I34,0)","v":49203},{"$f":"ROUND('Demand Forecast'!$I35,0)","v":50781},{"$f":"ROUND('Demand Forecast'!$I36,0)","v":52385},{"$f":"ROUND('Demand Forecast'!$I37,0)","v":54017},{"$f":"ROUND('Demand Forecast'!$I38,0)","v":55677}]
[null,"Contributed - Minor",{"$f":"ROUND('Demand Forecast'!$K33,0)","v":111062},{"$f":"ROUND('Demand Forecast'!$K34,0)","v":114767},{"$f":"ROUND('Demand Forecast'!$K35,0)","v":118536},{"$f":"ROUND('Demand Forecast'!$K36,0)","v":122369},{"$f":"ROUND('Demand Forecast'!$K37,0)","v":126266},{"$f":"ROUND('Demand Forecast'!$K38,0)","v":130231}]
[null,null,null,{"$f":"SUM(D12:D15)","v":312316},{"$f":"SUM(E12:E15)","v":317650},{"$f":"SUM(F12:F15)","v":323074},328589,{"$f":"SUM(H12:H15)","v":334200}]
[]
[null,"% split in Street Light Asset Base derived from standard estimates and number of lamps per streetlight type"]
[null,"Major",null,{"$f":"ROA_Major","v":0.45257854616371901}]
[null,"Minor",null,{"$f":"ROA_Minor","v":0.54742145383628094}]
[]
[null,"% weighting",{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"}]
[null,"Non-contributed - Major",{"$f":"C12/(C12+C14)","v":0.45579754005687334},{"$f":"D12/(D12+D14)","v":0.44783974862529458},0.44002249569935159,0.43235013653504401,0.42481259051026493,0.41740959316926168]
[null,"Non-contributed - Minor",0.49404813426206434,0.48582475381486162,0.47773675352255401,0.46978205294856795,0.46195835126578233,0.454260115994502]
[null,"Contributed - Major",{"$f":"C14/(C12+C14)","v":0.54420245994312666},0.55216025137470537,0.55997750430064841,0.56764986346495605,0.57518740948973512,0.58259040683073837]
[null,"Contributed - Minor",0.50595186573793571,0.51417524618513843,0.52226324647744593,0.53021794705143199,0.53804164873421767,0.545739884005498]
[]
["Calculated $ per luminaire per day"]
[]
[null,"$ per luminaire per day",{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"}]
[null,"Rate 1 - Major",1.2,{"$f":"SUM(D32:D33)","v":0.77873267507473232},{"$f":"SUM(E32:E33)","v":0.80270084091603811},0.83752964136469754,0.87308712550119727,0.90391407916603217]
[null,"      ROA & Depn",{"$f":"0.7367/(0.7367+0.2751)*C$31","v":0.87372998616327335},{"$f":"D$5*ROA_Major/D12/365","v":0.50790992354171693},{"$f":"E5*ROA_Major/E12/365","v":0.53629645555824257},{"$f":"F5*ROA_Major/F12/365","v":0.56766511703750389},{"$f":"G5*ROA_Major/G12/365","v":0.59817737874986499},{"$f":"H5*ROA_Major/H12/365","v":0.63133210446156662}]
[null,"      Opex",{"$f":"0.2751/(0.7367+0.2751)*C$31","v":0.3262700138367266},{"$f":"D$6*ROA_Major*D23/D12/365","v":0.27082275153301538},{"$f":"E6*ROA_Major*E23/E12/365","v":0.26640438535779559},{"$f":"F6*ROA_Major*F23/F12/365","v":0.2698645243271936},{"$f":"G6*ROA_Major*G23/G12/365","v":0.27490974675133228},{"$f":"H6*ROA_Major*H23/H12/365","v":0.27258197470446555}]
[null,"Rate 1 - Minor",0.48,{"$f":"SUM(D35:D36)","v":0.35686598366752131},{"$f":"SUM(E35:E36)","v":0.36746761710222953},0.38320052072146987,0.39932462123893947,0.41305461264349175]
[null,"      ROA & Depn",{"$f":"0.2935/(0.2935+0.1125)*C$34","v":0.34699507389162559},{"$f":"D$5*ROA_Minor/D13/365","v":0.2260883195785251},{"$f":"E$5*ROA_Minor/E13/365","v":0.23872003408182579},{"$f":"F$5*ROA_Minor/F13/365","v":0.25267871568401162},{"$f":"G$5*ROA_Minor/G13/365","v":0.26625817749987463},{"$f":"H$5*ROA_Minor/H13/365","v":0.28101361396113683}]
[null,"      Opex",{"$f":"0.1125/(0.2935+0.1125)*C$34","v":0.13300492610837439},{"$f":"D$6*ROA_Minor*D24/D13/365","v":0.13077766408899619},{"$f":"E$6*ROA_Minor*E24/E13/365","v":0.12874758302040371},{"$f":"F$6*ROA_Minor*F24/F13/365","v":0.13052180503745825},{"$f":"G$6*ROA_Minor*G24/G13/365","v":0.13306644373906484},{"$f":"H$6*ROA_Minor*H24/H13/365","v":0.13204099868235492}]
[null,"Rate 2 - Major"]
[null,"      Opex",0.33,{"$f":"D$6*ROA_Major*D25/D14/365","v":0.27082275153301538},{"$f":"E$6*ROA_Major*E25/E14/365","v":0.26640438535779559},{"$f":"F$6*ROA_Major*F25/F14/365","v":0.26986452432719354},{"$f":"G$6*ROA_Major*G25/G14/365","v":0.27490974675133234},{"$f":"H$6*ROA_Major*H25/H14/365","v":0.27258197470446555}]
[null,"Rate 2 - Minor"]
[null,"      Opex",0.13,{"$f":"D$6*ROA_Minor*D26/D15/365","v":0.13077766408899619},{"$f":"E$6*ROA_Minor*E26/E15/365","v":0.12874758302040371},{"$f":"F$6*ROA_Minor*F26/F15/365","v":0.13052180503745825},{"$f":"G$6*ROA_Minor*G26/G15/365","v":0.13306644373906484},{"$f":"H$6*ROA_Minor*H26/H15/365","v":0.13204099868235494}]
[]
[null,"Tariff Increase",{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"}]
[null,"Non-contributed - Major",null,{"$f":"(D31-C31)/C31","v":-0.3510561041043897},{"$f":"(E31-D31)/D31","v":3.0778425778789423E-2},4.3389515337884765E-2,4.2455194873534538E-2,3.5307992483726784E-2]
[null,"Non-contributed - Minor",null,{"$f":"(D34-C34)/C34","v":-0.25652920069266388},{"$f":"(E34-D34)/D34","v":2.9707604310601255E-2},4.2814394757575196E-2,4.2077449391540456E-2,3.4383032436001021E-2]
[null,"Contributed - Major",null,{"$f":"(D38-C38)/C38","v":-0.17932499535449889},{"$f":"(E38-D38)/D38","v":-1.6314604848408229E-2},1.2988295837363177E-2,1.8695389609720559E-2,-8.4674045732265855E-3]
[null,"Contributed - Minor",null,{"$f":"(D40-C40)/C40","v":5.9820314538167902E-3},{"$f":"(E40-D40)/D40","v":-1.5523148258795791E-2},1.378062387993232E-2,1.9495889601559773E-2,-7.7062633365383654E-3]
[]
[null,"Total Revenue",{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"},null,"NPV"]
[null,"Non-contributed - Major",null,{"$f":"D12*D31*365","v":11343062.97543568},{"$f":"E12*E31*365","v":11691012.654101523},{"$f":"F12*F31*365","v":12197057.233695675},12713610.968232647,13161183.334184451,null,{"$f":"NPV(WACC,D49:H49)","v":51467379.774937905},{"$f":"J49/$J$53","v":0.31774749862826862},{"$f":"K49+K51","v":0.45257854616371906},{"$f":"J49/(J49+J50)","v":0.44574035553102104}]
[null,"Non-contributed - Minor",null,{"$f":"D13*D34*365","v":14124839.497066656},{"$f":"E13*E34*365","v":14543247.508674081},{"$f":"F13*F34*365","v":15164649.034857007},15801281.252294153,16343069.568786008,null,{"$f":"NPV(WACC,D50:H50)","v":63997552.076753184},{"$f":"J50/$J$53","v":0.39510583557282353},{"$f":"K50+K52","v":0.54742145383628105},{"$f":"J50/(J50+J49)","v":0.55425964446897891}]
[null,"Contributed - Major",null,{"$f":"D14*D38*365","v":4863731.5229428187},{"$f":"E14*E38*365","v":4937822.59889179},5159951.3840112118,5420176.9234473528,5539439.5110514928,null,{"$f":"NPV(WACC,D51:H51)","v":21839355.963201806},{"$f":"J51/$J$53","v":0.13483104753545047},null,{"$f":"J51/(J51+J52)","v":0.46955463390222391}]
[null,"Contributed - Minor",null,{"$f":"D15*D40*365","v":5478270.4636931662},{"$f":"E15*E40*365","v":5570346.5778308995},5829715.3076294865,6132645.1685822178,6276478.4242816446,null,{"$f":"NPV(WACC,D52:H52)","v":24671431.890612558},{"$f":"J52/$J$53","v":0.15231561826345752},null,{"$f":"J52/(J52+J51)","v":0.53044536609777604}]
[null,null,null,{"$f":"SUM(D49:D52)","v":35809904.459138319},{"$f":"SUM(E49:E52)","v":36742429.339498296},38351372.960193381,40067714.312556371,41320170.838303596,null,{"$f":"SUM(J49:J52)","v":161975719.70550543}]
[null,null,null,null,{"$f":"E53/D53-1","v":2.6040976496434221E-2},{"$f":"F53/E53-1","v":4.3789799684406328E-2},{"$f":"G53/F53-1","v":4.4753061491291657E-2},{"$f":"H53/G53-1","v":3.1258496952862869E-2}]
[]
["Smoothed $ per luminaire per day - what % year on year for each year across all 4 tariffs gives you the same NPV as above (15/16 Base)"]
[]
[null,null,"CPI",null,2.3800000000000002E-2,2.3800000000000002E-2,2.3800000000000002E-2,2.3800000000000002E-2]
[null,null,"X",null,7.4056109559923983E-4,{"$f":"E59","v":7.4056109559923983E-4},{"$f":"F59","v":7.4056109559923983E-4},{"$f":"G59","v":7.4056109559923983E-4}]
[]
[null,"$ per luminaire per day (smoothed)",{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"}]
[null,"Rate 1 - Major",{"$f":"$C$31","v":1.2},{"$f":"SUM(D63:D64)","v":0.77873267507473232},{"$f":"SUM(E63:E64)","v":0.79667608817935043},0.81503295006318177,0.83381278733590936,0.85302534611758052,null,{"$f":"D62/C62-1","v":-0.3510561041043897}]
[null,"      ROA & Depn",{"$f":"C32","v":0.87372998616327335},{"$f":"$D$32","v":0.50790992354171693},{"$f":"D63*(1+E$58)*(1-E$59)","v":0.51961308930032524},{"$f":"E63*(1+F$58)*(1-F$59)","v":0.53158591722229198},{"$f":"F63*(1+G$58)*(1-G$59)","v":0.5438346208129069},{"$f":"G63*(1+H$58)*(1-H$59)","v":0.55636555674788979},null,-0.41868777358546083]
[null,"      Opex",{"$f":"C33","v":0.3262700138367266},{"$f":"$D$33","v":0.27082275153301538},{"$f":"D64*(1+E$58)*(1-E$59)","v":0.27706299887902525},{"$f":"E64*(1+F$58)*(1-F$59)","v":0.28344703284088979},0.28997816652300251,0.29665978936969073,null,-0.16994286925631596]
[null,"Rate 1 - Minor",{"$f":"$C$34","v":0.48},{"$f":"SUM(D66:D67)","v":0.35686598366752131},{"$f":"SUM(E66:E67)","v":0.36508882312564184},0.37350113171741062,0.38210727515527876,0.39091171974562955,null,-0.25652920069266394]
[null,"      ROA & Depn",{"$f":"C35","v":0.34699507389162559},{"$f":"$D$35","v":0.2260883195785251},{"$f":"D66*(1+E$58)*(1-E$59)","v":0.23129780448415987},{"$f":"E66*(1+F$58)*(1-F$59)","v":0.23662732536968351},0.24207964808176397,0.24765760219719238,null,-0.34843939701248439]
[null,"      Opex",{"$f":"C36","v":0.13300492610837439},{"$f":"$D$36","v":0.13077766408899619},{"$f":"D67*(1+E$58)*(1-E$59)","v":0.13379101864148193},0.13687380634772708,0.14002762707351477,0.1432541175484372,null,-1.6745710738288011E-2]
[null,"Rate 2 - Major"]
[null,"      Opex",{"$f":"$C$38","v":0.33},{"$f":"$D$38","v":0.27082275153301538},{"$f":"D69*(1+E$58)*(1-E$59)","v":0.27706299887902525},{"$f":"E69*(1+F$58)*(1-F$59)","v":0.28344703284088979},0.28997816652300251,0.29665978936969073,null,{"$f":"D69/C69-1","v":-0.17932499535449886}]
[null,"Rate 2 - Minor"]
[null,"      Opex",{"$f":"$C$40","v":0.13},{"$f":"$D$40","v":0.13077766408899619},{"$f":"D71*(1+E$58)*(1-E$59)","v":0.13379101864148193},{"$f":"E71*(1+F$58)*(1-F$59)","v":0.13687380634772708},0.14002762707351477,0.1432541175484372,null,{"$f":"D71/C71-1","v":5.9820314538168162E-3}]
[]
[null,"Tariff Increase",{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"}]
[null,"Non-contributed - Major",null,{"$f":"(D62-C62)/C62","v":-0.3510561041043897},{"$f":"(E62-D62)/D62","v":2.3041813550325398E-2},2.3041813550325592E-2,2.3041813550325495E-2,2.3041813550325418E-2]
[null,"Non-contributed - Minor",null,{"$f":"(D65-C65)/C65","v":-0.25652920069266388},{"$f":"(E65-D65)/D65","v":2.3041813550325477E-2},2.3041813550325436E-2,2.3041813550325498E-2,2.3041813550325328E-2]
[null,"Contributed - Major",null,{"$f":"(D69-C69)/C69","v":-0.17932499535449889},{"$f":"(E69-D69)/D69","v":2.3041813550325477E-2},2.3041813550325502E-2,2.3041813550325325E-2,2.3041813550325342E-2]
[null,"Contributed - Minor",null,{"$f":"(D71-C71)/C71","v":5.9820314538167902E-3},{"$f":"(E71-D71)/D71","v":2.3041813550325488E-2},2.3041813550325495E-2,2.3041813550325464E-2,2.3041813550325464E-2]
[]
[null,"Total Revenue",{"$f":"YEAR_0","v":"2014/15"},{"$f":"YEAR_1","v":"2015/16"},{"$f":"YEAR_2","v":"2016/17"},{"$f":"YEAR_3","v":"2017/18"},{"$f":"YEAR_4","v":"2018/19"},{"$f":"YEAR_5","v":"2019/20"},null,"NPV"]
[null,"Non-contributed - Major",null,{"$f":"D62*D12*365","v":11343062.97543568},{"$f":"E62*E12*365","v":11603264.570516527},11869434.881218374,12141710.820029628,12420232.439921387,null,{"$f":"NPV(WACC,D80:H80)","v":50099626.077263705},{"$f":"J80-$J$49","v":-1367753.6976741999}]
[null,"Non-contributed - Minor",null,{"$f":"D65*D13*365","v":14124839.497066656},{"$f":"E65*E13*365","v":14449102.098402372},14780808.663705682,15119990.609503508,15466955.786233285,null,{"$f":"NPV(WACC,D81:H81)","v":62387826.981321424},{"$f":"J81-$J$50","v":-1609725.09543176}]
[null,"Contributed - Major",null,{"$f":"D69*D14*365","v":4863731.5229428187},{"$f":"E69*E14*365","v":5135380.6933176601},5419656.0776100541,5717268.9766916549,6028751.3888487387,null,{"$f":"NPV(WACC,D82:H82)","v":22839563.930782329},{"$f":"J82-$J$51","v":1000207.9675805233}]
[null,"Contributed - Minor",null,{"$f":"D71*D15*365","v":5478270.4636931662},{"$f":"E71*E15*365","v":5788554.0477756457},6113425.4452722305,6453465.8514235113,6809486.348594442,null,{"$f":"NPV(WACC,D83:H83)","v":25762097.399212338},{"$f":"J83-$J$52","v":1090665.5085997805}]
[null,null,null,{"$f":"SUM(D80:D83)","v":35809904.459138319},{"$f":"SUM(E80:E83)","v":36976301.410012208},38183325.067806341,39432436.257648304,40725425.963597849,null,{"$f":"SUM(J80:J83)","v":161089114.38857979},-886605.31692565605]
[null,null,"De-escalated to 14/15",{"$f":"D84/(1+CPI)","v":34977441.354891889},{"$f":"E84/(1+CPI)^2","v":35277127.752737634},{"$f":"F84/(1+CPI)^3","v":35581837.270551838},{"$f":"G84/(1+CPI)^4","v":35891623.971686527},{"$f":"H84/(1+CPI)^5","v":36206788.857109554}]
[null,null,null,{"$f":"D84-D53","v":0},{"$f":"E84-E53","v":233872.07051391155},-168047.89238703996,-635278.05490806699,-594744.87470574677,null,{"$f":"J84-J53","v":-886605.31692564487}]
[]
[null,"Capital Revenue Split",null,null,null,null,null,null,null,"NPV"]
[null,"Major",null,{"$f":"D63*D12*365","v":7398243.8813544437},{"$f":"E63*E12*365","v":7567954.2023580698},7741557.4766070638,7919143.0020257868,8100802.1248439755,null,{"$f":"NPV(WACC,D89:H89)","v":32676293.245213632}]
[null,"Minor",null,{"$f":"D66*D13*365","v":8948628.8196731247},{"$f":"E66*E13*365","v":9154061.6431793701},9364210.504025856,9579095.3057901226,9798911.0835589152,null,{"$f":"NPV(WACC,D90:H90)","v":39525086.754987486}]
[null,null,null,{"$f":"SUM(D89:D90)","v":16346872.701027568},{"$f":"SUM(E89:E90)","v":16722015.845537439},{"$f":"SUM(F89:F90)","v":17105767.98063292},{"$f":"SUM(G89:G90)","v":17498238.307815909},{"$f":"SUM(H89:H90)","v":17899713.208402891},null,{"$f":"SUM(J89:J90)","v":72201380.000201121}]
[null,null,null,{"$f":"D89/D91","v":0.45257854616371901},{"$f":"E89/E91","v":0.45257427527062832},0.45257000360182736,0.45256801643217742,0.45256602888146347,null,0.45257158859183316]
[null,null,null,{"$f":"D90/D91","v":0.54742145383628094},{"$f":"E90/E91","v":0.54742572472937168},0.54742999639817258,0.54743198356782263,0.54743397111853653,null,{"$f":"J90/J91","v":0.54742841140816678}]
[null,"Opex Revenue Split",null,null,null,null,null,null,null,"NPV"]
[null,"Major - NC",null,{"$f":"D64*D12*365","v":3944819.0940812365},{"$f":"E64*E12*365","v":4035310.3681584564},4127877.4046113114,4222567.8180038426,4319430.315077411,null,{"$f":"NPV(WACC,D95:H95)","v":17423332.832050074}]
[null,"Minor - NC",null,{"$f":"D67*D13*365","v":5176210.6773935305},{"$f":"E67*E13*365","v":5295040.4552229987},5416598.1596798254,5540895.3037133859,5668044.7026743712,null,{"$f":"NPV(WACC,D96:H96)","v":22862740.226333927}]
[null,"Major - Cont",null,{"$f":"D69*D14*365","v":4863731.5229428187},{"$f":"E69*E14*365","v":5135380.6933176601},5419656.0776100541,5717268.9766916549,6028751.3888487387,null,{"$f":"NPV(WACC,D97:H97)","v":22839563.930782329}]
[null,"Minor - Cont",null,{"$f":"D71*D15*365","v":5478270.4636931662},{"$f":"E71*E15*365","v":5788554.0477756457},6113425.4452722305,6453465.8514235113,6809486.348594442,null,{"$f":"NPV(WACC,D98:H98)","v":25762097.399212338}]
[null,null,null,{"$f":"SUM(D95:D98)","v":19463031.758110754},20254285.564474761,21077557.087173421,21934197.949832395,22825712.755194962,null,88887734.38837868]
[null,"Major",null,{"$f":"(D95+D97)/D99","v":0.4525785461637189},{"$f":"(E95+E97)/E99","v":0.4527778100236306},0.45297153947842655,0.45316618448642432,0.45335634487781684,null,{"$f":"(J95+J97)/J99","v":0.45296347173065576}]
[null,"Minor",null,{"$f":"(D96+D98)/D99","v":0.54742145383628105},{"$f":"(E96+E98)/E99","v":0.5472221899763694},0.54702846052157339,0.54683381551357568,0.54664365512218316,null,{"$f":"(J96+J98)/J99","v":0.54703652826934412}]
[null,"Major","NC",{"$f":"D95/(D95+D97)","v":0.44783974862529463},{"$f":"E95/(E95+E97)","v":0.44002249569935153},0.43235013653504401,0.42481259051026499,0.41740959316926163,null,{"$f":"J95/(J95+J97)","v":0.43273917757785252}]
[null,null,"Cont",{"$f":"D97/(D97+D95)","v":0.55216025137470548},{"$f":"E97/(E97+E95)","v":0.55997750430064841},0.56764986346495605,0.57518740948973512,0.58259040683073837,null,{"$f":"J97/(J97+J95)","v":0.56726082242214748}]
[null,"Minor","NC",{"$f":"D96/(D96+D98)","v":0.48582475381486162},{"$f":"E96/(E96+E98)","v":0.47773675352255407},0.46978205294856801,0.46195835126578233,0.454260115994502,null,{"$f":"J96/(J96+J98)","v":0.47018645907667611}]
[null,null,"Cont",{"$f":"D98/(D98+D96)","v":0.51417524618513832},{"$f":"E98/(E98+E96)","v":0.52226324647744593},0.53021794705143199,0.53804164873421756,0.54573988400549811,null,{"$f":"J98/(J98+J96)","v":0.529813540923324}]

{"sheet":"PTRM output","index":5,"n_rows":38,"n_cols":9,"rows_kept":38,"cut":false,"hidden":false,"merged":[[45,1,45,5],[49,1,49,7],[51,1,51,7]]}
[]
[null,"ALTERNATIVE CONTROL SERVICES: STREETLIGHTS - (NOMINAL)"]
[]
["PTRM calculation to calculate return of, return on, operating and maintence & tax"]
[]
[null,null,{"$f":"'Demand Forecast'!$A$25-1&\"/\"&RIGHT('Demand Forecast'!$A$25,2)","v":"2014/15"},{"$f":"'Demand Forecast'!$A$26-1&\"/\"&RIGHT('Demand Forecast'!$A$26,2)","v":"2015/16"},{"$f":"'Demand Forecast'!$A$27-1&\"/\"&RIGHT('Demand Forecast'!$A$27,2)","v":"2016/17"},{"$f":"'Demand Forecast'!$A$28-1&\"/\"&RIGHT('Demand Forecast'!$A$28,2)","v":"2017/18"},{"$f":"'Demand Forecast'!$A$29-1&\"/\"&RIGHT('Demand Forecast'!$A$29,2)","v":"2018/19"},{"$f":"'Demand Forecast'!$A$30-1&\"/\"&RIGHT('Demand Forecast'!$A$30,2)","v":"2019/20"}]
[null,"ARR"]
[null,"Return on capital",10700000,{"$f":"[7]Analysis!G15*1000000","v":7291124.484565435},{"$f":"[7]Analysis!H15*1000000","v":7448852.2963321526},{"$f":"[7]Analysis!I15*1000000","v":7584746.5151227443},{"$f":"[7]Analysis!J15*1000000","v":7717752.3152117953},{"$f":"[7]Analysis!K15*1000000","v":7867664.5066754296}]
[null,"Return of capital",14000000,{"$f":"[7]Analysis!G$20*1000000","v":7755499.8871590989},{"$f":"[7]Analysis!H$20*1000000","v":8509650.9023608286},{"$f":"[7]Analysis!I$20*1000000","v":9343721.1232997291},{"$f":"[7]Analysis!J$20*1000000","v":10171904.933723727},{"$f":"[7]Analysis!K$20*1000000","v":11075160.230336722}]
[null,"O&M",14200000,{"$f":"[7]Analysis!G$22*1000000","v":17914920.691884331},{"$f":"[7]Analysis!H$22*1000000","v":17935566.745275851},{"$f":"[7]Analysis!I$22*1000000","v":18574545.926241446},{"$f":"[7]Analysis!J$22*1000000","v":19329697.668091383},{"$f":"[7]Analysis!K$22*1000000","v":19528986.70576198}]
[null,"Benchmark Tax liability",2200000,{"$f":"[7]Analysis!$G$28*1000000","v":2848359.3955294602},{"$f":"[7]Analysis!$G$28*1000000","v":2848359.3955294602},{"$f":"[7]Analysis!$G$28*1000000","v":2848359.3955294602},{"$f":"[7]Analysis!$G$28*1000000","v":2848359.3955294602},{"$f":"[7]Analysis!$G$28*1000000","v":2848359.3955294602}]
[null,"Total unsmoothed revenue - before adjustments",42400000,{"$f":"SUM(D8:D11)","v":35809904.459138326},{"$f":"SUM(E8:E11)","v":36742429.339498296},{"$f":"SUM(F8:F11)","v":38351372.960193381},{"$f":"SUM(G8:G11)","v":40067714.312556371},{"$f":"SUM(H8:H11)","v":41320170.838303596}]
[null,"NPV",{"$f":"NPV(WACC,D12:H12)","v":161975719.70550549}]
[]
[null,"ADJUSTMENTS (Non-System Revenue allocation)"]
[null,"Return on capital"]
[null,"Return of capital"]
[null,"O&M"]
[null,"Benchmark Tax liability"]
[null,"Total Adjustments"]
[]
[]
[null,"Total Unsmoothed revenue after adjustments",null,{"$f":"D12+D20","v":35809904.459138326},{"$f":"E12+E20","v":36742429.339498296},38351372.960193381,40067714.312556371,41320170.838303596]
[null,"NPV",{"$f":"NPV(WACC,D23:H23)","v":161975719.70550549}]
[null,"Total smoothed revenue after adjustments",46555354.485224739,{"$f":"C25*(1-D28)*(1+CPI)","v":39951510.167745106},{"$f":"D25*(1-E28)*(1+CPI)","v":40902356.109737441},{"$f":"E25*(1-F28)*(1+CPI)","v":41875832.185149193},{"$f":"F25*(1-G28)*(1+CPI)","v":42872476.991155744},{"$f":"G25*(1-H28)*(1+CPI)","v":43892841.943545252},{"$f":"H25/H23-1","v":6.2261870003131881E-2}]
[null,"NPV",{"$f":"NPV(WACC,D25:H25)","v":176743418.88052678}]
[null,null,{"$f":"C24-C26","v":-14767699.175021291}]
[null,"Adjusted revenue X factors",null,0.16179849312492242,0,0,0,0]
[]
[null,"Smoothed"]
[null,"ROA & Depn (incl Tax & Smoothing)",null,{"$f":"(D8+D9)/(D$8+D$9+D$10)*D$25","v":18237475.379783589},{"$f":"E8+E9+D$31/D$33*(E$25-E$8-E$9-E$10)","v":19157717.592993233},{"$f":"F8+F9+$D$31/$D$33*(F$25-F$8-F$9-F$10)","v":19837597.285911001},{"$f":"G8+G9+$D$31/$D$33*(G$25-G$8-G$9-G$10)","v":20470252.430768538},{"$f":"H8+H9+$D$31/$D$33*(H$25-H$8-H$9-H$10)","v":21417472.375505894}]
[null,"O&M (incl Tax & Smoothing)",null,{"$f":"(D10)/(D$8+D$9+D$10)*D$25","v":21714034.787961513},{"$f":"E10+D$32/D$33*(E$25-E$8-E$9-E$10)","v":21744638.516744211},{"$f":"F10+$D$32/$D$33*(F$25-F$8-F$9-F$10)","v":22038234.899238195},{"$f":"G10+$D$32/$D$33*(G$25-G$8-G$9-G$10)","v":22402224.560387205},{"$f":"H10+$D$32/$D$33*(H$25-H$8-H$9-H$10)","v":22475369.568039358}]
[null,null,null,{"$f":"SUM(D31:D32)","v":39951510.167745098},{"$f":"SUM(E31:E32)","v":40902356.109737441},41875832.185149193,42872476.991155744,43892841.943545252]
[]
[null,"Not Smoothed"]
[null,"ROA & Depn (incl Tax & Smoothing)",null,{"$f":"(D8+D9)/(D$8+D$9+D$10)*D$23","v":16346872.701027568},{"$f":"E8+E9+D$36/D$38*(E$23-E$8-E$9-E$10)","v":17258751.527996019},{"$f":"F8+F9+E$36/E$38*(F$23-F$8-F$9-F$10)","v":18266406.581317291},19246304.65925014,20311018.397318304]
[null,"O&M (incl Tax & Smoothing)",null,{"$f":"(D10)/(D$8+D$9+D$10)*D$23","v":19463031.758110754},{"$f":"E10+D$37/D$38*(E$23-E$8-E$9-E$10)","v":19483677.811502282},{"$f":"F10+E$37/E$38*(F$23-F$8-F$9-F$10)","v":20084966.37887609},20821409.653306231,21009152.440985292]
[null,null,null,{"$f":"SUM(D36:D37)","v":35809904.459138319},{"$f":"SUM(E36:E37)","v":36742429.339498296},{"$f":"SUM(F36:F37)","v":38351372.960193381},40067714.312556371,41320170.838303596]

{"sheet":"Demand Forecast","index":6,"n_rows":49,"n_cols":42,"rows_kept":49,"cut":false,"hidden":false,"merged":[[2,19,2,20],[2,21,2,22],[1,19,1,22],[1,24,1,27],[2,34,2,35],[1,34,1,38],[1,40,1,41],[22,24,22,25],[22,26,22,27],[21,24,21,27],[2,24,2,25],[2,26,2,27],[1,29,1,32],[2,29,2,30],[2,31,2,32],[2,36,2,37]]}
["FINAL Forecast of Street lights for 2015 -2020"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"$ per light per day (Fixed)",null,null,null,null,"% split based on lamps",null,null,null,null,"% split based on lamps",null,null,null,null,"Annual $ M",null,null,null,null,null,"Revenue %"]
["Actual Street lights On Hand",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Rate 1",null,"Rate 2",null,null,"Major",null,"Minor",null,null,"Major",null,"Minor",null,null,"Rate 1",null,"Rate 2",null,"Total"]
["EndFinancial Year","Rate 1 Major","% of Rate 1","Rate 1 Minor","% of Rate 1","Rate 1 Total","% Growth",null,"Rate 2 Major","% of Rate 2","Rate 2 Minor","% of Rate 2","Rate 2 Total","% Growth",null,"Total","Growth","% Growth",null,"Major","Minor","Major","Minor",null,"Rate 1","Rate 2","Rate 1","Rate 2",null,"Rate 1","Rate 2","Rate 1","Rate 2",null,"Major","Minor","Major","Minor",null,null,"Major","Minor"]
[2002,38923,{"$f":"IF($F5=0,0,B5/$F5)","v":0.26862159159138432},105976,{"$f":"IF($F5=0,0,D5/$F5)","v":0.73137840840861568},{"$f":"B5+D5","v":144899},null,null,24204,{"$f":"IF($M5=0,0,I5/$M5)","v":0.29518873102018417},57791,{"$f":"IF($M5=0,0,K5/$M5)","v":0.70481126897981583},{"$f":"I5+K5","v":81995},null,null,{"$f":"M5+F5","v":226894},null,null,null,null,null,null,null,null,{"$f":"B5/(B5+I5)","v":0.61658244491263647},{"$f":"I5/(B5+I5)","v":0.38341755508736358},{"$f":"D5/(D5+K5)","v":0.64711449803684506},{"$f":"K5/(D5+K5)","v":0.35288550196315499},null,{"$f":"B5/P5","v":0.17154706603083378},{"$f":"I5/P5","v":0.10667536382628011},{"$f":"D5/P5","v":0.46707272999726746},{"$f":"K5/P5","v":0.25470484014561867}]
[2003,39435,{"$f":"IF($F6=0,0,B6/$F6)","v":0.269788602312376},106735,0.730211397687624,146170,{"$f":"IF($P5=0,0,(F6-F5)/$P5)","v":5.601734730755331E-3},null,25580,{"$f":"IF($M6=0,0,I6/$M6)","v":0.29358766885881854},61549,0.70641233114118152,87129,{"$f":"IF($P5=0,0,(M6-M5)/$P5)","v":2.2627306142956622E-2},null,{"$f":"M6+F6","v":233299},{"$f":"P6-P5","v":6405},{"$f":"IF(P5=0,0,(P6-P5)/P5)","v":2.8229040873711954E-2},null,null,null,null,null,null,0.60655233407675146,{"$f":"I6/(B6+I6)","v":0.39344766592324848},{"$f":"D6/(D6+K6)","v":0.6342551876589575},{"$f":"K6/(D6+K6)","v":0.3657448123410425},null,{"$f":"B6/P6","v":0.16903201471073601},{"$f":"I6/P6","v":0.10964470486371566},{"$f":"D6/P6","v":0.45750303258908098},{"$f":"K6/P6","v":0.26382024783646735}]
[2004,39944,0.27141769949989131,107224,0.72858230050010875,147168,{"$f":"IF($P6=0,0,(F7-F6)/$P6)","v":4.2777723007814007E-3},null,27043,0.28676712299714752,67260,0.71323287700285254,94303,{"$f":"IF($P6=0,0,(M7-M6)/$P6)","v":3.0750238963733235E-2},null,241471,8172,{"$f":"IF(P6=0,0,(P7-P6)/P6)","v":3.5028011264514637E-2},null,null,null,null,null,null,0.59629480346932984,0.40370519653067011,0.61452052910295496,0.38547947089704498,null,0.16541944995465294,0.11199274447035047,0.4440450406052901,0.27854276496970648]
[2005,40153,0.27162339507258532,107673,0.72837660492741463,147826,2.7249649026177057E-3,null,29305,0.28535678117940327,73391,0.71464321882059667,102696,3.4757797002538608E-2,null,250522,9051,3.7482761905156314E-2,null,null,null,null,null,null,0.57809035676235998,0.42190964323764002,0.5946681836256793,0.4053318163743207,null,0.16027734091217538,0.11697575462434437,0.42979458889838018,0.2929523155651001]
[2006,40306,0.27182907666057449,107971,0.72817092333942557,148277,1.8002410965903195E-3,null,31066,0.28388405585203597,78366,0.71611594414796398,109432,2.6887858152178253E-2,null,257709,7187,2.868809924876857E-2,null,null,null,null,null,null,0.56473126716359356,0.43526873283640644,0.57943940280244932,0.42056059719755068,null,0.15640121221998457,0.12054681831057511,0.41896480138450731,0.30408716808493302]
[2007,40388,0.2721269943941354,108028,0.72787300560586454,148416,5.3936804690561831E-4,null,32666,0.27953106281020024,84194,0.72046893718979976,116860,2.8823207571330455E-2,null,265276,7567,2.9362575618236072E-2,null,null,null,null,null,null,0.55285131546527222,0.44714868453472773,0.56199602542893112,0.43800397457106888,null,0.1522489784224732,0.12313967339676413,0.40722869765828795,0.31738265052247472]
[2008,40274,0.27147960903269297,108076,0.72852039096730703,148350,-2.4879747885221432E-4,null,35212,0.28140783837350553,89916,0.71859216162649442,125128,3.1167538714395572E-2,null,273478,8202,3.0918741235543359E-2,null,null,null,null,null,null,0.53352939617942396,0.46647060382057598,0.54586043880560831,0.45413956119439169,null,0.14726595923620914,0.12875624364665531,0.39519083801987731,0.3287869590972583]
[2009,40226,0.2710208591602436,108198,0.72897914083975635,148424,2.7058849340714792E-4,null,37267,0.28372719797789081,94081,0.71627280202210919,131348,2.274405985124946E-2,null,279772,6294,2.3014648344656607E-2,null,null,null,null,null,null,{"$f":"B12/(B12+I12)","v":0.51909204702360212},{"$f":"I12/(B12+I12)","v":0.48090795297639788},{"$f":"D12/(D12+K12)","v":0.53489487292304194},{"$f":"K12/(D12+K12)","v":0.46510512707695806},null,0.14378136482564374,0.13320489541483779,0.38673634245028093,0.3362773973092375]
[2010,40251,0.27105233031872267,108248,0.72894766968127733,148499,2.6807543285246556E-4,null,39300,0.28653504039196243,97856,0.71346495960803757,137156,2.0759761520094934E-2,null,285655,5883,2.1027836952947399E-2,null,null,null,null,null,null,0.50597729758268284,0.49402270241731722,0.52521057330279863,0.47478942669720142,null,0.1409077383557088,0.13757854754861634,0.37894663142602092,0.34256708266965397]
[2011,40171,0.27065759331626466,108249,0.7293424066837354,148420,-2.7655738565752396E-4,null,41804,0.29162393877878462,101545,0.70837606122121533,143349,2.1679998599709441E-2,null,291769,6114,2.1403441214051917E-2,null,0.85,0.34,0.23,0.09,null,0.4900396462336078,0.50996035376639215,0.5159775780050907,0.4840224219949093,null,0.13768083655220398,0.14327772998502239,0.37100925732343054,0.34803217613934312,null,{"$f":"T14*B14*365/1000000","v":12.463052749999999},{"$f":"U14*D14*365/1000000","v":13.4337009},{"$f":"V14*I14*365/1000000","v":3.5094457999999999},{"$f":"W14*K14*365/1000000","v":3.3357532499999993},{"$f":"SUM(AI14:AL14)","v":32.741952699999999},null,{"$f":"(AI14+AK14)/AM14","v":0.48782974846823962},{"$f":"(AJ14+AL14)/AM14","v":0.51217025153176032}]
[2012,39932,0.26937035388081648,108310,0.73062964611918346,{"$f":"B15+D15","v":148242},-6.1007166628394382E-4,null,44170,0.29672374528916629,104689,0.70327625471083377,148859,1.8884802703508598E-2,null,297101,5332,1.8274731037224653E-2,null,0.89,0.36,0.24,0.1,null,0.47480440417588166,0.52519559582411834,0.50850003990629067,0.49149996009370939,null,0.13440547153998136,0.1486699809155809,0.36455616103614596,0.35236838650829178,null,{"$f":"T15*B15*365/1000000","v":12.971910200000002},{"$f":"U15*D15*365/1000000","v":14.231934000000001},{"$f":"V15*I15*365/1000000","v":3.8692919999999997},{"$f":"W15*K15*365/1000000","v":3.8211485000000005},{"$f":"SUM(AI15:AL15)","v":34.8942847},null,{"$f":"(AI15+AK15)/AM15","v":0.48263497431715519},{"$f":"(AJ15+AL15)/AM15","v":0.51736502568284493}]
[2013,39780,0.26858778729035571,108328,0.73141221270964429,{"$f":"B16+D16","v":148108},-4.5102507228181663E-4,null,45898,0.30019294286928938,106997,0.69980705713071056,152895,1.3584605908428447E-2,null,301003,3902,1.313358083614663E-2,null,1.01,0.41,0.27,0.11,null,0.46429655220710103,0.53570344779289902,0.50309067688378029,0.49690932311621966,null,0.13215815124766198,0.1524835300644844,0.35989010076311534,0.35546821792473832,null,{"$f":"T16*B16*365/1000000","v":14.664897000000002},{"$f":"U16*D16*365/1000000","v":16.211285199999999},{"$f":"V16*I16*365/1000000","v":4.5232479000000003},{"$f":"W16*K16*365/1000000","v":4.2959295499999994},{"$f":"SUM(AI16:AL16)","v":39.69535965},null,{"$f":"(AI16+AK16)/AM16","v":0.48338508755644943},{"$f":"(AJ16+AL16)/AM16","v":0.51661491244355051}]
[2014,39913,0.26901716026582911,108453,0.73098283973417089,{"$f":"B17+D17","v":148366},8.5713431427593748E-4,null,46883,0.30032413457350038,109225,0.69967586542649962,156108,1.0674312216157314E-2,null,304474,{"$f":"P17-P16","v":3471},{"$f":"IF(P16=0,0,(P17-P16)/P16)","v":1.1531446530433252E-2},null,1.1000000000000001,0.44,0.3,0.12,null,{"$f":"B17/(B17+I17)","v":0.45984838010968249},{"$f":"I17/(B17+I17)","v":0.54015161989031757},{"$f":"D17/(D17+K17)","v":0.49822673857716443},{"$f":"K17/(D17+K17)","v":0.50177326142283551},null,{"$f":"B17/P17","v":0.1310883687933945},{"$f":"I17/P17","v":0.15398030702128918},{"$f":"D17/P17","v":0.3561979019555036},{"$f":"K17/P17","v":0.35873342222981275},null,{"$f":"T17*B17*365/1000000","v":16.025069500000001},{"$f":"U17*D17*365/1000000","v":17.417551800000002},{"$f":"V17*I17*365/1000000","v":5.1336884999999999},{"$f":"W17*K17*365/1000000","v":4.7840550000000004},{"$f":"SUM(AI17:AL17)","v":43.360364799999999},null,{"$f":"(AI17+AK17)/AM17","v":0.48797463069314395},{"$f":"(AJ17+AL17)/AM17","v":0.51202536930685605}]
[]
[]
["Average growth (of last 5 years)",null,{"$f":"AVERAGE(C13:C17)","v":0.26973704501439777},null,{"$f":"AVERAGE(E13:E17)","v":0.73026295498560234},null,{"$f":"AVERAGE(G13:G17)","v":-4.2488875418976293E-5},null,null,{"$f":"AVERAGE(J13:J17)","v":0.29507996038054063},null,{"$f":"AVERAGE(L13:L17)","v":0.70492003961945948},null,{"$f":"AVERAGE(N13:N17)","v":1.7116696189579748E-2},null,null,null,{"$f":"AVERAGE(R13:R17)","v":1.7074207314160771E-2},null,null,null,null,null,null,{"$f":"SUM($B$13:$B$17)/(SUM($B$13:$B$17)+SUM($I$13:$I$17))","v":0.47846458519691365},{"$f":"SUM($I$13:$I$17)/(SUM($B$13:$B$17)+SUM($I$13:$I$17))","v":0.52153541480308629},{"$f":"SUM($D$13:$D$17)/(SUM($D$13:$D$17)+SUM($K$13:$K$17))","v":0.51001789245691687},{"$f":"SUM($K$13:$K$17)/(SUM($D$13:$D$17)+SUM($K$13:$K$17))","v":0.48998210754308313}]
["Total split",null,null,null,null,null,{"$f":"G20/R20","v":-2.4884830456367631E-3},null,null,null,null,null,null,{"$f":"N20/R20","v":1.0024884830456369}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"% split based on lamps"]
["Forecast Street lights On Hand",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Major",null,"Minor"]
["Financial Year","Rate 1 Major",null,"Rate 1 Minor",null,"Rate 1 Total","Growth",null,"Rate 2 Major",null,"Rate 2 Minor",null,"Rate 2 Total","Growth",null,"Total","Growth","Check",null,null,null,null,null,null,"Rate 1","Rate 2","Rate 1","Rate 2"]
[{"$f":"A17+1","v":2015},{"$f":"ROUND(B17+(F25-F17)*$C$20,0)","v":39909},null,{"$f":"ROUND(D17+((F25-F17)*$E$20),0)","v":108444},{"$f":"F25-D25-B25","v":0},{"$f":"ROUND(F17+(P17*$R$20)*$G$21,0)","v":148353},null,null,{"$f":"ROUND(I17+(M25-M17)*J20,0)","v":48421},null,{"$f":"ROUND(K17+((M25-M17)*L20),0)","v":112899},{"$f":"M25-I25-K25","v":0},{"$f":"ROUND(M17+(P17*$R$20)*$N$21,0)","v":161320},null,null,{"$f":"ROUND(P17*(1+$R$20),0)","v":309673},{"$f":"P25-M25-F25","v":0},{"$f":"M25+F25","v":309673},null,null,null,null,null,null,{"$f":"B25/(B25+I25)","v":0.4518170496999887},{"$f":"I25/(B25+I25)","v":0.5481829503000113},{"$f":"D25/(D25+K25)","v":0.48993643349913935},{"$f":"K25/(D25+K25)","v":0.51006356650086071}]
[{"$f":"A25+1","v":2016},{"$f":"ROUND(B25+(F26-F25)*$C$20,0)","v":39905},null,{"$f":"ROUND(D25+((F26-F25)*$E$20),0)","v":108435},{"$f":"F26-D26-B26","v":0},{"$f":"ROUND(F25+(P25*$R$20)*$G$21,0)","v":148340},{"$f":"F26-F25","v":-13},null,{"$f":"ROUND(I25+(M26-M25)*$J$20,0)","v":49985},null,{"$f":"ROUND(K25+((M26-M25)*$L$20),0)","v":116636},{"$f":"M26-I26-K26","v":0},{"$f":"ROUND(M25+(P25*$R$20)*$N$21,0)","v":166621},{"$f":"M26-M25","v":5301},null,{"$f":"ROUND(P25*(1+$R$20),0)","v":314960},{"$f":"P26-P25","v":5287},{"$f":"P26-M26-F26","v":-1},null,null,null,null,null,null,{"$f":"B26/(B26+I26)","v":0.44393147179886527},{"$f":"I26/(B26+I26)","v":0.55606852820113473},{"$f":"D26/(D26+K26)","v":0.48178130456611468},{"$f":"K26/(D26+K26)","v":0.51821869543388532}]
[2017,{"$f":"ROUND(B26+(F27-F26)*$C$20,0)","v":39901},null,{"$f":"ROUND(D26+((F27-F26)*$E$20),0)","v":108426},0,{"$f":"ROUND(F26+(P26*$R$20)*$G$21,0)","v":148327},{"$f":"F27-F26","v":-13},null,{"$f":"ROUND(I26+(M27-M26)*$J$20,0)","v":51576},null,{"$f":"ROUND(K26+((M27-M26)*$L$20),0)","v":120436},0,{"$f":"ROUND(M26+(P26*$R$20)*$N$21,0)","v":172012},{"$f":"M27-M26","v":5391},null,{"$f":"ROUND(P26*(1+$R$20),0)","v":320338},{"$f":"P27-P26","v":5378},{"$f":"P27-M27-F27","v":-1},null,null,null,null,null,null,{"$f":"B27/(B27+I27)","v":0.43618614515124021},0.56381385484875979,0.47376148071763768,0.52623851928236232]
[2018,39897,null,108416,0,148313,-14,null,53194,null,124301,0,177495,5483,null,325808,5470,0,null,null,null,null,null,null,0.42858063615172248,0.57141936384827752,0.46587056381785602,0.53412943618214392]
[2019,39893,null,108406,0,148299,-14,null,54840,null,128232,0,183072,5577,null,331371,5563,0,null,null,null,null,null,null,0.42110985612194274,0.57889014387805726,0.45810901038717367,0.54189098961282633]
[2020,39889,null,108396,0,148285,-14,null,56514,null,132230,0,188744,5672,null,337029,5658,0,null,null,null,null,null,null,0.41377343028743918,0.58622656971256082,0.45047501101294124,0.5495249889870587]
[]
["Mid Year","Rate 1 Major",null,"Rate 1 Minor",null,"Rate 1 Total","check",null,"Rate 2 Major",null,"Rate 2 Minor",null,"Rate 2 Total","check",null,"Total",null,"Check"]
[{"$f":"DATEVALUE(\"31/12/\"&A17)","v":"2014-12-31"},{"$f":"ROUND(B17+(F33-F17)*$C$20,0)","v":39911},null,{"$f":"ROUND(D17+((F33-F17)*$E$20),0)","v":108449},null,{"$f":"ROUND(F17+(P17*$R$20/2)*$G$21,0)","v":148360},null,null,{"$f":"ROUND(I17+(M33-M17)*$J$20,0)","v":47652},null,{"$f":"ROUND(K17+((M33-M17)*$L$20),0)","v":111062},null,{"$f":"ROUND(M17+(P17*$R$20/2)*$N$21,0)","v":158714},null,null,{"$f":"ROUND(P17*(1+$R$20/2),0)","v":307073}]
[{"$f":"DATEVALUE(\"31/12/\"&A25)","v":"2015-12-31"},{"$f":"ROUND(B25+(F34-F25)*$C$20,0)","v":39907},null,{"$f":"ROUND(D25+((F34-F25)*$E$20),0)","v":108439},null,{"$f":"ROUND(F25+(P25*$R$20/2)*$G$21,0)","v":148346},{"$f":"F34-F33","v":-14},null,{"$f":"ROUND(I25+(M34-M25)*$J$20,0)","v":49203},null,{"$f":"ROUND(K25+((M34-M25)*$L$20),0)","v":114767},null,{"$f":"ROUND(M25+(P25*$R$20/2)*$N$21,0)","v":163970},{"$f":"M34-M33","v":5256},null,{"$f":"ROUND(P25*(1+$R$20/2),0)","v":312317},{"$f":"P34-P33","v":5244},null,null,null,null,null,null,null,{"$f":"B34/(B34+I34)","v":0.44783974862529458},{"$f":"I34/(B34+I34)","v":0.55216025137470537},{"$f":"D34/(D34+K34)","v":0.48582475381486162},{"$f":"K34/(D34+K34)","v":0.51417524618513843},null,{"$f":"B34/P34","v":0.12777722634374689},{"$f":"I34/P34","v":0.15754185651117295},{"$f":"D34/P34","v":0.34720812507804572},{"$f":"K34/P34","v":0.36746959019201647}]
[{"$f":"DATEVALUE(\"31/12/\"&A26)","v":"2016-12-31"},{"$f":"ROUND(B26+(F35-F26)*$C$20,0)","v":39903},null,{"$f":"ROUND(D26+((F35-F26)*$E$20),0)","v":108430},null,{"$f":"ROUND(F26+(P26*$R$20/2)*$G$21,0)","v":148333},{"$f":"F35-F34","v":-13},null,{"$f":"ROUND(I26+(M35-M26)*$J$20,0)","v":50781},null,{"$f":"ROUND(K26+((M35-M26)*$L$20),0)","v":118536},null,{"$f":"ROUND(M26+(P26*$R$20/2)*$N$21,0)","v":169317},{"$f":"M35-M34","v":5347},null,{"$f":"ROUND(P26*(1+$R$20/2),0)","v":317649},{"$f":"P35-P34","v":5332},null,null,null,null,null,null,null,0.44002249569935159,0.55997750430064841,0.47773675352255401,0.52226324647744593,null,0.12561978787907407,0.15986513415751349,0.34135161766604016,0.37316660842628185]
[{"$f":"DATEVALUE(\"31/12/\"&A27)","v":"2017-12-31"},{"$f":"ROUND(B27+(F36-F27)*$C$20,0)","v":39899},null,{"$f":"ROUND(D27+((F36-F27)*$E$20),0)","v":108421},null,{"$f":"ROUND(F27+(P27*$R$20/2)*$G$21,0)","v":148320},-13,null,{"$f":"ROUND(I27+(M36-M27)*$J$20,0)","v":52385},null,{"$f":"ROUND(K27+((M36-M27)*$L$20),0)","v":122369},null,{"$f":"ROUND(M27+(P27*$R$20/2)*$N$21,0)","v":174754},5437,null,{"$f":"ROUND(P27*(1+$R$20/2),0)","v":323073},5424,null,null,null,null,null,null,null,0.43235013653504401,0.56764986346495605,0.46978205294856795,0.53021794705143199,null,0.12349840438538659,0.16214601653496269,0.33559288458026515,0.37876578977506631]
[{"$f":"DATEVALUE(\"31/12/\"&A28)","v":"2018-12-31"},{"$f":"ROUND(B28+(F37-F28)*$C$20,0)","v":39895},null,{"$f":"ROUND(D28+((F37-F28)*$E$20),0)","v":108411},null,{"$f":"ROUND(F28+(P28*$R$20/2)*$G$21,0)","v":148306},-14,null,{"$f":"ROUND(I28+(M37-M28)*$J$20,0)","v":54017},null,{"$f":"ROUND(K28+((M37-M28)*$L$20),0)","v":126266},null,{"$f":"ROUND(M28+(P28*$R$20/2)*$N$21,0)","v":180283},5529,null,{"$f":"ROUND(P28*(1+$R$20/2),0)","v":328589},5516,null,null,null,null,null,null,null,0.42481259051026493,0.57518740948973512,0.46195835126578233,0.53804164873421767,null,0.12141307225744015,0.16439077388470094,0.3299288777165395,0.38426727614131939]
[{"$f":"DATEVALUE(\"31/12/\"&A29)","v":"2019-12-31"},{"$f":"ROUND(B29+(F38-F29)*$C$20,0)","v":39891},null,{"$f":"ROUND(D29+((F38-F29)*$E$20),0)","v":108401},null,{"$f":"ROUND(F29+(P29*$R$20/2)*$G$21,0)","v":148292},-14,null,{"$f":"ROUND(I29+(M38-M29)*$J$20,0)","v":55677},null,{"$f":"ROUND(K29+((M38-M29)*$L$20),0)","v":130231},null,{"$f":"ROUND(M29+(P29*$R$20/2)*$N$21,0)","v":185908},5625,null,{"$f":"ROUND(P29*(1+$R$20/2),0)","v":334200},5611,null,null,null,null,null,null,null,0.41740959316926168,0.58259040683073837,0.454260115994502,0.545739884005498,null,0.11936265709156194,0.16659784560143626,0.32435966487133455,0.38967983243566728]
[]
["Average (5 yr) Additions",null,null,null,null,1125,"as per email from Peter Grant 14/8/2014",null,null,null,null,null,5689,"as per email from Peter Grant 14/8/2014"]
["Average (5 yr) Disposals",null,null,null,{"$f":"F41/F40","v":1.0097777777777779},1136,"as per email from Peter Grant 14/8/2014",null,null,null,null,{"$f":"M41/M40","v":0.12954825101072245},737,"as per email from Peter Grant 14/8/2014"]
[null,null,null,null,{"$f":"F42/F41","v":-9.683098591549295E-3},{"$f":"F40-F41","v":-11},null,null,null,null,null,{"$f":"M42/M41","v":6.7191316146540023},{"$f":"M40-M41","v":4952}]
[]
["Rate 1 (Financial Year)",null,null,null,"Additions","Disposals","Net",null,"Rate 2 (Financial Year)",null,null,"Additions","Disposals","Net",null,null,"Total"]
["2015/16",null,null,null,{"$f":"ROUND(F45/$E$41,0)","v":1330},{"$f":"ROUND(G26/$E$42,0)","v":1343},{"$f":"E45-F45","v":-13},null,"2015/16",null,null,{"$f":"ROUND(M45/$L$41,0)","v":6090},{"$f":"ROUND(N26/$L$42,0)","v":789},{"$f":"L45-M45","v":5301},null,null,{"$f":"G45+N45","v":5288}]
["2016/17",null,null,null,{"$f":"ROUND(F46/$E$41,0)","v":1330},{"$f":"ROUND(G27/$E$42,0)","v":1343},{"$f":"E46-F46","v":-13},null,"2016/17",null,null,{"$f":"ROUND(M46/$L$41,0)","v":6191},{"$f":"ROUND(N27/$L$42,0)","v":802},{"$f":"L46-M46","v":5389},null,null,{"$f":"G46+N46","v":5376}]
["2017/18",null,null,null,1432,1446,-14,null,"2017/18",null,null,6299,816,5483,null,null,5469]
["2018/19",null,null,null,1432,1446,-14,null,"2018/19",null,null,6407,830,5577,null,null,5563]
["2019/20",null,null,null,1432,1446,{"$f":"E49-F49","v":-14},null,"2019/20",null,null,6515,844,5671,null,null,5657]

{"sheet":"Distribution of Revenue","index":7,"n_rows":36,"n_cols":18,"rows_kept":36,"cut":false,"hidden":false,"merged":[]}
["Updated 3/3/2014 (data from Peter Grant)"]
[]
[null,null,"Category","Unknown","Aluminium","Concrete","Steel","Wood",null,null,"Steel & Other","Wood",null,null,"RAB from Roll Forward Model","$","Relativities","%"]
[null,"Major","Rate 1",2,4,1205,13431,25102,{"$f":"SUM(D4:H4)","v":39744},null,{"$f":"SUM(D4:G4)","v":14642},{"$f":"H4","v":25102},{"$f":"SUM(K4:L4)","v":39744},null,"OH Distiribution Line",1145.28,{"$f":"E14/E15","v":0.60981879610102097},{"$f":"P4/P6","v":0.4179976057695115}]
[null,null,"Rate 2",null,9,665,34362,11346,{"$f":"SUM(D5:H5)","v":46382},null,{"$f":"SUM(D5:G5)","v":35036},{"$f":"H5","v":11346},{"$f":"SUM(K5:L5)","v":46382},null,"UG Distribution Cable",1594.64,1,{"$f":"P5/P6","v":0.5820023942304885}]
[null,"Minor","Rate 1",5,314,2137,15477,90433,{"$f":"SUM(D6:H6)","v":108366},null,17933,90433,108366,null,null,2739.92,null,{"$f":"SUM(R4:R5)","v":1}]
[null,null,"Rate 2",null,23,317,93452,13931,{"$f":"SUM(D7:H7)","v":107723},null,93792,13931,107723]
[null,null,null,{"$f":"SUM(D4:D7)","v":7},{"$f":"SUM(E4:E7)","v":350},4324,156722,{"$f":"SUM(H4:H7)","v":140812},null,null,{"$f":"SUM(K4:K7)","v":161403},{"$f":"SUM(L4:L7)","v":140812},{"$f":"SUM(M4:M7)","v":302215},null,"Converted",null,"Qty","%"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"OH Distiribution Line",{"$f":"P4/Q4","v":1878.0660867171368},{"$f":"R9*K4","v":7918.5059014619028},{"$f":"P9/P11","v":0.54080766981709483}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"UG Distribution Cable",{"$f":"P5","v":1594.64},{"$f":"R10*K4","v":6723.4940985380981},{"$f":"P10/P11","v":0.45919233018290523}]
[null,"ROA","(only relevant for Rate 1)",null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(P9:P10)","v":3472.7060867171367},{"$f":"SUM(Q9:Q10)","v":14642},{"$f":"SUM(R9:R10)","v":1}]
[null,null,null,"Std Est No","Std Est","Volume","Installation Cost",null,"2010/15"]
[null,"Major","Timber",92431,{"$f":"'Standard Estimates'!$C$3","v":2197},{"$f":"L4","v":25102},{"$f":"E13*F13","v":55149094}]
[null,null,"Steel OH",92434,{"$f":"'Standard Estimates'!$C$6","v":8718},{"$f":"Q9","v":7918.5059014619028},{"$f":"E14*F14","v":69033534.448944867}]
[null,null,"Steel UG",92435,{"$f":"'Standard Estimates'!$C$7","v":14296.05},{"$f":"Q10","v":6723.4940985380981},{"$f":"E15*F15","v":96119407.807405576}]
[null,null,null,null,null,null,{"$f":"SUM(G13:G15)","v":220302036.25635046},{"$f":"G16/($G$16+$G$19)","v":0.45257854616371901},0.48]
[null,"Minor","Timber",92430,{"$f":"'Standard Estimates'!$C$2","v":1299},{"$f":"L6","v":90433},{"$f":"E17*F17","v":117472467}]
[null,null,"Steel","92432\n92433",{"$f":"'Standard Estimates'!$C$4","v":8308.5},{"$f":"K6","v":17933},{"$f":"E18*F18","v":148996330.5}]
[null,null,null,null,null,null,{"$f":"SUM(G17:G18)","v":266468797.5},{"$f":"G19/($G$16+$G$19)","v":0.54742145383628094},0.52]
[null,null,null,null,null,{"$f":"SUM(F13:F19)","v":148110}]
[null,"O&M","(Rate 1 & Rate2)"]
[null,null,null,"Std Est No","Std Est","Volume","Installation Cost"]
[null,"Major","Timber",92431,{"$f":"'Standard Estimates'!$C$3","v":2197},{"$f":"L4+L5","v":36448},{"$f":"E23*F23","v":80076256}]
[null,null,"Steel OH",92434,{"$f":"'Standard Estimates'!$C$6","v":8718},{"$f":"Q9+K5*R9","v":26866.243421173636},{"$f":"E24*F24","v":234219910.14579177}]
[null,null,"Steel UG",92435,{"$f":"'Standard Estimates'!$C$7","v":14296.05},{"$f":"Q10+K5*R10","v":22811.756578826367},{"$f":"E25*F25","v":326118012.63873065}]
[null,null,null,null,null,null,{"$f":"SUM(G23:G25)","v":640414178.78452241},{"$f":"G26/($G$26+$G$29)","v":0.3757747467598509}]
[null,"Minor","Timber",92430,{"$f":"'Standard Estimates'!$C$2","v":1299},{"$f":"L6+L7","v":104364},{"$f":"E27*F27","v":135568836}]
[null,null,"Steel","92432\n92433",{"$f":"'Standard Estimates'!$C$4","v":8308.5},{"$f":"K6+K7","v":111725},{"$f":"E28*F28","v":928267162.5}]
[null,null,null,null,null,null,{"$f":"SUM(G27:G28)","v":1063835998.5},{"$f":"G29/($G$26+$G$29)","v":0.6242252532401491}]
[null,null,null,null,null,{"$f":"SUM(F23:F29)","v":302215}]
[]
[null,null,"Category","ROA","Depn","O&M"]
[null,"Major","Rate 1",{"$f":"H16","v":0.45257854616371901},{"$f":"H16","v":0.45257854616371901},{"$f":"H26*'Demand Forecast'!Y20","v":0.17979490833592734}]
[null,null,"Rate 2",null,null,{"$f":"H26*'Demand Forecast'!Z20","v":0.19597983842392355}]
[null,"Minor","Rate 1",{"$f":"H19","v":0.54742145383628094},{"$f":"H19","v":0.54742145383628094},{"$f":"H29*'Demand Forecast'!AA20","v":0.31836604807592606}]
[null,null,"Rate 2",null,null,{"$f":"H29*'Demand Forecast'!AB20","v":0.30585920516422305}]

{"sheet":"Standard Estimates","index":8,"n_rows":36,"n_cols":7,"rows_kept":36,"cut":false,"hidden":false,"merged":[[3,2,4,2]]}
[]
["Minor Wood M50 Street Lighting - AER 2015",92430,{"$f":"B22","v":1299}]
["Major Wood S150 Street Lighting - AER 2015",92431,{"$f":"C22","v":2197}]
["Steel Minor F32 Streetlighting AER 2015",92432,{"$f":"AVERAGE(D22,E22)","v":8308.5}]
["Steel Minor Decorative Streetlighting AER 2015",92433]
["Steel O/H Major Streetlighting - AER 2015",92434,{"$f":"F22","v":8718}]
["Steel U/G Major Streetlighting - AER 2015",92435,{"$f":"G22","v":14296.05}]
[]
["Estimate No:",92430,92431,92432,92433,92434,92435]
["Version No:",6,4,5,6,4,4]
["Description:","Minor Wood M50 Street Lighting - AER 2015","Major Wood S150 Street Lighting - AER 2015","Steel Minor F32 Streetlighting AER 2015","Steel Minor Decorative Streetlighting AER 2015","Steel O/H Major Streetlighting - AER 2015","Steel U/G Major Streetlighting - AER 2015"]
["Print Date:","2014-02-26","2014-02-26","2014-02-26","2014-02-26","2014-02-26","2014-02-26"]
["Creation Date:","2014-01-30","2014-01-30","2014-01-30","2014-01-30","2014-01-30","2014-01-30"]
[]
["Project Estimated Cost Summary"]
["Materials (stock):",275,667,936,1275,1892,2270.21]
["Materials (purchases):",0,0,20,20,5,150]
["Materials (contract labour):",0,107,766,766,1736,2176]
["Labour:",560,663,3484,3484,2151,4772.96]
["Equipment:",0,0,0,0,0,0]
["Other:",464,760,2851,3015,2934,4926.88]
[null,{"$f":"SUM(B16:B21)","v":1299},2197,8057,8560,8718,14296.05]
["Summary of Estimated Hours"]
["Labour Hours (ord):",9,10,59,59,34,78.25]
["Labour Hours (ot):",0,0,0,0,0,0]
[null,{"$f":"SUM(B24:B25)","v":9},10,59,59,34,78.25]
["Adjustments SLPA/Escalation"]
["Materials:",0,0,0,0,0,0]
["Labour:",0,0,0,0,0,0]
[null,{"$f":"SUM(B28:B29)","v":0},0,0,0,0,0]
[]
["Previous Estimate for 2010/15 Determination",1068,1749,3579,3579,4644,8043]
[]
[null,{"$f":"(B22/B32)^(1/5)-1","v":3.9938315557268123E-2},4.666583793846435E-2,0.17620313617506711,0.1905356735445507,0.13423992612869884,0.12191407871427562]
[]
[null,"Full review of these standard estimates were done to reflect current practice and current materials"]