{"text_format":"1","workbook":"xlsx","n_sheets":13,"sheets":["Cover","User Guide","Map & Key","Inputs >>","F_Inputs","InpTime","InpAct","Calcs >>","Bioresources","Outputs >>","F_Outputs","QA >>","Track"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":58,"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":33,"n_cols":7,"rows_kept":33,"cut":false,"hidden":false,"merged":[[7,3,7,4],[18,3,18,4],[7,5,7,6]]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Cover"}]
[]
[null,null,"Model name:","Bioresources Forecasting Accuracy Incentive Model"]
[null,null,"Version number:",{"$f":" LEFT(D5, FIND(\".\", D5, 1) - 1)","v":"Bioresources FAIM v1a"}]
[null,null,"Filename:",{"$f":" MID(CELL(\"filename\"), FIND(\"[\", CELL(\"filename\"), 1) + 1, FIND(\"]\", CELL(\"filename\"), 1) - FIND(\"[\", CELL(\"filename\"), 1) - 1)","v":"Bioresources FAIM v1a.xlsm"}]
[null,null,"Date:","2018-06-06"]
[null,null,"Author:","Ofwat"]
[null,null,"Author contact information:","pr19@ofwat.gsi.gov.uk"]
[]
[]
[null,null,"Summary of model:","This illustrative model is designed to show how we will calculate the bioresources forecasting accuracy incentive."]
[null,null,null,"The financial penalty will apply symmetrically, where the total volume varies from the forecast used to determine the average revenue control. "]
[null,null,null,"We will calculate the absolute value of the penalty to apply, once the difference is greater than ±6% of business plan forecasts."]
[null,null,null,"We will apply the penalty as part of the reconciliation of 2020-2025 performance."]
[]
[null,null,"Known limitations:","There are a number of areas of further work to be carried out in the model. These are listed below:"]
[]
[null,null,null,"Issue",null,"Details","Model link"]
[null,null,null,"No known issues",null,"not applicable","not applicable"]
[]
[null,null,null,"The model has been subject to internal Ofwat quality assurance. "]
[null,null,null,"The model is not a policy statement. "]
[null,null,null,"Users should refer to the \"Delivering Water 2020: Our final methodology for the 2019 price review\" for information on proposed policy."]
[]
[null,null,"Feedback:","We would welcome feedback on the PR19 bioresources FAIM model. Please send any feedback to the following email address."]
[null,null,null,"pr19@ofwat.gsi.gov.uk"]
[]
[null,null,"Error check status:",{"$f":"Track!$F$3","v":0},{"$f":" IF(D28 = 0, \"No output changes\", \"Output changes\")","v":"No output changes"}]
[]
[null,null,"Instructions:","A model 'User guide' is present in the model to help the user in operating the model."]
[null,null,null,"Follow link >>","Goto User Guide"]
[]
["End of sheet"]

{"sheet":"User Guide","index":1,"n_rows":88,"n_cols":5,"rows_kept":88,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"User Guide"}]
[]
[null,"INTRODUCTION"]
[]
[null,null,null,null,"This model will calculate the penalty from the bioresources forecasting accuracy incentive mechanism."]
[]
[null,null,null,"NAVIGATION"]
[null,null,null,null,"In order to navigate through the model the following key strokes will prove useful:"]
[null,null,null,null,"- To go to a sheet or row select a blue font cell and then either press CTRL+ [ or double click"]
[null,null,null,null,"- To return to the last position press F5 + Enter"]
[null,null,null,null,"- In accordance with best practice, the model is saved with a \"Manual Calculation\" setting. To ensure a full recalculation press: Ctrl + Alt + F9"]
[]
[null,null,null,"INITIAL SET UP"]
[null,null,null,null,"On first use of the model the three inputs sheets need to be updated in the model. Please follow the steps below to update the inputs:"]
[]
[null,null,null,"1)","F_Inputs sheet: In this sheet raw data is dumped from another model / file. Please follow the steps below to update the sheet:"]
[null,null,null,"Step 1 - ","Go to relevant source model/file for the data that is to be dumped in this sheet."]
[null,null,null,"Step 2 - ","Select the whole data dump that is required for this model."]
[null,null,null,"Step 3 - ","Copy the data using Ctrl + C."]
[null,null,null,"Step 4 - ","Go to cell F5 in the \"F_Inputs\" sheet in this model."]
[null,null,null,null,{"$f":"F_Inputs!$A$1","v":"F_Inputs"}]
[null,null,null,"Step 5 - ","Press Alt E S V to paste the data."]
[null,null,null,"Step 6 - ","Review the check below to see if the stored labels for the raw data are matching with the dumped labels."]
[null,null,null,null,{"$f":"F_Inputs!$A$3 + F_Inputs!$B$3","v":0}]
[null,null,null,"Step 7 - ","If this check is OK the process is complete "]
[null,null,null,null,"otherwise review the active checks in Col B and correct the raw data and repeat the steps 1 to 6 above again."]
[]
[null,null,null,"2)","InpTime sheet : This is the sheet where all the Inputs related to dates and timelines are set up:"]
[null,null,null,null,{"$f":"InpTime!$A$1","v":"InpTime"}]
[null,null,null,null,"Update all Input cells as required."]
[]
[null,null,null,"3)","InpAct sheet : This is the sheet where all the Inputs used in the model calculations are present, all the inputs flow from this sheet into the model calculations:"]
[null,null,null,null,{"$f":"InpAct!$A$1","v":"InpAct"}]
[null,null,null,null,"Update all Input cells as required."]
[]
[null,null,null,null,"Recalculate the model once all the sheets are updated. Press Ctrl + Alt + F9"]
[]
[]
[]
[null,"HOW TO SAVE A PDF COPY OF ANY SHEET"]
[]
[null,null,null,"Step 1 - ","To save a sheet in PDF format, press button \"Save as PDF\""]
[]
[]
[]
[null,null,null,"Step 2 - ","In the dialog box, \"Enter sheet name\", type the name of the sheet you want to save"]
[]
[null,null,null,"Step 3 - ","In the dialog box, \"Save As\" change the file name if required"]
[]
[null,null,null,"Step 4 - ","Press \"Save\" to save a Pdf copy of the sheet"]
[]
[]
[]
[null,"HOW TO STORE TRACK IN TRACK SHEET"]
[]
[null,null,null,"USING MACRO"]
[null,null,null,null,"To store Track in the Track sheet using the macro, follow the steps below:"]
[]
[null,null,null,"Step 1 - ","Go to the Track sheet and press the \"Store Track\" button to save the track for a new case"]
[null,null,null,null,"Store Track"]
[]
[null,null,null,"Step 2 - ","A dialog box \"Enter the label for the new track column\" will appear, type the new track label (for example: \"version 17r\"), the label will appear in row 6"]
[]
[null,null,null,"Step 3 - ","A dialog box \"Enter the comment for the new track\" will appear, write a brief description of the change. The description will be appear at the bottom of the track column"]
[]
[null,null,null,"Step 4 - ","Press enter, a new track column will appear before the last stored track"]
[]
[null,null,null,"Step 5 - ","Review the difference between stored cases and active case (col H) by selecting the stored case in cell H6."]
[]
[null,null,null,"MANUAL METHOD"]
[]
[null,null,null,null,"To store Track in the Track sheet manually, follow the steps below:"]
[]
[null,null,null,"Step 1 - ","Select the column by pressing Ctrl + Spacebar and Copy (Ctrl + C). Then insert a new column after the last stored column by pressing Alt, I, E"]
[]
[null,null,null,"Step 2 - ","Select the newly inserted column (Ctrl + Spacebar) and delete the previously stored values from this column by pressing Delete button"]
[]
[null,null,null,"Step 3 - ","Go to active track column (H column). Select the range from H6 till 'do not delete row - range end' row and Copy (Ctrl + C)"]
[]
[null,null,null,"Step 4 - ","Paste the selected values (Alt E, S, V) in newly inserted column"]
[]
[null,null,null,"Step 5 - ","Update 'New track name' in row 6 of newly inserted column. Then select 'New track name' from the dropdown list present in H6."]
[]
[null,null,null,"Step 6 - ","Review the difference between stored cases and active case (col H) by selecting the stored case in cell H6."]
[]
[]
[]
["End of sheet"]

{"sheet":"Map & Key","index":2,"n_rows":94,"n_cols":26,"rows_kept":94,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Map & Key"}]
[]
["GENERIC MODEL DESIGN"]
[]
[null,null,null,null,null,"Table of Contents"]
[]
[]
[null,null,null,null,null,null,null,"DOCUMENTATION",null,null,null,"MODEL INPUTS",null,null,null,null,"MODEL CALCULATIONS",null,null,null,null,"MODEL OUTPUTS",null,null,null,"MODEL QA"]
[]
[null,null,null,null,null,null,null,"Cover",null,null,null,"F_Inputs",null,null,null,null,"Bioresources",null,null,null,null,"F_Outputs",null,null,null,"Check"]
[null,null,null,null,null,null,null,"Model cover sheet",null,null,null,"Inputs downloaded from other models",null,null,null,null,"Calculation of incentive penalty",null,null,null,null,"Outputs to be uploaded to Fountain",null,null,null,"Checks"]
[]
[null,null,null,null,null,null,null,"User Guide",null,null,null,"InpTime",null,null,null,null,null,null,null,null,null,null,null,null,null,"Track"]
[null,null,null,null,null,null,null,"Explanation of setup and macros",null,null,null,"Dates and timelines",null,null,null,null,null,null,null,null,null,null,null,null,null,"Model changes and track records"]
[]
[null,null,null,null,null,null,null,"Map & Key",null,null,null,"InpAct"]
[null,null,null,null,null,null,null,"ToC and key to formatting",null,null,null,"All inputs to be flowed into model"]
[]
[]
[]
[]
[]
["SHEET TABS"]
[]
[null,null,null,null,null,null,null,"Light Yellow",null,"Input sheets"]
[]
[null,null,null,null,null,null,null,"No colour (default Excel tab colour)",null,"Calculation and documentation sheets"]
[]
[null,null,null,null,null,null,null,"Pale Blue",null,"Key output sheets"]
[]
[null,null,null,null,null,null,null,"Turquoise",null,"Quality control sheets"]
[]
[null,null,null,null,null,null,null,"Yellow",null,"To be completed, temporary, restructured, or deleted"]
[]
[]
["COLOUR"]
[]
[null,"Font colour only"]
[null,null,null,null,null,null,null,"Blue font",null,"Imported from another sheet"]
[]
[null,null,null,null,null,null,null,"Red font ",null,"Exported to another sheet (except from Input sheets)"]
[]
[null,null,null,null,null,null,null,"Black font",null,"Within sheet link or calculation"]
[]
[null,"Font + shade combinations"]
[null,null,null,null,null,null,null,"Black font + Light Yellow shade",null,"Inputs"]
[]
[null,null,null,null,null,null,null,"Black font + Light Grey shade on entire row",null,"Within-worksheet counter-flow "]
[]
[null,null,null,null,null,null,null,"Blue font + Light Grey shade on entire row",null,"Between-worksheet counter-flow "]
[]
[null,null,null,null,null,null,null,"Empty Cell with Light Grey shade",null,"Empty cells being deliberately referenced"]
[]
[null,"Other"]
[null,null,null,null,null,null,null,"Black font + Pale Blue shade on entire row",null,"Section separator"]
[]
[null,null,null,null,null,null,null,"Light Turquoise shade",null,"Stored/dead/hard coded outputs"]
[]
[null,null,null,null,null,null,null,"Tan shade",null,"Pre-model time line actuals"]
[]
[null,null,null,null,null,null,null,"Yellow shade",null,"Work in progress / temporary"]
[]
[null,null,null,null,null,null,null,"Lime shade",null,"Values or logic to be reviewed"]
[]
[null,"Error checks & alerts"]
[null,null,null,null,null,null,null,"Green shade",null,"OK"]
[]
[null,null,null,null,null,null,null,"Red shade",null,"Error"]
[]
[null,null,null,null,null,null,null,"Gold shade",null,"Alert"]
[]
[]
["ABBREVIATIONS"]
[]
[null,null,null,null,null,null,null,"£","Great Britain Pound"]
[null,null,null,null,null,null,null,"BR","Bio resources"]
[null,null,null,null,null,null,null,"m","Million"]
[null,null,null,null,null,null,null,"na","Not available / applicable"]
[null,null,null,null,null,null,null,"TDS","Tonnes of dry solid"]
[]
["LIST OF MACROS USED"]
[]
[null,"Store track results"]
[null,null,null,null,null,null,null,"Store track results",null,null,null,"This macro helps in storing the results/outputs after any change is made in the model on \"Track\" sheet."]
[null,null,null,null,null,null,null,null,null,null,null,"These stored results then can be used to analyse the impact of any change made in the model."]
[null,null,null,null,null,null,null,null,null,null,null,"Goto macro"]
[]
[null,"Save as PDF"]
[null,null,null,null,null,null,null,"Save as PDF",null,null,null,"This macro helps in saving any worksheet or chart in PDF format."]
[]
[]
[]
[]
["End of sheet"]

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

{"sheet":"F_Inputs","index":4,"n_rows":20,"n_cols":18,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"FILENAME\", $A$1), LEN(CELL(\"FILENAME\", $A$1)) - SEARCH(\"]\", CELL(\"FILENAME\", $A$1)))","v":"F_Inputs"},null,null,null,null,null,null,"Note: Line items of this data dump must NOT change as they are directly linked in the model"]
[]
[{"$f":" IF(COUNTIF(A6:A15,\"< 0\") + COUNTIF(A6:A15,\">0\") <> 0, 1, 0)","v":0},{"$f":" IF(COUNTIF(B6:B15,\"< 0\") + COUNTIF(B6:B15,\">0\") <> 0, 1, 0)","v":0},null,null,null,null,null,"Financial year ending","year #",null,{"$f":" IFERROR(IF(RIGHT(K6, 2) - MID(K6, SEARCH(\"-\", K6, 1) - 2, 2) = 1, LEFT(K6, 4) + 1, K6), \"na\")","v":2021},2022,2023,2024,2025,"na"]
[null,null,null,null,null,null,null,"[Don't delete row]"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Price base"]
[null,null,"Stored Reference",null,null,"Acronym","Reference","Item description","Unit","Model","2020-21","2021-22","2022-23","2023-24","2024-25"]
[{"$f":" IF(G7 = \"\", 0,  COUNTIF(InpAct!$D$6:$D$89, G7)   - 1)","v":0},{"$f":" IF(C7 = G7, 0, 1)","v":0}]
[{"$f":" IF(G8 = \"\", 0,  COUNTIF(InpAct!$D$6:$D$89, G8)   - 1)","v":0},0,"XXX",null,null,null,"XXX","Standard average revenue control for five-year period","£/TDS",null,null,null,null,null,null,null,null,"2017/18 FYA CPIH deflated"]
[{"$f":" IF(G9 = \"\", 0,  COUNTIF(InpAct!$D$6:$D$89, G9)   - 1)","v":0},0,"YYY",null,null,null,"YYY","Company forecast of sludge produced","TDS",null,null,null,null,null,null,null,null,"Not applicable"]
[{"$f":" IF(G10 = \"\", 0,  COUNTIF(InpAct!$D$6:$D$89, G10)   - 1)","v":0},0,"ZZZ",null,null,null,"ZZZ","Outturn sludge produced in year","TDS",null,null,null,null,null,null,null,null,"Not applicable"]
[{"$f":" IF(G11 = \"\", 0,  COUNTIF(InpAct!$D$6:$D$89, G11)   - 1)","v":0},0]
[{"$f":" IF(G12 = \"\", 0,  COUNTIF(InpAct!$D$6:$D$89, G12)   - 1)","v":0},{"$f":" IF(C12 = G12, 0, 1)","v":0}]
[{"$f":" IF(G13 = \"\", 0,  COUNTIF(InpAct!$D$6:$D$89, G13)   - 1)","v":0},0]
[{"$f":" IF(G14 = \"\", 0,  COUNTIF(InpAct!$D$6:$D$89, G14)   - 1)","v":0},0]
[]
[]
[]
[]
[]
["End of sheet"]

{"sheet":"InpTime","index":5,"n_rows":37,"n_cols":14,"rows_kept":37,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"FILENAME\", $A$1), LEN(CELL(\"FILENAME\", $A$1)) - SEARCH(\"]\", CELL(\"FILENAME\", $A$1)))","v":"InpTime"}]
[null,null,null,null,{"$f":"E27","v":"Model period ending"},null,null,null,null,{"$f":"J27","v":"2021-03-31"},{"$f":"K27","v":"2022-03-31"},"2023-03-31","2024-03-31","2025-03-31"]
[null,null,null,null,{"$f":"E9","v":"Timeline label"},{"$f":"Track!$F$3","v":0},"Track chgs",null,null,{"$f":"J9","v":"Forecast"},{"$f":"K9","v":"Forecast"},"Forecast","Forecast","Forecast"]
[null,null,null,null,{"$f":"E35","v":"Financial year ending"},null,null,null,null,{"$f":"J35","v":2021},{"$f":"K35","v":2022},2023,2024,2025]
[null,null,null,null,{"$f":"E12","v":"Model column counter"},"Constant","Unit","Total",null,{"$f":"J12","v":1},{"$f":"K12","v":2},3,4,5]
[]
["MODEL PERIOD"]
[]
[null,null,null,null,"Timeline label",null,null,null,null,"Forecast","Forecast","Forecast","Forecast","Forecast"]
[]
[null,"Model column counter "]
[null,null,null,null,"Model column counter",null,"counter",null,null,{"$f":" I12 + 1","v":1},2,3,4,5]
[null,null,null,null,"Model column total",{"$f":" MAX(J12:N12)","v":5},"columns"]
[]
[null,null,null,null,{"$f":" E$12","v":"Model column counter"},0,"counter",0,0,1,2,3,4,5]
[null,null,null,null,"1st model column flag",null,"flag",{"$f":" SUM(J16:N16)","v":1},null,{"$f":" IF( J15 = 1, 1, 0)","v":1},0,0,0,0]
[]
[null,"Model period beginning"]
[null,null,null,null,{"$f":"InpAct!E9","v":"1st model column start date"},{"$f":"InpAct!F9","v":"2020-04-01"},{"$f":"InpAct!G9","v":"date"}]
[null,null,null,null,{"$f":"InpAct!E$19","v":"Months per model period"},{"$f":"InpAct!F$19","v":12},{"$f":"InpAct!G$19","v":"months"}]
[null,null,null,null,{"$f":" E$16","v":"1st model column flag"},0,"flag",1,0,1,0,0,0,0]
[null,null,null,null,"Model period beginning",null,"date",null,null,{"$f":" IF(J21 = 1, $F19, DATE(YEAR(I22), MONTH(I22) + $F20, DAY(1)))","v":"2020-04-01"},{"$f":" IF(K21 = 1, $F19, DATE(YEAR(J22), MONTH(J22) + $F20, DAY(1)))","v":"2021-04-01"},{"$f":" IF(L21 = 1, $F19, DATE(YEAR(K22), MONTH(K22) + $F20, DAY(1)))","v":"2022-04-01"},{"$f":" IF(M21 = 1, $F19, DATE(YEAR(L22), MONTH(L22) + $F20, DAY(1)))","v":"2023-04-01"},{"$f":" IF(N21 = 1, $F19, DATE(YEAR(M22), MONTH(M22) + $F20, DAY(1)))","v":"2024-04-01"}]
[]
[null,"Model period ending"]
[null,null,null,null,{"$f":"InpAct!E$19","v":"Months per model period"},{"$f":"InpAct!F$19","v":12},{"$f":"InpAct!G$19","v":"months"}]
[null,null,null,null,{"$f":" E$22","v":"Model period beginning"},"00:00:00","date","00:00:00","00:00:00","2020-04-01","2021-04-01","2022-04-01","2023-04-01","2024-04-01"]
[null,null,null,null,"Model period ending",null,"date",null,null,{"$f":" DATE(YEAR(J26), MONTH(J26) + $F25, DAY(J26) - 1)","v":"2021-03-31"},"2022-03-31","2023-03-31","2024-03-31","2025-03-31"]
[]
["FINANCIAL YEAR"]
[]
[null,null,null,null,{"$f":"InpAct!E11","v":"First modelling column financial year #"},{"$f":"InpAct!F11","v":2021},{"$f":"InpAct!G11","v":"year #"}]
[null,null,null,null,{"$f":"InpAct!E13","v":"Financial year end month number #"},{"$f":"InpAct!F13","v":3},{"$f":"InpAct!G13","v":"month #"}]
[null,null,null,null,{"$f":" E$27","v":"Model period ending"},"00:00:00","date","00:00:00","00:00:00","2021-03-31","2022-03-31","2023-03-31","2024-03-31","2025-03-31"]
[null,null,null,null,{"$f":" E$16","v":"1st model column flag"},{"$f":" F$16","v":0},"flag",1,0,{"$f":" J$16","v":1},0,0,0,0]
[null,null,null,null,"Financial year ending",null,"year #",null,null,{"$f":" IF(J34 = 1, $F31, IF(J33 > (DATE(I35, $F32 + 1, 1) - 1), I35 + 1, I35))","v":2021},2022,{"$f":" IF(L34 = 1, $F31, IF(L33 > (DATE(K35, $F32 + 1, 1) - 1), K35 + 1, K35))","v":2023},{"$f":" IF(M34 = 1, $F31, IF(M33 > (DATE(L35, $F32 + 1, 1) - 1), L35 + 1, L35))","v":2024},{"$f":" IF(N34 = 1, $F31, IF(N33 > (DATE(M35, $F32 + 1, 1) - 1), M35 + 1, M35))","v":2025}]
[]
["End of sheet"]

{"sheet":"InpAct","index":6,"n_rows":41,"n_cols":14,"rows_kept":41,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"FILENAME\", $A$1), LEN(CELL(\"FILENAME\", $A$1)) - SEARCH(\"]\", CELL(\"FILENAME\", $A$1)))","v":"InpAct"}]
[null,null,null,null,{"$f":"InpTime!E2","v":"Model period ending"},null,null,null,null,{"$f":"InpTime!J2","v":"2021-03-31"},{"$f":"InpTime!K2","v":"2022-03-31"},{"$f":"InpTime!L2","v":"2023-03-31"},{"$f":"InpTime!M2","v":"2024-03-31"},{"$f":"InpTime!N2","v":"2025-03-31"}]
[null,null,null,null,{"$f":"InpTime!E3","v":"Timeline label"},{"$f":"Track!$F$3","v":0},"Track chgs",null,null,{"$f":"InpTime!J3","v":"Forecast"},{"$f":"InpTime!K3","v":"Forecast"},{"$f":"InpTime!L3","v":"Forecast"},{"$f":"InpTime!M3","v":"Forecast"},{"$f":"InpTime!N3","v":"Forecast"}]
[null,null,null,null,{"$f":"InpTime!E4","v":"Financial year ending"},null,null,null,null,{"$f":"InpTime!J4","v":2021},{"$f":"InpTime!K4","v":2022},{"$f":"InpTime!L4","v":2023},{"$f":"InpTime!M4","v":2024},{"$f":"InpTime!N4","v":2025}]
[null,null,null,null,{"$f":"InpTime!E5","v":"Model column counter"},"Constant","Unit","Total",null,{"$f":"InpTime!J5","v":1},{"$f":"InpTime!K5","v":2},{"$f":"InpTime!L5","v":3},{"$f":"InpTime!M5","v":4},{"$f":"InpTime!N5","v":5}]
[]
["TIME"]
[]
[null,null,null,null,"1st model column start date","2020-04-01","date","Date should be the first date of the financial year"]
[]
[null,null,null,null,"First modelling column financial year #",2021,"year #"]
[]
[null,null,null,null,"Financial year end month number #",3,"month #"]
[]
[null,null,null,null,"Base financial year","2024-25","year"]
[]
["NON CHANGEABLE MODEL TECHNICAL INPUTS"]
[]
[null,null,null,null,"Months per model period",12,"months"]
[]
[null,null,null,null,"Track Tolerance Level",1E-3,"tolerance"]
[null,null,null,null,"Check Tolerance Level",9.9999999999999995E-8,"tolerance"]
[]
[null,null,null,null,"Thousands in a million",1000,"thousands"]
[]
[null,null,null,null,"Units in a million",1000000,"unit"]
[]
["BIORESOURCES INPUTS"]
[]
[null,null,null,null,"Penalty Rate",0.1,"%"]
[]
[null,null,null,null,"Deadband",0.06,"%"]
[]
[null,null,null,{"$f":"F_Inputs!G8","v":"XXX"},{"$f":"F_Inputs!H8 & \" - \" & F_Inputs!R8","v":"Standard average revenue control for five-year period - 2017/18 FYA CPIH deflated"},{"$f":"F_Inputs!K8","v":0},{"$f":"F_Inputs!I8","v":"£/TDS"},null,0]
[]
[null,null,null,{"$f":"F_Inputs!G9","v":"YYY"},{"$f":"F_Inputs!H9","v":"Company forecast of sludge produced"},0,{"$f":"F_Inputs!I9","v":"TDS"},{"$f":"SUM(J36:N36)","v":0},0,{"$f":"F_Inputs!K9","v":0},{"$f":"F_Inputs!L9","v":0},{"$f":"F_Inputs!M9","v":0},{"$f":"F_Inputs!N9","v":0},{"$f":"F_Inputs!O9","v":0}]
[]
[null,null,null,{"$f":"F_Inputs!G10","v":"ZZZ"},{"$f":"F_Inputs!H10","v":"Outturn sludge produced in year"},0,{"$f":"F_Inputs!I10","v":"TDS"},{"$f":"SUM(J38:N38)","v":0},0,{"$f":"F_Inputs!K10","v":0},{"$f":"F_Inputs!L10","v":0},{"$f":"F_Inputs!M10","v":0},{"$f":"F_Inputs!N10","v":0},{"$f":"F_Inputs!O10","v":0}]
[]
[]
["End of sheet"]

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

{"sheet":"Bioresources","index":8,"n_rows":40,"n_cols":14,"rows_kept":40,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Bioresources"}]
[null,null,null,null,{"$f":"InpTime!E2","v":"Model period ending"},null,null,null,null,{"$f":"InpTime!J2","v":"2021-03-31"},{"$f":"InpTime!K2","v":"2022-03-31"},{"$f":"InpTime!L2","v":"2023-03-31"},{"$f":"InpTime!M2","v":"2024-03-31"},{"$f":"InpTime!N2","v":"2025-03-31"}]
[null,null,null,null,{"$f":"InpTime!E3","v":"Timeline label"},{"$f":"Track!$F$3","v":0},"Track chgs",null,null,{"$f":"InpTime!J3","v":"Forecast"},{"$f":"InpTime!K3","v":"Forecast"},{"$f":"InpTime!L3","v":"Forecast"},{"$f":"InpTime!M3","v":"Forecast"},{"$f":"InpTime!N3","v":"Forecast"}]
[null,null,null,null,{"$f":"InpTime!E4","v":"Financial year ending"},null,null,null,null,{"$f":"InpTime!J4","v":2021},{"$f":"InpTime!K4","v":2022},{"$f":"InpTime!L4","v":2023},{"$f":"InpTime!M4","v":2024},{"$f":"InpTime!N4","v":2025}]
[null,null,null,null,{"$f":"InpTime!E5","v":"Model column counter"},"Constant","Unit","Total",null,{"$f":"InpTime!J5","v":1},{"$f":"InpTime!K5","v":2},{"$f":"InpTime!L5","v":3},{"$f":"InpTime!M5","v":4},{"$f":"InpTime!N5","v":5}]
[]
["BIORESOURCES FAIM CALCULATION"]
[]
[null,"Calculate the absolute forecast error"]
["SLUDGE PRODUCTION FORECASTING ERROR"]
[null,null,null,null,{"$f":"InpAct!E$36","v":"Company forecast of sludge produced"},{"$f":"InpAct!F$36","v":0},{"$f":"InpAct!G$36","v":"TDS"},{"$f":"InpAct!H$36","v":0},{"$f":"InpAct!I$36","v":0},{"$f":"InpAct!J$36","v":0},{"$f":"InpAct!K$36","v":0},{"$f":"InpAct!L$36","v":0},{"$f":"InpAct!M$36","v":0},{"$f":"InpAct!N$36","v":0}]
[null,null,null,null,"5-year total company forecast of sludge produced",{"$f":" SUM(J11:N11)","v":0},"TDS"]
[]
[null,null,null,null,{"$f":"InpAct!E38","v":"Outturn sludge produced in year"},{"$f":"InpAct!F38","v":0},{"$f":"InpAct!G38","v":"TDS"},{"$f":"InpAct!H38","v":0},{"$f":"InpAct!I38","v":0},{"$f":"InpAct!J38","v":0},{"$f":"InpAct!K38","v":0},{"$f":"InpAct!L38","v":0},{"$f":"InpAct!M38","v":0},{"$f":"InpAct!N38","v":0}]
[null,null,null,null,"5-year total outturn sludge produced in year",{"$f":"SUM(J14:N14)","v":0},"TDS"]
[]
[null,null,null,null,{"$f":"E12","v":"5-year total company forecast of sludge produced"},{"$f":"F12","v":0},"TDS"]
[null,null,null,null,{"$f":"E15","v":"5-year total outturn sludge produced in year"},{"$f":"F15","v":0},"TDS"]
[null,null,null,null,"Absolute 5-year Forecast Error",{"$f":"ABS(F18 - F17)","v":0},"TDS"]
[]
[null,null,null,null,{"$f":"E12","v":"5-year total company forecast of sludge produced"},{"$f":"F12","v":0},"TDS"]
[null,null,null,null,{"$f":"E15","v":"5-year total outturn sludge produced in year"},{"$f":"F15","v":0},{"$f":"G15","v":"TDS"}]
[null,null,null,null,"Forecast Error %",{"$f":" IF( F21 = 0, 0, ABS(( F22 - F21 ) / F21 ))","v":0},"%"]
[]
[null,"Calculate the penalty, if forecast error is outside the deadband"]
["CALCULATION OF PENALTY"]
[null,null,null,null,{"$f":"InpAct!E$32","v":"Deadband"},{"$f":"InpAct!F$32","v":0.06},{"$f":"InpAct!G$32","v":"%"}]
[null,null,null,null,{"$f":"E23","v":"Forecast Error %"},{"$f":"F23","v":0},{"$f":"G23","v":"%"}]
[null,null,null,null,"Penalty required ?",{"$f":"IF(F28 >= F27, TRUE, FALSE)","v":false},"Boolean"]
[]
[null,null,null,null,{"$f":"InpAct!E$26","v":"Units in a million"},{"$f":"InpAct!F$26","v":1000000},{"$f":"InpAct!G$26","v":"unit"}]
[null,null,null,null,{"$f":"InpAct!E$30","v":"Penalty Rate"},{"$f":"InpAct!F$30","v":0.1},{"$f":"InpAct!G$30","v":"%"}]
[null,null,null,null,{"$f":"InpAct!E$34","v":"Standard average revenue control for five-year period - 2017/18 FYA CPIH deflated"},{"$f":"InpAct!F$34","v":0},{"$f":"InpAct!G$34","v":"£/TDS"}]
[null,null,null,null,{"$f":"E19","v":"Absolute 5-year Forecast Error"},{"$f":"F19","v":0},{"$f":"G19","v":"TDS"}]
[null,null,null,null,{"$f":"E29","v":"Penalty required ?"},{"$f":"F29","v":false},"Boolean"]
[null,null,null,null,"Bio Resources Forecasting Accuracy Incentive Penalty - 2017/18 FYA CPIH deflated",{"$f":" IF($F35 = FALSE, 0,  $F32 * $F33 * F$34 / $F31)","v":0},"£m"]
[]
[]
[]
["End of sheet"]

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

{"sheet":"F_Outputs","index":10,"n_rows":23,"n_cols":18,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"FILENAME\", $A$1), LEN(CELL(\"FILENAME\", $A$1)) - SEARCH(\"]\", CELL(\"FILENAME\", $A$1)))","v":"F_Outputs"},null,null,null,null,null,null,"Note: Line items of this data upload must NOT change as they are directly linked in the model"]
[]
[null,{"$f":" IF(COUNTIF(B6:B18,\"< 0\") + COUNTIF(B6:B18,\">0\") <> 0, 1, 0)","v":0},null,null,null,null,null,"Financial year ending","year #",null,{"$f":" IFERROR(IF(RIGHT(K6, 2) - MID(K6, SEARCH(\"-\", K6, 1) - 2, 2) = 1, LEFT(K6, 4) + 1, K6), \"na\")","v":2021},2022,2023,2024,2025,"na"]
[null,null,null,null,null,null,null,"[Don't delete row]"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Price base"]
[null,null,"Stored Reference",null,null,"Acronym","Reference","Item description","Unit","Model","2020-21","2021-22","2022-23","2023-24","2024-25"]
[null,{"$f":" IF(C7 = G7, 0, 1)","v":0}]
[null,0,"AAA",null,null,null,"AAA",{"$f":"Bioresources!$E$12","v":"5-year total company forecast of sludge produced"},{"$f":"Bioresources!$G$12","v":"TDS"},{"$f":"Bioresources!$F$12","v":0},null,null,null,null,null,null,null,"Not applicable"]
[null,0,"BBB",null,null,null,"BBB",{"$f":"Bioresources!$E$15","v":"5-year total outturn sludge produced in year"},{"$f":"Bioresources!$G$15","v":"TDS"},{"$f":"Bioresources!$F$15","v":0},null,null,null,null,null,null,null,"Not applicable"]
[null,0,"CCC",null,null,null,"CCC",{"$f":"Bioresources!$E$19","v":"Absolute 5-year Forecast Error"},{"$f":"Bioresources!$G$19","v":"TDS"},{"$f":"Bioresources!$F$19","v":0},null,null,null,null,null,null,null,"Not applicable"]
[null,{"$f":" IF(C11 = G11, 0, 1)","v":0},"DDD",null,null,null,"DDD",{"$f":"Bioresources!$E$23","v":"Forecast Error %"},{"$f":"Bioresources!$G$23","v":"%"},{"$f":"Bioresources!$F$23","v":0},null,null,null,null,null,null,null,"Not applicable"]
[null,0,"EEE",null,null,null,"EEE",{"$f":"Bioresources!$E$35","v":"Penalty required ?"},{"$f":"Bioresources!$G$35","v":"Boolean"},{"$f":"Bioresources!$F$35","v":false},null,null,null,null,null,null,null,"Not applicable"]
[null,0,"FFF",null,null,null,"FFF",{"$f":"Bioresources!$E$36","v":"Bio Resources Forecasting Accuracy Incentive Penalty - 2017/18 FYA CPIH deflated"},{"$f":"Bioresources!$G$36","v":"£m"},{"$f":"Bioresources!$F$36","v":0},null,null,null,null,null,null,null,"2017/18 FYA CPIH deflated"]
[null,0]
[null,0]
[null,0]
[null,0]
[]
[]
[]
[]
[]
["End of sheet"]

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

{"sheet":"Track","index":12,"n_rows":31,"n_cols":15,"rows_kept":31,"cut":false,"hidden":false,"merged":[]}
[{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Track"},null,null,null,null,null,null,null,null,null,null,null,"do not del col",null,"do not del col"]
[null,null,null,null,null,null,null,null,null,{"$f":" SUM(J7:J21)","v":0}]
[null,null,null,null,null,{"$f":"J2","v":0},"Track chgs"]
[]
[null,null,null,null,null,null,"Unit",null,null,{"$f":" MATCH($H$6, M6:O6, 0)","v":2},"Difference","Diff %"]
[null,null,null,null,"Comparison column label",null,null,"Nil inputs",null,null,null,null,null,"Nil inputs"]
[null,null,null,null,"[do not delete row]"]
["INPUTS"]
[null,null,null,null,null,null,null,null,null,{"$f":" IF(ABS(K9) > InpAct!$F$21, 1, 0)","v":0},{"$f":" H9 - INDEX(M9:O9, $J$5)","v":0},{"$f":" IF(ABS(K9) > InpAct!$F$21, IF(INDEX(M9:O9, $J$5) = 0, 0, H9 / INDEX(M9:O9, $J$5) - 1), 0)","v":0},null,0]
[null,null,"Inputs",null,null,null,null,null,null,{"$f":" IF(ABS(K10) > InpAct!$F$21, 1, 0)","v":0},0,{"$f":" IF(ABS(K10) > InpAct!$F$21, IF(INDEX(M10:O10, $J$5) = 0, 0, H10 / INDEX(M10:O10, $J$5) - 1), 0)","v":0},null,0]
[null,null,null,null,{"$f":"Bioresources!E11","v":"Company forecast of sludge produced"},{"$f":"Bioresources!F11","v":0},{"$f":"Bioresources!G11","v":"TDS"},{"$f":"Bioresources!H11","v":0},null,{"$f":" IF(ABS(K11) > InpAct!$F$21, 1, 0)","v":0},0,{"$f":" IF(ABS(K11) > InpAct!$F$21, IF(INDEX(M11:O11, $J$5) = 0, 0, H11 / INDEX(M11:O11, $J$5) - 1), 0)","v":0},null,0]
[null,null,null,null,{"$f":"Bioresources!E14","v":"Outturn sludge produced in year"},{"$f":"Bioresources!F14","v":0},{"$f":"Bioresources!G14","v":"TDS"},{"$f":"Bioresources!H14","v":0},null,{"$f":" IF(ABS(K12) > InpAct!$F$21, 1, 0)","v":0},0,{"$f":" IF(ABS(K12) > InpAct!$F$21, IF(INDEX(M12:O12, $J$5) = 0, 0, H12 / INDEX(M12:O12, $J$5) - 1), 0)","v":0},null,0]
[null,null,null,null,{"$f":"Bioresources!E33","v":"Standard average revenue control for five-year period - 2017/18 FYA CPIH deflated"},0,{"$f":"Bioresources!G33","v":"£/TDS"},{"$f":"Bioresources!F33","v":0},null,{"$f":" IF(ABS(K13) > InpAct!$F$21, 1, 0)","v":0},0,{"$f":" IF(ABS(K13) > InpAct!$F$21, IF(INDEX(M13:O13, $J$5) = 0, 0, H13 / INDEX(M13:O13, $J$5) - 1), 0)","v":0},null,0]
[null,null,null,null,null,null,null,null,null,{"$f":" IF(ABS(K14) > InpAct!$F$21, 1, 0)","v":0},0,{"$f":" IF(ABS(K14) > InpAct!$F$21, IF(INDEX(M14:O14, $J$5) = 0, 0, H14 / INDEX(M14:O14, $J$5) - 1), 0)","v":0},null,0]
[null,null,"Outputs",null,null,null,null,null,null,{"$f":" IF(ABS(K15) > InpAct!$F$21, 1, 0)","v":0},0,{"$f":" IF(ABS(K15) > InpAct!$F$21, IF(INDEX(M15:O15, $J$5) = 0, 0, H15 / INDEX(M15:O15, $J$5) - 1), 0)","v":0},null,0]
[null,null,null,null,{"$f":"Bioresources!E19","v":"Absolute 5-year Forecast Error"},0,{"$f":"Bioresources!G19","v":"TDS"},{"$f":"Bioresources!F19","v":0},null,{"$f":" IF(ABS(K16) > InpAct!$F$21, 1, 0)","v":0},0,{"$f":" IF(ABS(K16) > InpAct!$F$21, IF(INDEX(M16:O16, $J$5) = 0, 0, H16 / INDEX(M16:O16, $J$5) - 1), 0)","v":0},null,0]
[null,null,null,null,{"$f":"Bioresources!E23","v":"Forecast Error %"},0,{"$f":"Bioresources!G23","v":"%"},{"$f":"Bioresources!F23","v":0},null,{"$f":" IF(ABS(K17) > InpAct!$F$21, 1, 0)","v":0},0,{"$f":" IF(ABS(K17) > InpAct!$F$21, IF(INDEX(M17:O17, $J$5) = 0, 0, H17 / INDEX(M17:O17, $J$5) - 1), 0)","v":0},null,0]
[null,null,null,null,{"$f":"Bioresources!E35","v":"Penalty required ?"},0,{"$f":"Bioresources!G35","v":"Boolean"},{"$f":"Bioresources!F35","v":false},null,{"$f":" IF(ABS(K18) > InpAct!$F$21, 1, 0)","v":0},0,{"$f":" IF(ABS(K18) > InpAct!$F$21, IF(INDEX(M18:O18, $J$5) = 0, 0, H18 / INDEX(M18:O18, $J$5) - 1), 0)","v":0},null,0]
[null,null,null,null,{"$f":"Bioresources!E36","v":"Bio Resources Forecasting Accuracy Incentive Penalty - 2017/18 FYA CPIH deflated"},0,{"$f":"Bioresources!G36","v":"£m"},{"$f":"Bioresources!F36","v":0},null,{"$f":" IF(ABS(K19) > InpAct!$F$21, 1, 0)","v":0},0,{"$f":" IF(ABS(K19) > InpAct!$F$21, IF(INDEX(M19:O19, $J$5) = 0, 0, H19 / INDEX(M19:O19, $J$5) - 1), 0)","v":0},null,0]
[null,null,null,null,null,null,null,null,null,{"$f":" IF(ABS(K20) > InpAct!$F$21, 1, 0)","v":0},0,{"$f":" IF(ABS(K20) > InpAct!$F$21, IF(INDEX(M20:O20, $J$5) = 0, 0, H20 / INDEX(M20:O20, $J$5) - 1), 0)","v":0},null,0]
["INFO ONLY"]
[]
[null,null,null,null,"Date",null,null,{"$f":" TODAY()","v":"2018-06-06"},null,null,null,null,null,"2018-04-04"]
[null,null,null,null,"Time",null,null,{"$f":" NOW()","v":43257.531840509262},null,null,null,null,null,43166.595949074072]
[null,null,null,null,"File",null,null,{"$f":" MID(CELL(\"filename\",$A$1), SEARCH(\"[\", CELL(\"filename\",$A$1)) + 1, SEARCH(\"]\", CELL(\"filename\",$A$1)) - SEARCH(\"[\", CELL(\"filename\",$A$1)) - 1)","v":"Bioresources FAIM v1a.xlsm"},null,null,null,null,null,"Model_v0.0.xlsm"]
[null,null,null,null,"[do not delete row]",null,null,null,null,"range end"]
[]
[null,null,null,null,"Comment: Key input and logic changes",null,null,null,null,null,null,null,null,"Nil inputs"]
[null,null,null,null,"Comment: Key result changes"]
[]
["End of sheet"]