{"text_format":"1","workbook":"xlsx","n_sheets":10,"sheets":["Disclaimer","Navigation","Linked Sheet","A&E SQL Code","A&E SQL Output","Price Adjustments","2015-16 A&E Tariff (s118)","Manual Adjustment Requests","Calculation","Manual Adjustments"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":6,"defined_names":128,"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":"Disclaimer","index":0,"n_rows":8,"n_cols":2,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[null,"2016-17 National tariff proposals: calculation model A&E"]

{"sheet":"Navigation","index":1,"n_rows":12,"n_cols":5,"rows_kept":12,"cut":false,"hidden":false,"merged":[[7,1,8,1],[5,1,6,1]]}
[]
[null,"Steps","Worksheet / Intermediary steps","Overview","Data source"]
[null,"1 >> A&E Prices ","Linked Sheet","This worksheet has the TED prices for A&E "]
[null,"2 >> SQL A&E code","A&E SQL code","This worksheet shows the A&E SQL code used to generate A&E SQL Output","Monitor SQL"]
[null,"3 >> SQL inputs","A&E SQL Output","This worksheet has the total cost and activities from SQL","Monitor SQL"]
[null,"4 >> Non SQL inputs","Price Adjustments","A variety of factors to adjust the prices. Includes: inflation, efficiency, CNST and quantum adjustment","Mixed (Monitor & External)"]
[null,null,"Manual Adjustment Requests","This worksheet displays the changes to tariffs agreed by Monitor after engagement with expert working groups"]
[null,"5 >> Processing","Calculation","This worksheet calculates the prices based on the A&E SQL output and applies factors to bring into comparison tariff year","Monitor SQL"]
[null,null,"Manual Adjustments","This worksheet implements manual adjustments to tariffs agreed by Monitor and calculates final prices","Mixed (Monitor & External)"]
[]
[]
[null,"Reference","2015-16 A&E Tariff (s118)","This worksheet has the s118 consultation 2015/16 published prices."]

{"sheet":"Linked Sheet","index":2,"n_rows":16,"n_cols":4,"rows_kept":16,"cut":false,"hidden":false,"merged":[[2,2,2,3]]}
["2016-17 tariff - A&E"]
["Home"]
[null,null,"Tariff (£)"]
["HRG code","HRG name","Type 1 and 2 Departments","Type 3 Departments"]
["VB01Z","Emergency Medicine, Any Investigation with Category 5 Treatment",{"$f":"VLOOKUP('Linked Sheet'!A5,'Manual Adjustments'!B:Q,15,FALSE)","v":274.44021858981444},{"$f":"VLOOKUP('Linked Sheet'!A5,'Manual Adjustments'!B:Q,16,FALSE)","v":63.813174964607526}]
["VB02Z","Emergency Medicine, Category 3 Investigation with Category 4 Treatment",{"$f":"VLOOKUP('Linked Sheet'!A6,'Manual Adjustments'!B:Q,15,FALSE)","v":274.44021858981444},{"$f":"VLOOKUP('Linked Sheet'!A6,'Manual Adjustments'!B:Q,16,FALSE)","v":63.813174964607526}]
["VB03Z","Emergency Medicine, Category 3 Investigation with Category 1-3 Treatment",{"$f":"VLOOKUP('Linked Sheet'!A7,'Manual Adjustments'!B:Q,15,FALSE)","v":201.61569243779155},{"$f":"VLOOKUP('Linked Sheet'!A7,'Manual Adjustments'!B:Q,16,FALSE)","v":63.813174964607526}]
["VB04Z","Emergency Medicine, Category 2 Investigation with Category 4 Treatment",{"$f":"VLOOKUP('Linked Sheet'!A8,'Manual Adjustments'!B:Q,15,FALSE)","v":186.1666638004333},{"$f":"VLOOKUP('Linked Sheet'!A8,'Manual Adjustments'!B:Q,16,FALSE)","v":63.813174964607526}]
["VB05Z","Emergency Medicine, Category 2 Investigation with Category 3 Treatment",{"$f":"VLOOKUP('Linked Sheet'!A9,'Manual Adjustments'!B:Q,15,FALSE)","v":149.41398720173493},{"$f":"VLOOKUP('Linked Sheet'!A9,'Manual Adjustments'!B:Q,16,FALSE)","v":63.813174964607526}]
["VB06Z","Emergency Medicine, Category 1 Investigation with Category 3-4 Treatment",{"$f":"VLOOKUP('Linked Sheet'!A10,'Manual Adjustments'!B:Q,15,FALSE)","v":108.13037126018084},{"$f":"VLOOKUP('Linked Sheet'!A10,'Manual Adjustments'!B:Q,16,FALSE)","v":63.813174964607526}]
["VB07Z","Emergency Medicine, Category 2 Investigation with Category 2 Treatment",{"$f":"VLOOKUP('Linked Sheet'!A11,'Manual Adjustments'!B:Q,15,FALSE)","v":134.0580142887527},{"$f":"VLOOKUP('Linked Sheet'!A11,'Manual Adjustments'!B:Q,16,FALSE)","v":63.813174964607526}]
["VB08Z","Emergency Medicine, Category 2 Investigation with Category 1 Treatment",{"$f":"VLOOKUP('Linked Sheet'!A12,'Manual Adjustments'!B:Q,15,FALSE)","v":123.67093436058747},{"$f":"VLOOKUP('Linked Sheet'!A12,'Manual Adjustments'!B:Q,16,FALSE)","v":63.813174964607526}]
["VB09Z","Emergency Medicine, Category 1 Investigation with Category 1-2 Treatment",{"$f":"VLOOKUP('Linked Sheet'!A13,'Manual Adjustments'!B:Q,15,FALSE)","v":86.69850108227736},{"$f":"VLOOKUP('Linked Sheet'!A13,'Manual Adjustments'!B:Q,16,FALSE)","v":63.813174964607526}]
["VB10Z","Emergency Medicine, Dental Care",{"$f":"VLOOKUP('Linked Sheet'!A14,'Manual Adjustments'!B:Q,15,FALSE)","v":64.985899694578038},{"$f":"VLOOKUP('Linked Sheet'!A14,'Manual Adjustments'!B:Q,16,FALSE)","v":63.813174964607526}]
["VB11Z","Emergency Medicine, No Investigation with No Significant Treatment",{"$f":"VLOOKUP('Linked Sheet'!A15,'Manual Adjustments'!B:Q,15,FALSE)","v":63.813174964607526},{"$f":"VLOOKUP('Linked Sheet'!A15,'Manual Adjustments'!B:Q,16,FALSE)","v":63.813174964607526}]
["VB99Z","Emergency Medicine, Patient Dead On Arrival",{"$f":"VLOOKUP('Linked Sheet'!A16,'Manual Adjustments'!B:Q,15,FALSE)","v":86.69850108227736},{"$f":"VLOOKUP('Linked Sheet'!A16,'Manual Adjustments'!B:Q,16,FALSE)","v":63.813174964607526}]

{"sheet":"A&E SQL Code","index":3,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["Home"]

{"sheet":"A&E SQL Output","index":4,"n_rows":22,"n_cols":4,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
["Home"]
[null,"A&E SQL Output"]
[]
[null,"Final A&E Total Activity and Cost Draft 13"]
[]
[]
[]
[]
[]
[null,"HRG","SumOfTotal Activity","SumOfTotal Cost"]
[null,"VB01Z",35309,10472809.19896874]
[null,"VB02Z",132813,39463545.33036197]
[null,"VB03Z",514348,112234381.46726754]
[null,"VB04Z",889265,179175096.02510938]
[null,"VB05Z",388207,62776796.195426948]
[null,"VB06Z",493592,57764409.298863024]
[null,"VB07Z",1755207,254662884.99818048]
[null,"VB08Z",4112656,550471142.91367149]
[null,"VB09Z",3941735,369865261.16630399]
[null,"VB10Z",10938,791590.30725656135]
[null,"VB11Z",4543052,322850507.72852898]
[null,"Total",{"$f":"SUBTOTAL(109,AnEActivityCost_DatabaseOne[SumOfTotal Activity])","v":16817122},{"$f":"SUBTOTAL(109,AnEActivityCost_DatabaseOne[SumOfTotal Cost])","v":1960528424.6299393}]

{"sheet":"Price Adjustments","index":5,"n_rows":96,"n_cols":13,"rows_kept":96,"cut":false,"hidden":false,"merged":[[87,1,87,3],[9,9,9,12],[9,1,9,7],[1,1,1,7]]}
["Home"]
[null,"Table 1 :  Price Adjustments : Inflation and Efficiency  for all models "]
[null,"Adjustment Type","2013/14","2014/15","2015/16","2016/17","Total uplift up to 15/16 price levels","Source "]
[null,"Inflation",2.7E-2,2.5000000000000001E-2,1.92529689878824E-2,0,{"$f":"(1+D4)*(1+E4)-1","v":4.4734293212579379E-2}]
[null,"Efficiency*",-0.04,-0.04,-3.5000000000000003E-2,0,{"$f":"(1+D5)*(1+E5)-1","v":-7.360000000000011E-2}]
[null,"Inflation and Efficiency (total adjustment)",-1.3000000000000001E-2,-1.4999999999999999E-2,-1.6420884926693469E-2,0,{"$f":"(1+D6)*(1+E6)-1","v":-3.1174571652793026E-2}]
[null,"* In 2015-16 the efficiency assumption of 4% was reduced by 0.2%",null,null,null,null,null,null,null,"Scaling factor"]
[null,"We use a chain linking the post adjustments for joining the efficiency and inflation adjustment together from 2015/16 onwards (this is a slight methodological change from the 13/14 DH method).",null,null,null,null,null,null,null,0.92069552180742797]
[]
[null,"Table 2:  Price Adjustments : CNST increase / decrease",null,null,null,null,null,null,null,"Table 3: Quantum reconciliation (QR) for APC and OPROC"]
[null,"Sub-chapter","2013/14","2014/15","2015/16","2016/17","Total uplift up to 16/17 price levels","Source ",null,"Sub-chapter","QR1 (RC TARGET)","QR1 + SCALING","SMOOTHING ADJUSTMENT"]
[null,"AA",2.7120600000000001E-3,4.0331200000000003E-3,1.0177220000000001E-2,0,{"$f":"(1+D12)*(1+E12)-1","v":1.4251385949526352E-2},null,null,"AA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"AB",4.6639100000000003E-3,2.5168999999999999E-3,6.6765899999999996E-3,0,9.2102943093710365E-3,null,null,"AB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"BZ",1.71356E-3,2.29656E-3,1.0261259999999999E-2,0,1.2581385599265582E-2,null,null,"BZ",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"CA",0,0,0,0,0,null,null,"CA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"CB",0,0,0,0,0,null,null,"CB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"CD",0,0,0,0,0,null,null,"CD",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"DZ",2.0988299999999999E-3,1.128E-5,1.97934E-3,0,1.9906423269553386E-3,null,null,"DZ",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"EA",6.8376000000000005E-4,8.7018999999999996E-4,3.78956E-3,0,4.6630476372164775E-3,null,null,"EA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"EB",9.0147000000000003E-4,7.2782000000000001E-4,2.8193300000000001E-3,0,3.5492019647604955E-3,null,null,"EB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"EC",0,0,0,0,0,null,null,"EC",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"FZ",6.9434299999999996E-3,2.0934299999999999E-3,9.1560400000000007E-3,0,1.1268637528817127E-2,null,null,"FZ",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"GA",8.3929999999999994E-3,3.2548799999999999E-3,1.18298E-2,0,1.5123184579423965E-2,null,null,"GA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"GB",3.9857699999999996E-3,1.46704E-3,5.3411600000000002E-3,0,6.8160356953663737E-3,null,null,"GB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"GC",6.1396799999999998E-3,1.7811400000000001E-3,9.45322E-3,0,1.1251197508270749E-2,null,null,"GC",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"HA",1.145872E-2,3.9247600000000002E-3,1.1981489999999999E-2,0,1.5953274472692458E-2,null,null,"HA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"HB",1.060436E-2,3.65578E-3,1.40532E-2,0,1.7760355407496098E-2,null,null,"HB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"HC",6.3163999999999998E-3,5.24217E-3,1.4706479999999999E-2,0,2.0025743868261614E-2,null,null,"HC",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"HD",4.2221799999999999E-3,1.2462000000000001E-4,6.6808199999999996E-3,0,6.8062725637882959E-3,null,null,"HD",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"HR",6.1125299999999997E-3,2.0507300000000002E-3,1.485676E-2,0,1.6937957203434717E-2,null,null,"HR",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"JA",6.4304100000000001E-3,1.27943E-3,1.0111719999999999E-2,0,1.1404087237919747E-2,null,null,"JA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"JB",0,0,0,0,0,null,null,"JB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"JC",3.6510800000000001E-3,1.78039E-3,8.9561699999999994E-3,0,1.0752505475506213E-2,null,null,"JC",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"JD",2.673E-3,1.0486E-3,4.9345500000000002E-3,0,5.9883243691301047E-3,null,null,"JD",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"KA",3.8450400000000001E-3,9.4410999999999996E-4,7.5987099999999998E-3,0,8.5499940180979728E-3,null,null,"KA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"KB",3.5167E-4,6.6025000000000003E-4,2.7523500000000002E-3,0,3.4144172390873084E-3,null,null,"KB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"KC",1.0156500000000001E-3,1.2352000000000001E-4,2.4360800000000002E-3,0,2.5599009046017862E-3,null,null,"KC",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"LA",8.7421000000000005E-4,5.1491999999999998E-4,2.3954200000000001E-3,0,2.9115734496665269E-3,null,null,"LA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"LB",4.5863400000000004E-3,1.1234999999999999E-3,5.6908599999999998E-3,0,6.8207536812101566E-3,null,null,"LB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"LD",0,0,0,0,0,null,null,"LD",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"LE",0,0,0,0,0,null,null,"LE",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"MA",5.160373E-2,4.0632200000000002E-3,2.111675E-2,0,2.5265772000935049E-2,null,null,"MA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"MB",4.6619290000000001E-2,1.50091E-3,1.6660899999999999E-2,0,1.8186816511418957E-2,null,null,"MB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"MC",0,0,0,0,0,null,null,"MC",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"NZ",-8.9280200000000004E-2,2.8267569999999999E-2,0,0,2.8267569999999909E-2,null,null,"NZ",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PB",0,0,0,0,0,null,null,"PB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PC",0,0,0,0,0,null,null,"PC",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PD",0,0,0,0,0,null,null,"PD",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PE",0,0,0,0,0,null,null,"PE",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PF",0,0,0,0,0,null,null,"PF",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PG",0,0,0,0,0,null,null,"PG",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PH",0,0,0,0,0,null,null,"PH",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PJ",0,0,0,0,0,null,null,"PJ",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PK",0,0,0,0,0,null,null,"PK",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PL",0,0,0,0,0,null,null,"PL",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PM",0,0,0,0,0,null,null,"PM",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PN",0,0,0,0,0,null,null,"PN",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PP",0,0,0,0,0,null,null,"PP",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PR",0,0,0,0,0,null,null,"PR",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PT",0,0,0,0,0,null,null,"PT",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PV",0,0,0,0,0,null,null,"PV",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PW",0,0,0,0,0,null,null,"PW",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"PX",0,0,0,0,0,null,null,"PX",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"RA",0,0,0,0,0,null,null,"RA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"SA",6.3409999999999996E-4,-5.5466000000000001E-4,4.7663999999999996E-3,0,4.2090962685761291E-3,null,null,"SA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"SB",0,0,0,0,0,null,null,"SB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"SC",0,0,0,0,0,null,null,"SC",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"SD",0,0,0,0,0,null,null,"SD",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"UZ",0,0,0,0,0,null,null,"UZ",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"VA",1.3678610000000001E-2,3.1799599999999999E-3,1.1581610000000001E-2,0,1.4798399056535461E-2,null,null,"VA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"VB",1.153917E-2,7.3352599999999997E-3,2.1468190000000002E-2,0,2.896092475537948E-2,null,null,"VB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"VC",0,0,0,0,0,null,null,"VC",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"WA",1.3777E-4,1.0210200000000001E-3,5.2826599999999998E-3,0,6.3090737015132348E-3,null,null,"WA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"WD",0,0,0,0,0,null,null,"WD",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"WF",0,0,0,0,0,null,null,"WF",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"XA",0,0,0,0,{"$f":"(1+D76)*(1+E76)-1","v":0},null,null,"XA",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"XB",0,0,0,0,0,null,null,"XB",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"XC",0,0,0,0,0,null,null,"XC",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"XD",0,0,0,0,0,null,null,"XD",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"YQ",0,0,0,0,0,null,null,"YQ",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"YR",0,0,0,0,0,null,null,"YR",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"YZ",0,0,0,0,0,null,null,"YZ",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"ZZ",0,0,0,0,0,null,null,"ZZ",-8.3152173705125492E-3,-8.6960261588458376E-2]
[null,"Maternity",0,2.8299999999999999E-2,0.11086894010165449,0,0.14230653110653124]
[]
[null,"Note: Maternity is delivery element only."]
[]
[null," Table 4: Quantum Reconciliation (QR1) for the rest of the models"]
[null,"Model","QR1 (RC TARGET)","QR1 + SCALING"]
[null,"Outpatient Attendances (OPATT)",1.1702913986164E-2,-6.8529657693413348E-2]
[null,"Accident and Emergency (A&E)",6.68831432242611E-3,-7.3146577147473857E-2]
[null,"Unbundled services",3.6740734670521699E-3,3.6740734670521746E-3]
[null,"Best Practice Tariffs",0,0]
[null,"Maternity pathway",0,0]
[null,"Other mandatory prices",0,0]
[null,"Non-mandatory Prices",0,0]

{"sheet":"2015-16 A&E Tariff (s118)","index":6,"n_rows":19,"n_cols":4,"rows_kept":19,"cut":false,"hidden":false,"merged":[[4,2,4,3]]}
["Home"]
[]
["2015-16 tariff - A&E (s.118)",null,"Source: ","https://www.gov.uk/government/uploads/system/uploads/attachment_data/file/379578/Annex_5a.xlsx"]
[]
[null,null,"Tariff (£)"]
["HRG code","HRG name","Type 1 and 2 Departments","Type 3 Departments"]
["VB01Z","Emergency Medicine, Any Investigation with Category 5 Treatment",239,56]
["VB02Z","Emergency Medicine, Category 3 Investigation with Category 4 Treatment",226,56]
["VB03Z","Emergency Medicine, Category 3 Investigation with Category 1-3 Treatment",192,56]
["VB04Z","Emergency Medicine, Category 2 Investigation with Category 4 Treatment",168,56]
["VB05Z","Emergency Medicine, Category 2 Investigation with Category 3 Treatment",146,56]
["VB06Z","Emergency Medicine, Category 1 Investigation with Category 3-4 Treatment",102,56]
["VB07Z","Emergency Medicine, Category 2 Investigation with Category 2 Treatment",128,56]
["VB08Z","Emergency Medicine, Category 2 Investigation with Category 1 Treatment",116,56]
["VB09Z","Emergency Medicine, Category 1 Investigation with Category 1-2 Treatment",81,56]
["VB10Z","Emergency Medicine, Dental Care",56,56]
["VB11Z","Emergency Medicine, No Investigation with No Significant Treatment",56,56]
[]
["Please note that patients who are dead on arrival (DOA) attract the tariff for VB09Z."]

{"sheet":"Manual Adjustment Requests","index":7,"n_rows":15,"n_cols":6,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["Home"]
["A&E"]
["HRG code","HRG Descriptions","NHS England comments","Summary of comments we received","Our assessment of comments made","Precise implementation instructions for manual adjustments (if applicable)"]
["VB01Z","Emergency Medicine, Any Investigation with Category 5 Treatment",null,"VB01Z is most resource intensive. Equalise Type 1 and 2 with same for VB02Z. Why the big increase from previous year? Change in reference costs or change in calculation methodology?","The price should be at least equal to VB02Z","Equalise VB01Z and VB02Z on clinical and statistical grounds). This small (£1) difference between these HRGs will be reviewed in the future iterations of the tariff. "]
["VB02Z","Emergency Medicine, Category 3 Investigation with Category 4 Treatment",null,"VB01Z is most resource intensive. Equalise Type 1 and 2 with same for VB02Z. Why the big increase from previous year? Change in reference costs or change in calculation methodology?","Clinical expert advise similar resource required for VB01Z than VB02Z, therefore Type 1 and 2 prices should be equalised. The small residual difference has been caused by minor adjustments to post average costs.","Equalise VB01Z and VB02Z on clinical and statistical grounds) will be within the error margins "]
["VB03Z","Emergency Medicine, Category 3 Investigation with Category 1-3 Treatment",null,"Why the big increase from previous year? Change in reference costs or change in calculation methodology?","Leave. There is currently no policy related to smoothing A&E when large changes occur. This may need to be reviewed in future iterations of the tariff. "]
["VB04Z","Emergency Medicine, Category 2 Investigation with Category 4 Treatment",null,"Why the big increase from previous year? Change in reference costs or change in calculation methodology?","Leave. There is currently no policy related to smoothing A&E when large changes occur. This may need to be reviewed in future iterations of the tariff. "]
["VB05Z","Emergency Medicine, Category 2 Investigation with Category 3 Treatment",null,"Why the big increase from previous year? Change in reference costs or change in calculation methodology?","Leave. There is currently no policy related to smoothing A&E when large changes occur. This may need to be reviewed in future iterations of the tariff. "]
["VB06Z","Emergency Medicine, Category 1 Investigation with Category 3-4 Treatment",null,"Why the big increase from previous year? Change in reference costs or change in calculation methodology?","Leave. There is currently no policy related to smoothing A&E when large changes occur. This may need to be reviewed in future iterations of the tariff. "]
["VB07Z","Emergency Medicine, Category 2 Investigation with Category 2 Treatment",null,"Why the big increase from previous year? Change in reference costs or change in calculation methodology?","Leave. There is currently no policy related to smoothing A&E when large changes occur. This may need to be reviewed in future iterations of the tariff. "]
["VB08Z","Emergency Medicine, Category 2 Investigation with Category 1 Treatment",null,"Why the big increase from previous year? Change in reference costs or change in calculation methodology?","Leave. There is currently no policy related to smoothing A&E when large changes occur. This may need to be reviewed in future iterations of the tariff. "]
["VB09Z","Emergency Medicine, Category 1 Investigation with Category 1-2 Treatment",null,"Why the big increase from previous year? Change in reference costs or change in calculation methodology?","Leave. There is currently no policy related to smoothing A&E when large changes occur. This may need to be reviewed in future iterations of the tariff. "]
["VB10Z","Emergency Medicine, Dental Care",null,"Why the big increase from previous year? Change in reference costs or change in calculation methodology?","Leave. There is currently no policy related to smoothing A&E when large changes occur. This may need to be reviewed in future iterations of the tariff. "]
["VB11Z","Emergency Medicine, No Investigation with No Significant Treatment",null,"Why the big increase from previous year? Change in reference costs or change in calculation methodology?","Leave. There is currently no policy related to smoothing A&E when large changes occur. This may need to be reviewed in future iterations of the tariff. "]
["VB99Z","Emergency Medicine, Patient Dead On Arrival"]

{"sheet":"Calculation","index":8,"n_rows":24,"n_cols":18,"rows_kept":24,"cut":false,"hidden":false,"merged":[[11,9,11,11],[11,4,11,5]]}
[null,null,"Home"]
[null,null,"A&E Price Calculation"]
[null,null,"Individual adjustment factors"]
[]
[null,null,"Adjustment Type",null,{"$f":"'Price Adjustments'!D3","v":"2014/15"},{"$f":"'Price Adjustments'!E3","v":"2015/16"}]
[null,null,"CNST (Subchapter VB)",null,{"$f":"'Price Adjustments'!D71","v":7.3352599999999997E-3},{"$f":"'Price Adjustments'!E71","v":2.1468190000000002E-2}]
[null,null,"Inflation",null,{"$f":"'Price Adjustments'!D4","v":2.5000000000000001E-2},{"$f":"'Price Adjustments'!E4","v":1.92529689878824E-2}]
[null,null,"Efficiency",null,{"$f":"'Price Adjustments'!D5","v":-0.04},{"$f":"'Price Adjustments'!E5","v":-3.5000000000000003E-2}]
[null,null,"Inflation and Efficiency",null,{"$f":"SUM(E7:E8)","v":-1.4999999999999999E-2},{"$f":"(1+F7)*(1+F8)-1","v":-1.6420884926693469E-2}]
[]
[null,null,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16]
[null,null,"Type 1 and Type 2 departments",null,"Data Pull",null,null,"Calculation",null,"Additional Adjustments","Additional Adjustments"]
[null,null,"HRG code","Description","Total Activity","Total Cost\n(£)",null,"Unit Cost\n(£)",null,"CNST\n2014/15 and 2015/16","Inflation and Efficiency\n2014/15 and 2015/16","Total Adjustment",null,"Modelled Tariff\n(£)",null,"Quantum Adjustment (QR1)",null,"Modelled Tariff with QR1\n(£)"]
[null,null,"VB01Z","Emergency Medicine, Any Investigation with Category 5 Treatment",{"$f":"VLOOKUP(C14,'A&E SQL Output'!$B$11:$D$22,2,FALSE)","v":35309},{"$f":"VLOOKUP(C14,'A&E SQL Output'!$B$11:$D$22,3,FALSE)","v":10472809.19896874},null,{"$f":"F14/E14","v":296.60452572909855},null,{"$f":"(1+$E$6)*(1+$F$6)-1","v":2.896092475537948E-2},{"$f":"(1+$E$9)*(1+$F$9)-1","v":-3.1174571652793026E-2},{"$f":"(1+J14)*(1+K14)-1","v":-3.1164913213312939E-3},null,{"$f":"H14*(1+L14)","v":295.68016029879624},null,{"$f":"QR1_AnE","v":-7.3146577147473857E-2},null,{"$f":"N14*(1+P14)","v":274.05216864252293}]
[null,null,"VB02Z","Emergency Medicine, Category 3 Investigation with Category 4 Treatment",{"$f":"VLOOKUP(C15,'A&E SQL Output'!$B$11:$D$22,2,FALSE)","v":132813},{"$f":"VLOOKUP(C15,'A&E SQL Output'!$B$11:$D$22,3,FALSE)","v":39463545.33036197},null,{"$f":"F15/E15","v":297.13616385716739},null,2.896092475537948E-2,{"$f":"(1+$E$9)*(1+$F$9)-1","v":-3.1174571652793026E-2},{"$f":"(1+J15)*(1+K15)-1","v":-3.1164913213312939E-3},null,{"$f":"H15*(1+L15)","v":296.21014158125286},null,-7.3146577147473857E-2,null,274.54338360821561]
[null,null,"VB03Z","Emergency Medicine, Category 3 Investigation with Category 1-3 Treatment",{"$f":"VLOOKUP(C16,'A&E SQL Output'!$B$11:$D$22,2,FALSE)","v":514348},{"$f":"VLOOKUP(C16,'A&E SQL Output'!$B$11:$D$22,3,FALSE)","v":112234381.46726754},null,218.20709221629625,null,2.896092475537948E-2,-3.1174571652793026E-2,-3.1164913213312939E-3,null,217.52705170715123,null,-7.3146577147473857E-2,null,201.61569243779155]
[null,null,"VB04Z","Emergency Medicine, Category 2 Investigation with Category 4 Treatment",{"$f":"VLOOKUP(C17,'A&E SQL Output'!$B$11:$D$22,2,FALSE)","v":889265},{"$f":"VLOOKUP(C17,'A&E SQL Output'!$B$11:$D$22,3,FALSE)","v":179175096.02510938},null,201.48672895605853,null,2.896092475537948E-2,-3.1174571652793026E-2,-3.1164913213312939E-3,null,200.85879731390355,null,-7.3146577147473857E-2,null,186.1666638004333]
[null,null,"VB05Z","Emergency Medicine, Category 2 Investigation with Category 3 Treatment",{"$f":"VLOOKUP(C18,'A&E SQL Output'!$B$11:$D$22,2,FALSE)","v":388207},{"$f":"VLOOKUP(C18,'A&E SQL Output'!$B$11:$D$22,3,FALSE)","v":62776796.195426948},null,161.7095935813289,null,2.896092475537948E-2,-3.1174571652793026E-2,-3.1164913213312939E-3,null,161.20562703635667,null,-7.3146577147473857E-2,null,149.41398720173493]
[null,null,"VB06Z","Emergency Medicine, Category 1 Investigation with Category 3-4 Treatment",{"$f":"VLOOKUP(C19,'A&E SQL Output'!$B$11:$D$22,2,FALSE)","v":493592},{"$f":"VLOOKUP(C19,'A&E SQL Output'!$B$11:$D$22,3,FALSE)","v":57764409.298863024},null,117.02865787707869,null,2.896092475537948E-2,-3.1174571652793026E-2,-3.1164913213312939E-3,null,116.66393908045772,null,-7.3146577147473857E-2,null,108.13037126018084]
[null,null,"VB07Z","Emergency Medicine, Category 2 Investigation with Category 2 Treatment",{"$f":"VLOOKUP(C20,'A&E SQL Output'!$B$11:$D$22,2,FALSE)","v":1755207},{"$f":"VLOOKUP(C20,'A&E SQL Output'!$B$11:$D$22,3,FALSE)","v":254662884.99818048},null,145.08994380616102,null,2.896092475537948E-2,-3.1174571652793026E-2,-3.1164913213312939E-3,null,144.63777225547668,null,-7.3146577147473857E-2,null,134.0580142887527]
[null,null,"VB08Z","Emergency Medicine, Category 2 Investigation with Category 1 Treatment",{"$f":"VLOOKUP(C21,'A&E SQL Output'!$B$11:$D$22,2,FALSE)","v":4112656},{"$f":"VLOOKUP(C21,'A&E SQL Output'!$B$11:$D$22,3,FALSE)","v":550471142.91367149},null,133.8480881731104,null,2.896092475537948E-2,-3.1174571652793026E-2,-3.1164913213312939E-3,null,133.4309517679421,null,-7.3146577147473857E-2,null,123.67093436058747]
[null,null,"VB09Z","Emergency Medicine, Category 1 Investigation with Category 1-2 Treatment",{"$f":"VLOOKUP(C22,'A&E SQL Output'!$B$11:$D$22,2,FALSE)","v":3941735},{"$f":"VLOOKUP(C22,'A&E SQL Output'!$B$11:$D$22,3,FALSE)","v":369865261.16630399},null,93.833111857165434,null,2.896092475537948E-2,-3.1174571652793026E-2,-3.1164913213312939E-3,null,93.540681778409066,null,-7.3146577147473857E-2,null,86.69850108227736]
[null,null,"VB10Z","Emergency Medicine, Dental Care",{"$f":"VLOOKUP(C23,'A&E SQL Output'!$B$11:$D$22,2,FALSE)","v":10938},{"$f":"VLOOKUP(C23,'A&E SQL Output'!$B$11:$D$22,3,FALSE)","v":791590.30725656135},null,{"$f":"F23/E23","v":72.370662576024998},null,0,-3.1174571652793026E-2,-3.1174571652793026E-2,null,70.114538169988606,null,-7.3146577147473857E-2,null,64.985899694578038]
[null,null,"VB11Z","Emergency Medicine, No Investigation with No Significant Treatment",{"$f":"VLOOKUP(C24,'A&E SQL Output'!$B$11:$D$22,2,FALSE)","v":4543052},{"$f":"VLOOKUP(C24,'A&E SQL Output'!$B$11:$D$22,3,FALSE)","v":322850507.72852898},null,{"$f":"F24/E24","v":71.064673644177745},null,0,-3.1174571652793026E-2,-3.1174571652793026E-2,null,68.849262883674967,null,-7.3146577147473857E-2,null,63.813174964607526]

{"sheet":"Manual Adjustments","index":9,"n_rows":18,"n_cols":17,"rows_kept":18,"cut":false,"hidden":false,"merged":[[2,3,2,4],[2,8,2,9],[2,10,2,16]]}
[null,"2016-17 tariff - accident & emergency - Manual Adjustments"]
[null,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16]
[null,"Home",null,"2015/16 Tariff (s.118)",null,"2016/17 Tariff",null,"Activity","Manual Adjustments",null,"Final Prices"]
[null,"HRG code","HRG name","Type 1 and 2 Departments\n(£)","Type 3 Departments\n(£)","Type 1 and 2 Departments\n(£)",null,"Type 1 and 2 Departments","Adjustment Request","Adjusted Price\n(£)","Price Decision","Chosen Price\n(£)","Chosen Price with QR2\n(£)","Inflation and Efficiency (total adjustment) 2016/17","CNST\n2016/17","Type 1 and 2 Departments\n(£)","Type 3 Departments\n(£)"]
[null,"VB01Z","Emergency Medicine, Any Investigation with Category 5 Treatment",{"$f":"VLOOKUP($B5,AandE_1516_Tariff,3, FALSE)","v":239},{"$f":"VLOOKUP($B5,AandE_1516_Tariff,4, FALSE)","v":56},{"$f":"VLOOKUP($B5,Calculation!$C$14:$R$24,16,FALSE)","v":274.05216864252293},null,{"$f":"LOOKUP(B5,AnEActivityCost_DatabaseOne[HRG],AnEActivityCost_DatabaseOne[SumOfTotal Activity])","v":35309},"Equalise VB01Z and VB02Z on clinical and statistical grounds) will be within the error margins ",{"$f":"SUMPRODUCT($F$5:$F$6,$H$5:$H$6)/SUM($H$5:$H$6)","v":274.44021858981444},"Adjusted",{"$f":"IF(K5=\"Modelled\",F5,IF(K5=\"Adjusted\",J5,\"Error\"))","v":274.44021858981444},{"$f":"+L5*$L$18","v":274.44021858981444},{"$f":"+'Price Adjustments'!$F$6","v":0},{"$f":"+'Price Adjustments'!$F$71","v":0},{"$f":"M5*(1+N5)*(1+O5)","v":274.44021858981444},{"$f":"MIN($P$5:$P$13,$P$15:$P$16)","v":63.813174964607526}]
[null,"VB02Z","Emergency Medicine, Category 3 Investigation with Category 4 Treatment",226,56,{"$f":"VLOOKUP($B6,Calculation!$C$14:$R$24,16,FALSE)","v":274.54338360821561},null,{"$f":"LOOKUP(B6,AnEActivityCost_DatabaseOne[HRG],AnEActivityCost_DatabaseOne[SumOfTotal Activity])","v":132813},"Equalise VB01Z and VB02Z on clinical and statistical grounds) will be within the error margins ",{"$f":"SUMPRODUCT($F$5:$F$6,$H$5:$H$6)/SUM($H$5:$H$6)","v":274.44021858981444},"Adjusted",{"$f":"IF(K6=\"Modelled\",F6,IF(K6=\"Adjusted\",J6,\"Error\"))","v":274.44021858981444},274.44021858981444,{"$f":"+'Price Adjustments'!$F$6","v":0},{"$f":"+'Price Adjustments'!$F$71","v":0},{"$f":"M6*(1+N6)*(1+O6)","v":274.44021858981444},{"$f":"MIN($P$5:$P$13,$P$15:$P$16)","v":63.813174964607526}]
[null,"VB03Z","Emergency Medicine, Category 3 Investigation with Category 1-3 Treatment",192,56,{"$f":"VLOOKUP($B7,Calculation!$C$14:$R$24,16,FALSE)","v":201.61569243779155},null,{"$f":"LOOKUP(B7,AnEActivityCost_DatabaseOne[HRG],AnEActivityCost_DatabaseOne[SumOfTotal Activity])","v":514348},null,null,"Modelled",201.61569243779155,201.61569243779155,{"$f":"+'Price Adjustments'!$F$6","v":0},{"$f":"+'Price Adjustments'!$F$71","v":0},201.61569243779155,63.813174964607526]
[null,"VB04Z","Emergency Medicine, Category 2 Investigation with Category 4 Treatment",168,56,{"$f":"VLOOKUP($B8,Calculation!$C$14:$R$24,16,FALSE)","v":186.1666638004333},null,{"$f":"LOOKUP(B8,AnEActivityCost_DatabaseOne[HRG],AnEActivityCost_DatabaseOne[SumOfTotal Activity])","v":889265},null,null,"Modelled",186.1666638004333,186.1666638004333,{"$f":"+'Price Adjustments'!$F$6","v":0},{"$f":"+'Price Adjustments'!$F$71","v":0},186.1666638004333,63.813174964607526]
[null,"VB05Z","Emergency Medicine, Category 2 Investigation with Category 3 Treatment",146,56,{"$f":"VLOOKUP($B9,Calculation!$C$14:$R$24,16,FALSE)","v":149.41398720173493},null,{"$f":"LOOKUP(B9,AnEActivityCost_DatabaseOne[HRG],AnEActivityCost_DatabaseOne[SumOfTotal Activity])","v":388207},null,null,"Modelled",149.41398720173493,149.41398720173493,{"$f":"+'Price Adjustments'!$F$6","v":0},{"$f":"+'Price Adjustments'!$F$71","v":0},149.41398720173493,63.813174964607526]
[null,"VB06Z","Emergency Medicine, Category 1 Investigation with Category 3-4 Treatment",102,56,{"$f":"VLOOKUP($B10,Calculation!$C$14:$R$24,16,FALSE)","v":108.13037126018084},null,{"$f":"LOOKUP(B10,AnEActivityCost_DatabaseOne[HRG],AnEActivityCost_DatabaseOne[SumOfTotal Activity])","v":493592},null,null,"Modelled",108.13037126018084,108.13037126018084,{"$f":"+'Price Adjustments'!$F$6","v":0},{"$f":"+'Price Adjustments'!$F$71","v":0},108.13037126018084,63.813174964607526]
[null,"VB07Z","Emergency Medicine, Category 2 Investigation with Category 2 Treatment",128,56,{"$f":"VLOOKUP($B11,Calculation!$C$14:$R$24,16,FALSE)","v":134.0580142887527},null,{"$f":"LOOKUP(B11,AnEActivityCost_DatabaseOne[HRG],AnEActivityCost_DatabaseOne[SumOfTotal Activity])","v":1755207},null,null,"Modelled",134.0580142887527,134.0580142887527,{"$f":"+'Price Adjustments'!$F$6","v":0},{"$f":"+'Price Adjustments'!$F$71","v":0},134.0580142887527,63.813174964607526]
[null,"VB08Z","Emergency Medicine, Category 2 Investigation with Category 1 Treatment",116,56,{"$f":"VLOOKUP($B12,Calculation!$C$14:$R$24,16,FALSE)","v":123.67093436058747},null,{"$f":"LOOKUP(B12,AnEActivityCost_DatabaseOne[HRG],AnEActivityCost_DatabaseOne[SumOfTotal Activity])","v":4112656},null,null,"Modelled",123.67093436058747,123.67093436058747,{"$f":"+'Price Adjustments'!$F$6","v":0},{"$f":"+'Price Adjustments'!$F$71","v":0},123.67093436058747,63.813174964607526]
[null,"VB09Z","Emergency Medicine, Category 1 Investigation with Category 1-2 Treatment",81,56,{"$f":"VLOOKUP($B13,Calculation!$C$14:$R$24,16,FALSE)","v":86.69850108227736},null,{"$f":"LOOKUP(B13,AnEActivityCost_DatabaseOne[HRG],AnEActivityCost_DatabaseOne[SumOfTotal Activity])","v":3941735},null,null,"Modelled",86.69850108227736,86.69850108227736,{"$f":"+'Price Adjustments'!$F$6","v":0},{"$f":"+'Price Adjustments'!$F$71","v":0},86.69850108227736,63.813174964607526]
[null,"VB10Z","Emergency Medicine, Dental Care",56,56,{"$f":"VLOOKUP($B14,Calculation!$C$14:$R$24,16,FALSE)","v":64.985899694578038},null,{"$f":"LOOKUP(B14,AnEActivityCost_DatabaseOne[HRG],AnEActivityCost_DatabaseOne[SumOfTotal Activity])","v":10938},null,null,"Modelled",64.985899694578038,64.985899694578038,{"$f":"+'Price Adjustments'!$F$6","v":0},0,64.985899694578038,63.813174964607526]
[null,"VB11Z","Emergency Medicine, No Investigation with No Significant Treatment",56,56,{"$f":"VLOOKUP($B15,Calculation!$C$14:$R$24,16,FALSE)","v":63.813174964607526},null,{"$f":"LOOKUP(B15,AnEActivityCost_DatabaseOne[HRG],AnEActivityCost_DatabaseOne[SumOfTotal Activity])","v":4543052},null,null,"Modelled",63.813174964607526,63.813174964607526,{"$f":"+'Price Adjustments'!$F$6","v":0},0,63.813174964607526,63.813174964607526]
[null,"VB99Z","Emergency Medicine, Patient Dead On Arrival","N/A","N/A",null,null,null,"Set equal to VB09Z",{"$f":"+F13","v":86.69850108227736},"Adjusted",86.69850108227736,86.69850108227736,{"$f":"+'Price Adjustments'!$F$6","v":0},{"$f":"+'Price Adjustments'!$F$71","v":0},{"$f":"M16*(1+N16)*(1+O16)","v":86.69850108227736},63.813174964607526]
[null,null,null,null,"Cost Quantum (£)",{"$f":"SUMPRODUCT(F5:F16,$H$5:$H$16)","v":1803042886.2220364},null,null,null,null,"Cost Quantum (£)",{"$f":"SUMPRODUCT(L5:L16,$H$5:$H$16)","v":1803042886.2220364},{"$f":"SUMPRODUCT(M5:M16,$H$5:$H$16)","v":1803042886.2220364}]
[null,null,null,null,null,null,null,null,null,null,"QR2",{"$f":"+F17/L17","v":1},{"$f":"+L17/M17-1","v":0}]