{"text_format":"1","workbook":"xlsx","n_sheets":27,"sheets":["Cover Sheet","Title page","About","Inputs>>","Inputs >>>","Inputs","I- XXX (2)","Calcs>>","Calculations >>>","Calculations","Outputs >>>","Key outputs","Appraisal","Costs & Benefits Summary","Data >>>","Maximum Charges","Deflator","Expected Change","Income uplift for cancelled ","HIDE >>>","Assumption for SU promoter cost","Drop downs","Outputs>>","Charts>>","Chart Format Instructions","Template sheet>>","X- XXX"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":2,"defined_names":20,"comments":1,"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 Sheet","index":0,"n_rows":52,"n_cols":13,"rows_kept":52,"cut":false,"hidden":true,"merged":[[10,7,10,8],[9,7,9,8],[11,7,11,8]]}
[]
[null,"File Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"[\",CELL(\"filename\",G2))+1,FIND(\"]\",CELL(\"filename\",G2))-FIND(\"[\",CELL(\"filename\",G2))-1),\"Please save the file to show here\")","v":"Herefordshire CBA.xlsm"}]
[null,"Sheet Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"]\",CELL(\"filename\",G2))+1,255),\"Please save the file to show here\")","v":"Cover Sheet"}]
[null,"Description and Purpose of Model:",null,null,null,null,{"$f":"H11","v":"Example Mode"}]
[null,"Version number",null,null,null,null,{"$f":"Version_number","v":0.03}]
[]
[]
[null,"Workbook Properties"]
[]
[null,null,null,null,null,null,"File Name",{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"[\",CELL(\"filename\",G2))+1,FIND(\"]\",CELL(\"filename\",G2))-FIND(\"[\",CELL(\"filename\",G2))-1),\"Please save the file to show here\")","v":"Herefordshire CBA.xlsm"}]
[null,null,null,null,null,null,"Description and Purpose of Model:","Example Mode"]
[null,null,null,null,null,null,"Version number",{"$f":"MAX(G19:G29)","v":0.03}]
[]
[]
[null,"Version Control"]
[]
[]
[null,null,null,null,null,null,"Version","Filename","Description","Changes since last version","Date Created","Analyst","QA status"]
[null,null,null,null,null,null,0.01,{"$f":"IF(G19=\"\",\"\",IFERROR(MID(CELL(\"filename\",G2),FIND(\"[\",CELL(\"filename\",G2))+1,FIND(\"]\",CELL(\"filename\",G2))-FIND(\"[\",CELL(\"filename\",G2))-1),\"Please save the file to show here\"))","v":"Herefordshire CBA.xlsm"},"Initial Development Version","N/A","2017-10-27","XXX","Not yet reviewed"]
[null,null,null,null,null,null,0.02,{"$f":"IF(G20=\"\",\"\",IFERROR(MID(CELL(\"filename\",G3),FIND(\"[\",CELL(\"filename\",G3))+1,FIND(\"]\",CELL(\"filename\",G3))-FIND(\"[\",CELL(\"filename\",G3))-1),\"Please save the file to show here\"))","v":"Herefordshire CBA.xlsm"},"Final Stage Prep","More user friendly\nAdapting for LA use\nChanging title page, about page, inputs and outputs ","2017-10-24","Louise Baker ","Not yet reviewed"]
[null,null,null,null,null,null,0.03,{"$f":"IF(G21=\"\",\"\",IFERROR(MID(CELL(\"filename\",G4),FIND(\"[\",CELL(\"filename\",G4))+1,FIND(\"]\",CELL(\"filename\",G4))-FIND(\"[\",CELL(\"filename\",G4))-1),\"Please save the file to show here\"))","v":"Herefordshire CBA.xlsm"},"Final Stage Calc for Review","Updates from LS QA","2018-11-08","Louise Baker ","Reviewed by LS"]
[null,null,null,null,null,null,null,{"$f":"IF(G22=\"\",\"\",IFERROR(MID(CELL(\"filename\",G5),FIND(\"[\",CELL(\"filename\",G5))+1,FIND(\"]\",CELL(\"filename\",G5))-FIND(\"[\",CELL(\"filename\",G5))-1),\"Please save the file to show here\"))","v":""}]
[null,null,null,null,null,null,null,{"$f":"IF(G23=\"\",\"\",IFERROR(MID(CELL(\"filename\",#REF!),FIND(\"[\",CELL(\"filename\",#REF!))+1,FIND(\"]\",CELL(\"filename\",#REF!))-FIND(\"[\",CELL(\"filename\",#REF!))-1),\"Please save the file to show here\"))","v":""}]
[null,null,null,null,null,null,null,{"$f":"IF(G24=\"\",\"\",IFERROR(MID(CELL(\"filename\",G6),FIND(\"[\",CELL(\"filename\",G6))+1,FIND(\"]\",CELL(\"filename\",G6))-FIND(\"[\",CELL(\"filename\",G6))-1),\"Please save the file to show here\"))","v":""}]
[null,null,null,null,null,null,null,""]
[null,null,null,null,null,null,null,""]
[null,null,null,null,null,null,null,""]
[null,null,null,null,null,null,null,""]
[]
[]
[null,"Legend"]
[]
[]
[null,null,null,null,null,null,"Banner 1: Properties and Section Breaks"]
[null,null,null,null,null,null,"Banner 2: Headers, Timeline and Key Labels"]
[null,null,null,null,null,null,"Input (Raw Data)"]
[null,null,null,null,null,null,"Input (Assumption)"]
[null,null,null,null,null,null,"Calculation"]
[null,null,null,null,null,null,"Carried through data"]
[null,null,null,null,null,null,"Output"]
[null,null,null,null,null,null,"ERROR/OK, FALSE/TRUE (manual or with Conditional Formatting)"]
[null,null,null,null,null,null,"Table heading"]
[]
[]
[null,"Table of Contents"]
[]
[]
[null,"Table of Contents"]
[]
[null,"I- XXX",null,null,null,null,"user input"]
[null,"C- XXX",null,null,null,null,"user input"]
[null,"O- XXX",null,null,null,null,"user input"]

{"sheet":"Title page","index":1,"n_rows":36,"n_cols":7,"rows_kept":36,"cut":false,"hidden":false,"merged":[[1,1,17,20],[18,3,21,9],[23,3,24,4]]}
[]
[null,"Local Authority Permit Calculator"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,"Contents"]
[]
[]
[]
[]
[null,null,null,"About "]
[null,null,null,null,null,null,"How to use the calculator "]
[null,null,null,null,null,null,"Assumptions "]
[null,null,null,null,null,null,"Costs and benefits "]
[null,null,null,null,null,null,"Net Present Value "]
[]
[null,null,null,"Input sheet "]
[null,null,null,null,null,null,"Inputs required"]
[]
[null,null,null,"Output sheet "]
[null,null,null,null,null,null,"Key inputs and assumptions "]
[null,null,null,null,null,null,"Key outputs "]
[null,null,null,null,null,null,"Appraisal outputs"]

{"sheet":"About","index":2,"n_rows":75,"n_cols":6,"rows_kept":75,"cut":false,"hidden":false,"merged":[[50,3,50,4],[53,3,53,4],[63,3,63,4],[34,3,34,4],[35,3,35,4],[1,3,1,5],[44,3,44,4],[30,3,30,4],[31,3,31,4],[32,3,32,4],[33,3,33,4],[11,3,11,5],[15,5,16,6],[19,5,20,6],[23,5,24,6],[29,3,29,4],[3,3,6,5]]}
[]
[null,null,null,"Using the Permit Scheme Calculator "]
[]
[null,null,null,"This calculator is for local authorities to assess the costs and benefits of using a permit fee scheme. \nOutputs from the calculator should be used as a guide for local authorities to assess the feasibility of a permit scheme. Outputs may also be used in a business case to support the local authority bid for a permit scheme."]
[]
[]
[]
[null,null,null,"Please read the guidance note and instructions below before using the calculator."]
[]
[]
[null,null,null,"How to use the calculator "]
[null,null,null,"On the 'Inputs' sheet, please input the requested data. This will be used to calculate a range of benefits and costs affiliated with the permit scheme. These are then displayed on the 'Key outputs' sheet."]
[]
[]
[null,null,null,"Inputs "]
[null,null,null,"Input the requested data into the blue highlighted cells ",null,"Inputs"]
[]
[]
[null,null,null,"Calculations "]
[null,null,null,"Using the inputs provided, the model will calculate how the permit scheme will impact the local authority.",null,"Calculations "]
[]
[]
[null,null,null,"Outputs "]
[null,null,null,"Key outputs are displayed on this sheet. ",null,"Outputs"]
[null,null,null,"Please use this sheet to assess the costs and benefits of the permit fee scheme in the local authority "]
[]
[]
[]
[null,null,null,"Assumptions"]
[null,null,null,"The outputs of the calculator are subject to a number of assumptions.\nThe assumptions are made to enable analysis in the abscence of data and in interests of proportionality. \nThis means that the outputs of the calculator should be treated as suggestive estimates, and should not be construed decisive statements about the performance of the scheme in your local authority."]
[]
[null,null,null,"Behavioural assumptions - reduction in the duration of works (measured in work days)"]
[null,null,null,"The default behavioural assumptions are sourced from the Evaluation of Street Works Permit Schemes Report 2018."]
[]
[null,null,null,"Cost assumptions - additional costs for works promoters"]
[null,null,null,"The cost assumptions are sourced from the Evaluation of Street Works Permit Schemes Report 2018. The calculator assumes that works promoters will face additional administration costs from using the permit scheme that are equal to 20% of local highway authority costs. "]
[]
[]
[null,null,null,"Costs"]
[]
[null,null,null,"Set-up costs for local authorities "]
[null,null,null,"Local authorities will incur a one time set-up cost in the first year of the scheme."]
[]
[null,null,null,"Running costs for local authorities"]
[null,null,null,"Local authorities will incur annual running costs. These costs occur as local authorities provide permits for works undertaken by both statutory undertakers and local authority works promoters.\nAnnual running costs in relation to statutory undertaker's works are recovered through the permit fee revenue."]
[null,null,null,"Permit fees"]
[null,null,null,"Statutory undertakers will pay a fee to apply for a works permit. Local authority works promoters will apply for a permit but do not pay a permit fee."]
[]
[null,null,null,"Benefits"]
[null,null,null,"Permit fees"]
[null,null,null,"Local authorities receive their income through permit fees from statutory undertakers. The fees received will cover the annual running costs of the permit scheme in relation to statutory undertaker's works. "]
[]
[null,null,null,"A reduction in the duration of works leads to lower congestion costs to road users"]
[null,null,null,"The calculator will estimate the reduction in the duration of works per year for the local authority.\nThe calculator then monetises the effects in terms of:"]
[null,null,null,"- Reduced journey times "]
[null,null,null,"- More reliable journeys"]
[null,null,null,"- Reduction in accidents "]
[null,null,null,"- Reduction in carbon emissions and fuel consumption"]
[]
[]
[null,null,null,"Net Present Value"]
[null,null,null,"The calculator will then calculate the total costs and benefits over the full appraisal period. "]
[null,null,null,{"$f":"Calculations!$C$205","v":"The appraisal covers the period of 10 years from 2019 to 2028, with a 2019 discount base.\nPlease refer to the guidance note for more information on appraisal."}]
[null,null,null,"The Net Present Value (NPV) shows the difference between the value of the Net Present Benefits and the Net Present Costs. This indicates the value for money that a permit fee scheme provides."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,"End of instructions"]

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

{"sheet":"Inputs >>>","index":4,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[[9,1,22,13]]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Inputs "]

{"sheet":"Inputs","index":5,"n_rows":131,"n_cols":15,"rows_kept":131,"cut":false,"hidden":false,"merged":[[122,11,122,13],[114,4,117,13],[101,3,105,5],[89,3,90,5],[92,11,92,13],[100,11,100,13],[60,3,61,5],[107,3,109,5],[76,3,78,5],[76,11,78,13],[107,11,109,13],[7,4,10,14],[12,3,12,14],[70,3,71,5],[83,3,84,5],[93,3,97,5],[16,3,16,5],[20,3,24,5],[36,3,40,5],[27,3,31,5],[47,3,48,5],[54,3,55,5],[67,11,67,13],[57,11,57,13],[14,3,14,5],[14,11,14,14],[50,12,51,13]]}
[]
[null,null,null,"File Name",null,null,null,null,null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",M2),FIND(\"[\",CELL(\"filename\",M2))+1,FIND(\"]\",CELL(\"filename\",M2))-FIND(\"[\",CELL(\"filename\",M2))-1),\"Please save the file to show here\")","v":"Herefordshire CBA.xlsm"}]
[null,null,null,"Sheet Name",null,null,null,null,null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",M2),FIND(\"]\",CELL(\"filename\",M2))+1,255),\"Please save the file to show here\")","v":"Inputs"}]
[null,null,null,"Description and Purpose of Sheet",null,null,null,null,null,null,null,null,"user input"]
[null,null,null,"Version number",null,null,null,null,null,null,null,null,{"$f":"Version_number","v":0.03}]
[]
[]
[null,null,null,null,"Inputs"]
[]
[]
[]
[]
[null,null,null,"Please follow the instructions below and enter the inputs into the blue highlighted cells. \nWhen complete, click the 'Go to outputs' button at the bottom of the sheet. "]
[]
[null,null,null,"Instructions",null,null,null,null,null,null,null,"Inputs"]
[]
[null,null,null,"Enter the name of the local authority ",null,null,null,null,null,null,null,"Name of the local authority:","Herefordshire Council"]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Total number of street works"]
[null,null,null,null,null,null,null,null,null,null,null,"Current number of street works per year "]
[null,null,null,"Enter the current number of street works that take place, per year, in the local authority.\nWorks should be broken down by works type - major, standard, minor, immediate\nWorks should be broken down by works promoter - works undertaken by Statutory Undertakers (Utility Companies) or by local authorities",null,null,null,null,null,null,null,null,"Statutory Undertaker ","Local Authority"]
[null,null,null,null,null,null,null,null,null,null,null,"Major",370,304]
[null,null,null,null,null,null,null,null,null,null,null,"Standard",684,113]
[null,null,null,null,null,null,null,null,null,null,null,"Minor",2263,430]
[null,null,null,null,null,null,null,null,null,null,null,"Immediate",1432,1047]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Current number of work days per year "]
[null,null,null,"Enter the total number of days taken to complete works\nThis should be broken down by works type and works promoter",null,null,null,null,null,null,null,null,"Statutory Undertaker ","Local Authority"]
[null,null,null,null,null,null,null,null,null,null,null,"Major",11470,8512]
[null,null,null,null,null,null,null,null,null,null,null,"Standard",6430,1186]
[null,null,null,null,null,null,null,null,null,null,null,"Minor",4073,857]
[null,null,null,null,null,null,null,null,null,null,null,"Immediate",2724,2228]
[]
[null,null,null,null,null,null,null,null,null,null,null,"0-2 category roads"]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Percentage of works completed on 0-2 category roads"]
[null,null,null,"Enter the percentage of all works that are completed on 0-2 category roads\nThis should be broken down by works type and works promoter",null,null,null,null,null,null,null,null,"Statutory Undertaker ","Local Authority"]
[null,null,null,null,null,null,null,null,null,null,null,"Major",0.25,0.28000000000000003]
[null,null,null,null,null,null,null,null,null,null,null,"Standard",0.22,0.25]
[null,null,null,null,null,null,null,null,null,null,null,"Minor",0.2,0.24]
[null,null,null,null,null,null,null,null,null,null,null,"Immediate",0.24,0.2]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Major Works"]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Proportion of major works completed by the number of days:"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Number of works:"]
[null,null,null,null,null,null,null,null,null,null,null,"Major works - over 10 days or traffic regulation order required",0.92,{"$f":"IF(OR($M$47=\"\", SUM($M$22:$N$22)=0), \"\", $M$47*$N$50)","v":620.08000000000004}]
[null,null,null,"Enter the proportion of major works completed by each time period.\nUse the number of works column to check the proportions are consistent with the breakdown of major works. ",null,null,null,null,null,null,null,"Major works - 4 to 10 days and no traffic regulation order required",0.03,{"$f":"IF(OR($M$48=\"\", SUM($M$22:$N$22)=0), \"\", $M$48*$N$50)","v":20.22}]
[null,null,null,null,null,null,null,null,null,null,null,"Major works - up to 3 days and no traffic regulation order required",0.05,{"$f":"IF(OR($M$49=\"\", SUM($M$22:$N$22=0)), \"\", $M$49*$N$50)","v":33.700000000000003}]
[null,null,null,null,null,null,null,null,null,null,null,"Total:   ",{"$f":"IF(SUM(M47:M49)=0,\"\",SUM(M47:M49))","v":1},{"$f":"IF($M$22+$N$22=0, \"\",$M$22+$N$22)","v":674}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF($M$50<>1,\"The proportional split of major works do not sum to 100%, please correct before proceeding.\",\"\")","v":""}]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Permit Fee Structure"]
[]
[null,null,null,"Select the preferred permit fee structure",null,null,null,null,null,null,null,"Preferred permit fee structure","Maximum"]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Maximum Permit Fee Structure"]
[null,null,null,null,null,null,null,null,null,null,null,null,"Road Category 0-2 or traffic sensitive road","Road Category 3-4 or non traffic sensitive road"]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"L70","v":"Major works - over 10 days or traffic regulation order required"},{"$f":"'Maximum Charges'!D8","v":240},{"$f":"'Maximum Charges'!E8","v":150}]
[null,null,null,"It is recommended to use the maximum permit fee structure, unless there is strong evidence to use the custom permit fee structure. \nThe maximum permit fee structure is sourced from the Statutory Guidance for Highway Authority Permit Schemes 2015. ",null,null,null,null,null,null,null,"Major works - 4 to 10 days and no traffic regulation order required",{"$f":"'Maximum Charges'!D9","v":130},{"$f":"'Maximum Charges'!E9","v":75}]
[null,null,null,null,null,null,null,null,null,null,null,"Major works - up to 3 days and no traffic regulation order required",{"$f":"'Maximum Charges'!D10","v":65},{"$f":"'Maximum Charges'!E10","v":45}]
[null,null,null,null,null,null,null,null,null,null,null,"Standard works",{"$f":"'Maximum Charges'!D11","v":130},{"$f":"'Maximum Charges'!E11","v":75}]
[null,null,null,null,null,null,null,null,null,null,null,"Minor works",{"$f":"'Maximum Charges'!D12","v":65},{"$f":"'Maximum Charges'!E12","v":45}]
[null,null,null,null,null,null,null,null,null,null,null,"Immediate works",{"$f":"'Maximum Charges'!D13","v":60},{"$f":"'Maximum Charges'!E13","v":40}]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Custom Permit Fee Structure"]
[null,null,null,null,null,null,null,null,null,null,null,null,"Road Category 0-2 or Traffic-sensitive","Road Category 3-4 and non traffic sensitive"]
[null,null,null,null,null,null,null,null,null,null,null,"Major works - over 10 days or traffic regulation order required"]
[null,null,null,"If a custom permit fee structure is preferred, please enter the fee per work split by works type and road type.",null,null,null,null,null,null,null,"Major works - 4 to 10 days and no traffic regulation order required"]
[null,null,null,null,null,null,null,null,null,null,null,"Major works - up to 3 days and no traffic regulation order required"]
[null,null,null,null,null,null,null,null,null,null,null,"Standard works"]
[null,null,null,null,null,null,null,null,null,null,null,"Minor works"]
[null,null,null,null,null,null,null,null,null,null,null,"Immediate works"]
[]
[null,null,null,"Please provide a short statement of supporting evidence for the custom permit fee structure. "]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Local Authority costs"]
[]
[null,null,null,"Enter the expected set-up cost for the scheme. This includes the cost for a consultant if required. The cash (nominal) price base year is 2019. ",null,null,null,null,null,null,null,"Set-up costs (first year only):",30000,null,"."]
[null,null,null,null,null,null,null,null,null,null,null,"Price base year:",2019]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Behavioural change"]
[]
[null,null,null,"Select the expected behavioural change in the duration of works due to the permit scheme.",null,null,null,null,null,null,null,"Behavioural change in the duration of works:","Default "]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Default behavioural change "]
[null,null,null,"It is recommended to use the default behavioural change unless there is strong evidence to use the custom behavioural change inputs",null,null,null,null,null,null,null,null,"Statutory Undertaker","Local Authority"]
[null,null,null,null,null,null,null,null,null,null,null,"Major",{"$f":"'Expected Change'!D13","v":-0.13900000000000001},{"$f":"'Expected Change'!D8","v":-6.5000000000000002E-2}]
[null,null,null,null,null,null,null,null,null,null,null,"Standard",{"$f":"'Expected Change'!D14","v":-1.6E-2},{"$f":"'Expected Change'!D9","v":-7.6999999999999999E-2}]
[null,null,null,null,null,null,null,null,null,null,null,"Minor",{"$f":"'Expected Change'!D15","v":8.1000000000000003E-2},{"$f":"'Expected Change'!D10","v":-8.8999999999999996E-2}]
[null,null,null,null,null,null,null,null,null,null,null,"Immediate",{"$f":"'Expected Change'!D16","v":0.03},{"$f":"'Expected Change'!D11","v":0}]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Custom behavioural change "]
[null,null,null,"If a custom behavioural change is expected, enter the percentage change in the duration of works expected due to the permit scheme.\n",null,null,null,null,null,null,null,null,"Statutory Undertaker","Local Authority"]
[null,null,null,null,null,null,null,null,null,null,null,"Major"]
[null,null,null,null,null,null,null,null,null,null,null,"Standard"]
[null,null,null,null,null,null,null,null,null,null,null,"Minor"]
[null,null,null,null,null,null,null,null,null,null,null,"Immediate"]
[]
[null,null,null,"Please provide a short statement of supporting evidence for the custom behavioural change of the duration of works"]
[]
[]
[]
[]
[]
[]
[null,null,null,null,"Go to outputs >>"]
[]
[]
[]
[]
[]
[]
[null,null,null,null,"To hide:","QUADRO outputs",null,null,null,null,null,"Source:"]
[null,null,null,null,null,null,null,null,null,null,null,"Kent QUADRO outputs from Lane Rental IA - including reliability uplift of 10%"]
[null,null,null,null,"Consumer ",0.4412665663126204]
[null,null,null,null,"Business",0.50227901933745278]
[null,null,null,null,"Fuel carbon emissions",4.7701506363358841E-2]
[null,null,null,null,"Accident",8.7529079865679964E-3]
[]
[]
[]
[null,null,null,"END"]

{"sheet":"I- XXX (2)","index":6,"n_rows":35,"n_cols":7,"rows_kept":35,"cut":false,"hidden":true,"merged":[]}
[]
[null,"File Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"[\",CELL(\"filename\",G2))+1,FIND(\"]\",CELL(\"filename\",G2))-FIND(\"[\",CELL(\"filename\",G2))-1),\"Please save the file to show here\")","v":"Herefordshire CBA.xlsm"}]
[null,"Sheet Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"]\",CELL(\"filename\",G2))+1,255),\"Please save the file to show here\")","v":"I- XXX (2)"}]
[null,"Description and Purpose of Sheet",null,null,null,null,"user input"]
[null,"Version number",null,null,null,null,{"$f":"Version_number","v":0.03}]
[]
[]
[null,"Header 1"]
[]
[null,null,"Permit fee structure"]
[]
[null,null,null,"Road Category 0-2 or Traffic-sensitive","Road Category 3-4 and non trafficsensitive"]
[null,null,"Provisional Advance "]
[null,null,"Major works – over 10 days and/or requiring a traffic regulation order."]
[null,null,"Major works – 4 to 10 days and not requiring a traffic regulation order."]
[null,null,"Major works – up to 3 days and/or requiring a traffic regulation order."]
[null,null,"Activity Standard"]
[null,null,"Activity Minor"]
[null,null,"Immediate Activity"]
[null,null,"Permit Variation"]
[]
[]
[null,null,"Distribution of major works"]
[null,null,null,"Number of Works","Percentage"]
[null,null,"Over 10 days and/or traffic regulation order:",10,{"$f":"D25/(D$25+D$26+D$27)","v":0.33333333333333331}]
[null,null,"4 to 10 days and no traffic regulation order:",10,{"$f":"D26/(D$25+D$26+D$27)","v":0.33333333333333331}]
[null,null,"1 to 4 days and no traffic regulation order:",10,{"$f":"D27/(D$25+D$26+D$27)","v":0.33333333333333331}]
[]
[]
[null,null,"Expected behaviour change"]
[]
[]
[null,null,"Percentage of works moving to off-peak:"]
[]
[null,"END"]

{"sheet":"Calcs>>","index":7,"n_rows":11,"n_cols":3,"rows_kept":11,"cut":false,"hidden":true,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Calculations"]

{"sheet":"Calculations >>>","index":8,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":true,"merged":[[9,1,22,13]]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Calculations"]

{"sheet":"Calculations","index":9,"n_rows":213,"n_cols":12,"rows_kept":213,"cut":false,"hidden":true,"merged":[[206,5,208,7],[81,5,83,7],[195,9,196,11],[70,10,70,11],[49,9,50,9],[51,9,52,9],[53,9,54,9],[49,7,50,8],[51,7,52,8],[53,7,54,8],[77,10,77,11],[144,7,144,8],[144,9,144,10],[159,5,159,6],[159,3,159,4],[175,3,175,4],[175,5,175,6],[198,2,199,2],[198,3,199,4],[198,5,199,6],[200,2,201,2],[200,3,201,4],[200,5,201,6],[186,3,186,4],[186,5,186,6],[81,2,83,4],[144,6,145,6],[117,5,117,6],[22,2,22,3],[13,2,13,3],[78,2,78,3],[85,2,85,4],[26,6,31,8],[87,2,88,4],[79,2,79,3]]}
[]
[null,"File Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"[\",CELL(\"filename\",G2))+1,FIND(\"]\",CELL(\"filename\",G2))-FIND(\"[\",CELL(\"filename\",G2))-1),\"Please save the file to show here\")","v":"Herefordshire CBA.xlsm"}]
[null,"Sheet Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"]\",CELL(\"filename\",G2))+1,255),\"Please save the file to show here\")","v":"Calculations"}]
[null,"Description and Purpose of Sheet",null,null,null,null,"user input"]
[null,"Version number",null,null,null,null,{"$f":"Version_number","v":0.03}]
[]
[]
[null,"Scenario"]
[]
[null,null,"Name of Local Authority ",null,{"$f":"IF(Inputs!$M$17=\"\",\"\",Inputs!$M$17)","v":"Herefordshire Council"}]
[]
[null,null,"Preferred Permit Fee Structure ",null,{"$f":"Inputs!M55","v":"Maximum"}]
[]
[null,null,"Behavioural change in the number of workdays",null,{"$f":"Inputs!$M$90","v":"Default "}]
[]
[null,"Reduction in workdays "]
[]
[null,null,"Total number of works ",{"$f":"SUM(Inputs!$M$22:$N$25)","v":6643},null,"The number of works that take place in the local authority will not change. \nThe permit fee scheme will only impact the time taken to complete these works. \nThe total duration of all works is measured in work days and is shown below. "]
[]
[]
[]
[]
[null,null,"Behavioural change in the number of workdays:",null,{"$f":"Inputs!$M$90","v":"Default "}]
[]
[null,null,"Behavioural change in the reduction of workdays "]
[]
[null,null,null,"Statutory undertaker (Utility)","Local  Authority",null,{"$f":"IF(OR(D28>0, D29>0, D30>0, D31>0,E28>0, E29>0, E30>0, E31>0), \"The duration of works will increase for one or more of the categories in this table. Please refer to the guidance note for more information\", \"\")","v":"The duration of works will increase for one or more of the categories in this table. Please refer to the guidance note for more information"}]
[null,null,"Major",{"$f":"IF($E$23='Drop downs'!$B$10, Inputs!M95, Inputs!M103)","v":-0.13900000000000001},{"$f":"IF($E$23='Drop downs'!$B$10, Inputs!N95, Inputs!N103)","v":-6.5000000000000002E-2}]
[null,null,"Standard",{"$f":"IF($E$23='Drop downs'!$B$10, Inputs!M96, Inputs!M104)","v":-1.6E-2},{"$f":"IF($E$23='Drop downs'!$B$10, Inputs!N96, Inputs!N104)","v":-7.6999999999999999E-2}]
[null,null,"Minor",{"$f":"IF($E$23='Drop downs'!$B$10, Inputs!M97, Inputs!M105)","v":8.1000000000000003E-2},{"$f":"IF($E$23='Drop downs'!$B$10, Inputs!N97, Inputs!N105)","v":-8.8999999999999996E-2}]
[null,null,"Immediate",{"$f":"IF($E$23='Drop downs'!$B$10, Inputs!M98, Inputs!M106)","v":0.03},{"$f":"IF($E$23='Drop downs'!$B$10, Inputs!N98, Inputs!N106)","v":0}]
[]
[]
[null,null,"Reduction in number of workdays "]
[]
[null,null,"Current number of workdays "]
[]
[null,null,null,"Statutory undertaker (Utility company)","Local  Authority","Total "]
[null,null,{"$f":"Inputs!L22","v":"Major"},{"$f":"Inputs!M29","v":11470},{"$f":"Inputs!N29","v":8512},{"$f":"SUM(D39:E39)","v":19982}]
[null,null,{"$f":"Inputs!L23","v":"Standard"},{"$f":"Inputs!M30","v":6430},{"$f":"Inputs!N30","v":1186},{"$f":"SUM(D40:E40)","v":7616}]
[null,null,{"$f":"Inputs!L24","v":"Minor"},{"$f":"Inputs!M31","v":4073},{"$f":"Inputs!N31","v":857},4930]
[null,null,{"$f":"Inputs!L25","v":"Immediate"},{"$f":"Inputs!M32","v":2724},{"$f":"Inputs!N32","v":2228},4952]
[]
[null,null,"Total ",{"$f":"SUM(D39:D42)","v":24697},{"$f":"SUM(E39:E42)","v":12783},37480]
[]
[]
[null,null,"Expected change in workdays"]
[]
[null,null,null,"Statutory undertaker (Utility company)","Local  Authority","Total "]
[null,null,"Major",{"$f":"D39*D28","v":-1594.3300000000002},-553.28,{"$f":"D50+E50","v":-2147.61},null,"Total current number of workdays ",null,{"$f":"$F$44","v":37480}]
[null,null,"Standard",-102.88,-91.322000000000003,{"$f":"D51+E51","v":-194.202}]
[null,null,"Minor",329.91300000000001,-76.272999999999996,253.64000000000001,null,"Expected change in workdays ",null,{"$f":"$F$55","v":-2006.452}]
[null,null,"Immediate",81.72,0,81.72]
[null,null,null,null,null,null,null,"Expected number of workdays due to permit scheme ",null,{"$f":"$F$65","v":35473.547999999995}]
[null,null,"Total ",{"$f":"SUM(D50:D53)","v":-1285.577},{"$f":"SUM(E50:E53)","v":-720.875},-2006.452]
[]
[null,null,"Expected number of workdays"]
[]
[null,null,null,"Statutory undertaker (Utility)","Local  Authority","Total "]
[null,null,"Major",{"$f":"D39+D50","v":9875.67},{"$f":"E39+E50","v":7958.72},{"$f":"D60+E60","v":17834.39}]
[null,null,"Standard",{"$f":"D40+D51","v":6327.12},1094.6779999999999,{"$f":"D61+E61","v":7421.7979999999998}]
[null,null,"Minor",{"$f":"D41+D52","v":4402.9130000000005},780.72699999999998,5183.6400000000003]
[null,null,"Immediate",{"$f":"D42+D53","v":2805.72},2228,5033.7199999999993]
[]
[null,null,"Total ",{"$f":"SUM(D60:D63)","v":23411.423000000003},{"$f":"SUM(E60:E63)","v":12062.125000000002},35473.547999999995]
[]
[null,"Congestion cost impact due to reduction in workdays "]
[]
[null,null,"Congestion impact costs "]
[]
[null,null,null,null,null,null,null,null,null,null,"Impact/day (2010 prices)"]
[null,null,null,"Statutory undertaker (Utility)","Local  Authority","Total ",null,null,null,null,"Statutory undertaker (Utility)","Local  Authority"]
[null,null,"Major",{"$f":"D50*K80","v":-731942.00828899897},{"$f":"E50*L80","v":-841653.3277528336},{"$f":"D73+E73","v":-1573595.3360418326},null,null,null,"Major",{"$f":"'Expected Change'!E13","v":403.13},{"$f":"'Expected Change'!E8","v":1335.78}]
[null,null,"Standard",-19366.767808470329,-42482.511152827399,{"$f":"D74+E74","v":-61849.278961297729},null,null,null,"Standard",{"$f":"'Expected Change'!E14","v":165.3},{"$f":"'Expected Change'!E9","v":408.49}]
[null,null,"Minor",38517.79017995797,-28595.466368835674,9922.3238111222963,null,null,null,"Minor",{"$f":"'Expected Change'!E15","v":102.52},{"$f":"'Expected Change'!E10","v":329.21}]
[null,null,"Immediate",14031.258616956671,{"$f":"E53*L83","v":0},14031.258616956671,null,null,null,"Immediate",{"$f":"'Expected Change'!E16","v":150.77000000000001},{"$f":"'Expected Change'!E11","v":224.59}]
[null,null,"Total",{"$f":"SUM(D73:D76)","v":-698759.72730055463},{"$f":"SUM(E73:E76)","v":-912731.30527449667},-1611491.0325750513]
[null,null,null,null,null,null,null,null,null,null,"Impact/day (2018 prices)"]
[null,null,"Total impact on congestion costs due to permit scheme ",null,{"$f":"$F$77","v":-1611491.0325750513},null,null,null,null,null,"Statutory undertaker (Utility)","Local  Authority"]
[null,null,{"$f":"IF($E$79<0,\"Benefits\",\"Costs\")&\" to road users from the\"&IF($E$79<0,\" decrease\",\" increase\")&\" in congestion costs\"","v":"Benefits to road users from the decrease in congestion costs"},null,null,null,null,null,null,"Major",{"$f":"K73*100/Deflator!$C$27","v":459.09065769884461},{"$f":"L73*100/Deflator!$C$27","v":1521.2068532259138}]
[null,null,null,null,null,null,null,null,null,"Standard",{"$f":"K74*100/Deflator!$C$27","v":188.24618787393399},{"$f":"L74*100/Deflator!$C$27","v":465.19470831593043}]
[null,null,{"$f":"\"The expected behavioural change will\"&IF($J$52<0,\" decrease\",\" increase\")&\" the duration of current works.  \nThis is equivalent to\"&IF($J$52<0,\" a decrease of \",\" an increase of \")&TEXT(IF($J$52<0,ROUND(-$J$52,0),ROUND($J$52,0)),\"###,###\")&\" work days.\"","v":"The expected behavioural change will decrease the duration of current works.  \nThis is equivalent to a decrease of 2,006 work days."},null,null,{"$f":"\"The \"&IF($J$52<0, \"reduction\", \"increase\") &\" in the duration of works \" &IF($F$77<0,\"decreases\",\"increases\")&\" the costs of congestion by\n \"&TEXT(ROUND(ABS($E$79),0),\"£###,###,###\")&\" per year and is therefore a \"&IF($E$79<0,\"benefit\",\"cost\")&\" to road users. \"","v":"The reduction in the duration of works decreases the costs of congestion by\n £1,611,491 per year and is therefore a benefit to road users. "},null,null,null,"Minor",{"$f":"K75*100/Deflator!$C$27","v":116.75135620590268},{"$f":"L75*100/Deflator!$C$27","v":374.90942232291474}]
[null,null,null,null,null,null,null,null,null,"Immediate",{"$f":"K76*100/Deflator!$C$27","v":171.69919991381144},{"$f":"L76*100/Deflator!$C$27","v":255.7665537483777}]
[]
[]
[null,null,{"$f":"\"Breakdown of the \" &IF($F$77<0, \"benefits\", \"costs\") &\" to road users from the\"&IF($F$77<0,\" decrease\",\" increase\")&\" in congestion costs:\"","v":"Breakdown of the benefits to road users from the decrease in congestion costs:"}]
[]
[null,null,"A breakdown of the change in the duration of works, measured in work days, is shown in this table. \nPlease refer to the guidance note for further information."]
[]
[]
[null,null,"Congestion impact costs breakdown "]
[null,null,"****This is being measured as a benefit"]
[null,null,"Total impact of change in workdays:",{"$f":"-($E$79)","v":1611491.0325750513}]
[]
[]
[null,null,"Business"]
[null,null,"Journey Time Savings & Reliability ",{"$f":"$D$93*Inputs!$F$125","v":809418.135512896}]
[]
[null,null,"Non-business "]
[null,null,"Journey Time Savings & Reliability ",{"$f":"$D$93*Inputs!$F$124","v":711097.11458797194}]
[null,null,"Accident",{"$f":"$D$93*Inputs!$F$127","v":14105.232729308875}]
[null,null,"Fuel carbon ",{"$f":"$D$93*Inputs!$F$126","v":76870.549744874515}]
[]
[]
[]
[null,"Set up and operating costs to Local Authorities"]
[]
[null,null,"Set up costs"]
[]
[null,null,"Set-up cost",{"$f":"Inputs!$M$84","v":30000},null,"First year cost to the Local Authority ",null,null,{"$f":"IF($D$114= \" \", \" \", $D$114+(D118+H118+H119))","v":506145.64455357974}]
[null,null,"Price base:",{"$f":"Inputs!M85","v":2019},null,"Subsequent annual cost to the Local Authority (second year onwards) ",null,null,{"$f":"($D$118+$H$118+$H$119)","v":476605.11518563848}]
[null,null,"Deflator:",{"$f":"IF($D$111='Drop downs'!$B$15, \" \", INDEX(Deflator!$B$25:$C$37,MATCH($D$111,Deflator!$B$25:$B$37),2))","v":101.55539065104706}]
[]
[null,null,"Set-up cost (2018 prices): ",{"$f":"IF($D$112=\" \", $D$110, (D110*100)/D112)","v":29540.529367941232}]
[]
[null,null,"Operating Costs"]
[]
[null,null,"Running costs in relation to SU permitting: ",{"$f":"$D$196","v":313418.33078351611},null,"Running costs should LAs pay permit fees ",null,{"$f":"$E$196","v":135988.98700176866},"This is equal to the admin cost that LAs have to do to review permit applications"]
[null,null,"This is equal to the permit fees that utility companies pay ",null,null,"Admin fees for LA works promoters",null,{"$f":"$D$126","v":27197.797400353735}]
[]
[null,"Additional administration costs for works promoters"]
[]
[null,null,"This is the cost to apply for a permit fee "]
[null,null,"SU admin costs (2018 prices)",{"$f":"H124*D118","v":62683.666156703228},null,"Administration cost uplift for works promoters",null,{"$f":"'Assumption for SU promoter cost'!$G$7","v":0.2}]
[]
[null,null,"LA works promoter admin costs (2018 prices)",{"$f":"H124*H118","v":27197.797400353735}]
[]
[]
[null,null,"Permit fee structure"]
[]
[null,null,"Permit fee structure:",{"$f":"Inputs!$M$55","v":"Maximum"}]
[]
[null,null,null,"Road Category 0-2 or Traffic-sensitive","Road Category 3-4 and non traffic sensitive"]
[null,null,"Major over 10 days",{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!M60, Inputs!M70)","v":240},{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!N60, Inputs!N70)","v":150}]
[null,null,"Major 4 to 10 days",{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!M61, Inputs!M71)","v":130},{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!N61, Inputs!N71)","v":75}]
[null,null,"Major up to 3 days",{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!M62, Inputs!M72)","v":65},{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!N62, Inputs!N72)","v":45}]
[null,null,"Standard",{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!M63, Inputs!M73)","v":130},{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!N63, Inputs!N73)","v":75}]
[null,null,"Minor",{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!M64, Inputs!M74)","v":65},{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!N64, Inputs!N74)","v":45}]
[null,null,"Immediate",{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!M65, Inputs!M75)","v":60},{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!N65, Inputs!N75)","v":40}]
[]
[null,null,"Number of works breakdown "]
[]
[null,null,"Total number of works ",null,null,null,"Works type split by road type "]
[]
[null,null,null,"Statutory undertaker (Utility)","Local Authority",null,null,"Statutory undertaker (Utility)",null,"Local Authority"]
[null,null,"Major ",{"$f":"Inputs!$M$22","v":370},{"$f":"Inputs!$N$22","v":304},null,null,"0-2 roads ","3-4 roads ","0-2 roads ","3-4 roads "]
[null,null,"Major over 10 days",{"$f":"Inputs!$M$22*Inputs!$M47","v":340.40000000000003},{"$f":"Inputs!$N$22*Inputs!$M47","v":279.68},null,"Major",{"$f":"Inputs!M38","v":0.25},{"$f":"(1-$H147)","v":0.75},{"$f":"Inputs!N38","v":0.28000000000000003},{"$f":"(1-$J147)","v":0.72}]
[null,null,"Major 4 to 10 days",{"$f":"Inputs!$M$22*Inputs!$M48","v":11.1},{"$f":"Inputs!$N$22*Inputs!$M48","v":9.1199999999999992},null,"Standard",{"$f":"Inputs!M39","v":0.22},{"$f":"(1-$H148)","v":0.78},{"$f":"Inputs!N39","v":0.25},{"$f":"(1-$J148)","v":0.75}]
[null,null,"Major up to 3 days",{"$f":"Inputs!$M$22*Inputs!$M49","v":18.5},{"$f":"Inputs!$N$22*Inputs!$M49","v":15.200000000000001},null,"Minor",{"$f":"Inputs!M40","v":0.2},0.8,{"$f":"Inputs!N40","v":0.24},0.76]
[null,null,"Standard ",{"$f":"Inputs!M23","v":684},{"$f":"Inputs!N23","v":113},null,"Immediate",{"$f":"Inputs!M41","v":0.24},0.76,{"$f":"Inputs!N41","v":0.2},0.8]
[null,null,"Minor ",{"$f":"Inputs!M24","v":2263},{"$f":"Inputs!N24","v":430}]
[null,null,"Immediate ",{"$f":"Inputs!M25","v":1432},{"$f":"Inputs!N25","v":1047}]
[]
[null,null,"Total ",{"$f":"($D$146+$D$150+$D$151+$D$152)","v":4749},{"$f":"($E$146+$E$150+$E$151+$E$152)","v":1894}]
[]
[]
[]
[null,null,"Breakdown of works by works type and road type "]
[]
[null,null,null,"Statutory undertaker (Utility)",null,"Local Authority"]
[null,null,null,"0-2 roads ","3-4 roads ","0-2 roads ","3-4 roads "]
[null,null,"Major ",{"$f":"SUM(D163:D165)","v":92.500000000000014},{"$f":"SUM(E163:E165)","v":277.5},{"$f":"SUM(F163:F165)","v":85.120000000000019},{"$f":"SUM(G163:G165)","v":218.87999999999997}]
[null,null,"Major over 10 days",{"$f":"D147*$H$147","v":85.100000000000009},{"$f":"D147*$I$147","v":255.3},{"$f":"E147*$J$147","v":78.310400000000016},{"$f":"E147*$K$147","v":201.36959999999999}]
[null,null,"Major 4 to 10 days",{"$f":"D148*$H$147","v":2.7749999999999999},{"$f":"D148*$I$147","v":8.3249999999999993},{"$f":"E148*$J$147","v":2.5535999999999999},{"$f":"E148*$K$147","v":6.5663999999999989}]
[null,null,"Major up to 3 days",{"$f":"D149*$H$147","v":4.625},{"$f":"D149*$I$147","v":13.875},{"$f":"E149*$J$147","v":4.2560000000000011},{"$f":"E149*$K$147","v":10.944000000000001}]
[null,null,"Standard ",{"$f":"D150*H148","v":150.47999999999999},{"$f":"D150*I148","v":533.52},28.25,{"$f":"E150*K148","v":84.75}]
[null,null,"Minor ",{"$f":"D151*H149","v":452.6},1810.4,103.2,{"$f":"E151*K149","v":326.8}]
[null,null,"Immediate ",{"$f":"D152*H150","v":343.68},1088.32,209.4,{"$f":"E152*K150","v":837.6}]
[]
[null,null,"Total ",{"$f":"D162+D166+D167+D168","v":1039.26},{"$f":"E162+E166+E167+E168","v":3709.74},{"$f":"F162+F166+F167+F168","v":425.97},1468.0300000000002]
[]
[]
[]
[null,null,"Revenue from the permit scheme "]
[]
[null,null,null,"Statutory undertaker (Utility)",null,"Local Authority"]
[null,null,null,"0-2 roads ","3-4 roads ","0-2 roads ","3-4 roads "]
[null,null,"Major ",{"$f":"SUM(D179:D181)","v":21085.375000000004},{"$f":"SUM(E179:E181)","v":39543.75},19403.104000000003,31190.399999999998]
[null,null,"Major over 10 days",{"$f":"D163*D134","v":20424.000000000004},38295,{"$f":"F163*D134","v":18794.496000000003},{"$f":"G163*E134","v":30205.439999999999}]
[null,null,"Major 4 to 10 days",360.75,624.375,331.96799999999996,492.4799999999999]
[null,null,"Major up to 3 days",300.625,624.375,276.6400000000001,492.48]
[null,null,"Standard ",19562.399999999998,40014,3672.5,6356.25]
[null,null,"Minor ",29419,81468,6708,14706]
[null,null,"Immediate ",20620.8,43532.799999999996,12564,33504]
[]
[null,null,"Total by road type",{"$f":"D178+D182+D183+D184","v":90687.574999999997},{"$f":"E178+E182+E183+E184","v":204558.55},42347.604000000007,85756.65]
[null,null,"Total by works promoter ",{"$f":"D186+E186","v":295246.125},null,{"$f":"F186+G186","v":128104.254}]
[]
[]
[]
[null,null,"Uplift for cancelled works "]
[]
[null,null,null,"Statutory undertaker (Utility)","Local  Authority","Total "]
[null,null,"Revenue uplift for cancelled works",{"$f":"$D$187*'Income uplift for cancelled '!$G$11","v":18172.205783516092},{"$f":"$F$187*'Income uplift for cancelled '!$G$11","v":7884.7330017686581},{"$f":"SUM(D194:E194)","v":26056.93878528475}]
[]
[null,null,"Total revenue (including uplift)",{"$f":"D187+D194","v":313418.33078351611},{"$f":"F187+E194","v":135988.98700176866},{"$f":"SUM(D196,E196)","v":449407.31778528477}]
[]
[]
[null,null,"Total revenue from the permit scheme ","Statutory Undertaker ",null,"Local Authority "]
[]
[null,null,{"$f":"$D$201","v":313418.33078351611},{"$f":"$D$196","v":313418.33078351611},null,{"$f":"$E$196","v":135988.98700176866}]
[]
[]
[]
[null,null,{"$f":"\"The appraisal covers the period of 10 years from \"&(Appraisal!$D$11)&\" to \"&(Appraisal!$M$11)&\", with a 2019 discount base.\n\"&\"Please refer to the guidance note for more information on appraisal.\"","v":"The appraisal covers the period of 10 years from 2019 to 2028, with a 2019 discount base.\nPlease refer to the guidance note for more information on appraisal."}]
[]
[null,null,null,null,null,{"$f":"\"The local authority will recover annual operating costs related to statutory undertaker permitting through revenue from statutory undertaker's permit fees.\nCosts to run the permit scheme for local authority works are non-recoverable.\"","v":"The local authority will recover annual operating costs related to statutory undertaker permitting through revenue from statutory undertaker's permit fees.\nCosts to run the permit scheme for local authority works are non-recoverable."}]
[null,null,"Total operating costs ",{"$f":"$I$111","v":476605.11518563848}]
[null,null,"Total revenue from permit scheme ",{"$f":"$D$201","v":313418.33078351611}]
[null,null,null,{"$f":"D209/D208","v":0.65760588965025935}]
[]
[]
[null,"END"]

{"sheet":"Outputs >>>","index":10,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[[9,1,22,13]]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Outputs"]

{"sheet":"Key outputs","index":11,"n_rows":122,"n_cols":14,"rows_kept":122,"cut":false,"hidden":false,"merged":[[108,3,108,5],[109,3,109,5],[113,9,115,13],[77,9,80,13],[81,3,81,4],[82,3,82,4],[83,3,83,4],[79,3,79,5],[80,3,80,5],[78,3,78,4],[77,3,77,5],[26,3,26,4],[27,3,27,4],[31,18,38,25],[32,8,34,13],[36,8,38,13],[49,9,52,13],[28,3,28,4],[0,3,1,8],[3,4,3,5],[22,3,22,4],[21,8,22,13],[21,3,21,6],[50,3,50,4],[23,3,23,4],[24,3,24,4],[25,3,25,4],[3,6,3,7],[43,3,43,4],[42,3,42,4],[19,3,19,5],[32,3,32,5],[66,9,68,13],[63,3,63,5],[56,3,57,4],[56,5,57,5],[60,3,61,4],[60,5,61,5],[58,5,59,5],[58,3,59,4],[57,9,60,13],[121,3,121,4],[119,3,119,4],[120,3,120,4],[82,9,83,13],[97,3,97,6],[98,3,98,6],[87,3,87,4],[90,3,90,6],[93,3,93,6],[90,9,93,13],[94,4,94,6],[89,3,89,6],[119,9,120,13],[102,9,104,13],[108,9,109,13],[103,3,103,5]]}
[null,null,null,"Output Summary"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Prices are based to 2018"]
[]
[null,null,null,null,"Local Authority:",null,{"$f":"IF(Inputs!$M$17=\"\", \"\", Inputs!$M$17)","v":"Herefordshire Council"}]
[]
[null,null,null,"Key Inputs and Assumptions"]
[]
[null,null,null,"Works input "]
[]
[null,null,null,"Current number of street works per year",null,null,null,"Current number of work days per year"]
[]
[null,null,null,null,"Statutory Undertaker ","Local Authority",null,null,"Statutory Undertaker ","Local Authority"]
[null,null,null,"Major",{"$f":"Inputs!M22","v":370},{"$f":"Inputs!N22","v":304},null,"Major",{"$f":"Inputs!M29","v":11470},{"$f":"Inputs!N29","v":8512}]
[null,null,null,"Standard",{"$f":"Inputs!M23","v":684},{"$f":"Inputs!N23","v":113},null,"Standard",{"$f":"Inputs!M30","v":6430},{"$f":"Inputs!N30","v":1186}]
[null,null,null,"Minor",{"$f":"Inputs!M24","v":2263},{"$f":"Inputs!N24","v":430},null,"Minor",{"$f":"Inputs!M31","v":4073},{"$f":"Inputs!N31","v":857}]
[null,null,null,"Immediate",{"$f":"Inputs!M25","v":1432},{"$f":"Inputs!N25","v":1047},null,"Immediate",{"$f":"Inputs!M32","v":2724},{"$f":"Inputs!N32","v":2228}]
[]
[null,null,null,"Permit scheme fee structure "]
[]
[null,null,null,"Preferred permit fee structure",null,null,{"$f":"Inputs!$M$55","v":"Maximum"}]
[null,null,null,null,null,null,null,null,"Rationale for custom permit fee structure: "]
[null,null,null,"Permit Fee Structure",null,null,null,null,{"$f":"IF(Inputs!$L$77=\"\", \"\", Inputs!$L$77)","v":""}]
[null,null,null,null,null,"Road Category 0-2 or Traffic-sensitive","Road Category 3-4 and Non-traffic sensitive"]
[null,null,null,{"$f":"Inputs!L60","v":"Major works - over 10 days or traffic regulation order required"},null,{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!M60, Inputs!M70)","v":240},{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!N60, Inputs!N70)","v":150}]
[null,null,null,{"$f":"Inputs!L61","v":"Major works - 4 to 10 days and no traffic regulation order required"},null,{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!M61, Inputs!M71)","v":130},{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!N61, Inputs!N71)","v":75}]
[null,null,null,{"$f":"Inputs!L62","v":"Major works - up to 3 days and no traffic regulation order required"},null,{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!M62, Inputs!M72)","v":65},{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!N62, Inputs!N72)","v":45}]
[null,null,null,{"$f":"Inputs!L63","v":"Standard works"},null,{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!M63, Inputs!M73)","v":130},{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!N63, Inputs!N73)","v":75}]
[null,null,null,{"$f":"Inputs!L64","v":"Minor works"},null,{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!M64, Inputs!M74)","v":65},{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!N64, Inputs!N74)","v":45}]
[null,null,null,{"$f":"Inputs!L65","v":"Immediate works"},null,{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!M65, Inputs!M75)","v":60},{"$f":"IF(Inputs!$M$55='Drop downs'!$B$4, Inputs!N65, Inputs!N75)","v":40}]
[]
[null,null,null,"Behavioural change in the duration of works "]
[null,null,null,null,null,null,null,null,"Rationale for custom behavioural change: "]
[null,null,null,"Behavioural change in the duration of works:",null,null,{"$f":"Inputs!$M$90","v":"Default "},null,{"$f":"IF(Inputs!$L$108=\"\", \"\", Inputs!$L$108)","v":""}]
[]
[null,null,null,null,"Statutory Undertaker","Local Authority"]
[null,null,null,"Major",{"$f":"IF(Inputs!$M$90='Drop downs'!$B$10, Inputs!M95, Inputs!M103)","v":-0.13900000000000001},{"$f":"IF(Inputs!$M$90='Drop downs'!$B$10, Inputs!N95, Inputs!N103)","v":-6.5000000000000002E-2}]
[null,null,null,"Standard",{"$f":"IF(Inputs!$M$90='Drop downs'!$B$10, Inputs!M96, Inputs!M104)","v":-1.6E-2},{"$f":"IF(Inputs!$M$90='Drop downs'!$B$10, Inputs!N96, Inputs!N104)","v":-7.6999999999999999E-2},null,null,{"$f":"Calculations!$G$27","v":"The duration of works will increase for one or more of the categories in this table. Please refer to the guidance note for more information"}]
[null,null,null,"Minor",{"$f":"IF(Inputs!$M$90='Drop downs'!$B$10, Inputs!M97, Inputs!M105)","v":8.1000000000000003E-2},{"$f":"IF(Inputs!$M$90='Drop downs'!$B$10, Inputs!N97, Inputs!N105)","v":-8.8999999999999996E-2}]
[null,null,null,"Immediate",{"$f":"IF(Inputs!$M$90='Drop downs'!$B$10, Inputs!M98, Inputs!M106)","v":0.03},{"$f":"IF(Inputs!$M$90='Drop downs'!$B$10, Inputs!N98, Inputs!N106)","v":0}]
[]
[null,null,null,"Local authority costs "]
[]
[null,null,null,"Set-up costs",null,{"$f":"Inputs!$M$84","v":30000}]
[null,null,null,"Year of price base ",null,{"$f":"Calculations!$D$111","v":2019}]
[]
[null,null,null,"Key Outputs"]
[]
[null,null,null,"Number of works "]
[]
[null,null,null,null,null,null,null,null,null,{"$f":"Calculations!$F$18","v":"The number of works that take place in the local authority will not change. \nThe permit fee scheme will only impact the time taken to complete these works. \nThe total duration of all works is measured in work days and is shown below. "}]
[null,null,null,"Total number of works",null,{"$f":"Calculations!$D$18","v":6643}]
[]
[]
[]
[null,null,null,"Impact on number of workdays (total duration of works)"]
[]
[null,null,null,"Total current number of work days ",null,{"$f":"Calculations!$J$50","v":37480}]
[null,null,null,null,null,null,null,null,null,{"$f":"Calculations!$C$82","v":"The expected behavioural change will decrease the duration of current works.  \nThis is equivalent to a decrease of 2,006 work days."}]
[null,null,null,"Expected number of work days due to permit scheme",null,{"$f":"Calculations!$J$54","v":35473.547999999995}]
[]
[null,null,null,"Expected change in work days ",null,{"$f":"Calculations!$J$52","v":-2006.452}]
[]
[]
[null,null,null,"Breakdown of the impact on the number work days"]
[null,null,null,null,{"$f":"Calculations!D49","v":"Statutory undertaker (Utility company)"},{"$f":"Calculations!E49","v":"Local  Authority"},{"$f":"Calculations!F49","v":"Total "}]
[null,null,null,{"$f":"Calculations!C50","v":"Major"},{"$f":"Calculations!D50","v":-1594.3300000000002},{"$f":"Calculations!E50","v":-553.28},{"$f":"Calculations!F50","v":-2147.61}]
[null,null,null,{"$f":"Calculations!C51","v":"Standard"},{"$f":"Calculations!D51","v":-102.88},{"$f":"Calculations!E51","v":-91.322000000000003},{"$f":"Calculations!F51","v":-194.202},null,null,{"$f":"Calculations!$C$88","v":"A breakdown of the change in the duration of works, measured in work days, is shown in this table. \nPlease refer to the guidance note for further information."}]
[null,null,null,{"$f":"Calculations!C52","v":"Minor"},{"$f":"Calculations!D52","v":329.91300000000001},{"$f":"Calculations!E52","v":-76.272999999999996},{"$f":"Calculations!F52","v":253.64000000000001}]
[null,null,null,{"$f":"Calculations!C53","v":"Immediate"},{"$f":"Calculations!D53","v":81.72},{"$f":"Calculations!E53","v":0},{"$f":"Calculations!F53","v":81.72}]
[]
[null,null,null,{"$f":"Calculations!C55","v":"Total "},{"$f":"Calculations!D55","v":-1285.577},{"$f":"Calculations!E55","v":-720.875},{"$f":"Calculations!F55","v":-2006.452}]
[]
[null,null,null,"Impact on the costs of congestion to road users"]
[]
[null,null,null,{"$f":"Calculations!$C$80","v":"Benefits to road users from the decrease in congestion costs"},null,null,null,{"$f":"ABS(Calculations!$E$79)","v":1611491.0325750513}]
[]
[null,null,null,{"$f":"Calculations!C86","v":"Breakdown of the benefits to road users from the decrease in congestion costs:"}]
[null,null,null,"Business",null,null,null,null,null,{"$f":"Calculations!$F$82","v":"The reduction in the duration of works decreases the costs of congestion by\n £1,611,491 per year and is therefore a benefit to road users. "}]
[null,null,null,"Journey Time Savings & Reliability ",null,{"$f":"Calculations!$D$97","v":809418.135512896}]
[]
[null,null,null,"Non-business "]
[null,null,null,"Journey Time Savings & Reliability ",null,{"$f":"Calculations!$D$100","v":711097.11458797194}]
[null,null,null,"Accident",null,{"$f":"Calculations!D101","v":14105.232729308875},null,null,null,"Please refer to the guidance note for further information on congestion cost impacts"]
[null,null,null,"Fuel carbon ",null,{"$f":"Calculations!D102","v":76870.549744874515}]
[]
[null,null,null,"Costs to the local authority to set up and operate the permit scheme"]
[]
[null,null,null,"Set-up cost ",null,{"$f":"IF(Calculations!$D$114=\" \", \" \", Calculations!$D$114)","v":29540.529367941232}]
[]
[null,null,null,"  Operational costs (per year)",null,null,null,{"$f":"Calculations!$I$111","v":476605.11518563848}]
[null,null,null,"LA costs:",null,null,null,null,null,{"$f":"IFERROR(Calculations!$F$207, \"\")","v":"The local authority will recover annual operating costs related to statutory undertaker permitting through revenue from statutory undertaker's permit fees.\nCosts to run the permit scheme for local authority works are non-recoverable."}]
[null,null,null,null,"Costs to review and issue permits to LA works promoters ",null,null,{"$f":"Calculations!$H$118","v":135988.98700176866}]
[null,null,null,null,"Costs to review, issue permits and receive permit fees from statutory undertakers ",null,null,{"$f":"Calculations!$D$118","v":313418.33078351611}]
[null,null,null,"LA works promoter costs:"]
[null,null,null,null,"Costs to apply for a permit (administration costs)",null,null,{"$f":"Calculations!$H$119","v":27197.797400353735}]
[]
[]
[null,null,null,"First year cost to the local authority ",null,null,null,{"$f":"Calculations!$I$110","v":506145.64455357974}]
[null,null,null,"Subsequent annual cost to the local authority (second year onwards) ",null,null,null,{"$f":"Calculations!$I$111","v":476605.11518563848}]
[]
[null,null,null,"Costs to local authority works promoters"]
[]
[null,null,null,null,null,null,null,null,null,"Local authority works promoters will apply for a permit but do not pay a permit fee. \nCosts to local authority works promoters are the additional administration of using the permit scheme. "]
[null,null,null,"  Administration costs (per year)",null,null,{"$f":"Calculations!$D$126","v":27197.797400353735}]
[]
[]
[null,null,null,"Costs to statutory undertakers "]
[]
[null,null,null,"Permit fee costs (per year)",null,null,{"$f":"Calculations!$D$201","v":313418.33078351611},null,null,"Statutory undertakers will apply for a work permit and pay a permit fee. "]
[null,null,null,"Administration costs (per year)",null,null,{"$f":"Calculations!$D$124","v":62683.666156703228}]
[]
[null,null,null,"Annual revenue to the local authority "]
[]
[null,null,null,"Annual revenue from the permit scheme",null,null,{"$f":"Calculations!$C$201","v":313418.33078351611},null,null,"Local authority revenue from the permit scheme comes from permit fees paid by statutory undertakers. \nLocal authority works promoters do not pay permit fees. "]
[null,null,null,"Annual revenue from local authority works promoters permit fees",null,null,0]
[null,null,null,"Annual revenue from statutory undertakers permit fees",null,null,{"$f":"Calculations!$D$201","v":313418.33078351611}]
[]
[null,null,null,"Appraisal Outputs "]
[]
[null,null,null,"Net Present Value ",null,{"$f":"Appraisal!$D$76","v":9199640.3233459517},null,null,null,{"$f":"Calculations!$C$205","v":"The appraisal covers the period of 10 years from 2019 to 2028, with a 2019 discount base.\nPlease refer to the guidance note for more information on appraisal."}]
[null,null,null,"Net Present Costs ",null,{"$f":"Appraisal!$D$74","v":7369376.0344296396}]
[null,null,null,"Net Present Benefits ",null,{"$f":"Appraisal!$D$75","v":16569016.357775591}]

{"sheet":"Appraisal","index":12,"n_rows":82,"n_cols":13,"rows_kept":82,"cut":false,"hidden":true,"merged":[]}
[]
[null,"File Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"[\",CELL(\"filename\",G2))+1,FIND(\"]\",CELL(\"filename\",G2))-FIND(\"[\",CELL(\"filename\",G2))-1),\"Please save the file to show here\")","v":"Herefordshire CBA.xlsm"}]
[null,"Sheet Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"]\",CELL(\"filename\",G2))+1,255),\"Please save the file to show here\")","v":"Appraisal"}]
[null,"Description and Purpose of Sheet",null,null,null,null,"user input"]
[null,"Version number",null,null,null,null,{"$f":"Version_number","v":0.03}]
[]
[]
[]
[null,null,"Undiscounted Impacts"]
[]
[null,null,null,2019,2020,2021,2022,2023,2024,2025,2026,2027,2028]
[null,null,"Costs"]
[null,null,"costs to business ",{"$f":"SUM(D14:D15)","v":376101.99694021931},{"$f":"SUM(E14:E15)","v":376101.99694021931},376101.99694021931,376101.99694021931,376101.99694021931,376101.99694021931,376101.99694021931,376101.99694021931,376101.99694021931,376101.99694021931]
[null,null,"Permit fees for Statutory Undertakers ",{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611}]
[null,null,"SU admin costs ",{"$f":"Calculations!$D$124","v":62683.666156703228},{"$f":"Calculations!$D$124","v":62683.666156703228},{"$f":"Calculations!$D$124","v":62683.666156703228},{"$f":"Calculations!$D$124","v":62683.666156703228},{"$f":"Calculations!$D$124","v":62683.666156703228},{"$f":"Calculations!$D$124","v":62683.666156703228},{"$f":"Calculations!$D$124","v":62683.666156703228},{"$f":"Calculations!$D$124","v":62683.666156703228},{"$f":"Calculations!$D$124","v":62683.666156703228},{"$f":"Calculations!$D$124","v":62683.666156703228}]
[]
[null,null,"costs to non -business ",{"$f":"SUM(D18:D21)","v":506145.64455357974},{"$f":"SUM(E18:E21)","v":476605.11518563854},476605.11518563854,476605.11518563854,476605.11518563854,476605.11518563854,476605.11518563854,476605.11518563854,476605.11518563854,476605.11518563854]
[null,null,"LA works promoters admin costs",{"$f":"Calculations!$D$126","v":27197.797400353735},{"$f":"Calculations!$D$126","v":27197.797400353735},{"$f":"Calculations!$D$126","v":27197.797400353735},{"$f":"Calculations!$D$126","v":27197.797400353735},{"$f":"Calculations!$D$126","v":27197.797400353735},{"$f":"Calculations!$D$126","v":27197.797400353735},{"$f":"Calculations!$D$126","v":27197.797400353735},{"$f":"Calculations!$D$126","v":27197.797400353735},{"$f":"Calculations!$D$126","v":27197.797400353735},{"$f":"Calculations!$D$126","v":27197.797400353735}]
[null,null,"Set up costs LA's ",{"$f":"Calculations!$D$114","v":29540.529367941232},0,0,0,0,0,0,0,0,0]
[null,null,"LA costs to review LA work promoter permits ",{"$f":"Calculations!$H$118","v":135988.98700176866},{"$f":"Calculations!$H$118","v":135988.98700176866},{"$f":"Calculations!$H$118","v":135988.98700176866},{"$f":"Calculations!$H$118","v":135988.98700176866},{"$f":"Calculations!$H$118","v":135988.98700176866},{"$f":"Calculations!$H$118","v":135988.98700176866},{"$f":"Calculations!$H$118","v":135988.98700176866},{"$f":"Calculations!$H$118","v":135988.98700176866},{"$f":"Calculations!$H$118","v":135988.98700176866},{"$f":"Calculations!$H$118","v":135988.98700176866}]
[null,null,"LA costs to review utility company permits",{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611},{"$f":"Calculations!$D$118","v":313418.33078351611}]
[]
[null,null,"Benefits"]
[null,null,"Benefits to business ",{"$f":"SUM(D25:D26)","v":809418.135512896},{"$f":"SUM(E25:E26)","v":809418.135512896},809418.135512896,809418.135512896,809418.135512896,809418.135512896,809418.135512896,809418.135512896,809418.135512896,809418.135512896]
[null,null,"Journey time and reliability ",{"$f":"Calculations!$D$97","v":809418.135512896},{"$f":"Calculations!$D$97","v":809418.135512896},{"$f":"Calculations!$D$97","v":809418.135512896},{"$f":"Calculations!$D$97","v":809418.135512896},{"$f":"Calculations!$D$97","v":809418.135512896},{"$f":"Calculations!$D$97","v":809418.135512896},{"$f":"Calculations!$D$97","v":809418.135512896},{"$f":"Calculations!$D$97","v":809418.135512896},{"$f":"Calculations!$D$97","v":809418.135512896},{"$f":"Calculations!$D$97","v":809418.135512896}]
[]
[null,null,"Benefits to non-business ",{"$f":"SUM(D28:D31)","v":1115491.2278456716},{"$f":"SUM(E28:E31)","v":1115491.2278456716},1115491.2278456716,1115491.2278456716,1115491.2278456716,1115491.2278456716,1115491.2278456716,1115491.2278456716,1115491.2278456716,1115491.2278456716]
[null,null,"Revenue from permit fee income ",{"$f":"Calculations!$C$201","v":313418.33078351611},{"$f":"Calculations!$C$201","v":313418.33078351611},{"$f":"Calculations!$C$201","v":313418.33078351611},{"$f":"Calculations!$C$201","v":313418.33078351611},{"$f":"Calculations!$C$201","v":313418.33078351611},{"$f":"Calculations!$C$201","v":313418.33078351611},{"$f":"Calculations!$C$201","v":313418.33078351611},{"$f":"Calculations!$C$201","v":313418.33078351611},{"$f":"Calculations!$C$201","v":313418.33078351611},{"$f":"Calculations!$C$201","v":313418.33078351611}]
[null,null,"Journey time and reliability",{"$f":"Calculations!$D$100","v":711097.11458797194},{"$f":"Calculations!$D$100","v":711097.11458797194},{"$f":"Calculations!$D$100","v":711097.11458797194},{"$f":"Calculations!$D$100","v":711097.11458797194},{"$f":"Calculations!$D$100","v":711097.11458797194},{"$f":"Calculations!$D$100","v":711097.11458797194},{"$f":"Calculations!$D$100","v":711097.11458797194},{"$f":"Calculations!$D$100","v":711097.11458797194},{"$f":"Calculations!$D$100","v":711097.11458797194},{"$f":"Calculations!$D$100","v":711097.11458797194}]
[null,null,"Accident",{"$f":"Calculations!$D$101","v":14105.232729308875},{"$f":"Calculations!$D$101","v":14105.232729308875},{"$f":"Calculations!$D$101","v":14105.232729308875},{"$f":"Calculations!$D$101","v":14105.232729308875},{"$f":"Calculations!$D$101","v":14105.232729308875},{"$f":"Calculations!$D$101","v":14105.232729308875},{"$f":"Calculations!$D$101","v":14105.232729308875},{"$f":"Calculations!$D$101","v":14105.232729308875},{"$f":"Calculations!$D$101","v":14105.232729308875},{"$f":"Calculations!$D$101","v":14105.232729308875}]
[null,null,"Fuel carbon",{"$f":"Calculations!$D$102","v":76870.549744874515},{"$f":"Calculations!$D$102","v":76870.549744874515},{"$f":"Calculations!$D$102","v":76870.549744874515},{"$f":"Calculations!$D$102","v":76870.549744874515},{"$f":"Calculations!$D$102","v":76870.549744874515},{"$f":"Calculations!$D$102","v":76870.549744874515},{"$f":"Calculations!$D$102","v":76870.549744874515},{"$f":"Calculations!$D$102","v":76870.549744874515},{"$f":"Calculations!$D$102","v":76870.549744874515},{"$f":"Calculations!$D$102","v":76870.549744874515}]
[]
[]
[]
[null,null,"Discounted Impacts"]
[]
[null,null,"Discount rate:",3.5000000000000003E-2]
[]
[null,null,null,{"$f":"D11","v":2019},2020,2021,2022,2023,2024,2025,2026,2027,2028]
[null,null,"Discount factor",1,{"$f":"D40/(1+$D$37)","v":0.96618357487922713},0.93351070036640305,0.90194270566802237,0.87144222769857238,0.84197316685852408,0.81350064430775282,0.78599096068381924,0.75941155621625056,0.73373097218961414]
[]
[null,null,"Costs"]
[null,null,"costs to business ",{"$f":"SUM(D44:D46)","v":376101.99694021931},{"$f":"SUM(E44:E46)","v":363383.57192291727},351095.23857286695,339222.45272740774,327751.16205546638,316667.78942557145,305959.21683630091,295612.76989014586,285616.2027924115,275957.68385740242]
[null,null,"Permit fees for Statutory Undertakers ",{"$f":"D14*D$40","v":313418.33078351611},302819.64326909772,292579.36547738913,282685.37727283977,273125.96837955533,263889.82452130952,254966.01403025075,246343.97490845487,238013.50232700957,229964.73654783535]
[null,null,"SU admin costs ",{"$f":"D15*D$40","v":62683.666156703228},60563.928653819552,58515.873095477829,56537.075454567952,54625.193675911069,52777.964904261906,50993.202806050154,49268.794981690975,47602.700465401918,45992.947309567076]
[]
[null,null,"costs to non -business ",{"$f":"SUM(D48:D51)","v":506145.64455357974},{"$f":"SUM(E48:E51)","v":460488.03399578604},444915.9748751556,429870.50712575426,415333.82330990752,401288.71817382373,387718.5682838877,374607.31235158234,361939.43222375109,349699.93451570161]
[null,null,"LA works promoters admin costs",{"$f":"D18*D$40","v":27197.797400353735},{"$f":"E18*E$40","v":26278.06512111472},25389.434899627751,24530.854975485752,23701.309155058698,22899.815608752368,22125.425708939489,21377.222907187912,20654.321649456924,19955.866327977707]
[null,null,"Set up costs LA's ",{"$f":"D19*D$40","v":29540.529367941232},{"$f":"E19*E$40","v":0},0,0,0,0,0,0,0,0]
[null,null,"LA costs to review LA work promoter permits ",{"$f":"D20*D$40","v":135988.98700176866},{"$f":"E20*E$40","v":131390.3256055736},126947.17449813875,122654.27487742875,118506.54577529349,114499.07804376182,110627.12854469744,106886.11453593956,103271.6082472846,99779.331639888522]
[null,null,"LA costs to review utility company permits",{"$f":"D21*D$40","v":313418.33078351611},{"$f":"E21*E$40","v":302819.64326909772},292579.36547738913,282685.37727283977,273125.96837955533,263889.82452130952,254966.01403025075,246343.97490845487,238013.50232700957,229964.73654783535]
[]
[null,null,"Benefits"]
[null,null,"Benefits to business ",{"$f":"SUM(D55:D56)","v":809418.135512896},{"$f":"SUM(E55:E56)","v":782046.50774192857},755600.49057191168,730048.78316126741,705361.14315098303,681508.35087051499,658462.17475412088,636195.33792668686,614681.48591950431,593895.15547778201]
[null,null,"Journey time and reliability ",{"$f":"D25*D$40","v":809418.135512896},782046.50774192857,755600.49057191168,730048.78316126741,705361.14315098303,681508.35087051499,658462.17475412088,636195.33792668686,614681.48591950431,593895.15547778201]
[]
[null,null,"Benefits to non-business ",{"$f":"SUM(D58:D62)","v":1115491.2278456716},{"$f":"SUM(E58:E62)","v":1077769.3022663493},1041322.9973587915,1006109.1761920694,972086.16057204781,939213.68171212357,907452.83257209999,876766.02180879225,847116.92928385746,818470.46307619091]
[null,null,"Revenue from permit fee income ",{"$f":"D28*D$40","v":313418.33078351611},302819.64326909772,292579.36547738913,282685.37727283977,273125.96837955533,263889.82452130952,254966.01403025075,246343.97490845487,238013.50232700957,229964.73654783535]
[null,null,"Journey time and reliability",{"$f":"D29*D$40","v":711097.11458797194},687050.35225891019,663816.76546754607,641368.8555241992,619680.05364656926,598724.68951359356,578477.96088269912,558915.90423449199,540015.36641013727,521753.97720786207]
[null,null,"Accident",{"$f":"D30*D$40","v":14105.232729308875},13628.244182907127,13167.385683968238,12722.111771949991,12291.895431835739,11876.227470372696,11474.615913403572,11086.585423578332,10711.676737756843,10349.446123436564]
[null,null,"Fuel carbon",{"$f":"D31*D$40","v":76870.549744874515},74271.062555434313,71759.480729888237,69332.831623080434,66988.243114087367,64722.940206847707,62534.241745746585,60419.557242267234,58376.383808953855,56402.30319705687]
[]
[]
[null,null,"Costs to business",{"$f":"D43","v":376101.99694021931},363383.57192291727,351095.23857286695,339222.45272740774,327751.16205546638,316667.78942557145,305959.21683630091,295612.76989014586,285616.2027924115,275957.68385740242]
[null,null,"Costs to non-business",{"$f":"D47","v":506145.64455357974},460488.03399578604,444915.9748751556,429870.50712575426,415333.82330990752,401288.71817382373,387718.5682838877,374607.31235158234,361939.43222375109,349699.93451570161]
[null,null,"Benefits to business",{"$f":"D54","v":809418.135512896},{"$f":"E54","v":782046.50774192857},755600.49057191168,730048.78316126741,705361.14315098303,681508.35087051499,658462.17475412088,636195.33792668686,614681.48591950431,593895.15547778201]
[null,null,"Benefits to non-business ",{"$f":"D57","v":1115491.2278456716},{"$f":"E57","v":1077769.3022663493},1041322.9973587915,1006109.1761920694,972086.16057204781,939213.68171212357,907452.83257209999,876766.02180879225,847116.92928385746,818470.46307619091]
[]
[]
[null,null,"Total Costs ",{"$f":"SUM(D64:D65)","v":882247.64149379905},{"$f":"SUM(E64:E65)","v":823871.60591870337},796011.21344802249,769092.95985316206,743084.9853653739,717956.50759939523,693677.78512018861,670220.0822417282,647555.63501616265,625657.61837310402]
[null,null,"Total Benefits ",{"$f":"SUM(D66:D67)","v":1924909.3633585675},{"$f":"SUM(E66:E67)","v":1859815.8100082779},1796923.4879307032,1736157.9593533368,1677447.3037230307,1620722.0325826386,1565915.0073262209,1512961.3597354791,1461798.4152033618,1412365.6185539728]
[]
[]
[null,null,"NPC",{"$f":"SUM(D70:M70)","v":7369376.0344296396}]
[null,null,"NPB",{"$f":"SUM(D71:M71)","v":16569016.357775591}]
[null,null,"NPV",{"$f":"D75-D74","v":9199640.3233459517}]
[]
[]
[]
[]
[]
[null,"END"]

{"sheet":"Costs & Benefits Summary","index":13,"n_rows":60,"n_cols":9,"rows_kept":60,"cut":false,"hidden":true,"merged":[[28,6,28,7],[26,2,26,3],[27,2,27,3],[30,2,30,3],[31,2,31,3],[26,6,26,7],[28,2,28,3],[14,7,16,9],[11,3,11,4],[22,2,22,3],[23,2,23,3],[25,2,25,3],[21,6,21,7],[22,6,22,7],[23,6,23,7],[25,6,25,7],[32,2,32,3],[38,2,38,3],[39,2,39,3],[40,2,40,3],[34,2,34,3],[35,2,35,3]]}
[]
[null,"File Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"[\",CELL(\"filename\",G2))+1,FIND(\"]\",CELL(\"filename\",G2))-FIND(\"[\",CELL(\"filename\",G2))-1),\"Please save the file to show here\")","v":"Herefordshire CBA.xlsm"}]
[null,"Sheet Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"]\",CELL(\"filename\",G2))+1,255),\"Please save the file to show here\")","v":"Costs & Benefits Summary"}]
[null,"Description and Purpose of Sheet",null,null,null,null,"user input"]
[null,"Version number",null,null,null,null,{"$f":"Version_number","v":0.03}]
[]
[]
[null,"Output Summary"]
[]
[null,null,"Changes in workdays"]
[]
[null,null,null,"Number of work days"]
[null,null,null,"Before permit scheme","After permit scheme","Change in number of work days ","Percentage change in number of work days "]
[null,null,"Major",{"$f":"SUM(Inputs!M29:N29)","v":19982},{"$f":"D14+F14","v":17834.39},{"$f":"Calculations!D50+Calculations!E50","v":-2147.61},{"$f":"IF((D14)=0, \"\",F14/D14)","v":-0.1074772295065559}]
[null,null,"Standard",{"$f":"SUM(Inputs!M30:N30)","v":7616},{"$f":"D15+F15","v":7421.7979999999998},{"$f":"Calculations!D51+Calculations!E51","v":-194.202},{"$f":"IF((D15)=0, \"\",F15/D15)","v":-2.549921218487395E-2}]
[null,null,"Minor",{"$f":"SUM(Inputs!M31:N31)","v":4930},5183.6400000000003,{"$f":"Calculations!D52+Calculations!E52","v":253.64000000000001},5.1448275862068966E-2]
[null,null,"Immediate*",{"$f":"SUM(Inputs!M32:N32)","v":4952},5033.72,{"$f":"Calculations!D53+Calculations!E53","v":81.72},1.6502423263327948E-2]
[null,null,"Total",{"$f":"SUM(D14:D17)","v":37480},{"$f":"SUM(E14:E17)","v":35473.547999999995},{"$f":"SUM(F14:F17)","v":-2006.452},-5.3533938100320172E-2]
[]
[null,null,"Annual Cost and Benefits Summary (2018 prices)"]
[]
[null,null,"Costs",null,null,null,"Benefits"]
[null,null,"Cost incured by LAs (1st year):",null,{"$f":"E27+E24+E28","v":478947.84715322603},null,"Benefits for the society:",null,{"$f":"I24","v":1611491.0325750513}]
[null,null,"LHA set-up costs (only the 1st year)",null,{"$f":"Calculations!D114","v":29540.529367941232},null,"Benefit of reduced work duration**",null,{"$f":"ABS(Calculations!E79)","v":1611491.0325750513}]
[]
[null,null,"Cost incured LAs (from 2nd year)",null,{"$f":"SUM(E27:E29)","v":476605.11518563848},null,"Benefits for LAs:",null,{"$f":"I27","v":313418.33078351611}]
[null,null,"LHA running costs for SU permits",null,{"$f":"Calculations!D118","v":313418.33078351611},null,"Permit fee income",null,{"$f":"Calculations!D196","v":313418.33078351611}]
[null,null,"LHA running costs for LHA permits",null,{"$f":"Calculations!E196","v":135988.98700176866}]
[null,null,"LHA works promoter admin costs",null,{"$f":"Calculations!D126","v":27197.797400353735},null,"Total",null,{"$f":"I24+I27","v":1924909.3633585675}]
[]
[null,null,"Cost incured by SUs",null,{"$f":"E33+E32","v":376101.99694021931}]
[null,null,"Permit fee paid by SUs",null,{"$f":"Calculations!D201","v":313418.33078351611}]
[null,null,"Works promoter admin costs",null,{"$f":"Calculations!D124","v":62683.666156703228}]
[]
[null,null,"Total (1st year)",null,{"$f":"E23+E31","v":855049.84409344534}]
[null,null,"Total ( From 2nd year)",null,{"$f":"E26+E31","v":852707.11212585773}]
[]
[]
[null,null,"Net benefits (annual)",null,{"$f":"E41/10","v":919964.03233459522}]
[null,null,"Net benefits (1st year)",null,{"$f":"E39-E35","v":64914.188241149881}]
[null,null,"NPV over 10 years",null,{"$f":"Appraisal!D76","v":9199640.3233459517}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"END"]

{"sheet":"Data >>>","index":14,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":true,"merged":[]}

{"sheet":"Maximum Charges","index":15,"n_rows":14,"n_cols":5,"rows_kept":14,"cut":false,"hidden":true,"merged":[[4,2,4,4]]}
[]
[null,"Source:","Permit fee strcuture for street works and road works - Statutory Guidance for Highway Authority Permit Schemes. Section 6 page 29"]
[null,"Link: ","https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/465803/statutory-guide-for-permit-schemes.pdf"]
[]
[null,null,"Revised maximum fee structure for each category of works and for a hierarchy of main and minor roads - Road category refers to the reinstatement category of the street under the New Roads and Street Works Act 1991"]
[null,null,null,"Road Category 0-2 or Traffic-sensitive","Road Category 3-4 and non trafficsensitive"]
[null,null,"Provisional Advance (It is suggested this fee applies only where value has been added in processing the works)",150,75]
[null,null,"Major works – over 10 days and all major works requiring a traffic regulation order.",240,150]
[null,null,"Major works – 4 to 10 days",130,75]
[null,null,"Major works – up to 3 days",65,45]
[null,null,"Activity Standard",130,75]
[null,null,"Activity Minor",65,45]
[null,null,"Immediate Activity",60,40]
[null,null,"Permit Variation",45,35]

{"sheet":"Deflator","index":16,"n_rows":47,"n_cols":3,"rows_kept":47,"cut":false,"hidden":true,"merged":[]}
[]
[null,null,"GDP deflator1"]
[]
[]
[null,null,"(2018 = 100)"]
[null,"Year"]
[null,1990,55.222101983597227]
[null,1991,58.847340875334886]
[null,1992,60.643314519630501]
[null,1993,62.226537090564335]
[null,1994,62.976386128297278]
[null,1995,64.50386003834096]
[null,1996,67.137639529865822]
[null,1997,67.765747675619536]
[null,1998,68.547115390887569]
[null,1999,69.116323007997593]
[null,2000,70.52954171197662]
[null,2001,71.136571742128723]
[null,2002,72.720385288266371]
[null,2003,74.473907213756917]
[null,2004,76.343752758525369]
[null,2005,78.360160153831913]
[null,2006,80.736569944109434]
[null,2007,82.816802661424532]
[null,2008,85.168194501408578]
[null,2009,86.453762561370525]
[null,2010,87.810543133388308]
[null,2011,89.576968017494778]
[null,2012,90.974033399237058]
[null,2013,92.706969688462522]
[null,2014,94.299056887453105]
[null,2015,94.730468786214473]
[null,2016,96.596669983892937]
[null,2017,98.495867297115922]
[null,2018,100]
[null,2019,101.55539065104706]
[null,2020,103.22728434546413]
[null,2021,104.99261181009065]
[null,2022,106.9048399882868]
[null,2023,109.25674646802909]
[null,2024,111.66039489032575]
[null,2025,114.11692357791293]
[null,2026,116.62749589662702]
[null,2027,119.19330080635282]
[null,2028,121.81555342409258]
[null,2029,124.49549559942261]
[null,2030,127.2343965026099]

{"sheet":"Expected Change","index":17,"n_rows":16,"n_cols":5,"rows_kept":16,"cut":false,"hidden":true,"merged":[[6,2,6,4],[11,2,11,4]]}
[]
[null,"Source:","Evaluation of Street Works Permit Schemes 2017 Report - Table 3.9 Estimation of the impact of permit schemes"]
[null,"Link: ","https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/700502/permit-schemes-evaluation-report.pdf"]
[]
[null,null,"Table 3.9 Estimation of the impact of permit schemes"]
[null,null,"Type of road","Duration change","Impact*/day (2010 prices)"]
[null,null,"Highways"]
[null,null,"Major",-6.5000000000000002E-2,1335.78]
[null,null,"Standard",-7.6999999999999999E-2,408.49]
[null,null,"Minor",-8.8999999999999996E-2,329.21]
[null,null,"Immediate",0,224.59]
[null,null,"SU"]
[null,null,"Major",-0.13900000000000001,403.13]
[null,null,"Standard",-1.6E-2,165.3]
[null,null,"Minor",8.1000000000000003E-2,102.52]
[null,null,"Immediate",0.03,150.77000000000001]

{"sheet":"Income uplift for cancelled ","index":18,"n_rows":11,"n_cols":7,"rows_kept":11,"cut":false,"hidden":true,"merged":[]}
[]
[]
[null,"Source:","Evaluation of Street Works Permit Schemes Annexes 2017 - Figure 8.2 Proportion of permit fee income by final works status"]
[null,"Link: ","https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/700503/permit-schemes-evaluation-annexes.pdf"]
[]
[null,null,"Figure 8.2 - Proportion of permit fee income by final works status"]
[null,null,null,2014,2015,2016,"Average"]
[null,null,"Planned works",0.19400000000000001,0.17299999999999999,0.249,{"$f":"AVERAGE(D8:F8)","v":0.20533333333333334}]
[null,null,"Cancelled works",6.3E-2,6.0999999999999999E-2,0.05,{"$f":"AVERAGE(D9:F9)","v":5.7999999999999996E-2}]
[null,null,"Works Undertaken",0.74399999999999999,0.76600000000000001,0.70099999999999996,0.73699999999999999]
[null,null,"Income uplift for cancelled works ",{"$f":"D9/(D8+D10)","v":6.7164179104477612E-2},{"$f":"E9/(E8+E10)","v":6.4962726304579332E-2},5.2631578947368425E-2,{"$f":"G9/(G8+G10)","v":6.1549345596038196E-2}]

{"sheet":"HIDE >>>","index":19,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":true,"merged":[]}

{"sheet":"Assumption for SU promoter cost","index":20,"n_rows":9,"n_cols":7,"rows_kept":9,"cut":false,"hidden":true,"merged":[[8,2,13,10]]}
[]
[]
[null,"Source:","Evaluation of Street Works Permit Schemes Annexes 2017 - Page 67"]
[null,"Link: ","https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/700503/permit-schemes-evaluation-annexes.pdf"]
[]
[]
[null,null,"Uplift for works promoters additional administration costs ",null,null,null,0.2]
[]
[null,null,"\"Equally, establishing robust data relating to the cost to works promoters of additional administration and changes in working practices have been challenging. Based on a review of information provided by respondents to a survey undertaken as part of this study, supported by wider evidence derived from permit scheme evaluations, promoter administration costs have been estimated at 20% of the cost to the LHA. It has not been possible to estimate the costs of any changes to working practices implemented by promoters in response to directions or conditions being applied by LHAs.\""]

{"sheet":"Drop downs","index":21,"n_rows":28,"n_cols":2,"rows_kept":28,"cut":false,"hidden":true,"merged":[]}
[]
[null,"Preferred permit scheme structure "]
[null,"Select"]
[null,"Maximum"]
[null,"Custom "]
[]
[]
[null,"Change in the number of work days behavioural change "]
[null,"Select"]
[null,"Default "]
[null,"Custom "]
[]
[]
[null,"Price base year "]
[null,"Select"]
[null,2008]
[null,2009]
[null,2010]
[null,2011]
[null,2012]
[null,2013]
[null,2014]
[null,2015]
[null,2016]
[null,2017]
[null,2018]
[null,2019]
[null,2020]

{"sheet":"Outputs>>","index":22,"n_rows":11,"n_cols":3,"rows_kept":11,"cut":false,"hidden":true,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Outputs"]

{"sheet":"Charts>>","index":23,"n_rows":11,"n_cols":3,"rows_kept":11,"cut":false,"hidden":true,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Charts"]

{"sheet":"Chart Format Instructions","index":24,"n_rows":30,"n_cols":1,"rows_kept":30,"cut":false,"hidden":true,"merged":[[29,0,30,17]]}
["Chart formatting tool"]
[]
["This toolkit allows you to automatically format your charts and graphs to the DfT statistics house style. "]
["Once formatted, the font on the charts and text will be a suitable size for reading and printing when pasted in word or used in presentations."]
["This toolkit is not designed to change the chart type (e.g. if the original graph you have created is a bar chart the macro can change it into a DfT bar chart but not an area chart)."]
[]
["To use the toolkit:"]
[]
["Go to the worksheet containing the chart to be formatted and click on it"]
["Select the appropriate chart type from the DfT tab at the top of the spreadsheet"]
["Click the appropriate button"]
[]
["The chart will be moved to a new sheet, if not already the case, and formatted to a consistent style. "]
["If you want to keep the original chart - take a copy before running the macro"]
["You can reposition the location of any text boxes or labels if you have applied if needed but do not adjust any formatting."]
[]
["Formatting the chart"]
[]
["When you run the macro for either bar or line charts you will be presented with some formatting options for your axes"]
["For bar charts, you will be asked to change the increments for your value axes labels. It is recommended you have no more than 6 values showing."]
["For example, if you have a chart with values ranging from 0 to 1000, you should have increments of 200"]
["For line charts, you will be asked the same as above and also the increments for your category axes. Try to make it so your labels do no overlap"]
["For example, if you have a chart showing data over time from 1990 to 2014, you may want to reduce the frequency of your labels to show one every 3 years"]
["For line charts, you will also be given two different label options to use for your chart. Where possible, move the text box labels and position them on the chart next to the lines and delete the legend above the chart"]
["Where this isn't possible, delete the individual text boxes and leave the legend above the chart"]
[]
["Exporting the chart"]
[]
["When copying and exporting the chart to another location e.g. word, the chart will look better if copied from it's own worksheet (i.e. how the macro places it)."]
["If you do embed it in a worksheet, the chart is set up to retain the original font size, even if the chart is resized. This is to ensure the font is still readable when copied and pasted elsewhere. Please do not adjust the font manaully - it will become small and unreadable when pasted elsewhere."]

{"sheet":"Template sheet>>","index":25,"n_rows":11,"n_cols":3,"rows_kept":11,"cut":false,"hidden":true,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Sheet for use in template"]

{"sheet":"X- XXX","index":26,"n_rows":35,"n_cols":7,"rows_kept":35,"cut":false,"hidden":true,"merged":[]}
[]
[null,"File Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"[\",CELL(\"filename\",G2))+1,FIND(\"]\",CELL(\"filename\",G2))-FIND(\"[\",CELL(\"filename\",G2))-1),\"Please save the file to show here\")","v":"Herefordshire CBA.xlsm"}]
[null,"Sheet Name",null,null,null,null,{"$f":"IFERROR(MID(CELL(\"filename\",G2),FIND(\"]\",CELL(\"filename\",G2))+1,255),\"Please save the file to show here\")","v":"X- XXX"}]
[null,"Description and Purpose of Sheet",null,null,null,null,"user input"]
[null,"Version number",null,null,null,null,{"$f":"Version_number","v":0.03}]
[]
[]
[null,"Header 1"]
[]
[null,null,"Header 2"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"END"]