{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["AER Final decision changes","Purpose","Allocations","CPI","PTRM output","Pricing Input","Table for reg proposals"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":1,"defined_names":1348,"comments":0,"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":"AER Final decision changes","index":0,"n_rows":3,"n_cols":2,"rows_kept":3,"cut":false,"hidden":false,"merged":[]}
["Cells","Description of changes"]
["CPI' - Row 4","updated CPI forecast"]
["PTRM output'","replaced values with values from draft decision public lighting PTRM"]

{"sheet":"Purpose","index":1,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["The purpose of this file is to breakdown the output from the PTRM for the Public Lighting Pricing Model "]

{"sheet":"Allocations","index":2,"n_rows":7,"n_cols":6,"rows_kept":7,"cut":false,"hidden":false,"merged":[[3,1,3,5]]}
["Ergon Energy "]
[]
["Table 1: Proportions used allocate the opex and capex between Rate 1 (Conventional and LED) and Rate 2A LED"]
[null,"2025-30 regulatory period"]
["Tariff","2025-26","2026-27","2027-28","2028-29","2029-30"]
["Rate 1",0,0,0,0,0]
["Rate 2A",0,0.25,0.25,0.25,0.25]

{"sheet":"CPI","index":3,"n_rows":6,"n_cols":8,"rows_kept":6,"cut":false,"hidden":false,"merged":[[1,1,1,5]]}
["Table 1: Forecast inflation"]
[null,"2025-30 regulatory period"]
[null,"2025-26","2026-27","2027-28","2028-29","2029-30"]
["CPI",3.2000000000000001E-2,2.7E-2,2.6333333333333334E-2,2.5666666666666667E-2,2.5000000000000001E-2]
[]
[null,"Source: Ergon 2025-30 Public Lighting PTRM",null,null,null,null,null,"cell 377 of PTRM input"]

{"sheet":"PTRM output","index":4,"n_rows":23,"n_cols":28,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
["Table 1a: Building Blocks: unsmoothed"]
["Revenue ($ Nominal)","2025-26","2026-27","2027-28","2028-29","2029-30"]
["Return on",7.976354971115903,8.6314780973491843,9.3144911982460776,10.046007376546072,10.776526773568243]
["Depreciation",8.2143212688012248,9.2027553726768136,10.270484666108187,11.422048178967277,12.663039077836206]
["Opex",11.045599167220891,10.976833332239643,10.897497390897701,10.841707151839362,10.801635379003297]
["Tax"]
["Smoothed Revenue",{"$f":"SUM(B3:B6)","v":27.236275407138017},{"$f":"SUM(C3:C6)","v":28.811066802265643},30.482473255251964,32.309762707352711,34.241201230407746]
[]
["Table 1b: Building Blocks: Adjusted for Smoothing",null,null,null,null,null,null,"Table 2: Capex use to split revenue (return on) between Conv and LED ",null,null,null,null,null,null,null,"Table 4: Allocation of depreciation between Conv and LED",null,null,null,null,null,null,"Table 5: Real opex from PTRM Used to allocate the $ Nominal opex between CONV and LED"]
["Revenue ($ Nominal)","2025-26","2026-27","2027-28","2028-29","2029-30",null,"Value of Asset Base","2025-26","2026-27","2027-28","2028-29","2029-30",null,null,"Depreciation","2025-26","2026-27","2027-28","2028-29","2029-30",null,"Opex ($ Real)","2025-26","2026-27","2027-28","2028-29","2029-30"]
["Return on",{"$f":"B3/B$7*B$23","v":7.9763549711159012},{"$f":"C3/C$7*C$23","v":8.6370166768859757},9.3247472229612107,10.043337670211841,10.760646591412359,null,"Conventional",90.714902090826314,{"$f":"I11-Q11","v":82.223003933487533},{"$f":"J11-R11","v":73.731105776148752},65.239207618809971,56.747309461471197,null,null,"Conventional",8.4918981573387757,8.4918981573387757,8.4918981573387757,8.4918981573387757,8.4918981573387757,null,"Conventional",2.01736438066045,1.4046733546436001,0.86976027669431999,0.40539590381093199,0]
["Depreciation",{"$f":"B4/B$7*B$23","v":8.214321268801223},9.2086605249594644,10.281793318650777,11.419012792546551,12.644378950002038,null,"LED",40.250142441791844,{"$f":"I12+17.63-Q12","v":54.907661414113448},{"$f":"J12+17.86-R12","v":68.89922456072938},{"$f":"K12+18.07-S12","v":82.193269807259114},{"$f":"L12+18.3-T12","v":94.79869986251984},null,null,"LED",2.9724810276783953,3.8684368533840736,4.7759547534702538,5.6945699447392686,6.6253999299870827,null,"LED",8.6706444815538593,8.93024262163431,9.1136302405124496,9.2591286208403414,9.3694409876749898]
["Opex",11.045599167220887,10.983876861028923,10.90949643627714,10.838825989883945,10.785718197057371,null,"Total ",{"$f":"I11+I12","v":130.96504453261815},{"$f":"J11+J12","v":137.130665347601},142.63033033687813,147.43247742606908,151.54600932399103,null,null,"Total",{"$f":"SUM(Q11:Q12)","v":11.464379185017171},{"$f":"SUM(R11:R12)","v":12.360335010722849},13.26785291080903,14.186468102078045,15.117298087325858,null,"Total",{"$f":"SUM(X11:X12)","v":10.688008862214309},{"$f":"SUM(Y11:Y12)","v":10.334915976277911},9.9833905172067698,9.6645245246512737,9.3694409876749898]
["Tax",0,0,0,0,0]
["Smoothed Revenue",{"$f":"SUM(B11:B14)","v":27.23627540713801},{"$f":"SUM(C11:C14)","v":28.829554062874365},{"$f":"SUM(D11:D14)","v":30.516036977889129},{"$f":"SUM(E11:E14)","v":32.301176452642338},{"$f":"SUM(F11:F14)","v":34.190743738471767},null,"Source: Ergon 2025-30 Public Lighting PTRM",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Source: Ergon 2025-30 Public Lighting PTRM"]
[]
["Note:",null,null,null,null,null,null,"Table 3: Split of Revenue (Return on) between Conv and LED ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Table 6: Split of $ nominal opex between CONV and LED"]
["Revenue streams are upscaled to align with the smooth revenue from the PTRM",null,null,null,null,null,null,"Return on ($ Nominal)","2025-26","2026-27","2027-28","2028-29","2029-30",null,null,"Depreciation","2025-26","2026-27","2027-28","2028-29","2029-30",null,"Opex ($ Nominal)","2025-26","2026-27","2027-28","2028-29","2029-30"]
[null,null,null,null,null,null,null,"Conventional",{"$f":"B11*(I11/I13)","v":5.5249418868119529},{"$f":"C11*(J11/J13)","v":5.1787209986698768},4.820320630318542,4.4441998323017344,4.029388466593776,null,null,"Conventional",{"$f":"B4*Q11/Q13","v":6.0845143483639372},{"$f":"C4*R11/R13","v":6.3225520444128716},6.5734757837153417,6.8371400961877917,7.1132577786198858,null,"Conventional",{"$f":"B$13*(X11/X$13)","v":2.0848596413296407},{"$f":"C$13*(Y11/Y$13)","v":1.4928770773548499},0.95044330106671115,0.45465409573027815,{"$f":"F$13*(AB11/AB$13)","v":0}]
["Source: Ergon 2025-30 Public Lighting PTRM",null,null,null,null,null,null,"LED",{"$f":"B11*(I12/I13)","v":2.4514130843039488},{"$f":"C11*(J12/J13)","v":3.4582956782160972},4.5044265926426679,5.5991378379101064,6.7312581248185834,null,null,"LED",{"$f":"B4*Q12/Q13","v":2.129806920437288},{"$f":"C4*R12/R13","v":2.8802033282639417},3.6970088823928453,4.5849080827794833,5.5497812992163205,null,"LED",{"$f":"B$13*(X12/X$13)","v":8.9607395258912472},{"$f":"C$13*(Y12/Y$13)","v":9.4909997836740718},9.9590531352104286,10.384171894153667,10.785718197057371]
[null,null,null,null,null,null,null,"Total ",{"$f":"SUM(I19:I20)","v":7.9763549711159012},{"$f":"SUM(J19:J20)","v":8.637016676885974},9.3247472229612107,10.043337670211841,10.76064659141236,null,null,"Total",{"$f":"SUM(Q19:Q20)","v":8.2143212688012248},{"$f":"SUM(R19:R20)","v":9.2027553726768136},10.270484666108187,11.422048178967275,12.663039077836206,null,"Total",{"$f":"SUM(X19:X20)","v":11.045599167220889},{"$f":"SUM(Y19:Y20)","v":10.983876861028921},10.90949643627714,10.838825989883945,10.785718197057371]
[]
["smoothed",27.23627540713801,28.829554062874365,30.516036977889126,32.301176452642338,34.190743738471767,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Note: uses the Real opex values in Table 5 to allocate the Nominal opex values in Table 1 between LED and CONV"]

{"sheet":"Pricing Input","index":5,"n_rows":27,"n_cols":16,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
["Ergon Energy"]
["Table 1: Revenue (Return on, return of, opex) by light type ",null,null,null,null,null,null,null,"Table 2: Unit cost of residual value of legacy CONV infrastructure"]
["Light type","Revenue","2025-26","2026-27","2027-28","2028-29","2029-30",null,null,"2024-25"]
["CONV","Opex (inc Tax)",{"$f":"'PTRM output'!X19*1000000","v":2084859.6413296408},{"$f":"'PTRM output'!Y19*1000000","v":1492877.0773548498},{"$f":"'PTRM output'!Z19*1000000","v":950443.3010667112},{"$f":"'PTRM output'!AA19*1000000","v":454654.09573027818},{"$f":"'PTRM output'!AB19*1000000","v":0},null,"Volume of Conventional - Rate 1 lights",{"$f":"'[1]ERG - Yearly volume scenarios'!$J$5+'[1]ERG - Yearly volume scenarios'!$J$7","v":35359},"Source:  file \"Ergon Energy -  11.01 - ACS Public Lighting Capex & Opex Forecasting Model 2025-30 - November\""]
["CONV","Capital (RoA & Depn) - R1",{"$f":"('PTRM output'!I19+'PTRM output'!Q19)*1000000","v":11609456.235175891},{"$f":"('PTRM output'!J19+'PTRM output'!R19)*1000000","v":11501273.043082748},{"$f":"('PTRM output'!K19+'PTRM output'!S19)*1000000","v":11393796.414033882},{"$f":"('PTRM output'!L19+'PTRM output'!T19)*1000000","v":11281339.928489527},{"$f":"('PTRM output'!M19+'PTRM output'!U19)*1000000","v":11142646.245213661},null,"Total capital Rate 1 CONV",{"$f":"C5","v":11609456.235175891}]
["CONV","Capital (RoA & Depn) - R2",0,0,0,0,0,null,"Derived unit cost",{"$f":"J5/J4","v":328.33101148719959}]
["LED","Opex (inc Tax)",{"$f":"'PTRM output'!X20*1000000","v":8960739.5258912481},{"$f":"('PTRM output'!Y20)*1000000","v":9490999.7836740725},{"$f":"('PTRM output'!Z20)*1000000","v":9959053.1352104284},{"$f":"('PTRM output'!AA20)*1000000","v":10384171.894153668},{"$f":"('PTRM output'!AB20)*1000000","v":10785718.19705737},null,"Proportion for pole & cabling",0.8]
["LED","Capital (RoA & Depn) - R1",{"$f":"(('PTRM output'!Q20+'PTRM output'!I20)*(1-Allocations!B7))*1000000","v":4581220.0047412366},{"$f":"(('PTRM output'!R20+'PTRM output'!J20)*(1-Allocations!C7))*1000000","v":4753874.2548600286},{"$f":"(('PTRM output'!S20+'PTRM output'!K20)*(1-Allocations!D7))*1000000","v":6151076.6062766351},{"$f":"(('PTRM output'!T20+'PTRM output'!L20)*(1-Allocations!E7))*1000000","v":7638034.4405171918},{"$f":"(('PTRM output'!U20+'PTRM output'!M20)*(1-Allocations!F7))*1000000","v":9210779.5680261794},null,"Unit cos for pole & cabling",{"$f":"J6*J7","v":262.66480918975969}]
["LED","Capital (RoA & Depn) - R2A",{"$f":"(('PTRM output'!Q20+'PTRM output'!I20)*Allocations!B7)*1000000","v":0},{"$f":"(('PTRM output'!R20+'PTRM output'!J20)*Allocations!C7)*1000000","v":1584624.7516200095},{"$f":"(('PTRM output'!S20+'PTRM output'!K20)*Allocations!D7)*1000000","v":2050358.8687588782},{"$f":"(('PTRM output'!T20+'PTRM output'!L20)*Allocations!E7)*1000000","v":2546011.4801723976},{"$f":"(('PTRM output'!U20+'PTRM output'!M20)*Allocations!F7)*1000000","v":3070259.8560087262}]
[null,"Total",{"$f":"SUM(C4:C9)","v":27236275.407138016},{"$f":"SUM(D4:D9)","v":28823648.910591707},{"$f":"SUM(E4:E9)","v":30504728.325346537},{"$f":"SUM(F4:F9)","v":32304211.839063063},{"$f":"SUM(G4:G9)","v":34209403.866305932},null,"Note"]
[null,null,null,null,null,null,null,null,"This area is used to transfer the legacy residual value of legacy conventional lights to the LED asset base once the asset is converted"]
[]
[null,null,null,null,null,null,null,null,"Table 3: Estimated residual value from CONV to LED "]
[null,null,null,null,null,null,null,null,"Residual value to LED","2025-26","2026-27","2027-28","2028-29","2029-30"]
[null,null,null,null,null,null,null,null,"Number of Rate 1 lights CONV converted to LED",6039,12261,9892,9325,11929]
[null,null,null,null,null,null,null,null,"Nominal unit cost",{"$f":"$J$8","v":262.66480918975969},{"$f":"J16*(1+CPI!C4)","v":269.75675903788317},{"$f":"K16*(1+CPI!D4)","v":276.86035369254745},{"$f":"L16*(1+CPI!E4)","v":283.96643610398951},{"$f":"M16*(1+CPI!F4)","v":291.0655970065892}]
[null,null,null,null,null,null,null,null,"Value of CONV pole & cabling ",{"$f":"J15*J16","v":1586232.7826969589},{"$f":"K15*K16","v":3307487.6225634855},{"$f":"L15*L16","v":2738702.6187266796},{"$f":"M15*M16","v":2647987.0166697023},{"$f":"N15*N16","v":3472121.5066916025}]
[]
[null,null,null,null,null,null,null,null,"Table 4: Modified revenue for the Public Lighting Model"]
[null,null,null,null,null,null,null,null,"Light type","Tariffs","Revenue","2025-26","2026-27","2027-28","2028-29","2029-30"]
[null,null,null,null,null,null,null,null,"CONV","Rate 1 & 2","Opex (inc Tax)",{"$f":"C4","v":2084859.6413296408},{"$f":"D4","v":1492877.0773548498},{"$f":"E4","v":950443.3010667112},{"$f":"F4","v":454654.09573027818},{"$f":"G4","v":0}]
[null,null,null,null,null,null,null,null,"CONV","Rate 1","Capital (RoA & Depn) - R1",{"$f":"C5-J17","v":10023223.452478932},{"$f":"D5-K17","v":8193785.4205192626},{"$f":"E5-L17","v":8655093.7953072023},{"$f":"F5-M17","v":8633352.9118198249},{"$f":"G5-N17","v":7670524.7385220584}]
[null,null,null,null,null,null,null,null,"CONV","Rate 2","Capital (RoA & Depn) - R2",{"$f":"C6","v":0},0,0,0,0]
[null,null,null,null,null,null,null,null,"LED","Rate 1, 2, 2A","Opex (inc Tax)",8960739.5258912481,9490999.7836740725,9959053.1352104284,10384171.894153668,10785718.19705737]
[null,null,null,null,null,null,null,null,"LED","Rate 1","Capital (RoA & Depn) - R1",{"$f":"C8+J17","v":6167452.7874381952},{"$f":"D8+K17","v":8061361.8774235137},{"$f":"E8+L17","v":8889779.2250033151},{"$f":"F8+M17","v":10286021.457186894},{"$f":"G8+N17","v":12682901.074717782}]
[null,null,null,null,null,null,null,null,"LED","Rate 2A","Capital (RoA & Depn) - R2A",{"$f":"C9","v":0},{"$f":"D9","v":1584624.7516200095},{"$f":"E9","v":2050358.8687588782},{"$f":"F9","v":2546011.4801723976},{"$f":"G9","v":3070259.8560087262}]
[null,null,null,null,null,null,null,null,null,null,"Total",{"$f":"SUM(L21:L26)","v":27236275.407138016},{"$f":"SUM(M21:M26)","v":28823648.91059171},{"$f":"SUM(N21:N26)","v":30504728.325346537},{"$f":"SUM(O21:O26)","v":32304211.839063063},{"$f":"SUM(P21:P26)","v":34209403.866305932}]

{"sheet":"Table for reg proposals","index":6,"n_rows":20,"n_cols":7,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Revenue"]
[]
["Conventional","2025-26","2026-27","2027-28","2028-29","2029-30","Total"]
["Return on capital",{"$f":"'PTRM output'!I19","v":5.5249418868119529},{"$f":"'PTRM output'!J19","v":5.1787209986698768},{"$f":"'PTRM output'!K19","v":4.820320630318542},{"$f":"'PTRM output'!L19","v":4.4441998323017344},{"$f":"'PTRM output'!M19","v":4.029388466593776},{"$f":"SUM(B4:F4)","v":23.997571814695881}]
["Depreciation",{"$f":"'PTRM output'!Q11","v":8.4918981573387757},{"$f":"'PTRM output'!R11","v":8.4918981573387757},{"$f":"'PTRM output'!S11","v":8.4918981573387757},{"$f":"'PTRM output'!T11","v":8.4918981573387757},{"$f":"'PTRM output'!U11","v":8.4918981573387757},{"$f":"SUM(B5:F5)","v":42.459490786693877}]
["Opex",{"$f":"'PTRM output'!X19","v":2.0848596413296407},{"$f":"'PTRM output'!Y19","v":1.4928770773548499},{"$f":"'PTRM output'!Z19","v":0.95044330106671115},{"$f":"'PTRM output'!AA19","v":0.45465409573027815},{"$f":"'PTRM output'!AB19","v":0},4.9828341154814799]
["Total",{"$f":"SUM(B4:B6)","v":16.101699685480369},{"$f":"SUM(C4:C6)","v":15.163496233363501},14.26266208872403,13.390752085370789,12.521286623932552,71.439896716871246]
[]
["LED","2025-26","2026-27","2027-28","2028-29","2029-30","Total"]
["Return on capital",{"$f":"'PTRM output'!I20","v":2.4514130843039488},{"$f":"'PTRM output'!J20","v":3.4582956782160972},{"$f":"'PTRM output'!K20","v":4.5044265926426679},{"$f":"'PTRM output'!L20","v":5.5991378379101064},{"$f":"'PTRM output'!M20","v":6.7312581248185834},{"$f":"SUM(B10:F10)","v":22.744531317891404}]
["Depreciation",{"$f":"'PTRM output'!Q12","v":2.9724810276783953},{"$f":"'PTRM output'!R12","v":3.8684368533840736},{"$f":"'PTRM output'!S12","v":4.7759547534702538},{"$f":"'PTRM output'!T12","v":5.6945699447392686},{"$f":"'PTRM output'!U12","v":6.6253999299870827},{"$f":"SUM(B11:F11)","v":23.936842509259073}]
["Opex",{"$f":"'PTRM output'!X20","v":8.9607395258912472},{"$f":"'PTRM output'!Y20","v":9.4909997836740718},{"$f":"'PTRM output'!Z20","v":9.9590531352104286},{"$f":"'PTRM output'!AA20","v":10.384171894153667},{"$f":"'PTRM output'!AB20","v":10.785718197057371},49.580682535986789]
["Total",{"$f":"SUM(B10:B12)","v":14.384633637873591},{"$f":"SUM(C10:C12)","v":16.817732315274242},19.23943448132335,21.677879676803041,24.142376251863038,96.262056363137262]
[]
["CONV + LED combined","2025-26","2026-27","2027-28","2028-29","2029-30","Total"]
["Return on capital",{"$f":"B4+B10","v":7.9763549711159012},{"$f":"C4+C10","v":8.637016676885974},9.3247472229612107,10.043337670211841,10.76064659141236,{"$f":"SUM(B16:F16)","v":46.742103132587282}]
["Depreciation",{"$f":"B5+B11","v":11.464379185017171},12.360335010722849,13.26785291080903,14.186468102078045,15.117298087325858,66.396333295952957]
["Opex",11.045599167220889,10.983876861028921,10.90949643627714,10.838825989883945,10.785718197057371,54.563516651468262]
["Grand total ",{"$f":"B7+B13","v":30.486333323353961},{"$f":"C7+C13","v":31.981228548637745},{"$f":"D7+D13","v":33.502096570047378},{"$f":"E7+E13","v":35.06863176217383},{"$f":"F7+F13","v":36.663662875795588},{"$f":"SUM(B19:F19)","v":167.70195308000848}]
["Reconciliation",{"$f":"'PTRM output'!B23-B19","v":-3.2500579162159511},{"$f":"'PTRM output'!C23-C19","v":-3.1516744857633796},{"$f":"'PTRM output'!D23-D19","v":-2.9860595921582522},{"$f":"'PTRM output'!E23-E19","v":-2.767455309531492},{"$f":"'PTRM output'!F23-F19","v":-2.4729191373238208}]