{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Inputs >>>","Inputs and Assumptions","Outputs >>>","(EGX&EGN) Building Blocks","Energex Smoothed Charges","Ergon Smoothed Charges"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":31,"defined_names":708,"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":"Inputs >>>","index":0,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Inputs and Assumptions","index":1,"n_rows":79,"n_cols":9,"rows_kept":79,"cut":false,"hidden":false,"merged":[]}
["Inputs and Assumptions"]
[]
[]
["Inputs"]
[]
["Meter Population (millions)"]
["Year","2019-20","2020-21","2021-22","2022-23","2023-24","2024-25"]
["Energex Meters",{"$f":"B14/Million","v":1.9692940000000001},{"$f":"B8*(1-'Inputs and Assumptions'!$B$52)","v":1.891471085878313},{"$f":"C8*(1-'Inputs and Assumptions'!$B$52)","v":1.8167235916595919},{"$f":"D8*(1-'Inputs and Assumptions'!$B$52)","v":1.7449299823475402},{"$f":"E8*(1-'Inputs and Assumptions'!$B$52)","v":1.6759735257876818},{"$f":"F8*(1-'Inputs and Assumptions'!$B$52)","v":1.6097421028677947}]
["Ergon Meters",{"$f":"B15/Million","v":0.972055},{"$f":"B9*(1-'Inputs and Assumptions'!$B$53)","v":0.89561985151922385},{"$f":"C9*(1-'Inputs and Assumptions'!$B$53)","v":0.82519499250075001},{"$f":"D9*(1-'Inputs and Assumptions'!$B$53)","v":0.76030781865010599},{"$f":"E9*(1-'Inputs and Assumptions'!$B$53)","v":0.70052288774638571},{"$f":"F9*(1-'Inputs and Assumptions'!$B$53)","v":0.64543899749421163}]
[]
[]
["Meter Population"]
["Year","2019-20","2020-21","2021-22","2022-23","2023-24","2024-25"]
["Energex Meters",1969294,{"$f":"B14*(1-'Inputs and Assumptions'!$B$52)","v":1891471.0858783128},{"$f":"C14*(1-'Inputs and Assumptions'!$B$52)","v":1816723.5916595918},{"$f":"D14*(1-'Inputs and Assumptions'!$B$52)","v":1744929.9823475401},{"$f":"E14*(1-'Inputs and Assumptions'!$B$52)","v":1675973.5257876818},{"$f":"F14*(1-'Inputs and Assumptions'!$B$52)","v":1609742.1028677949}]
["Ergon Meters",972055,{"$f":"B15*(1-'Inputs and Assumptions'!$B$53)","v":895619.85151922388},{"$f":"C15*(1-'Inputs and Assumptions'!$B$53)","v":825194.99250075012},{"$f":"D15*(1-'Inputs and Assumptions'!$B$53)","v":760307.81865010608},{"$f":"E15*(1-'Inputs and Assumptions'!$B$53)","v":700522.88774638576},{"$f":"F15*(1-'Inputs and Assumptions'!$B$53)","v":645438.99749421165}]
[]
[]
["Network Tariff Code Population"]
["Year","2019-20","2020-21","2021-22","2022-23","2023-24","2024-25"]
["Energex"]
["Primary",1361804,{"$f":"B21*(1-$B$57)","v":1307987.9848480877},1256298.6806522533,1206652.0436668214,1158967.3514019584,1113167.0713736874]
["Load Control",678181,{"$f":"B22*(1-$B$57)","v":651380.52139093506},625639.14891087543,600915.02861352195,577167.9605443452,554359.33337784198]
["Solar PV",317796,{"$f":"B23*(1-$B$57)","v":305237.28057252208},293174.85887584666,281589.12212707644,270461.23260479246,259773.09702003549]
["Ergon"]
["Primary",677268,{"$f":"B25*(1-$B$58)","v":624012.70051460224},574945.00021192012,529735.61755406845,488081.16324510355,449702.10425841098]
["Load Control",373240,{"$f":"B26*(1-$B$58)","v":343891.1927627913},316850.15662794793,291935.42570427142,268979.80322354287,247829.23952321577]
["Solar PV",117242,108022.96437116916,99528.844880971694,91702.639536009519,84491.828554106251,77848.021916677928]
[]
[]
["Customers by Tariff Code"]
["Year","2019-20","2020-21","2021-22","2022-23","2023-24","2024-25"]
["Energex"]
["Primary (Non-capital)",1302988,{"$f":"B33*(1-$B$62)","v":1251496.2861037566},{"$f":"C33*(1-$B$62)","v":1202039.4310089545},{"$f":"D33*(1-$B$62)","v":1154537.0207998687},{"$f":"E33*(1-$B$62)","v":1108911.8193723436},{"$f":"F33*(1-$B$62)","v":1065089.6428524652}]
["Primary (Capital)",1266056,1216023.7715154535,1167968.7256256181,1121812.7276734693,1077480.7307413972,1034900.653629366]
["Load Control (Non-capital)",655894.33108441404,629974.58106187615,605079.13237781764,581167.50650790497,558200.82456540188,536141.74408640375]
["Load Control (Capital)",654731.04643569968,628857.26730492094,604005.97282666143,580136.75626870955,557210.80769935728,535190.85088475118]
["Solar PV (Non-capital)",301066.5820292799,289168.97874699981,277741.54708891129,266765.70672829228,256223.61159911042,246098.1208793773]
["Solar PV (Capital)",286159.53514589689,274851.03122075368,263989.41878556123,253557.03750212523,243536.92493666912,233912.7889799221]
["Ergon"]
["Primary (Non-capital)",652625,{"$f":"B40*(1-$B$63)","v":601307.44206627551},554025.11378553882,510460.71482223267,470321.89497043367,433339.29226191912]
["Primary (Capital)",482668,{"$f":"B41*(1-$B$63)","v":444714.59175980859},{"$f":"C41*(1-$B$63)","v":409745.5562086014},{"$f":"D41*(1-$B$63)","v":377526.22455746779},{"$f":"E41*(1-$B$63)","v":347840.38061917532},{"$f":"F41*(1-$B$63)","v":320488.80983332847}]
["Load Control (Non-capital)",360781,{"$f":"B42*(1-$B$63)","v":332411.87551214395},306273.48718890711,282190.42659150343,260001.07809128985,239556.53430614434]
["Load Control (Capital)",269218,{"$f":"B43*(1-$B$63)","v":248048.7062833918},{"$f":"C43*(1-$B$63)","v":228544.00778872281},{"$f":"D43*(1-$B$63)","v":210573.01317450585},{"$f":"E43*(1-$B$63)","v":194015.12341720014},{"$f":"F43*(1-$B$63)","v":178759.22250016377}]
["Solar PV (Non-capital)",114849,105818.1320266151,97497.384092174456,89830.91765810168,82767.28491164044,76259.083511954275]
["Solar PV (Capital)",75936,{"$f":"B45*(1-$B$63)","v":69964.959847913735},{"$f":"C45*(1-$B$63)","v":64463.437717554007},{"$f":"D45*(1-$B$63)","v":59394.514216803014},{"$f":"E45*(1-$B$63)","v":54724.173018923357},{"$f":"F45*(1-$B$63)","v":50421.072587168892}]
[]
[]
[]
["Assumptions"]
[]
["Meter Decline Rate"]
["Energex Meter Decline Rate",3.9518179673368813E-2,"Source: Metering Opex model Dec 2019 "]
["Ergon Meter Decline Rate",7.863253466190305E-2,"Source: Metering Opex model Dec 2019 (Updated per rate supplied in the AER information request)"]
[]
[]
["NTC Decline Rate"]
["Energex NTC Decline Rate",3.9518179673368813E-2]
["Ergon NTC Decline Rate",7.863253466190305E-2]
[]
[]
["Customer Decline Rate"]
["Energex Customer Decline Rate",3.9518179673368813E-2]
["Ergon Customer Decline Rate",7.863253466190305E-2]
[]
[]
["Inflation"]
["Year","2017-18","2018-19","2019-20","2020-21","2021-22","2022-23","2023-24","2024-25"]
["Inflation Assumption (CPI % increase) (constant)",null,null,null,2.3699999999999999E-2,2.3699999999999999E-2,2.3699999999999999E-2,2.3699999999999999E-2,2.3699999999999999E-2]
["Cumulative Inflation Index (CPI end period)",null,null,1,{"$f":"D69*(1+E68)","v":1.0237000000000001},{"$f":"E69*(1+F68)","v":1.0479616900000002},1.0727983820530003,1.0982237037076565,1.124251605485528]
[]
[]
["WACC"]
["Year","2017-18","2018-19","2019-20","2020-21","2021-22","2022-23","2023-24","2024-25"]
["Time Varying Nominal Vanilla WACC",null,null,null,4.6734187198390856E-2,4.5406260215142207E-2,4.4078333231893557E-2,4.2750406248644908E-2,4.1422479265396259E-2]
[]
["Numbers"]
["Days per year",365.25]
["Hundred",100]
["Million",{"$f":"10^6","v":1000000}]

{"sheet":"Outputs >>>","index":2,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"(EGX&EGN) Building Blocks","index":3,"n_rows":34,"n_cols":17,"rows_kept":34,"cut":false,"hidden":false,"merged":[]}
["Building Block Components"]
[]
[]
["Energex",null,null,null,null,null,null,null,null,"Ergon"]
[]
["Building Blocks ($m, nominal)",null,null,null,null,null,null,null,null,"Building Blocks ($m, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,"Total",null,null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,"Total"]
["Return on Capital",14.844991768122407,13.357978980330369,11.896293218503306,10.450651535585504,9.0163405906130336,null,{"$f":"SUM(B8:F8)","v":59.566256093154628},null,"Return on Capital",3.2337686824394161,2.9405511948950553,2.6477793005087698,2.3559452557096323,2.0657440976585706,null,{"$f":"SUM(K8:O8)","v":13.243788531211443}]
["Return of Capital (regulatory depreciation)",23.459326218082673,24.298230866045131,25.432453395608384,26.789546096964699,28.196679837617534,null,{"$f":"SUM(B9:F9)","v":128.17623641431842},null,"Return of Capital (regulatory depreciation)",4.5231017360274546,4.7776447780314948,5.0443377497822359,5.3209123289579461,5.6077186102813403,null,{"$f":"SUM(K9:O9)","v":25.273715203080471}]
["Operating Expenditure",22.33223961524649,21.936644144337809,21.553638794761238,21.179983138211597,20.782617617202771,null,107.78512330975991,null,"Operating Expenditure",39.637876803004922,37.971452003376776,36.429564243939183,35.000878755823628,33.640210586377918,null,182.67998239252242]
["Revenue Adjustments",0,0,0,0,0,null,0,null,"Revenue Adjustments",0,0,0,0,0,null,0]
["Net Tax Allowance",1.5017365942807732,1.5601835548167031,1.6584500750012161,1.8139210684378706,2.0258666974891342,null,8.5601579900256972,null,"Net Tax Allowance",0.10732079177315471,0.13082592896207115,0.15583753283825819,0.18159312984744197,0.20804557350645086,null,0.78362295692737693]
["Annual Revenue Requirement (unsmoothed)",{"$f":"SUM(B8:B12)","v":62.138294195732342},{"$f":"SUM(C8:C12)","v":61.153037545530012},60.540835483874147,60.234101839199674,60.021504742922474,null,304.08777380725866,null,"Annual Revenue Requirement (unsmoothed)",{"$f":"SUM(K8:K12)","v":47.502068013244944},{"$f":"SUM(L8:L12)","v":45.820473905265395},44.277518827068441,42.85932947033865,41.521718867824283,null,221.98110908374173]
[]
["Unsmoothed Revenue Allocation ($m, nominal)",null,null,null,null,null,null,null,null,"Unsmoothed Revenue Allocation ($m, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,"Total",null,null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,"Total"]
["Non-Capital Revenue (unsmoothed)",{"$f":"B10","v":22.33223961524649},{"$f":"C10","v":21.936644144337809},21.553638794761238,21.179983138211597,20.782617617202771,null,{"$f":"SUM(B17:F17)","v":107.78512330975991},null,"Non-Capital Revenue (unsmoothed)",{"$f":"K10","v":39.637876803004922},{"$f":"L10","v":37.971452003376776},36.429564243939183,35.000878755823628,33.640210586377918,null,{"$f":"SUM(K17:O17)","v":182.67998239252242}]
["Capital Revenue (unsmoothed)",{"$f":"B8+B9+B12","v":39.806054580485849},{"$f":"C8+C9+C12","v":39.216393401192207},38.987196689112906,39.054118700988077,39.2388871257197,null,{"$f":"SUM(B18:F18)","v":196.30265049749875},null,"Capital Revenue (unsmoothed)",{"$f":"K8+K9+K12","v":7.8641912102400262},{"$f":"L8+L9+L12","v":7.8490219018886211},7.8479545831292636,7.85845071451502,7.8815082814463615,null,{"$f":"SUM(K18:O18)","v":39.301126691219295}]
["Annual Revenue Requirement (unsmoothed)",{"$f":"B17+B18","v":62.138294195732342},{"$f":"C17+C18","v":61.153037545530012},60.540835483874147,60.234101839199674,60.021504742922474,null,304.08777380725866,null,"Annual Revenue Requirement (unsmoothed)",{"$f":"K17+K18","v":47.502068013244951},{"$f":"L17+L18","v":45.820473905265395},{"$f":"M17+M18","v":44.277518827068448},{"$f":"N17+N18","v":42.85932947033865},{"$f":"O17+O18","v":41.521718867824276},null,221.98110908374173]
[]
["Building Blocks ($m, real 2019-20)",null,null,null,null,null,null,null,null,"Building Blocks ($m, real 2019-20)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,"Total",null,null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,"Total"]
["Return on Capital",{"$f":"B8/'Inputs and Assumptions'!$E$69","v":14.501310704427475},{"$f":"C8/'Inputs and Assumptions'!$F$69","v":12.746629106575801},{"$f":"D8/'Inputs and Assumptions'!$G$69","v":11.089029791168706},{"$f":"E8/'Inputs and Assumptions'!$H$69","v":9.5159588163172923},{"$f":"F8/'Inputs and Assumptions'!$I$69","v":8.0198600977039884},null,{"$f":"SUM(B23:F23)","v":55.872788516193268},null,"Return on Capital",{"$f":"K8/'Inputs and Assumptions'!$E$69","v":3.158902688716827},{"$f":"L8/'Inputs and Assumptions'!$F$69","v":2.8059720340493124},{"$f":"M8/'Inputs and Assumptions'!$G$69","v":2.4681052328227313},{"$f":"N8/'Inputs and Assumptions'!$H$69","v":2.1452325676051669},{"$f":"O8/'Inputs and Assumptions'!$I$69","v":1.8374393130321056},null,{"$f":"SUM(K23:O23)","v":12.415651836226141}]
["Return of Capital (regulatory depreciation)",{"$f":"B9/'Inputs and Assumptions'!$E$69","v":22.916211993828927},{"$f":"C9/'Inputs and Assumptions'!$F$69","v":23.186182374706014},{"$f":"D9/'Inputs and Assumptions'!$G$69","v":23.706647792419886},{"$f":"E9/'Inputs and Assumptions'!$H$69","v":24.393523839015575},{"$f":"F9/'Inputs and Assumptions'!$I$69","v":25.080399885611278},null,{"$f":"SUM(B24:F24)","v":119.28296588558169},null,"Return of Capital (regulatory depreciation)",{"$f":"K9/'Inputs and Assumptions'!$E$69","v":4.4183859881092644},{"$f":"L9/'Inputs and Assumptions'!$F$69","v":4.5589880084562004},{"$f":"M9/'Inputs and Assumptions'!$G$69","v":4.7020370594975658},{"$f":"N9/'Inputs and Assumptions'!$H$69","v":4.8450168312651494},{"$f":"O9/'Inputs and Assumptions'!$I$69","v":4.9879569510239188},null,{"$f":"SUM(K24:O24)","v":23.512384838352098}]
["Operating Expenditure",{"$f":"B10/'Inputs and Assumptions'!$E$69","v":21.815218926684075},{"$f":"C10/'Inputs and Assumptions'!$F$69","v":20.932677552685924},{"$f":"D10/'Inputs and Assumptions'!$G$69","v":20.091043345455386},{"$f":"E10/'Inputs and Assumptions'!$H$69","v":19.285672915915896},{"$f":"F10/'Inputs and Assumptions'!$I$69","v":18.485735324547239},null,100.61034806528852,null,"Operating Expenditure",{"$f":"K10/'Inputs and Assumptions'!$E$69","v":38.720207876335763},{"$f":"L10/'Inputs and Assumptions'!$F$69","v":36.233626062587049},{"$f":"M10/'Inputs and Assumptions'!$G$69","v":33.957512290636011},{"$f":"N10/'Inputs and Assumptions'!$H$69","v":31.870445554634234},{"$f":"O10/'Inputs and Assumptions'!$I$69","v":29.922314917975854},null,170.70410670216893]
["Revenue Adjustments",{"$f":"B11/'Inputs and Assumptions'!$E$69","v":0},{"$f":"C11/'Inputs and Assumptions'!$F$69","v":0},{"$f":"D11/'Inputs and Assumptions'!$G$69","v":0},{"$f":"E11/'Inputs and Assumptions'!$H$69","v":0},{"$f":"F11/'Inputs and Assumptions'!$I$69","v":0},null,0,null,"Revenue Adjustments",{"$f":"K11/'Inputs and Assumptions'!$E$69","v":0},{"$f":"L11/'Inputs and Assumptions'!$F$69","v":0},{"$f":"M11/'Inputs and Assumptions'!$G$69","v":0},{"$f":"N11/'Inputs and Assumptions'!$H$69","v":0},{"$f":"O11/'Inputs and Assumptions'!$I$69","v":0},null,0]
["Net Tax Allowance",{"$f":"B12/'Inputs and Assumptions'!$E$69","v":1.4669694190493046},{"$f":"C12/'Inputs and Assumptions'!$F$69","v":1.4887791888811344},{"$f":"D12/'Inputs and Assumptions'!$G$69","v":1.5459103059304227},{"$f":"E12/'Inputs and Assumptions'!$H$69","v":1.6516863206594294},{"$f":"F12/'Inputs and Assumptions'!$I$69","v":1.8019691389404133},null,7.9553143734607046,null,"Net Tax Allowance",{"$f":"K12/'Inputs and Assumptions'!$E$69","v":0.10483617443895155},{"$f":"L12/'Inputs and Assumptions'!$F$69","v":0.1248384651943442},{"$f":"M12/'Inputs and Assumptions'!$G$69","v":0.14526264715279857},{"$f":"N12/'Inputs and Assumptions'!$H$69","v":0.16535167583286969},{"$f":"O12/'Inputs and Assumptions'!$I$69","v":0.18505250291957795},null,0.72534146553854195]
["Annual Revenue Requirement (unsmoothed)",{"$f":"B13/'Inputs and Assumptions'!$E$69","v":60.69971104398978},{"$f":"C13/'Inputs and Assumptions'!$F$69","v":58.354268222848873},{"$f":"D13/'Inputs and Assumptions'!$G$69","v":56.432631234974401},{"$f":"E13/'Inputs and Assumptions'!$H$69","v":54.846841891908198},{"$f":"F13/'Inputs and Assumptions'!$I$69","v":53.387964446802918},null,283.72141684052417,null,"Annual Revenue Requirement (unsmoothed)",{"$f":"K13/'Inputs and Assumptions'!$E$69","v":46.4023327276008},{"$f":"L13/'Inputs and Assumptions'!$F$69","v":43.723424570286902},{"$f":"M13/'Inputs and Assumptions'!$G$69","v":41.272917230109101},{"$f":"N13/'Inputs and Assumptions'!$H$69","v":39.026046629337422},{"$f":"O13/'Inputs and Assumptions'!$I$69","v":36.93276368495146},null,207.35748484228569]
[]
["Unsmoothed Revenue Allocation ($m, real 2019-20)",null,null,null,null,null,null,null,null,"Unsmoothed Revenue Allocation ($m, real 2019-20)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,"Total",null,null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,"Total"]
["Non-Capital Revenue (unsmoothed)",{"$f":"B25","v":21.815218926684075},{"$f":"C25","v":20.932677552685924},20.091043345455386,19.285672915915896,18.485735324547239,null,{"$f":"SUM(B32:F32)","v":100.61034806528852},null,"Non-Capital Revenue (unsmoothed)",{"$f":"K25","v":38.720207876335763},{"$f":"L25","v":36.233626062587049},33.957512290636011,31.870445554634234,29.922314917975854,null,{"$f":"SUM(K32:O32)","v":170.70410670216893}]
["Capital Revenue (unsmoothed)",{"$f":"B23+B24+B27","v":38.884492117305712},{"$f":"C23+C24+C27","v":37.421590670162949},36.341587889519012,35.561168975992295,34.902229122255683,null,{"$f":"SUM(B33:F33)","v":183.11106877523565},null,"Capital Revenue (unsmoothed)",{"$f":"K23+K24+K27","v":7.6821248512650433},{"$f":"L23+L24+L27","v":7.4897985076998568},7.3154049394730958,7.1556010747031866,7.0104487669756024,null,{"$f":"SUM(K33:O33)","v":36.653378140116779}]
["Annual Revenue Requirement (unsmoothed)",{"$f":"B32+B33","v":60.699711043989787},{"$f":"C32+C33","v":58.354268222848873},{"$f":"D32+D33","v":56.432631234974394},{"$f":"E32+E33","v":54.846841891908191},{"$f":"F32+F33","v":53.387964446802926},null,283.72141684052417,null,"Annual Revenue Requirement (unsmoothed)",{"$f":"K32+K33","v":46.402332727600808},{"$f":"L32+L33","v":43.723424570286909},{"$f":"M32+M33","v":41.272917230109108},{"$f":"N32+N33","v":39.026046629337422},{"$f":"O32+O33","v":36.932763684951453},null,207.35748484228571]

{"sheet":"Energex Smoothed Charges","index":4,"n_rows":98,"n_cols":13,"rows_kept":98,"cut":false,"hidden":false,"merged":[[2,5,3,7]]}
["Energex Smoothed Charges"]
[]
[null,null,null,null,null,{"$f":"IF(ROUND((Energex_Non_Capital_NPV_Difference+Energex_Capital_NPV_Difference),5)<>ROUND(0,5),\"Charges not smoothed. Press button to smooth charges.\", \"Smoothing complete.\")","v":"Smoothing complete."}]
[]
["Smoothed Charges"]
[]
["Primary ($, nominal)",null,null,null,null,null,null,"Primary (cents per day, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,null,"2020-21","2021-22","2022-23","2023-24","2024-25"]
["Non-capital",{"$f":"B67*Million/B39*(1-B34)","v":13.569521170717959},{"$f":"B9*(1+C30)*(1-C34)","v":13.891118822463975},{"$f":"C9*(1+D30)*(1-D34)","v":14.220338338556372},{"$f":"D9*(1+E30)*(1-E34)","v":14.557360357180158},{"$f":"E9*(1+F30)*(1-F34)","v":14.90236979764533},null,"Non-capital",{"$f":"B9/DaysinYear*OneHundred","v":3.7151324218255879},3.8031810602228546,3.8933164513501359,3.9855880512471344,4.0800464880616918]
["Capital",{"$f":"B68*Million/B39*(1-B35)","v":25.391443860586147},{"$f":"B10*(1+C30)*(1-C35)","v":25.993221080082041},{"$f":"C10*(1+D30)*(1-D35)","v":26.609260419679988},{"$f":"D10*(1+E30)*(1-E35)","v":27.239899891626404},{"$f":"E10*(1+F30)*(1-F35)","v":27.885485519057951},null,"Capital",6.951798456012634,7.1165560794201346,7.285218458502392,7.4578781359689001,7.6346298477913619]
["Total",{"$f":"B9+B10","v":38.960965031304106},{"$f":"C9+C10","v":39.884339902546017},40.829598758236358,41.797260248806566,42.787855316703279,null,"Total",{"$f":"I9+I10","v":10.666930877838222},{"$f":"J9+J10","v":10.91973713964299},{"$f":"K9+K10","v":11.178534909852528},{"$f":"L9+L10","v":11.443466187216035},{"$f":"M9+M10","v":11.714676335853053}]
[]
["Load Control ($, nominal)",null,null,null,null,null,null,"Load Control (cents per day, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,null,"2020-21","2021-22","2022-23","2023-24","2024-25"]
["Non-capital",{"$f":"B69*Million/B40*(1-B34)","v":4.0708563512153884},{"$f":"B15*(1+C30)*(1-C34)","v":4.1673356467391933},{"$f":"C15*(1+D30)*(1-D34)","v":4.2661015015669124},{"$f":"D15*(1+E30)*(1-E34)","v":4.3672081071540481},{"$f":"E15*(1+F30)*(1-F34)","v":4.4707109392935989},null,"Non-capital",{"$f":"B15/DaysinYear*OneHundred","v":1.1145397265476764},1.1409543180668564,1.1679949354050412,1.1956764153741406,1.2240139464185076]
["Capital",{"$f":"B70*Million/B40*(1-B35)","v":7.6174331581758459},{"$f":"B16*(1+C30)*(1-C35)","v":7.797966324024614},{"$f":"C16*(1+D30)*(1-D35)","v":7.982778125903998},{"$f":"D16*(1+E30)*(1-E35)","v":8.1719699674879234},{"$f":"E16*(1+F30)*(1-F35)","v":8.3656456557173868},null,"Capital",2.085539536803791,2.1349668238260406,2.1855655375507181,2.2373634407906704,2.2903889543374092]
["Total",{"$f":"B15+B16","v":11.688289509391234},{"$f":"C15+C16","v":11.965301970763807},{"$f":"D15+D16","v":12.24887962747091},{"$f":"E15+E16","v":12.539178074641971},{"$f":"F15+F16","v":12.836356595010987},null,"Total",{"$f":"I15+I16","v":3.2000792633514674},{"$f":"J15+J16","v":3.2759211418928968},{"$f":"K15+K16","v":3.3535604729557593},{"$f":"L15+L16","v":3.433039856164811},{"$f":"M15+M16","v":3.5144029007559165}]
[]
["Solar PV ($, nominal)",null,null,null,null,null,null,"Solar PV (cents per day, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,null,"2020-21","2021-22","2022-23","2023-24","2024-25"]
["Non-capital",{"$f":"B71*Million/B41*(1-B34)","v":9.498664819502574},{"$f":"B21*(1+C30)*(1-C34)","v":9.723783175724785},{"$f":"C21*(1+D30)*(1-D34)","v":9.9542368369894625},{"$f":"D21*(1+E30)*(1-E34)","v":10.190152250026113},{"$f":"E21*(1+F30)*(1-F34)","v":10.431658858351733},null,"Non-capital",{"$f":"B21/DaysinYear*OneHundred","v":2.6005926952779124},2.6622267421559984,2.7253215159450956,2.7899116358729947,2.8560325416431853]
["Capital",{"$f":"B72*Million/B41*(1-B35)","v":17.774010702410305},{"$f":"B22*(1+C30)*(1-C35)","v":18.19525475605743},{"$f":"C22*(1+D30)*(1-D35)","v":18.626482293775993},{"$f":"D22*(1+E30)*(1-E35)","v":19.067929924138486},{"$f":"E22*(1+F30)*(1-F35)","v":19.519839863340568},null,"Capital",4.8662589192088452,4.9815892555940948,5.0996529209516748,5.2205146951782302,5.3442408934539536]
["Total",{"$f":"B21+B22","v":27.27267552191288},{"$f":"C21+C22","v":27.919037931782213},{"$f":"D21+D22","v":28.580719130765456},{"$f":"E21+E22","v":29.258082174164599},{"$f":"F21+F22","v":29.951498721692303},null,"Total",{"$f":"I21+I22","v":7.4668516144867576},{"$f":"J21+J22","v":7.6438159977500932},{"$f":"K21+K22","v":7.8249744368967704},{"$f":"L21+L22","v":8.0104263310512245},{"$f":"M21+M22","v":8.200273435097138}]
[]
[]
["Calculations"]
[]
["Inflation"]
["Year","2020-21","2021-22","2022-23","2023-24","2024-25"]
["Inflation Assumption (CPI % increase) (constant)",{"$f":"'Inputs and Assumptions'!E68","v":2.3699999999999999E-2},{"$f":"'Inputs and Assumptions'!F68","v":2.3699999999999999E-2},{"$f":"'Inputs and Assumptions'!G68","v":2.3699999999999999E-2},{"$f":"'Inputs and Assumptions'!H68","v":2.3699999999999999E-2},{"$f":"'Inputs and Assumptions'!I68","v":2.3699999999999999E-2}]
[]
["X-Factor (used to determine annual metering charges)"]
["Year","2020-21","2021-22","2022-23","2023-24","2024-25"]
["X-Factor (non-capital)",-4.3325443574003634E-2,0,0,0,0]
["X-Factor (capital)",-9.5281665590357584E-2,0,0,0,0]
[]
["Network Tariff Code (NTC)",null,null,null,null,null,null,"Customers by Tariff Code"]
["Year","2020-21","2021-22","2022-23","2023-24","2024-25",null,"Year","2020-21","2021-22","2022-23","2023-24","2024-25"]
["Primary",{"$f":"'Inputs and Assumptions'!C21","v":1307987.9848480877},{"$f":"'Inputs and Assumptions'!D21","v":1256298.6806522533},{"$f":"'Inputs and Assumptions'!E21","v":1206652.0436668214},{"$f":"'Inputs and Assumptions'!F21","v":1158967.3514019584},{"$f":"'Inputs and Assumptions'!G21","v":1113167.0713736874},null,"Primary (Non-capital)",{"$f":"'Inputs and Assumptions'!C33","v":1251496.2861037566},{"$f":"'Inputs and Assumptions'!D33","v":1202039.4310089545},{"$f":"'Inputs and Assumptions'!E33","v":1154537.0207998687},{"$f":"'Inputs and Assumptions'!F33","v":1108911.8193723436},{"$f":"'Inputs and Assumptions'!G33","v":1065089.6428524652}]
["Load Control",{"$f":"'Inputs and Assumptions'!C22","v":651380.52139093506},{"$f":"'Inputs and Assumptions'!D22","v":625639.14891087543},{"$f":"'Inputs and Assumptions'!E22","v":600915.02861352195},{"$f":"'Inputs and Assumptions'!F22","v":577167.9605443452},{"$f":"'Inputs and Assumptions'!G22","v":554359.33337784198},null,"Primary (Capital)",{"$f":"'Inputs and Assumptions'!C34","v":1216023.7715154535},{"$f":"'Inputs and Assumptions'!D34","v":1167968.7256256181},{"$f":"'Inputs and Assumptions'!E34","v":1121812.7276734693},{"$f":"'Inputs and Assumptions'!F34","v":1077480.7307413972},{"$f":"'Inputs and Assumptions'!G34","v":1034900.653629366}]
["Solar PV",{"$f":"'Inputs and Assumptions'!C23","v":305237.28057252208},{"$f":"'Inputs and Assumptions'!D23","v":293174.85887584666},{"$f":"'Inputs and Assumptions'!E23","v":281589.12212707644},{"$f":"'Inputs and Assumptions'!F23","v":270461.23260479246},{"$f":"'Inputs and Assumptions'!G23","v":259773.09702003549},null,"Load Control (Non-capital)",{"$f":"'Inputs and Assumptions'!C35","v":629974.58106187615},{"$f":"'Inputs and Assumptions'!D35","v":605079.13237781764},{"$f":"'Inputs and Assumptions'!E35","v":581167.50650790497},{"$f":"'Inputs and Assumptions'!F35","v":558200.82456540188},{"$f":"'Inputs and Assumptions'!G35","v":536141.74408640375}]
["Total",{"$f":"SUM(B39:B41)","v":2264605.7868115446},{"$f":"SUM(C39:C41)","v":2175112.6884389753},{"$f":"SUM(D39:D41)","v":2089156.1944074198},2006596.5445510959,1927299.5017715651,null,"Load Control (Capital)",{"$f":"'Inputs and Assumptions'!C36","v":628857.26730492094},{"$f":"'Inputs and Assumptions'!D36","v":604005.97282666143},{"$f":"'Inputs and Assumptions'!E36","v":580136.75626870955},{"$f":"'Inputs and Assumptions'!F36","v":557210.80769935728},{"$f":"'Inputs and Assumptions'!G36","v":535190.85088475118}]
[null,null,null,null,null,null,null,"Solar PV (Non-capital)",{"$f":"'Inputs and Assumptions'!C37","v":289168.97874699981},{"$f":"'Inputs and Assumptions'!D37","v":277741.54708891129},{"$f":"'Inputs and Assumptions'!E37","v":266765.70672829228},{"$f":"'Inputs and Assumptions'!F37","v":256223.61159911042},{"$f":"'Inputs and Assumptions'!G37","v":246098.1208793773}]
["Relationships",null,null,null,null,null,null,"Solar PV (Capital)",{"$f":"'Inputs and Assumptions'!C38","v":274851.03122075368},{"$f":"'Inputs and Assumptions'!D38","v":263989.41878556123},{"$f":"'Inputs and Assumptions'!E38","v":253557.03750212523},{"$f":"'Inputs and Assumptions'!F38","v":243536.92493666912},{"$f":"'Inputs and Assumptions'!G38","v":233912.7889799221}]
[null,"Factor","Notes:"]
["Primary",1,"Weighted based on meter population"]
["Load Control",0.3,"Cost of Single Phase single element meter only"]
["Solar PV",0.7,"Based on 75% meter replacement vs meter reprogram"]
[]
["Revenue Weights (%)"]
["Year","2020-21","2021-22","2022-23","2023-24","2024-25"]
["Primary",{"$f":"B39*$B$46/SUMPRODUCT(B$39:B$41,$B$46:$B$48)","v":0.7617565546643188},{"$f":"C39*$B$46/SUMPRODUCT(C$39:C$41,$B$46:$B$48)","v":0.7617565546643188},0.7617565546643188,0.7617565546643188,0.7617565546643188]
["Load Control",{"$f":"B40*$B$47/SUMPRODUCT(B$39:B$41,$B$46:$B$48)","v":0.11380686692037968},{"$f":"C40*$B$47/SUMPRODUCT(C$39:C$41,$B$46:$B$48)","v":0.11380686692037965},0.11380686692037967,0.11380686692037965,0.11380686692037967]
["Solar PV",{"$f":"B41*$B$48/SUMPRODUCT(B$39:B$41,$B$46:$B$48)","v":0.12443657841530154},{"$f":"C41*$B$48/SUMPRODUCT(C$39:C$41,$B$46:$B$48)","v":0.12443657841530152},0.12443657841530155,0.12443657841530155,0.12443657841530156]
[]
["Building Blocks ($m, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25"]
["Return on Capital",{"$f":"'(EGX&EGN) Building Blocks'!B8","v":14.844991768122407},{"$f":"'(EGX&EGN) Building Blocks'!C8","v":13.357978980330369},{"$f":"'(EGX&EGN) Building Blocks'!D8","v":11.896293218503306},{"$f":"'(EGX&EGN) Building Blocks'!E8","v":10.450651535585504},{"$f":"'(EGX&EGN) Building Blocks'!F8","v":9.0163405906130336}]
["Return of Capital (regulatory depreciation)",{"$f":"'(EGX&EGN) Building Blocks'!B9","v":23.459326218082673},{"$f":"'(EGX&EGN) Building Blocks'!C9","v":24.298230866045131},{"$f":"'(EGX&EGN) Building Blocks'!D9","v":25.432453395608384},{"$f":"'(EGX&EGN) Building Blocks'!E9","v":26.789546096964699},{"$f":"'(EGX&EGN) Building Blocks'!F9","v":28.196679837617534}]
["Operating Expenditure",{"$f":"'(EGX&EGN) Building Blocks'!B10","v":22.33223961524649},{"$f":"'(EGX&EGN) Building Blocks'!C10","v":21.936644144337809},{"$f":"'(EGX&EGN) Building Blocks'!D10","v":21.553638794761238},{"$f":"'(EGX&EGN) Building Blocks'!E10","v":21.179983138211597},{"$f":"'(EGX&EGN) Building Blocks'!F10","v":20.782617617202771}]
["Revenue Adjustments",{"$f":"'(EGX&EGN) Building Blocks'!B11","v":0},{"$f":"'(EGX&EGN) Building Blocks'!C11","v":0},{"$f":"'(EGX&EGN) Building Blocks'!D11","v":0},{"$f":"'(EGX&EGN) Building Blocks'!E11","v":0},{"$f":"'(EGX&EGN) Building Blocks'!F11","v":0}]
["Net Tax Allowance",{"$f":"'(EGX&EGN) Building Blocks'!B12","v":1.5017365942807732},{"$f":"'(EGX&EGN) Building Blocks'!C12","v":1.5601835548167031},{"$f":"'(EGX&EGN) Building Blocks'!D12","v":1.6584500750012161},{"$f":"'(EGX&EGN) Building Blocks'!E12","v":1.8139210684378706},{"$f":"'(EGX&EGN) Building Blocks'!F12","v":2.0258666974891342}]
["Annual Revenue Requirement (unsmoothed)",{"$f":"'(EGX&EGN) Building Blocks'!B13","v":62.138294195732342},{"$f":"'(EGX&EGN) Building Blocks'!C13","v":61.153037545530012},{"$f":"'(EGX&EGN) Building Blocks'!D13","v":60.540835483874147},{"$f":"'(EGX&EGN) Building Blocks'!E13","v":60.234101839199674},{"$f":"'(EGX&EGN) Building Blocks'!F13","v":60.021504742922474}]
[]
["Unsmoothed Revenue Allocation between Metering Type ($m, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25"]
["Primary (Non-Capital)",{"$f":"B60*B52","v":17.011729907248178},{"$f":"C60*C52","v":16.710382464287974},16.418625628776521,16.133990983212431,15.831295192986357]
["Primary (Capital)",{"$f":"(B58+B59+B62)*B52","v":30.322522992010725},{"$f":"(C58+C59+C62)*C52","v":29.873344723652703},29.698752625918786,29.749730907116021,29.890479465750332]
["Load Control (Non-Capital)",{"$f":"B60*B53","v":2.5415622219263883},{"$f":"C60*C53","v":2.4965407408143787},2.4529521019653244,2.4104275223863323,2.3652045974181339]
["Load Control (Capital)",{"$f":"(B58+B59+B62)*B53","v":4.5302023562667229},{"$f":"(C58+C59+C62)*C53","v":4.4630948649067363},4.4370107051965393,4.4446268896960603,4.4656548052205807]
["Solar PV (Non-Capital)",{"$f":"B60*B54","v":2.7789474860719232},{"$f":"C60*C54","v":2.7297209392354569},2.6820610640193925,2.6355646326128319,2.5861178267982803]
["Solar PV (Capital)",{"$f":"(B58+B59+B62)*B54","v":4.9533292322084002},{"$f":"(C58+C59+C62)*C54","v":4.8799538126327668},4.8514333579975828,4.8597609041759977,4.8827528547487864]
["Annual Revenue Requirement (unsmoothed)",{"$f":"SUM(B67:B72)","v":62.138294195732328},{"$f":"SUM(C67:C72)","v":61.153037545530026},60.540835483874154,60.234101839199681,60.021504742922474]
[]
["Unsmoothed Revenue Allocation ($m, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,"NPV"]
["Non-Capital Revenue (unsmoothed)",{"$f":"B60","v":22.33223961524649},{"$f":"C60","v":21.936644144337809},21.553638794761238,21.179983138211597,20.782617617202771,null,{"$f":"SUMPRODUCT($B77:$F77,$B$86:$F$86)","v":94.776750428101877}]
["Capital Revenue (unsmoothed)",{"$f":"B58+B59+B62","v":39.806054580485849},{"$f":"C58+C59+C62","v":39.216393401192207},38.987196689112906,39.054118700988077,39.2388871257197,null,{"$f":"SUMPRODUCT($B78:$F78,$B$86:$F$86)","v":172.40009049074666}]
["Annual Revenue Requirement (unsmoothed)",{"$f":"B77+B78","v":62.138294195732342},{"$f":"C77+C78","v":61.153037545530012},60.540835483874147,60.234101839199674,60.021504742922474,null,{"$f":"SUMPRODUCT($B79:$F79,$B$86:$F$86)","v":267.17684091884854}]
[]
["Calculated Revenue Allocation ($m, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,"NPV"]
["Non-Capital Revenue (smoothed)",{"$f":"(B9*I39+B15*I41+B21*I43)/1000000","v":22.293460579095434},{"$f":"(C9*J39+C15*J41+C21*J43)/1000000","v":21.919938985671404},21.552675657098597,21.19156573764807,20.836506128423533,null,{"$f":"SUMPRODUCT($B83:$F83,$B$86:$F$86)","v":94.776750428101877}]
["Capital Revenue (smoothed)",{"$f":"(B10*I40+B16*I42+B22*I44)/1000000","v":40.552082697787412},{"$f":"(C10*J40+C16*J42+C22*J44)/1000000","v":39.872642263127233},39.204585690245274,38.54772224526355,37.901864390004455,null,{"$f":"SUMPRODUCT($B84:$F84,$B$86:$F$86)","v":172.40009049074663}]
["Annual Revenue Requirement (smoothed)",{"$f":"B83+B84","v":62.84554327688285},{"$f":"C83+C84","v":61.792581248798641},{"$f":"D83+D84","v":60.757261347343871},{"$f":"E83+E84","v":59.73928798291162},{"$f":"F83+F84","v":58.738370518427985},null,267.17684091884854]
["WACC",{"$f":"1/PRODUCT($B87:B87)","v":0.95535238289725111},{"$f":"1/PRODUCT($B87:C87)","v":0.91385753008657311},{"$f":"1/PRODUCT($B87:D87)","v":0.87527678814842536},{"$f":"1/PRODUCT($B87:E87)","v":0.83939242114254764},{"$f":"1/PRODUCT($B87:F87)","v":0.80600566806916085}]
["WACC",{"$f":"1+'Inputs and Assumptions'!E74","v":1.0467341871983908},{"$f":"1+'Inputs and Assumptions'!F74","v":1.0454062602151422},{"$f":"1+'Inputs and Assumptions'!G74","v":1.0440783332318935},{"$f":"1+'Inputs and Assumptions'!H74","v":1.042750406248645},{"$f":"1+'Inputs and Assumptions'!I74","v":1.0414224792653963}]
[null,null,null,null,null,"Difference in Final Year Revenue",null,"NPV Difference"]
[null,null,null,null,null,{"$f":"F85/F79-1","v":-2.1377908301204229E-2},null,{"$f":"H77-H83","v":0}]
[null,null,null,null,null,null,null,{"$f":"H78-H84","v":0}]
[]
[]
["Notes"]
["- Values in green cells were sourced from the AER's preliminary decision Energex - Metering Pricing Model - April 2015.xlsx "]
[]
[]
[]
["Return to top"]

{"sheet":"Ergon Smoothed Charges","index":5,"n_rows":98,"n_cols":13,"rows_kept":98,"cut":false,"hidden":false,"merged":[[2,5,3,7]]}
["Ergon Smoothed Charges"]
[]
[null,null,null,null,null,{"$f":"IF(ROUND((Ergon_Non_Capital_NPV_Difference+Ergon_Capital_NPV_Difference),5)<>ROUND(0, 5),\"Charges not smoothed. Press button to smooth charges.\", \"Smoothing complete.\")","v":"Smoothing complete."}]
[]
["Smoothed Charges"]
[]
["Primary ($, nominal)",null,null,null,null,null,null,"Primary (cents per day, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,null,"2020-21","2021-22","2022-23","2023-24","2024-25"]
["Non-capital",{"$f":"B67*Million/B39*(1-B34)","v":54.502377991851006},{"$f":"B9*(1+C30)*(1-C34)","v":55.794084350257876},{"$f":"C9*(1+D30)*(1-D34)","v":57.116404149358992},{"$f":"D9*(1+E30)*(1-E34)","v":58.470062927698805},{"$f":"E9*(1+F30)*(1-F34)","v":59.855803419085269},null,"Non-capital",{"$f":"B9/DaysinYear*OneHundred","v":14.921937848556057},15.275587775566837,15.637619205847772,16.008230781026366,16.38762585053669]
["Capital",{"$f":"B68*Million/B39*(1-B35)","v":15.834173110053033},{"$f":"B10*(1+C30)*(1-C35)","v":16.209443012761291},{"$f":"C10*(1+D30)*(1-D35)","v":16.593606812163735},{"$f":"D10*(1+E30)*(1-E35)","v":16.986875293612016},{"$f":"E10*(1+F30)*(1-F35)","v":17.389464238070623},null,"Capital",4.3351603312944649,4.4379036311461437,4.5430819472043087,4.6507529893530508,4.7609758352007185]
["Total",{"$f":"B9+B10","v":70.336551101904035},{"$f":"C9+C10","v":72.003527363019174},73.710010961522727,75.456938221310821,77.245267657155892,null,"Total",{"$f":"I9+I10","v":19.257098179850523},{"$f":"J9+J10","v":19.713491406712983},20.18070115305208,20.658983770379418,21.148601685737408]
[]
["Load Control ($, nominal)",null,null,null,null,null,null,"Load Control (cents per day, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,null,"2020-21","2021-22","2022-23","2023-24","2024-25"]
["Non-capital",{"$f":"B69*Million/B40*(1-B34)","v":20.039317666934608},{"$f":"B15*(1+C30)*(1-C34)","v":20.514249495640957},{"$f":"C15*(1+D30)*(1-D34)","v":21.00043720868765},{"$f":"D15*(1+E30)*(1-E34)","v":21.498147570533547},{"$f":"E15*(1+F30)*(1-F34)","v":22.007653667955193},null,"Non-capital",{"$f":"B15/DaysinYear*OneHundred","v":5.4864661648007136},5.6164954129064908,5.749606354192375,5.8858720247867344,6.0253671917741798]
["Capital",{"$f":"B70*Million/B40*(1-B35)","v":5.8218748729280945},{"$f":"B16*(1+C30)*(1-C35)","v":5.9598533074164903},{"$f":"C16*(1+D30)*(1-D35)","v":6.1011018308022615},{"$f":"D16*(1+E30)*(1-E35)","v":6.2456979441922753},{"$f":"E16*(1+F30)*(1-F35)","v":6.3937209854696322},null,"Capital",1.5939424703430787,1.6317189068902096,1.6703906449835078,1.7099789032696169,1.7505054032771066]
["Total",{"$f":"B15+B16","v":25.861192539862703},{"$f":"C15+C16","v":26.474102803057448},27.101539039489911,27.743845514725823,28.401374653424824,null,"Total",{"$f":"I15+I16","v":7.080408635143792},{"$f":"J15+J16","v":7.2482143197967002},7.4199969991758827,7.5958509280563513,7.7758725950512861]
[]
["Solar PV ($, nominal)",null,null,null,null,null,null,"Solar PV (cents per day, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,null,"2020-21","2021-22","2022-23","2023-24","2024-25"]
["Non-capital",{"$f":"B71*Million/B41*(1-B34)","v":13.553009840522098},{"$f":"B21*(1+C30)*(1-C34)","v":13.874216173742472},{"$f":"C21*(1+D30)*(1-D34)","v":14.20303509706017},{"$f":"D21*(1+E30)*(1-E34)","v":14.539647028860497},{"$f":"E21*(1+F30)*(1-F34)","v":14.884236663444492},null,"Non-capital",{"$f":"B21/DaysinYear*OneHundred","v":3.7106118659882537},3.7985533672121754,3.8885790820151049,3.9807384062588631,4.0750819064871981]
["Capital",{"$f":"B72*Million/B41*(1-B35)","v":3.9374557933814454},{"$f":"B22*(1+C30)*(1-C35)","v":4.0307734956845858},{"$f":"C22*(1+D30)*(1-D35)","v":4.126302827532311},{"$f":"D22*(1+E30)*(1-E35)","v":4.2240962045448267},{"$f":"E22*(1+F30)*(1-F35)","v":4.3242072845925392},null,"Capital",1.0780166443207242,1.1035656387911255,1.1297201444304752,1.1564945118534775,1.183903431784405]
["Total",{"$f":"B21+B22","v":17.490465633903543},{"$f":"C21+C22","v":17.904989669427056},18.32933792459248,18.763743233405322,19.208443948037029,null,"Total",{"$f":"I21+I22","v":4.7886285103089783},{"$f":"J21+J22","v":4.9021190060033009},5.0182992264455804,5.1372329181123408,5.2589853382716036]
[]
[]
["Calculations"]
[]
["Inflation"]
["Year","2020-21","2021-22","2022-23","2023-24","2024-25"]
["Inflation Assumption (CPI % increase) (constant)",{"$f":"'Inputs and Assumptions'!E68","v":2.3699999999999999E-2},{"$f":"'Inputs and Assumptions'!F68","v":2.3699999999999999E-2},{"$f":"'Inputs and Assumptions'!G68","v":2.3699999999999999E-2},{"$f":"'Inputs and Assumptions'!H68","v":2.3699999999999999E-2},{"$f":"'Inputs and Assumptions'!I68","v":2.3699999999999999E-2}]
[]
["X-Factor (used to determine annual metering charges)"]
["Year","2020-21","2021-22","2022-23","2023-24","2024-25"]
["X-Factor (non-capital)",-6.8814998327819218E-2,0,0,0,0]
["X-Factor (capital)",-0.56508812347311821,0,0,0,0]
[]
["Network Tariff Code (NTC)",null,null,null,null,null,null,"Customers by Tariff Code"]
["Year","2020-21","2021-22","2022-23","2023-24","2024-25",null,"Year","2020-21","2021-22","2022-23","2023-24","2024-25"]
["Primary",{"$f":"'Inputs and Assumptions'!C25","v":624012.70051460224},{"$f":"'Inputs and Assumptions'!D25","v":574945.00021192012},{"$f":"'Inputs and Assumptions'!E25","v":529735.61755406845},{"$f":"'Inputs and Assumptions'!F25","v":488081.16324510355},{"$f":"'Inputs and Assumptions'!G25","v":449702.10425841098},null,"Primary (Non-capital)",{"$f":"'Inputs and Assumptions'!C40","v":601307.44206627551},{"$f":"'Inputs and Assumptions'!D40","v":554025.11378553882},{"$f":"'Inputs and Assumptions'!E40","v":510460.71482223267},{"$f":"'Inputs and Assumptions'!F40","v":470321.89497043367},{"$f":"'Inputs and Assumptions'!G40","v":433339.29226191912}]
["Load Control",{"$f":"'Inputs and Assumptions'!C26","v":343891.1927627913},{"$f":"'Inputs and Assumptions'!D26","v":316850.15662794793},{"$f":"'Inputs and Assumptions'!E26","v":291935.42570427142},{"$f":"'Inputs and Assumptions'!F26","v":268979.80322354287},{"$f":"'Inputs and Assumptions'!G26","v":247829.23952321577},null,"Primary (Capital)",{"$f":"'Inputs and Assumptions'!C41","v":444714.59175980859},{"$f":"'Inputs and Assumptions'!D41","v":409745.5562086014},{"$f":"'Inputs and Assumptions'!E41","v":377526.22455746779},{"$f":"'Inputs and Assumptions'!F41","v":347840.38061917532},{"$f":"'Inputs and Assumptions'!G41","v":320488.80983332847}]
["Solar PV",{"$f":"'Inputs and Assumptions'!C27","v":108022.96437116916},{"$f":"'Inputs and Assumptions'!D27","v":99528.844880971694},{"$f":"'Inputs and Assumptions'!E27","v":91702.639536009519},{"$f":"'Inputs and Assumptions'!F27","v":84491.828554106251},{"$f":"'Inputs and Assumptions'!G27","v":77848.021916677928},null,"Load Control (Non-capital)",{"$f":"'Inputs and Assumptions'!C42","v":332411.87551214395},{"$f":"'Inputs and Assumptions'!D42","v":306273.48718890711},{"$f":"'Inputs and Assumptions'!E42","v":282190.42659150343},{"$f":"'Inputs and Assumptions'!F42","v":260001.07809128985},{"$f":"'Inputs and Assumptions'!G42","v":239556.53430614434}]
["Total",{"$f":"SUM(B39:B41)","v":1075926.8576485626},{"$f":"SUM(C39:C41)","v":991324.00172083976},913373.68279434938,841552.79502275272,775379.36569830473,null,"Load Control (Capital)",{"$f":"'Inputs and Assumptions'!C43","v":248048.7062833918},{"$f":"'Inputs and Assumptions'!D43","v":228544.00778872281},{"$f":"'Inputs and Assumptions'!E43","v":210573.01317450585},{"$f":"'Inputs and Assumptions'!F43","v":194015.12341720014},{"$f":"'Inputs and Assumptions'!G43","v":178759.22250016377}]
[null,null,null,null,null,null,null,"Solar PV (Non-capital)",{"$f":"'Inputs and Assumptions'!C44","v":105818.1320266151},{"$f":"'Inputs and Assumptions'!D44","v":97497.384092174456},{"$f":"'Inputs and Assumptions'!E44","v":89830.91765810168},{"$f":"'Inputs and Assumptions'!F44","v":82767.28491164044},{"$f":"'Inputs and Assumptions'!G44","v":76259.083511954275}]
["Relationships",null,null,null,null,null,null,"Solar PV (Capital)",{"$f":"'Inputs and Assumptions'!C45","v":69964.959847913735},{"$f":"'Inputs and Assumptions'!D45","v":64463.437717554007},{"$f":"'Inputs and Assumptions'!E45","v":59394.514216803014},{"$f":"'Inputs and Assumptions'!F45","v":54724.173018923357},{"$f":"'Inputs and Assumptions'!G45","v":50421.072587168892}]
[null,"Factor"]
["Primary",1]
["Load Control",0.36767785930241087]
["Solar PV",0.24866822953208562]
[]
["Revenue Weights (%)"]
["Year","2020-21","2021-22","2022-23","2023-24","2024-25"]
["Primary",{"$f":"B39*$B$46/SUMPRODUCT(B$39:B$41,$B$46:$B$48)","v":0.8027789152256114},{"$f":"C39*$B$46/SUMPRODUCT(C$39:C$41,$B$46:$B$48)","v":0.8027789152256114},0.8027789152256114,0.8027789152256114,0.8027789152256114]
["Load Control",{"$f":"B40*$B$47/SUMPRODUCT(B$39:B$41,$B$46:$B$48)","v":0.16266385491868504},{"$f":"C40*$B$47/SUMPRODUCT(C$39:C$41,$B$46:$B$48)","v":0.16266385491868504},0.16266385491868507,0.16266385491868504,0.16266385491868504]
["Solar PV",{"$f":"B41*$B$48/SUMPRODUCT(B$39:B$41,$B$46:$B$48)","v":3.4557229855703607E-2},{"$f":"C41*$B$48/SUMPRODUCT(C$39:C$41,$B$46:$B$48)","v":3.4557229855703607E-2},3.4557229855703607E-2,3.4557229855703614E-2,3.4557229855703614E-2]
[]
["Building Blocks ($m, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25"]
["Return on Capital",{"$f":"'(EGX&EGN) Building Blocks'!K8","v":3.2337686824394161},{"$f":"'(EGX&EGN) Building Blocks'!L8","v":2.9405511948950553},{"$f":"'(EGX&EGN) Building Blocks'!M8","v":2.6477793005087698},{"$f":"'(EGX&EGN) Building Blocks'!N8","v":2.3559452557096323},{"$f":"'(EGX&EGN) Building Blocks'!O8","v":2.0657440976585706}]
["Return of Capital (regulatory depreciation)",{"$f":"'(EGX&EGN) Building Blocks'!K9","v":4.5231017360274546},{"$f":"'(EGX&EGN) Building Blocks'!L9","v":4.7776447780314948},{"$f":"'(EGX&EGN) Building Blocks'!M9","v":5.0443377497822359},{"$f":"'(EGX&EGN) Building Blocks'!N9","v":5.3209123289579461},{"$f":"'(EGX&EGN) Building Blocks'!O9","v":5.6077186102813403}]
["Operating Expenditure",{"$f":"'(EGX&EGN) Building Blocks'!K10","v":39.637876803004922},{"$f":"'(EGX&EGN) Building Blocks'!L10","v":37.971452003376776},{"$f":"'(EGX&EGN) Building Blocks'!M10","v":36.429564243939183},{"$f":"'(EGX&EGN) Building Blocks'!N10","v":35.000878755823628},{"$f":"'(EGX&EGN) Building Blocks'!O10","v":33.640210586377918}]
["Revenue Adjustments",{"$f":"'(EGX&EGN) Building Blocks'!K11","v":0},{"$f":"'(EGX&EGN) Building Blocks'!L11","v":0},{"$f":"'(EGX&EGN) Building Blocks'!M11","v":0},{"$f":"'(EGX&EGN) Building Blocks'!N11","v":0},{"$f":"'(EGX&EGN) Building Blocks'!O11","v":0}]
["Net Tax Allowance",{"$f":"'(EGX&EGN) Building Blocks'!K12","v":0.10732079177315471},{"$f":"'(EGX&EGN) Building Blocks'!L12","v":0.13082592896207115},{"$f":"'(EGX&EGN) Building Blocks'!M12","v":0.15583753283825819},{"$f":"'(EGX&EGN) Building Blocks'!N12","v":0.18159312984744197},{"$f":"'(EGX&EGN) Building Blocks'!O12","v":0.20804557350645086}]
["Annual Revenue Requirement (unsmoothed)",{"$f":"'(EGX&EGN) Building Blocks'!K13","v":47.502068013244944},{"$f":"'(EGX&EGN) Building Blocks'!L13","v":45.820473905265395},{"$f":"'(EGX&EGN) Building Blocks'!M13","v":44.277518827068441},{"$f":"'(EGX&EGN) Building Blocks'!N13","v":42.85932947033865},{"$f":"'(EGX&EGN) Building Blocks'!O13","v":41.521718867824283}]
[]
["Unsmoothed Revenue Allocation between Metering Type ($m, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25"]
["Primary (Non-Capital)",{"$f":"B60*B52","v":31.820451741762717},{"$f":"C60*C52","v":30.482681048812175},29.244886065891219,28.097967479543239,27.005651762493596]
["Primary (Capital)",{"$f":"(B58+B59+B62)*B52","v":6.3132068888832764},{"$f":"(C58+C59+C62)*C52","v":6.3010292879802128},6.3001724669843755,6.3085985399522988,6.3271086685211833]
["Load Control (Non-Capital)",{"$f":"B60*B53","v":6.4476498415687038},{"$f":"C60*C53","v":6.1765827597290919},5.92577335292704,5.6933778639637804,5.4720463342565901]
["Load Control (Capital)",{"$f":"(B58+B59+B62)*B53","v":1.2792196580752817},{"$f":"(C58+C59+C62)*C53","v":1.2767521599023921},1.2765785457185681,1.2782858869115079,1.2820365196336057]
["Solar PV (Non-Capital)",{"$f":"B60*B54","v":1.3697752196735031},{"$f":"C60*C54","v":1.3121881948355085},1.2589048251209278,1.2095334123166106,1.1625124896277357]
["Solar PV (Capital)",{"$f":"(B58+B59+B62)*B54","v":0.27176466328146853},{"$f":"(C58+C59+C62)*C54","v":0.27124045400601698},0.27120357042632054,0.27156628765121382,0.27236309329157349]
["Annual Revenue Requirement (unsmoothed)",{"$f":"SUM(B67:B72)","v":47.502068013244958},{"$f":"SUM(C67:C72)","v":45.820473905265402},44.277518827068455,42.859329470338643,41.521718867824283]
[]
["Unsmoothed Revenue Allocation ($m, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,"NPV"]
["Non-Capital Revenue (unsmoothed)",{"$f":"B60","v":39.637876803004922},{"$f":"C60","v":37.971452003376776},36.429564243939183,35.000878755823628,33.640210586377918,null,{"$f":"SUMPRODUCT($B77:$F77,$B$86:$F$86)","v":160.94826215205535}]
["Capital Revenue (unsmoothed)",{"$f":"B58+B59+B62","v":7.8641912102400262},{"$f":"C58+C59+C62","v":7.8490219018886211},7.8479545831292636,7.85845071451502,7.8815082814463615,null,{"$f":"SUMPRODUCT($B78:$F78,$B$86:$F$86)","v":34.503958381639819}]
["Annual Revenue Requirement (unsmoothed)",{"$f":"B77+B78","v":47.502068013244951},{"$f":"C77+C78","v":45.820473905265395},44.277518827068448,42.85932947033865,41.521718867824276,null,{"$f":"SUMPRODUCT($B79:$F79,$B$86:$F$86)","v":195.45222053369514}]
[]
["Calculated Revenue Allocation ($m, nominal)"]
[null,"2020-21","2021-22","2022-23","2023-24","2024-25",null,"NPV"]
["Non-Capital Revenue (smoothed)",{"$f":"(B9*I39+B15*I41+B21*I43)/1000000","v":40.868146851120954},{"$f":"(C9*J39+C15*J41+C21*J43)/1000000","v":38.546994444074038},36.357674500984118,34.292699448652556,32.345006979029527,null,{"$f":"SUMPRODUCT($B83:$F83,$B$86:$F$86)","v":160.94826215205538}]
["Capital Revenue (smoothed)",{"$f":"(B10*I40+B16*I42+B22*I44)/1000000","v":8.7612802973518402},{"$f":"(C10*J40+C16*J42+C22*J44)/1000000","v":8.2636735199979725},7.794328879736482,7.3516411966755753,6.9340964589224185,null,{"$f":"SUMPRODUCT($B84:$F84,$B$86:$F$86)","v":34.503958381639819}]
["Annual Revenue Requirement (smoothed)",{"$f":"B83+B84","v":49.629427148472793},{"$f":"C83+C84","v":46.81066796407201},{"$f":"D83+D84","v":44.1520033807206},{"$f":"E83+E84","v":41.644340645328128},{"$f":"F83+F84","v":39.279103437951946},null,{"$f":"SUMPRODUCT($B85:$F85,$B$86:$F$86)","v":195.4522205336952}]
["WACC",{"$f":"1/PRODUCT($B87:B87)","v":0.95535238289725111},{"$f":"1/PRODUCT($B87:C87)","v":0.91385753008657311},{"$f":"1/PRODUCT($B87:D87)","v":0.87527678814842536},{"$f":"1/PRODUCT($B87:E87)","v":0.83939242114254764},{"$f":"1/PRODUCT($B87:F87)","v":0.80600566806916085}]
["WACC",{"$f":"1+'Inputs and Assumptions'!E74","v":1.0467341871983908},{"$f":"1+'Inputs and Assumptions'!F74","v":1.0454062602151422},{"$f":"1+'Inputs and Assumptions'!G74","v":1.0440783332318935},{"$f":"1+'Inputs and Assumptions'!H74","v":1.042750406248645},{"$f":"1+'Inputs and Assumptions'!I74","v":1.0414224792653963}]
[null,null,null,null,null,"Difference in Final Year Revenue",null,"NPV Difference"]
[null,null,null,null,null,{"$f":"F85/F79-1","v":-5.401065974679975E-2},null,{"$f":"H77-H83","v":0}]
[null,null,null,null,null,null,null,{"$f":"H78-H84","v":0}]
[]
[]
["Notes"]
["- Values in green cells were sourced from the AER's preliminary decision Ergon Energy - Metering Pricing Model - April 2015.xlsx "]
[]
[]
[]
["Return to top"]