{"text_format":"1","workbook":"xlsx","n_sheets":13,"sheets":["Cover","Model information and guide","FAST colour guide","Inputs -->","Inputs","Calcs-->","Time","Index","Calculations","Outputs-->","Output","Integrity checks-->","Check"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":8,"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":"Cover","index":0,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Model information and guide","index":1,"n_rows":34,"n_cols":6,"rows_kept":34,"cut":false,"hidden":false,"merged":[[19,2,21,8],[27,2,27,3],[26,2,26,3]]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Model information and guide"}]
[]
[null,"Model name:","Bad debt Retail Exit Code price cap adjustment model "]
[null,"Version number:",2]
[null,"Filename:",{"$f":" MID(CELL(\"filename\"), FIND(\"[\", CELL(\"filename\"), 1) + 1, FIND(\"]\", CELL(\"filename\"), 1) - FIND(\"[\", CELL(\"filename\"), 1) - 1)","v":"Bad debt REC adjustment model - February 2022_final.xlsm"}]
[null,"Date:","2022-02-28"]
[null,"Author: ","Samuel Adams - Associate, Economist"]
[null,"Author contact information:","samuel.adams@ofwat.gov.uk"]
[]
[]
[null,"Summary of model:","The bad debt Retail Exit Code price cap adjustment model calculates the appropriate uplift to the net and gross margins in the REC price caps to give Retailers additional pricing flexibility to recoup their portion of excess bad debt costs arising from 01 October 2019 to 31 March 2021."]
[null,null,"The model uses outturn customer bad debt costs of 2.87% of market turnover - this is calculated as a weighted average for the years 2019-20 and 2020-21. "]
[null,null,"The model assumes that industry real revenue returns to pre-pandemic levels (2019-20 industry revenue) by the final forecast period 2023-24.  "]
[null,null,"The model incorporates the allowance of an efficient cost of financing bad debt of 3.5% (real terms) in its determination of the present value of an increase industry revenue from an uplift to REC price caps. "]
[null,null,"The model specifies the REC price cap adjustment in constant price terms (2020-21 prices), using the annual CPIH index for October to adjust annual revenue forecasts accordingly.   "]
[null,null,"Details of our methodology are included in appendix A1 of the Bad debt February 2022 decision document. "]
[null,null,"Follow link >>","Bad debt February 2022 decision document"]
[]
[]
[null,"Disclaimer:","Macros must be enabled in this model.  This model uses a macro to implement the solver function to determine the uplift to REC net and gross margins such that present value of the increase in industry revenue from an uplift in prices equals the value of Retailer portion of excess bad debt costs arising from 01 October 2019 to 31 March 2021.  This macro is run by clicking the 'Recalculate REC price cap adjustment' button in the 'Calculations' sheet.  If any inputs are altered and the error check status is highlighted red, the macro needs to be run by clicking the 'Recalculate REC price cap adjustment' button."]
[]
[]
[]
[null,"Known limitations of the model:"]
[]
[null,null,"Issue",null,"Details","Model link"]
[null,null,"Use of macros",null,"Macros must be enabled for this model to calculate the adjustment to REC net and gross margins. ","n/a"]
[null,null,"Hardware requirements ",null,"The macros in this model have been tested on 32 bit Excel. The macros do not contain any code, such as DECLARE statements, that will not work on 64 bit Excel but they have not been tested on 64 bit Excel.","n/a"]
[]
[]
[null,"Error check status",{"$f":"Check!C$8","v":0}]
[]
[]
["END"]

{"sheet":"FAST colour guide","index":2,"n_rows":55,"n_cols":7,"rows_kept":55,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"FAST colour guide"}]
[]
["Cell / Row / Column colour"]
[]
[null,"Font colour"]
[null,null,null,null,"Ofwat second blue text (no shade)",null,"Imported from another sheet/section"]
[]
[null,null,null,null,"Ofwat red text (no shade)",null,"Exported to another sheet/section *"]
[]
[null,null,null,null,"Black text (no shade)",null,"Neither imported nor exported"]
[]
[null,null,null,null,"* Except from input sheets (sheets with 'Inp' prefix)"]
[]
[null,null,null,null,"Ofwat dark green (no shade)",null,"Documentation"]
[]
[null,"Font and shade combinations"]
[null,null,null,null,"Black text + light yellow shade",null,"Inputs"]
[]
[null,null,null,null,"Black text + light grey shade on ENTIRE row",null,"Within-worksheet counter-flow "]
[]
[null,null,null,null,"Ofwat second blue text + light grey shade",null,"Between-worksheet counter-flow "]
[]
[null,null,null,null,"Light blue",null,"Stored/dead/hard coded outputs"]
[]
[null,null,null,null,"White text (Franklin Gothic Demi) + Ofwat purple shade",null,"Warning text"]
[]
[null,null,null,null,0,null,"Conditional formatting for error checks*"]
[]
[null,null,null,null,"* returns green when value is zero. For all other values it returns red"]
[]
[null,"Other"]
[null,null,null,null,"Empty cell with light grey shade",null,"Empty cells being referenced/used"]
[]
[null,null,null,null,"Entire row/column with blue text + Ofwat light blue shade",null,"Section separator"]
[]
[null,"Checks and administration"]
[]
[null,null,null,null,"Lemon shade",null,"Values or logic to be reviewed/discussed"]
[]
[null,"Worksheet tab colour coding"]
[]
[null,null,null,null,"Ofwat yellow – 50%",null,"Input sheets"]
[]
[null,null,null,null,"Ofwat grey – 33%",null,"Documentation and calculation cheets"]
[]
[null,null,null,null,"Second blue – 66%",null,"Cached results sheets"]
[]
[null,null,null,null,"Ofwat purple – 66%",null,"Quality control"]
[]
[null,null,null,null,"Ofwat yellow – 100%",null,"Temporary"]
[]
[null,null,null,null,"Ofwat green – 66%",null,"Outputs"]
[]
[]
["END"]

{"sheet":"Inputs -->","index":3,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Inputs","index":4,"n_rows":61,"n_cols":14,"rows_kept":61,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Inputs"}]
[null,null,null,null,{"$f":"Time!E$22","v":"Model Period BEG"},null,null,null,null,{"$f":"Time!J$22","v":"2019-04-01"},{"$f":"Time!K$22","v":"2020-04-01"},{"$f":"Time!L$22","v":"2021-04-01"},{"$f":"Time!M$22","v":"2022-04-01"},{"$f":"Time!N$22","v":"2023-04-01"}]
[null,null,null,null,{"$f":"Time!E$23","v":"Model Period END"},{"$f":" Check!F$8","v":0},"Error chks",null,null,{"$f":"Time!J$23","v":"2020-03-31"},{"$f":"Time!K$23","v":"2021-03-31"},{"$f":"Time!L$23","v":"2022-03-31"},{"$f":"Time!M$23","v":"2023-03-31"},{"$f":"Time!N$23","v":"2024-03-31"}]
[null,null,null,null,{"$f":"Time!E$59","v":"Outturn vs Forecast"},null,null,null,null,{"$f":"Time!J$59","v":"Outturn"},{"$f":"Time!K$59","v":"Outturn"},{"$f":"Time!L$59","v":"Forecast"},{"$f":"Time!M$59","v":"Forecast"},{"$f":"Time!N$59","v":"Forecast"}]
[null,null,null,null,{"$f":"Time!E$94","v":"Financial Year Ending"},null,null,null,null,{"$f":"Time!J$94","v":2020},{"$f":"Time!K$94","v":2021},{"$f":"Time!L$94","v":2022},{"$f":"Time!M$94","v":2023},{"$f":"Time!N$94","v":2024}]
[null,null,null,null,{"$f":"Time!E$11","v":"Model column counter"},"Constant","Unit","Total",null,{"$f":"Time!J$11","v":1},{"$f":"Time!K$11","v":2},{"$f":"Time!L$11","v":3},{"$f":"Time!M$11","v":4},{"$f":"Time!N$11","v":5}]
[]
["TIME"]
[]
[null,null,null,null,"First date of time ruler","2019-04-01","date"]
[]
[null,null,null,null,"Last Outturn Date","2021-03-31","date"]
[]
[null,null,null,null,"Acquisition date (midnight)","2021-03-31","date"]
[null,null,null,null,"Length of forecast period",3,"years"]
[null,null,null,null,"Last forecast date",{"$f":"DATE(YEAR(F14)+F15,MONTH(F14),DAY(F14))","v":"2024-03-31"},"date"]
[]
[null,null,null,null,"First date of REC price cap adjustment","2022-04-01","date"]
[null,null,null,null,"Length of REC price cap adjustment period",2,"years"]
[null,null,null,null,"Last date of REC price cap adjustment period",{"$f":"DATE(YEAR(F14)+F15,MONTH(F14),DAY(F14))","v":"2024-03-31"},"date"]
[]
[null,null,null,null,"Operation Start Date (midnight)","2021-03-31","date"]
[null,null,null,null,"Operation Finish Date (midnight)","2024-03-31","date"]
[null,null,null,null,"First Modelling Column Financial Year Number",2020,"year"]
[null,null,null,null,"Financial Year End Month Number",3,"month #"]
[]
[]
["BAD DEBT INPUTS"]
[]
[null,null,null,null,"Industry wide outturn bad debt as a proportion of turnover for the two years ended 31 March 2021",2.87E-2,"%"]
[null,null,null,null,"Excess bad debt threshold",0.02,"%"]
[]
[null,null,null,null,"Excess bad debt sharing factor",0.75,"%"]
[]
[]
["INDUSTRY REVENUE INPUTS"]
[]
[null,null,null,null,"Annual industry revenue - nominal",null,"£000s",null,null,2565000,2194000,2400000,2540000,{"$f":" J38","v":2565000}]
[null,null,null,null,"Annual industry revenue - financial year price terms",null,"year",null,null,2020,2021,2022,2022,2020]
[]
[null,null,null,null,"Industry revenue from 01 October 2019 to 31 March 2020 - nominal",null,"£000s",null,null,1257000]
[null,null,null,null,"Industry revenue from 01 October 2019 to 31 March 2020 - financial year price terms",null,"year",null,null,2020]
[]
[]
["FINANCING COST INPUT"]
[]
[null,null,null,null,"Efficient cost of financing excess bad debt",3.5000000000000003E-2,"%"]
[null,null,null,null,"Present value reference year",2021,"year"]
[]
[]
["INFLATION"]
[]
[null,null,null,null,"Base year prices financial year",2021,"year"]
[]
[null,null,null,"CPIH indices"]
[null,null,null,null,"Consumer Price Index (with housing) for October 2019",108.3,"Index"]
[null,null,null,null,"Consumer Price Index (with housing) for October 2020 - base year",109.2,"Index"]
[null,null,null,null,"Consumer Price Index (with housing) for October 2021",113.4,"Index"]
[]
[]
["END"]

{"sheet":"Calcs-->","index":5,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Time","index":6,"n_rows":97,"n_cols":14,"rows_kept":97,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Time"}]
[null,null,null,null,{"$f":"Time!E$22","v":"Model Period BEG"},null,null,null,null,{"$f":"Time!J$22","v":"2019-04-01"},{"$f":"Time!K$22","v":"2020-04-01"},{"$f":"Time!L$22","v":"2021-04-01"},{"$f":"Time!M$22","v":"2022-04-01"},{"$f":"Time!N$22","v":"2023-04-01"}]
[null,null,null,null,{"$f":"Time!E$23","v":"Model Period END"},{"$f":" Check!F$8","v":0},"Error chks",null,null,{"$f":"Time!J$23","v":"2020-03-31"},{"$f":"Time!K$23","v":"2021-03-31"},{"$f":"Time!L$23","v":"2022-03-31"},{"$f":"Time!M$23","v":"2023-03-31"},{"$f":"Time!N$23","v":"2024-03-31"}]
[null,null,null,null,{"$f":"Time!E$59","v":"Outturn vs Forecast"},null,null,null,null,{"$f":"Time!J$59","v":"Outturn"},{"$f":"Time!K$59","v":"Outturn"},{"$f":"Time!L$59","v":"Forecast"},{"$f":"Time!M$59","v":"Forecast"},{"$f":"Time!N$59","v":"Forecast"}]
[null,null,null,null,{"$f":"Time!E$94","v":"Financial Year Ending"},null,null,null,null,{"$f":"Time!J$94","v":2020},{"$f":"Time!K$94","v":2021},{"$f":"Time!L$94","v":2022},{"$f":"Time!M$94","v":2023},{"$f":"Time!N$94","v":2024}]
[null,null,null,null,{"$f":"Time!E$11","v":"Model column counter"},"Constant","Unit","Total",null,{"$f":"Time!J$11","v":1},{"$f":"Time!K$11","v":2},{"$f":"Time!L$11","v":3},{"$f":"Time!M$11","v":4},{"$f":"Time!N$11","v":5}]
[]
["MODEL PERIOD"]
[]
[null,"Model Column Counter "]
[null,null,null,null,"Model column counter",null,"counter",null,null,{"$f":" I11 + 1","v":1},{"$f":" J11 + 1","v":2},{"$f":" K11 + 1","v":3},4,5]
[null,null,null,null,"Model Column Total",{"$f":" MAX(J11:N11)","v":5},"column"]
[]
[null,null,null,null,{"$f":" E$11","v":"Model column counter"},0,"counter",0,0,1,2,3,4,5]
[null,null,null,null,"First model column flag",null,"flag",{"$f":" SUM(J15:N15)","v":1},null,{"$f":" IF( J14 = 1, 1, 0)","v":1},0,0,0,0]
[]
[null,null,null,null,{"$f":" Inputs!E$10","v":"First date of time ruler"},{"$f":" Inputs!F$10","v":"2019-04-01"},{"$f":" Inputs!G$10","v":"date"}]
[null,null,null,null,"First model period BEG",{"$f":" DATE(YEAR(F17), MONTH(F17), 1)","v":"2019-04-01"},"month"]
[]
[null,null,null,null,{"$f":" E$18","v":"First model period BEG"},{"$f":" F$18","v":"2019-04-01"},{"$f":" G$18","v":"month"}]
[null,null,null,null,{"$f":" E$15","v":"First model column flag"},0,"flag",1,0,1,0,0,0,0]
[null,null,null,null,"Model Period BEG",null,"date",null,null,{"$f":" IF( J21 = 1, $F20, I23 + 1)","v":"2019-04-01"},"2020-04-01","2021-04-01","2022-04-01","2023-04-01"]
[null,null,null,null,"Model Period END",null,"date",null,null,{"$f":" DATE(YEAR(J22), MONTH(J22) + 12, DAY(1) - 1)","v":"2020-03-31"},{"$f":" DATE(YEAR(K22), MONTH(K22) + 12, DAY(1) - 1)","v":"2021-03-31"},"2022-03-31","2023-03-31","2024-03-31"]
[]
[null,null,null,null,{"$f":" E$23","v":"Model Period END"},{"$f":"F$23","v":"00:00:00"},"date","00:00:00","00:00:00","2020-03-31","2021-03-31","2022-03-31","2023-03-31","2024-03-31"]
[null,null,null,"less",{"$f":" E$22","v":"Model Period BEG"},"00:00:00","date","00:00:00","00:00:00","2019-04-01","2020-04-01","2021-04-01","2022-04-01","2023-04-01"]
[null,null,null,null,"Days in Model Period",null,"days",{"$f":" SUM(J27:N27)","v":1827},null,{"$f":" J25 - J26 + 1","v":366},365,365,365,366]
[]
[]
["OUTTURN PERIOD"]
[]
[null,null,null,null,{"$f":" Inputs!E$12","v":"Last Outturn Date"},{"$f":" Inputs!F$12","v":"2021-03-31"},{"$f":" Inputs!G$12","v":"date"}]
[null,null,null,null,{"$f":" E$23","v":"Model Period END"},"00:00:00","date","00:00:00","00:00:00","2020-03-31","2021-03-31","2022-03-31","2023-03-31","2024-03-31"]
[null,null,null,null,"Last Outturn Flag",null,"flag",{"$f":" SUM(J34:N34)","v":1},null,{"$f":" IF(J33 = $F32, 1, 0)","v":0},1,0,0,0]
[null,null,null,null,"Outturn Period Flag",null,"flag",{"$f":" SUM(J35:N35)","v":2},null,{"$f":" IF($F32 >= J33, 1, 0)","v":1},{"$f":" IF($F32 >= K33, 1, 0)","v":1},0,0,0]
[null,null,null,null,"Outturn Period Total",{"$f":" SUM(J35:N35)","v":2},"columns"]
[]
[null,null,null,null,{"$f":" Inputs!E$14","v":"Acquisition date (midnight)"},{"$f":" Inputs!F$14","v":"2021-03-31"},{"$f":" Inputs!G$14","v":"date"}]
[null,null,null,null,{"$f":" E$23","v":"Model Period END"},"00:00:00","date","00:00:00","00:00:00","2020-03-31","2021-03-31","2022-03-31","2023-03-31","2024-03-31"]
[null,null,null,null,"Acquisition / initial balance date flag",null,"flag",{"$f":" SUM(J40:N40)","v":1},null,{"$f":" IF(J39 = $F38, 1, 0)","v":0},1,0,0,0]
[]
[]
["FORECAST PERIOD"]
[]
[null,null,null,null,{"$f":" E$34","v":"Last Outturn Flag"},0,"flag",1,0,0,1,0,0,0]
[null,null,null,null,"1st Forecast Period Flag",null,"flag",{"$f":" SUM(J46:N46)","v":1},null,{"$f":" I45","v":0},{"$f":" J45","v":0},1,0,0]
[]
[null,null,null,null,{"$f":"Inputs!E$16","v":"Last forecast date"},{"$f":"Inputs!F$16","v":"2024-03-31"},{"$f":"Inputs!G$16","v":"date"}]
[null,null,null,null,{"$f":" E$23","v":"Model Period END"},{"$f":" F$23","v":"00:00:00"},"date","00:00:00",null,{"$f":" J$23","v":"2020-03-31"},"2021-03-31","2022-03-31","2023-03-31","2024-03-31"]
[null,null,null,null,"Last Forecast Period Flag",null,"flag",{"$f":" SUM(J50:N50)","v":1},null,{"$f":" IF(AND($F48 > I49, $F48 <= J49), 1, 0)","v":0},{"$f":" IF(AND($F48 > J49, $F48 <= K49), 1, 0)","v":0},0,0,1]
[]
[null,null,null,null,{"$f":" E$46","v":"1st Forecast Period Flag"},0,"flag",1,0,{"$f":" J$46","v":0},0,1,0,0]
[null,null,null,null,{"$f":" E$50","v":"Last Forecast Period Flag"},0,"flag",1,0,0,0,0,0,1]
[null,null,null,null,"Forecast Period Flag",null,"flag",{"$f":" SUM(J54:N54)","v":3},null,{"$f":" J52 - I53 + I54","v":0},0,1,1,1]
[null,null,null,null,"Forecast Period Total ",{"$f":" SUM(J54:N54)","v":3},"columns"]
[]
[null,null,null,null,{"$f":" E$35","v":"Outturn Period Flag"},0,"flag",2,0,1,1,0,0,0]
[null,null,null,null,{"$f":" E$54","v":"Forecast Period Flag"},0,"flag",3,0,0,0,1,1,1]
[null,null,null,null,"Outturn vs Forecast",null,"flag",null,null,{"$f":" IF(J57 = 1, \"Outturn\", IF(J58 = 1, \"Forecast\", \"Post-Fcst\"))","v":"Outturn"},{"$f":" IF(K57 = 1, \"Outturn\", IF(K58 = 1, \"Forecast\", \"Post-Fcst\"))","v":"Outturn"},"Forecast","Forecast","Forecast"]
[]
[]
["REC ADJUSTMENT PERIOD"]
[]
[null,null,null,null,{"$f":" Inputs!E18","v":"First date of REC price cap adjustment"},{"$f":" Inputs!F18","v":"2022-04-01"},{"$f":" Inputs!G18","v":"date"}]
[null,null,null,null,{"$f":" E$23","v":"Model Period END"},"00:00:00","date","00:00:00","00:00:00","2020-03-31","2021-03-31","2022-03-31","2023-03-31","2024-03-31"]
[null,null,null,null,"1st REC price cap adjustment period flag",null,"flag",{"$f":" SUM( J66:N66 )","v":1},null,{"$f":" IF(AND($F64 > I65, $F64 <= J65), 1, 0)","v":0},{"$f":" IF(AND($F64 > J65, $F64 <= K65), 1, 0)","v":0},0,1,0]
[]
[null,null,null,null,{"$f":" E$66","v":"1st REC price cap adjustment period flag"},0,"flag",1,0,0,0,0,1,0]
[null,null,null,null,"REC price cap adjustment Period Flag",null,"flag",{"$f":" SUM( J69:N69 )","v":2},null,{"$f":" I69 + J68","v":0},{"$f":" J69 + K68","v":0},0,1,1]
[null,null,null,null,"REC price cap adjustment Period Total",{"$f":" SUM(J69:N69)","v":2},"columns"]
[]
[]
["MODELLING PERIOD CHECK"]
[]
[null,null,null,null,{"$f":" E$12","v":"Model Column Total"},{"$f":" F$12","v":5},{"$f":" G$12","v":"column"}]
[null,null,null,"less",{"$f":" E$36","v":"Outturn Period Total"},{"$f":" F$36","v":2},{"$f":" G$36","v":"columns"}]
[null,null,null,"less",{"$f":" E$55","v":"Forecast Period Total "},{"$f":" F$55","v":3},{"$f":" G$55","v":"columns"}]
[null,null,null,null,"Modelling Period Check",{"$f":" IF( F75 - SUM(F76:F77) <> 0, 1, 0 )","v":0},"check"]
[]
[]
["REC PRICE CAP ADJUSTMENT PERIOD CHECK"]
[]
[null,null,null,null,{"$f":" Inputs!E19","v":"Length of REC price cap adjustment period"},{"$f":" Inputs!F19","v":2},"years"]
[null,null,null,"less",{"$f":" E70","v":"REC price cap adjustment Period Total"},{"$f":" F70","v":2},"columns"]
[null,null,null,null,"REC price cap adjustment Period Check",{"$f":" IF( F83 - F84 <> 0, 1, 0 )","v":0},"check"]
[]
[]
["FINANCIAL YEAR"]
[]
[null,null,null,null,{"$f":"Inputs!E24","v":"First Modelling Column Financial Year Number"},{"$f":"Inputs!F24","v":2020},{"$f":"Inputs!G24","v":"year"}]
[null,null,null,null,{"$f":"Inputs!E25","v":"Financial Year End Month Number"},{"$f":"Inputs!F25","v":3},{"$f":"Inputs!G25","v":"month #"}]
[null,null,null,null,{"$f":" E$23","v":"Model Period END"},{"$f":" F$23","v":"00:00:00"},"date","00:00:00","00:00:00","2020-03-31","2021-03-31","2022-03-31","2023-03-31","2024-03-31"]
[null,null,null,null,{"$f":" E$15","v":"First model column flag"},0,"flag",1,0,{"$f":" J$15","v":1},0,0,0,0]
[null,null,null,null,"Financial Year Ending",null,"year #",null,null,{"$f":" IF(J93 = 1, $F90, IF(J92 > (DATE(I94, $F91 + 1, 1) - 1), I94 + 1, I94))","v":2020},{"$f":" IF(K93 = 1, $F90, IF(K92 > (DATE(J94, $F91 + 1, 1) - 1), J94 + 1, J94))","v":2021},{"$f":" IF(L93 = 1, $F90, IF(L92 > (DATE(K94, $F91 + 1, 1) - 1), K94 + 1, K94))","v":2022},{"$f":" IF(M93 = 1, $F90, IF(M92 > (DATE(L94, $F91 + 1, 1) - 1), L94 + 1, L94))","v":2023},{"$f":" IF(N93 = 1, $F90, IF(N92 > (DATE(M94, $F91 + 1, 1) - 1), M94 + 1, M94))","v":2024}]
[]
[]
["END"]

{"sheet":"Index","index":7,"n_rows":19,"n_cols":14,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Index"}]
[null,null,null,null,{"$f":"Time!E$22","v":"Model Period BEG"},null,null,null,null,{"$f":"Time!J$22","v":"2019-04-01"},{"$f":"Time!K$22","v":"2020-04-01"},{"$f":"Time!L$22","v":"2021-04-01"},{"$f":"Time!M$22","v":"2022-04-01"},{"$f":"Time!N$22","v":"2023-04-01"}]
[null,null,null,null,{"$f":"Time!E$23","v":"Model Period END"},{"$f":" Check!F$8","v":0},"Error chks",null,null,{"$f":"Time!J$23","v":"2020-03-31"},{"$f":"Time!K$23","v":"2021-03-31"},{"$f":"Time!L$23","v":"2022-03-31"},{"$f":"Time!M$23","v":"2023-03-31"},{"$f":"Time!N$23","v":"2024-03-31"}]
[null,null,null,null,{"$f":"Time!E$59","v":"Outturn vs Forecast"},null,null,null,null,{"$f":"Time!J$59","v":"Outturn"},{"$f":"Time!K$59","v":"Outturn"},{"$f":"Time!L$59","v":"Forecast"},{"$f":"Time!M$59","v":"Forecast"},{"$f":"Time!N$59","v":"Forecast"}]
[null,null,null,null,{"$f":"Time!E$94","v":"Financial Year Ending"},null,null,null,null,{"$f":"Time!J$94","v":2020},{"$f":"Time!K$94","v":2021},{"$f":"Time!L$94","v":2022},{"$f":"Time!M$94","v":2023},{"$f":"Time!N$94","v":2024}]
[null,null,null,null,{"$f":"Time!E$11","v":"Model column counter"},"Constant","Unit","Total",null,{"$f":"Time!J$11","v":1},{"$f":"Time!K$11","v":2},{"$f":"Time!L$11","v":3},{"$f":"Time!M$11","v":4},{"$f":"Time!N$11","v":5}]
[]
["CPIH INDEXATION"]
[]
[null,null,null,"Constant price adjustments to base year"]
[null,null,null,null,{"$f":" Inputs!E56","v":"Consumer Price Index (with housing) for October 2019"},{"$f":" Inputs!F56","v":108.3},{"$f":" Inputs!G56","v":"Index"},{"$f":" Inputs!H56","v":0},{"$f":" Inputs!I56","v":0},{"$f":" Inputs!J56","v":0},{"$f":" Inputs!K56","v":0},{"$f":" Inputs!L56","v":0},{"$f":" Inputs!M56","v":0},{"$f":" Inputs!N56","v":0}]
[null,null,null,null,{"$f":" Inputs!E57","v":"Consumer Price Index (with housing) for October 2020 - base year"},{"$f":" Inputs!F57","v":109.2},{"$f":" Inputs!G57","v":"Index"},{"$f":" Inputs!H57","v":0},{"$f":" Inputs!I57","v":0},{"$f":" Inputs!J57","v":0},{"$f":" Inputs!K57","v":0},{"$f":" Inputs!L57","v":0},{"$f":" Inputs!M57","v":0},{"$f":" Inputs!N57","v":0}]
[null,null,null,null,{"$f":" Inputs!E58","v":"Consumer Price Index (with housing) for October 2021"},{"$f":" Inputs!F58","v":113.4},{"$f":" Inputs!G58","v":"Index"},{"$f":" Inputs!H58","v":0},{"$f":" Inputs!I58","v":0},{"$f":" Inputs!J58","v":0},{"$f":" Inputs!K58","v":0},{"$f":" Inputs!L58","v":0},{"$f":" Inputs!M58","v":0},{"$f":" Inputs!N58","v":0}]
[null,null,null,null,{"$f":" Inputs!E53","v":"Base year prices financial year"},{"$f":" Inputs!F53","v":2021},{"$f":" Inputs!G53","v":"year"},{"$f":" Inputs!H53","v":0},{"$f":" Inputs!I53","v":0},{"$f":" Inputs!J53","v":0},{"$f":" Inputs!K53","v":0},{"$f":" Inputs!L53","v":0},{"$f":" Inputs!M53","v":0},{"$f":" Inputs!N53","v":0}]
[null,null,null,null,{"$f":" Inputs!E39","v":"Annual industry revenue - financial year price terms"},{"$f":" Inputs!F39","v":0},{"$f":" Inputs!G39","v":"year"},{"$f":" Inputs!H39","v":0},{"$f":" Inputs!I39","v":0},{"$f":" Inputs!J39","v":2020},{"$f":" Inputs!K39","v":2021},{"$f":" Inputs!L39","v":2022},{"$f":" Inputs!M39","v":2022},{"$f":" Inputs!N39","v":2020}]
[null,null,null,null,"Index to inflate prices to base year 2020-21 CALC",null,"Index",0,null,{"$f":" IF( J15 < $F14, $F11 / $F12, 0 )","v":0.99175824175824168},{"$f":" IF( K15 < $F14, $F11 / $F12, 0 )","v":0},0,0,0.99175824175824168]
[null,null,null,null,"Index to deflate prices to base year 2020-21 CALC",null,"Index",0,null,{"$f":" IF( J15 > $F14, $F13 / $F12, 0 )","v":0},{"$f":" IF( K15 > $F14, $F13 / $F12, 0 )","v":0},{"$f":" IF( L15 > $F14, $F13 / $F12, 0 )","v":1.0384615384615385},1.0384615384615385,0]
[]
["END"]

{"sheet":"Calculations","index":8,"n_rows":90,"n_cols":14,"rows_kept":90,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Calculations"}]
[null,null,null,null,{"$f":"Time!E$22","v":"Model Period BEG"},null,null,null,null,{"$f":"Time!J$22","v":"2019-04-01"},{"$f":"Time!K$22","v":"2020-04-01"},{"$f":"Time!L$22","v":"2021-04-01"},{"$f":"Time!M$22","v":"2022-04-01"},{"$f":"Time!N$22","v":"2023-04-01"}]
[null,null,null,null,{"$f":"Time!E$23","v":"Model Period END"},{"$f":" Check!F$8","v":0},"Error chks",null,null,{"$f":"Time!J$23","v":"2020-03-31"},{"$f":"Time!K$23","v":"2021-03-31"},{"$f":"Time!L$23","v":"2022-03-31"},{"$f":"Time!M$23","v":"2023-03-31"},{"$f":"Time!N$23","v":"2024-03-31"}]
[null,null,null,null,{"$f":"Time!E$59","v":"Outturn vs Forecast"},null,null,null,null,{"$f":"Time!J$59","v":"Outturn"},{"$f":"Time!K$59","v":"Outturn"},{"$f":"Time!L$59","v":"Forecast"},{"$f":"Time!M$59","v":"Forecast"},{"$f":"Time!N$59","v":"Forecast"}]
[null,null,null,null,{"$f":"Time!E$94","v":"Financial Year Ending"},null,null,null,null,{"$f":"Time!J$94","v":2020},{"$f":"Time!K$94","v":2021},{"$f":"Time!L$94","v":2022},{"$f":"Time!M$94","v":2023},{"$f":"Time!N$94","v":2024}]
[null,null,null,null,{"$f":"Time!E$11","v":"Model column counter"},"Constant","Unit","Total",null,{"$f":"Time!J$11","v":1},{"$f":"Time!K$11","v":2},{"$f":"Time!L$11","v":3},{"$f":"Time!M$11","v":4},{"$f":"Time!N$11","v":5}]
[]
["2020-21 EXCESS BAD DEBT TO BE RECOUPED BY RETAILERS"]
[]
[null,"Excess bad debt costs which Retailers in aggregate may pass through to customers"]
[]
[null,null,null,null,{"$f":" Inputs!E41","v":"Industry revenue from 01 October 2019 to 31 March 2020 - nominal"},{"$f":" Inputs!F41","v":0},{"$f":" Inputs!G41","v":"£000s"},{"$f":" Inputs!H41","v":0},{"$f":" Inputs!I41","v":0},{"$f":" Inputs!J41","v":1257000},{"$f":" Inputs!K41","v":0},{"$f":" Inputs!L41","v":0},{"$f":" Inputs!M41","v":0},{"$f":" Inputs!N41","v":0}]
[null,null,null,null,{"$f":" Inputs!E42","v":"Industry revenue from 01 October 2019 to 31 March 2020 - financial year price terms"},{"$f":" Inputs!F42","v":0},{"$f":" Inputs!G42","v":"year"},{"$f":" Inputs!H42","v":0},{"$f":" Inputs!I42","v":0},{"$f":" Inputs!J42","v":2020},{"$f":" Inputs!K42","v":0},{"$f":" Inputs!L42","v":0},{"$f":" Inputs!M42","v":0},{"$f":" Inputs!N42","v":0}]
[null,null,null,null,{"$f":" Index!E16","v":"Index to inflate prices to base year 2020-21 CALC"},{"$f":" Index!F16","v":0},{"$f":" Index!G16","v":"Index"},{"$f":" Index!H16","v":0},{"$f":" Index!I16","v":0},{"$f":" Index!J16","v":0.99175824175824168},{"$f":" Index!K16","v":0},{"$f":" Index!L16","v":0},{"$f":" Index!M16","v":0},{"$f":" Index!N16","v":0.99175824175824168}]
[null,null,null,null,"01 October 2019 to 31 March 2020 total industry revenue inflated to 2020-21 prices CALC",0,"£000s",0,null,{"$f":" IF( J12 > 0, J12 / J14, 0 )","v":1267445.9833795014},{"$f":" IF( K12 > 0, K12 / K14, 0 )","v":0},0,0,0]
[]
[null,null,null,null,{"$f":" E15","v":"01 October 2019 to 31 March 2020 total industry revenue inflated to 2020-21 prices CALC"},0,"£000s",0,0,1267445.9833795014,0,0,0,0]
[null,null,null,null,{"$f":" Inputs!E38","v":"Annual industry revenue - nominal"},{"$f":" Inputs!F38","v":0},{"$f":" Inputs!G38","v":"£000s"},{"$f":" Inputs!H38","v":0},{"$f":" Inputs!I38","v":0},{"$f":" Inputs!J38","v":2565000},{"$f":" Inputs!K38","v":2194000},{"$f":" Inputs!L38","v":2400000},{"$f":" Inputs!M38","v":2540000},{"$f":" Inputs!N38","v":2565000}]
[null,null,null,null,{"$f":" Inputs!E30","v":"Industry wide outturn bad debt as a proportion of turnover for the two years ended 31 March 2021"},{"$f":" Inputs!F30","v":2.87E-2},{"$f":" Inputs!G30","v":"%"}]
[null,null,null,null,{"$f":" Inputs!E31","v":"Excess bad debt threshold"},{"$f":" Inputs!F31","v":0.02},{"$f":" Inputs!G31","v":"%"}]
[null,null,null,null,{"$f":" Inputs!E33","v":"Excess bad debt sharing factor"},{"$f":" Inputs!F33","v":0.75},{"$f":" Inputs!G33","v":"%"}]
[null,null,null,null,"Value of Retailer portion of excess bad debt costs arising from 01 October 2019 to 31 March 2021 CALC",{"$f":" ( J17 + ( K18 ) ) * ( F19 - F20 ) * F21","v":22585.935041551245},"£000s",0]
[]
[]
[null,"Real terms adjustment to annual industry revenue"]
[]
[null,null,null,null,{"$f":" Inputs!E38","v":"Annual industry revenue - nominal"},{"$f":" Inputs!F38","v":0},{"$f":" Inputs!G38","v":"£000s"},{"$f":" Inputs!H38","v":0},{"$f":" Inputs!I38","v":0},{"$f":" Inputs!J38","v":2565000},{"$f":" Inputs!K38","v":2194000},{"$f":" Inputs!L38","v":2400000},{"$f":" Inputs!M38","v":2540000},{"$f":" Inputs!N38","v":2565000}]
[null,null,null,null,{"$f":" Inputs!E39","v":"Annual industry revenue - financial year price terms"},{"$f":" Inputs!F39","v":0},{"$f":" Inputs!G39","v":"year"},{"$f":" Inputs!H39","v":0},{"$f":" Inputs!I39","v":0},{"$f":" Inputs!J39","v":2020},{"$f":" Inputs!K39","v":2021},{"$f":" Inputs!L39","v":2022},{"$f":" Inputs!M39","v":2022},{"$f":" Inputs!N39","v":2020}]
[null,null,null,null,"2020-21 priced total industry revenue CALC",0,"£000s",0,null,{"$f":" IF( J28 = 2021, J27, 0)","v":0},{"$f":" IF( K28 = 2021, K27, 0)","v":2194000},0,0,0]
[]
[null,null,null,null,{"$f":" Inputs!E38","v":"Annual industry revenue - nominal"},{"$f":" Inputs!F38","v":0},{"$f":" Inputs!G38","v":"£000s"},{"$f":" Inputs!H38","v":0},{"$f":" Inputs!I38","v":0},{"$f":" Inputs!J38","v":2565000},{"$f":" Inputs!K38","v":2194000},{"$f":" Inputs!L38","v":2400000},{"$f":" Inputs!M38","v":2540000},{"$f":" Inputs!N38","v":2565000}]
[null,null,null,null,{"$f":" Inputs!E39","v":"Annual industry revenue - financial year price terms"},{"$f":" Inputs!F39","v":0},{"$f":" Inputs!G39","v":"year"},{"$f":" Inputs!H39","v":0},{"$f":" Inputs!I39","v":0},{"$f":" Inputs!J39","v":2020},{"$f":" Inputs!K39","v":2021},{"$f":" Inputs!L39","v":2022},{"$f":" Inputs!M39","v":2022},{"$f":" Inputs!N39","v":2020}]
[null,null,null,null,{"$f":" Index!E16","v":"Index to inflate prices to base year 2020-21 CALC"},{"$f":" Index!F16","v":0},{"$f":" Index!G16","v":"Index"},{"$f":" Index!H16","v":0},{"$f":" Index!I16","v":0},{"$f":" Index!J16","v":0.99175824175824168},{"$f":" Index!K16","v":0},{"$f":" Index!L16","v":0},{"$f":" Index!M16","v":0},{"$f":" Index!N16","v":0.99175824175824168}]
[null,null,null,null,"2019-20 priced total industry revenue inflated to 2020-21 prices CALC",0,"£000s",0,null,{"$f":" IF( J28 = 2020, J27 / J33, 0)","v":2586315.7894736845},{"$f":" IF( K28 = 2020, K27 / K33, 0)","v":0},0,0,2586315.7894736845]
[]
[null,null,null,null,{"$f":" Inputs!E38","v":"Annual industry revenue - nominal"},{"$f":" Inputs!F38","v":0},{"$f":" Inputs!G38","v":"£000s"},{"$f":" Inputs!H38","v":0},{"$f":" Inputs!I38","v":0},{"$f":" Inputs!J38","v":2565000},{"$f":" Inputs!K38","v":2194000},{"$f":" Inputs!L38","v":2400000},{"$f":" Inputs!M38","v":2540000},{"$f":" Inputs!N38","v":2565000}]
[null,null,null,null,{"$f":" Inputs!E39","v":"Annual industry revenue - financial year price terms"},{"$f":" Inputs!F39","v":0},{"$f":" Inputs!G39","v":"year"},{"$f":" Inputs!H39","v":0},{"$f":" Inputs!I39","v":0},{"$f":" Inputs!J39","v":2020},{"$f":" Inputs!K39","v":2021},{"$f":" Inputs!L39","v":2022},{"$f":" Inputs!M39","v":2022},{"$f":" Inputs!N39","v":2020}]
[null,null,null,null,{"$f":" Index!E17","v":"Index to deflate prices to base year 2020-21 CALC"},{"$f":" Index!F17","v":0},{"$f":" Index!G17","v":"Index"},{"$f":" Index!H17","v":0},{"$f":" Index!I17","v":0},{"$f":" Index!J17","v":0},{"$f":" Index!K17","v":0},{"$f":" Index!L17","v":1.0384615384615385},{"$f":" Index!M17","v":1.0384615384615385},{"$f":" Index!N17","v":0}]
[null,null,null,null,"2021-22 priced total industry revenue deflated to 2020-21 prices CALC",0,"£000s",0,null,{"$f":" IF( J28 = 2022, J27 / J38, 0)","v":0},{"$f":" IF( K28 = 2022, K27 / K38, 0)","v":0},{"$f":" IF( L28 = 2022, L27 / L38, 0)","v":2311111.111111111},{"$f":" IF( M28 = 2022, M27 / M38, 0)","v":2445925.9259259258},0]
[]
[null,null,null,null,{"$f":" E29","v":"2020-21 priced total industry revenue CALC"},{"$f":" F29","v":0},"£000s",0,0,0,2194000,0,0,0]
[null,null,null,null,{"$f":" E34","v":"2019-20 priced total industry revenue inflated to 2020-21 prices CALC"},{"$f":" F34","v":0},"£000s",0,0,{"$f":" J34","v":2586315.7894736845},0,0,0,2586315.7894736845]
[null,null,null,null,{"$f":" E39","v":"2021-22 priced total industry revenue deflated to 2020-21 prices CALC"},{"$f":" F39","v":0},"£000s",0,0,0,0,2311111.111111111,2445925.9259259258,0]
[]
[null,null,null,null,"Constant price industry revenue (2020-21 prices)",0,"£000s",0,null,{"$f":" SUM( J41:J43 )","v":2586315.7894736845},{"$f":" SUM( K41:K43 )","v":2194000},2311111.111111111,{"$f":" SUM( M41:M43 )","v":2445925.9259259258},2586315.7894736845]
[]
[]
[null,"Uplift of REC price caps for 2022-23 and 2023-24"]
[]
[null,null,null,null,{"$f":" E45","v":"Constant price industry revenue (2020-21 prices)"},{"$f":" F45","v":0},"£000s",0,0,{"$f":" J45","v":2586315.7894736845},2194000,2311111.111111111,2445925.9259259258,2586315.7894736845]
[null,null,null,null,"Clicking the 'Recalculate REC price cap adjustment' button recalculates the uplift to the net and gross margins in REC price caps (shown in cell F52) when model inputs change - this uses a macro.  When cell F87 is highlighted red, the REC price cap adjustment needs to be recalculated. An explanation of this calculation and the methodology is detailed in annex A3 of the Bad debt December 2021 consultation document. "]
[null,null,null,null,"Uplift to the net and gross margins in REC price caps - MACRO CALC",4.8929597722241359E-3,"%"]
[null,null,null,null,{"$f":" Time!E69","v":"REC price cap adjustment Period Flag"},{"$f":" Time!F69","v":0},{"$f":" Time!G69","v":"flag"},{"$f":" Time!H69","v":2},{"$f":" Time!I69","v":0},{"$f":" Time!J69","v":0},{"$f":" Time!K69","v":0},{"$f":" Time!L69","v":0},{"$f":" Time!M69","v":1},{"$f":" Time!N69","v":1}]
[null,null,null,null,"Constant price industry revenue for financial years 2022-23 and 2023-24 after the REC price cap adjustment CALC",0,"£000s",0,null,{"$f":" IF( J53 = 1, J50 * ( 1 + $F52 ), 0 )","v":0},{"$f":" IF( K53 = 1, K50 * ( 1 + $F52 ), 0 )","v":0},0,2457893.7430873211,2598970.528589847]
[]
[]
[null,"Constant price increase in annual industry revenue from REC price cap adjustment"]
[]
[null,null,null,null,{"$f":" E54","v":"Constant price industry revenue for financial years 2022-23 and 2023-24 after the REC price cap adjustment CALC"},{"$f":" F54","v":0},"£000s",0,0,0,0,0,2457893.7430873211,2598970.528589847]
[null,null,null,null,{"$f":" E45","v":"Constant price industry revenue (2020-21 prices)"},{"$f":" F45","v":0},"£000s",0,0,2586315.7894736845,2194000,2311111.111111111,2445925.9259259258,2586315.7894736845]
[null,null,null,null,"Constant price increase in industry revenue for financial years 2022-23 and 2023-24 after the REC price cap adjustment CALC",0,"£000s",{"$f":"SUM( J61:N61 )","v":24622.556277557742},null,{"$f":"IF( J59 = 0, 0, J59 - J60 )","v":0},{"$f":"IF( K59 = 0, 0, K59 - K60 )","v":0},0,11967.817161395214,12654.739116162527]
[]
[null,null,null,null,"Constant price increase in industry revenue for financial years 2022-23 and 2023-24 after the REC price cap adjustment TOTAL",{"$f":" H61","v":24622.556277557742},"£000s",0]
[]
[]
[null,"Present value of the increase in industry revenue from an uplift to REC price caps"]
[]
[null,null,null,null,{"$f":" Time!E94","v":"Financial Year Ending"},{"$f":" Time!F94","v":0},{"$f":" Time!G94","v":"year #"},{"$f":" Time!H94","v":0},{"$f":" Time!I94","v":0},{"$f":" Time!J94","v":2020},{"$f":" Time!K94","v":2021},{"$f":" Time!L94","v":2022},{"$f":" Time!M94","v":2023},{"$f":" Time!N94","v":2024}]
[null,null,null,null,{"$f":" Inputs!E48","v":"Present value reference year"},{"$f":" Inputs!F48","v":2021},{"$f":" Inputs!G48","v":"year"}]
[null,null,null,null,{"$f":" Time!E69","v":"REC price cap adjustment Period Flag"},{"$f":" Time!F69","v":0},{"$f":" Time!G69","v":"flag"},{"$f":" Time!H69","v":2},{"$f":" Time!I69","v":0},{"$f":" Time!J69","v":0},{"$f":" Time!K69","v":0},{"$f":" Time!L69","v":0},{"$f":" Time!M69","v":1},{"$f":" Time!N69","v":1}]
[null,null,null,null,"Present value counter CALC",0,"year #",0,null,{"$f":" IF( J70 = 1, J68 - $F69, 0)","v":0},{"$f":" IF( K70 = 1, K68 - $F69, 0)","v":0},0,2,3]
[]
[null,null,null,null,{"$f":" E61","v":"Constant price increase in industry revenue for financial years 2022-23 and 2023-24 after the REC price cap adjustment CALC"},{"$f":" F61","v":0},"£000s",{"$f":" H61","v":24622.556277557742},0,0,0,0,11967.817161395214,12654.739116162527]
[null,null,null,null,{"$f":" Inputs!E47","v":"Efficient cost of financing excess bad debt"},{"$f":" Inputs!F47","v":3.5000000000000003E-2},{"$f":" Inputs!G47","v":"%"},{"$f":" Inputs!H47","v":0}]
[null,null,null,null,{"$f":" E71","v":"Present value counter CALC"},{"$f":" F71","v":0},"year #",0,0,0,0,0,2,3]
[null,null,null,null,{"$f":" Time!E69","v":"REC price cap adjustment Period Flag"},{"$f":" Time!F69","v":0},{"$f":" Time!G69","v":"flag"},{"$f":" Time!H69","v":2},{"$f":" Time!I69","v":0},{"$f":" Time!J69","v":0},{"$f":" Time!K69","v":0},{"$f":" Time!L69","v":0},{"$f":" Time!M69","v":1},{"$f":" Time!N69","v":1}]
[null,null,null,null,"Present value in 2020-21 prices of a revenue increase in 2022-23 and 2023-24 after the REC price cap adjustment CALC",0,"£000s",{"$f":" SUM( M77:N77 )","v":22585.935018145694},null,{"$f":" IF( J76 = 1, J73 / ( 1 + $F74 ) ^ J75, 0 )","v":0},{"$f":" IF( K76 = 1, K73 / ( 1 + $F74 ) ^ K75, 0 )","v":0},0,11172.085380191105,11413.849637954589]
[]
[null,null,null,null,"Present value in 2020-21 prices of a revenue increase in 2022-23 and 2023-24 after the REC price cap adjustment TOTAL",{"$f":" H77","v":22585.935018145694},"£000s"]
[]
[]
[null,"Difference between the Retailer portion of excess bad debt costs and the present value of the increase in industry revenue from REC price cap adjustment"]
[]
[null,null,null,null,{"$f":" E22","v":"Value of Retailer portion of excess bad debt costs arising from 01 October 2019 to 31 March 2021 CALC"},{"$f":" F22","v":22585.935041551245},"£000s",0]
[null,null,null,null,{"$f":" E79","v":"Present value in 2020-21 prices of a revenue increase in 2022-23 and 2023-24 after the REC price cap adjustment TOTAL"},{"$f":" F79","v":22585.935018145694},"£000s",0]
[null,null,null,null,"Difference in the Retailer portion of excess bad debt costs and the present value of the increase in industry revenue from REC price cap adjustment",{"$f":" ROUND( F84 - F85, 4 )","v":0},"£000s"]
[null,null,null,null,"Difference in the Retailer portion of excess bad debt costs and the present value of the increase in industry revenue from REC price cap adjustment - Check",{"$f":" IF( $F86 <> 0, 1, 0 )","v":0},"check"]
[]
[]
["END"]

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

{"sheet":"Output","index":10,"n_rows":18,"n_cols":14,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Output"}]
[null,null,null,null,{"$f":"Time!E$22","v":"Model Period BEG"},null,null,null,null,{"$f":"Time!J$22","v":"2019-04-01"},{"$f":"Time!K$22","v":"2020-04-01"},{"$f":"Time!L$22","v":"2021-04-01"},{"$f":"Time!M$22","v":"2022-04-01"},{"$f":"Time!N$22","v":"2023-04-01"}]
[null,null,null,null,{"$f":"Time!E$23","v":"Model Period END"},{"$f":" Check!F$8","v":0},"Error chks",null,null,{"$f":"Time!J$23","v":"2020-03-31"},{"$f":"Time!K$23","v":"2021-03-31"},{"$f":"Time!L$23","v":"2022-03-31"},{"$f":"Time!M$23","v":"2023-03-31"},{"$f":"Time!N$23","v":"2024-03-31"}]
[null,null,null,null,{"$f":"Time!E$59","v":"Outturn vs Forecast"},null,null,null,null,{"$f":"Time!J$59","v":"Outturn"},{"$f":"Time!K$59","v":"Outturn"},{"$f":"Time!L$59","v":"Forecast"},{"$f":"Time!M$59","v":"Forecast"},{"$f":"Time!N$59","v":"Forecast"}]
[null,null,null,null,{"$f":"Time!E$94","v":"Financial Year Ending"},null,null,null,null,{"$f":"Time!J$94","v":2020},{"$f":"Time!K$94","v":2021},{"$f":"Time!L$94","v":2022},{"$f":"Time!M$94","v":2023},{"$f":"Time!N$94","v":2024}]
[null,null,null,null,{"$f":"Time!E$11","v":"Model column counter"},"Constant","Unit","Total",null,{"$f":"Time!J$11","v":1},{"$f":"Time!K$11","v":2},{"$f":"Time!L$11","v":3},{"$f":"Time!M$11","v":4},{"$f":"Time!N$11","v":5}]
[]
["Proportion of bad debt to be recouped by Retailers"]
[]
[null,null,null,null,{"$f":" Calculations!E22","v":"Value of Retailer portion of excess bad debt costs arising from 01 October 2019 to 31 March 2021 CALC"},{"$f":" Calculations!F22","v":22585.935041551245},{"$f":" Calculations!G22","v":"£000s"},{"$f":" Calculations!H22","v":0}]
[]
[null,null,null,null,{"$f":" Calculations!E52","v":"Uplift to the net and gross margins in REC price caps - MACRO CALC"},{"$f":" Calculations!F52","v":4.8929597722241359E-3},{"$f":" Calculations!G52","v":"%"},{"$f":" Calculations!H52","v":0}]
[]
[null,null,null,null,{"$f":" Calculations!E61","v":"Constant price increase in industry revenue for financial years 2022-23 and 2023-24 after the REC price cap adjustment CALC"},{"$f":" Calculations!F61","v":0},{"$f":" Calculations!G61","v":"£000s"},{"$f":" Calculations!H61","v":24622.556277557742},{"$f":" Calculations!I61","v":0},{"$f":" Calculations!J61","v":0},{"$f":" Calculations!K61","v":0},{"$f":" Calculations!L61","v":0},{"$f":" Calculations!M61","v":11967.817161395214},{"$f":" Calculations!N61","v":12654.739116162527}]
[]
[null,null,null,null,{"$f":" Calculations!E77","v":"Present value in 2020-21 prices of a revenue increase in 2022-23 and 2023-24 after the REC price cap adjustment CALC"},{"$f":" Calculations!F77","v":0},{"$f":" Calculations!G77","v":"£000s"},{"$f":" Calculations!H77","v":22585.935018145694},{"$f":" Calculations!I77","v":0},{"$f":" Calculations!J77","v":0},{"$f":" Calculations!K77","v":0},{"$f":" Calculations!L77","v":0},{"$f":" Calculations!M77","v":11172.085380191105},{"$f":" Calculations!N77","v":11413.849637954589}]
[]
["END"]

{"sheet":"Integrity checks-->","index":11,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Check","index":12,"n_rows":16,"n_cols":14,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Check"}]
[null,null,null,null,{"$f":"Time!E$22","v":"Model Period BEG"},null,null,null,null,{"$f":"Time!J$22","v":"2019-04-01"},{"$f":"Time!K$22","v":"2020-04-01"},{"$f":"Time!L$22","v":"2021-04-01"},{"$f":"Time!M$22","v":"2022-04-01"},{"$f":"Time!N$22","v":"2023-04-01"}]
[null,null,null,null,{"$f":"Time!E$23","v":"Model Period END"},{"$f":" Check!F$8","v":0},"Error chks",null,null,{"$f":"Time!J$23","v":"2020-03-31"},{"$f":"Time!K$23","v":"2021-03-31"},{"$f":"Time!L$23","v":"2022-03-31"},{"$f":"Time!M$23","v":"2023-03-31"},{"$f":"Time!N$23","v":"2024-03-31"}]
[null,null,null,null,{"$f":"Time!E$59","v":"Outturn vs Forecast"},null,null,null,null,{"$f":"Time!J$59","v":"Outturn"},{"$f":"Time!K$59","v":"Outturn"},{"$f":"Time!L$59","v":"Forecast"},{"$f":"Time!M$59","v":"Forecast"},{"$f":"Time!N$59","v":"Forecast"}]
[null,null,null,null,{"$f":"Time!E$94","v":"Financial Year Ending"},null,null,null,null,{"$f":"Time!J$94","v":2020},{"$f":"Time!K$94","v":2021},{"$f":"Time!L$94","v":2022},{"$f":"Time!M$94","v":2023},{"$f":"Time!N$94","v":2024}]
[null,null,null,null,{"$f":"Time!E$11","v":"Model column counter"},"Constant","Unit","Total",null,{"$f":"Time!J$11","v":1},{"$f":"Time!K$11","v":2},{"$f":"Time!L$11","v":3},{"$f":"Time!M$11","v":4},{"$f":"Time!N$11","v":5}]
[]
[null,null,null,null,"Total error checks",{"$f":" SUM(F11:F15)","v":0},"check"]
[]
["[Do not delete]"]
[]
[null,null,null,null,{"$f":"Time!E78","v":"Modelling Period Check"},{"$f":" Time!F78","v":0},{"$f":"Time!G78","v":"check"}]
[null,null,null,null,{"$f":" Time!E85","v":"REC price cap adjustment Period Check"},{"$f":" Time!F85","v":0},{"$f":" Time!G85","v":"check"}]
[null,null,null,null,{"$f":"Calculations!E87","v":"Difference in the Retailer portion of excess bad debt costs and the present value of the increase in industry revenue from REC price cap adjustment - Check"},{"$f":" Calculations!F87","v":0},{"$f":"Calculations!G87","v":"check"}]
[]
["END"]