{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Cover page","Instructions","Summary","Tool","Products","List","Forms","Assumtpions and Data Source","Excercises"],"not_rendered":{"charts":1,"images":5,"macros":0,"external_links":14,"defined_names":71,"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 page","index":0,"n_rows":2,"n_cols":2,"rows_kept":2,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Quantification Tool EID "]

{"sheet":"Instructions","index":1,"n_rows":34,"n_cols":8,"rows_kept":34,"cut":false,"hidden":false,"merged":[[8,1,15,4],[3,7,26,7]]}
[]
[null,"Instructions"]
[]
[null,null,null,null,null,null,null,"- Quantification Model -"]
[]
[]
[null,"Overview"]
[]
[null,"This tool is a Consumption-based and Target-based tool that allows you to run a 1 year forecast for EID products with quarterly projected needs. Projections can be either based on labs estimates of tests to be performed or on national targets, whenever consumption data are not available. This tool quantifies for the lab-related supplies (Reagents and non-reagent Consumables) and the site-related supplies (DBS collection materials).\nNote: It is important to work closely with all the lab managers of every EID lab in the country when completing this forecast.  Suggested default settings for some inputs are shown below in blue italicized text. Other notes are shown in grey italicized text"]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Instructions"]
[]
[null,null,"Input cells"]
[]
[]
[null,null,"Output cells"]
[]
[]
[null,null,"Calculation cell"]
[]
[null,"Important: Input products and pricing information on the product page (Yellow tab)"]
[]
[null,"Contacts"]
[]
[]
[null,"Paolo Maggiore (CHAI)","pmaggiore@clintonHealthAccess.org"]
[null,"Anisa Ghadrshenas (CHAI)","aghadrshenas@clintonHealthAccess.org"]

{"sheet":"Summary","index":2,"n_rows":43,"n_cols":8,"rows_kept":43,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"EID Forecasting Summary"]
[]
[]
[]
[]
[null,"Forecasting - quarterly procurements"]
[]
[null,null,"1 year forecasting beginning in:",{"$f":"CONCATENATE(Tool!D9,\"  \",Tool!E9)","v":"  "}]
[]
[null,null,null,"Quarter 1","Quarter 2","Quarter 3","Quarter 4","Total"]
[]
[null,null,"Expected # Tests to be covered (including westage, controls, losses, buffer etc.)",{"$f":"Tool!D557+Tool!D558+Tool!D559","v":0},{"$f":"Tool!E557+Tool!E558+Tool!E559","v":0},{"$f":"Tool!F557+Tool!F558+Tool!F559","v":0},{"$f":"Tool!G557+Tool!G558+Tool!G559","v":0},{"$f":"SUM(D14:G14)","v":0}]
[]
[null,"Procurement Outputs - Volumes"]
[]
[null,null,null,{"$f":"Tool!D230","v":"Procurement for Consumption in 0 (0)"},{"$f":"Tool!E230","v":"Procurement for Consumption in 0 (0)"},{"$f":"Tool!F230","v":"Procurement for consumption in 0 (0)"},{"$f":"Tool!G230","v":"Procurement for Consumption in 0 (0)"},"Total Procurement"]
[]
[null,null,"DBS Bundles - 20 Test Kits",{"$f":"IF(Tool!D232=\"\",0,Tool!D232)","v":0},{"$f":"Tool!E232","v":0},{"$f":"Tool!F232","v":0},{"$f":"Tool!G232","v":0},{"$f":"SUM(D20:G20)","v":0}]
[null,null,"DBS Bundles - 50 Test Kits",{"$f":"Tool!D233","v":0},{"$f":"Tool!E233","v":0},{"$f":"Tool!F233","v":0},{"$f":"Tool!G233","v":0},0]
[null,null,"Amplicor Reagent Kits",{"$f":"Tool!D234","v":0},{"$f":"Tool!E234","v":0},{"$f":"Tool!F234","v":0},{"$f":"Tool!G234","v":0},{"$f":"SUM(D22:G22)","v":0}]
[null,null,"CAP/CTM Reagent Kits*",{"$f":"Tool!D235","v":0},{"$f":"Tool!E235","v":0},{"$f":"Tool!F235","v":0},{"$f":"Tool!G235","v":0},0]
[null,null,"m2000 Reagent Kits*",{"$f":"Tool!D236","v":0},{"$f":"Tool!E236","v":0},{"$f":"Tool!F236","v":0},{"$f":"Tool!G236","v":0},0]
[null,null,"Generic Lab Consumables Bundles Amplicor",{"$f":"Tool!D237","v":0},{"$f":"Tool!E237","v":0},{"$f":"Tool!F237","v":0},{"$f":"Tool!G237","v":0},0]
[null,null,"Generic Lab Consumables Bundles CAP/CTM",{"$f":"Tool!D238","v":0},{"$f":"Tool!E238","v":0},{"$f":"Tool!F238","v":0},{"$f":"Tool!G238","v":0},0]
[null,null,"Generic Lab Consumables Bundles Abbott",{"$f":"Tool!D239","v":0},{"$f":"Tool!E239","v":0},{"$f":"Tool!F239","v":0},{"$f":"Tool!G239","v":0},0]
[null,null,"* Reagents are bundled with proprietary lab consumables"]
[]
[null,"Procurement Outputs - Budget"]
[]
[null,null,null,{"$f":"Tool!D244","v":"Procurement for Consumption in 0 (0)"},{"$f":"Tool!E244","v":"Procurement for Consumption in 0 (0)"},{"$f":"Tool!F244","v":"Procurement for Consumption in 0 (0)"},{"$f":"Tool!G244","v":"Procurement for Consumption in 0 (0)"},"Total Procurement"]
[]
[null,null,"DBS Bundles - 20 Test Kits",{"$f":"Tool!D246","v":0},{"$f":"Tool!E246","v":0},{"$f":"Tool!F246","v":0},{"$f":"Tool!G246","v":0},{"$f":"SUM(D34:G34)","v":0}]
[null,null,"DBS Bundles - 50 Test Kits",{"$f":"Tool!D247","v":0},{"$f":"Tool!E247","v":0},{"$f":"Tool!F247","v":0},{"$f":"Tool!G247","v":0},0]
[null,null,"Amplicor Reagent Kits",{"$f":"Tool!D248","v":0},{"$f":"Tool!E248","v":0},{"$f":"Tool!F248","v":0},{"$f":"Tool!G248","v":0},0]
[null,null,"CAP/CTM Reagent Kits",{"$f":"Tool!D249","v":0},{"$f":"Tool!E249","v":0},{"$f":"Tool!F249","v":0},{"$f":"Tool!G249","v":0},0]
[null,null,"m2000 Reagent Kits",{"$f":"Tool!D250","v":0},{"$f":"Tool!E250","v":0},{"$f":"Tool!F250","v":0},{"$f":"Tool!G250","v":0},0]
[null,null,"Generic Lab Consumables Bundles Amplicor",{"$f":"Tool!D251","v":0},{"$f":"Tool!E251","v":0},{"$f":"Tool!F251","v":0},{"$f":"Tool!G251","v":0},0]
[null,null,"Generic Lab Consumables Bundles CAP/CTM",{"$f":"Tool!D252","v":0},{"$f":"Tool!E252","v":0},{"$f":"Tool!F252","v":0},{"$f":"Tool!G252","v":0},0]
[null,null,"Generic Lab Consumables Bundles Abbott",{"$f":"Tool!D253","v":0},{"$f":"Tool!E253","v":0},{"$f":"Tool!F253","v":0},{"$f":"Tool!G253","v":0},0]
[]
[null,null,"Total Cost",{"$f":"SUM(D34:D42)","v":0},{"$f":"SUM(E34:E42)","v":0},{"$f":"SUM(F34:F42)","v":0},{"$f":"SUM(G34:G42)","v":0},{"$f":"SUM(H34:H42)","v":0}]

{"sheet":"Tool","index":3,"n_rows":572,"n_cols":23,"rows_kept":572,"cut":false,"hidden":false,"merged":[[103,5,106,6],[329,7,334,8],[355,7,360,8],[5,6,5,7],[5,8,5,9],[273,7,278,8],[171,5,173,7],[213,5,215,7],[98,5,100,6],[137,5,138,6],[33,5,37,6],[169,5,169,7],[26,5,28,6],[16,5,17,6],[70,3,70,6]]}
[]
[null,"EID Forecasting Tool"]
[]
[null,"Input"]
[]
[]
[null,"General Program Information"]
[null,null,null,"Quarter","Year"]
[null,null,"This is a 1 Year Forecast that begins in:"]
[]
[null,null,"MTCT rate - Mother to Child transmission rate",null,null,"This figure is used to calculate the expected # of confirmatory tests."]
[]
[null,null,"Algorithm"]
[null,null,null,null,"with same sample?"]
[null,null,"HIV positive infants are tested again to confirm HIV positivity",null,null,"It is unlikely that confirmatory tests are conducted with a different sample."]
[]
[null,null,"HIV negative infants are tested again at the end of breastfeeding",null,null,"This refers to infants who are HIV-negative after the first PCR but are still exposed through breastfeeding."]
[]
[]
[null,null,"National Target next year"]
[]
[null,null,"% Target realistically achievable based on the latest information available"]
[]
[null,null,"Loss To Follow Up (LTFU)"]
[]
[null,null,"HEI infants lost between first and confirmatory test",null,null,"It is unlikely that this figure is available. If it is not, leave blank."]
[null,null,"HEI infants lost between first testing period and end of breastfeeding",null,null,"This refers to the total number of HEI who are LTFU. Gayle Sherman wrote a paper that says 88% of exposed infants are lost to follow up by 18 months."]
[]
[]
[null,null,"# of Expected EID Tests Per Year",{"$f":"$E$20*$D$22+IF($D$20=\"Target # HEI\",IF(AND($D$15=\"yes\",$E$15=\"Yes\"),($D$11)*$E$20*$D$22,IF(AND(D15=\"Yes\",E15=\"No\"),($D$11)*$E$20*$D$22*(1-$D$26)))+IF($D$17=\"Yes\",(1-$D$11)*$E$20*$D$22*(1-$D$27),0),0)","v":0}]
[null,null,"# of Expected EID Samples Per Year",{"$f":"IF($D$20=\"Target # HEI\",($E$20*$D$22+IF($D$17=\"Yes\",(1-$D$11)*$E$20*$D$22*(1-$D$27),0)+IF(AND($D$15=\"Yes\",E15=\"No\"),(1-$D$26)*$E$20*$D$22*$D$11,0)),IF(D20<>\"Target # HEI\",(IF(AND($D$15=\"Yes\",$E$15=\"Yes\"),E20*$D$22,0)+IF(AND($D$15=\"Yes\",$E$15=\"No\"),$E$20*$D$22+$D$11*$E$20*$D$22*(1-$D$27))),0)+IF($D$15=\"No\",E20*$D$22,0))","v":0}]
[]
[]
[null,"Data Available",null,null,null,"If there is no consumption data available, input the table below with the % of tests performed of the total on each platform (eg. Roche CAP/CTM 80%, Roche Amplicor 20%).\nIMPORTANT: The platforms should be named exactly as follows: Roche CAP/CTM, Roche Amplicor, Abbott m2000."]
[]
[null,null,"Consumption Data available?"]
[]
[]
[null,"Laboratories - General Information"]
[]
[null,null,"Lab Name","Testing Platform","weight over total # tests (%)"]
[]
[null,null,null,null,null,{"$f":"H75/$H$96","v":{"$error":"#DIV/0!"}}]
[null,null,null,null,null,{"$f":"H76/$H$96","v":{"$error":"#DIV/0!"}}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[null,null,null,null,null,{"$error":"#DIV/0!"}]
[]
[]
[]
[]
[null,"Laboratories - Expected Testing During Forecast Period (# samples)"]
[]
[null,null,"Consumption Data available at the Lab",null,null,null,null,null,null,"Trend Line # Tests lst 4 quarters"]
[]
[null,null,null,"These are the last four quarters with available consumption data"]
[null,null,"Consumption Data"]
[null,null,"Lab Name",{"$f":"CONCATENATE(,$D$69,\" in \", E571,\" (\", E572,\")\")","v":" in 0 (0)"},{"$f":"CONCATENATE(,$D$69,\" in \", F571,\" (\", F572,\")\")","v":" in 0 (0)"},{"$f":"CONCATENATE(,$D$69,\" in \", G571,\" (\", G572,\")\")","v":" in 0 (0)"},{"$f":"CONCATENATE(,$D$69,\" in \", H571,\" (\", H572,\")\")","v":" in 0 (0)"},{"$f":"CONCATENATE(,\"Total \",$D$69,\"\")","v":"Total "}]
[]
[null,null,{"$f":"IF(ISBLANK($C43),\"\",CONCATENATE($C43,\" \",$D43))","v":""},null,null,null,null,{"$f":"SUM(D75:G75)","v":0}]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[null,null,"",null,null,null,null,0]
[]
[null,null,"TOTAL",{"$f":"SUM(D75:D94)","v":0},{"$f":"SUM(E75:E94)","v":0},{"$f":"SUM(F75:F94)","v":0},{"$f":"SUM(G75:G94)","v":0},{"$f":"SUM(H75:H94)","v":0}]
[]
[]
[null,null,null,null,null,"Data follows a 'trend' when there has not been high variability of testing volumes from a quarter to another in the last year (no alternate drops/increases)"]
[null,null,"Testing data follow a clear trend?"]
[]
[]
[null,null,"Scale up plan"]
[null,null,null,null,null,"The growth rate should reflect the interventions expected to be put in place to address the issues experienced in the previous quarters (eg. Machine downtimes, stock outs etc.) and/or increase capacity (eg. New staff, automation etc.)"]
[null,null,"Scale Up plan next year (% growth expected)"]
[]
[]
[]
[]
[]
[null,null,"Estimates (based on variability and trend line)"]
[null,null,"Lab Name",{"$f":"D73","v":" in 0 (0)"},{"$f":"E73","v":" in 0 (0)"},{"$f":"F73","v":" in 0 (0)"},{"$f":"G73","v":" in 0 (0)"},{"$f":"H73","v":"Total "}]
[]
[null,null,{"$f":"C75","v":""},{"$f":"IF($D$36=\"No\",$D$30/4*E43,(IF(ISBLANK(D75),\"\",MAX(G75+$E383+G75*($F301/4),$G75+STDEV($D75:$G75)))))","v":""},{"$f":"IF($D$36=\"No\",$D$30/4*$E43,IF(ISBLANK(E75),\"\",(D114+$E383+$G75*($F301/4))))","v":""},"","",{"$f":"SUM(D114:G114)","v":0}]
[null,null,"",{"$f":"IF($D$36=\"No\",$D$30/4*E44,((IF(ISBLANK(D76),\"\",MAX(G76+$E384+G76*($F302/4),$G76+STDEV($D76:$G76))))))","v":""},"","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",{"$f":"SUM(D123:G123)","v":0}]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[null,null,"","","","","",0]
[]
[null,null,"TOTAL",{"$f":"SUM(D114:D133)","v":0},{"$f":"SUM(E114:E133)","v":0},{"$f":"SUM(F114:F133)","v":0},{"$f":"SUM(G114:G133)","v":0},{"$f":"SUM(H114:H133)","v":0}]
[]
[]
[null,null,"National Target # of EID Tests Per Year",{"$f":"D30","v":0},null,"These two numbers should ideally be within 10% of each other"]
[null,null,"Estimated # of EID Tests Per Year",{"$f":"H351","v":0}]
[]
[]
[]
[null,"Laboratories - Westage and Controls"]
[]
[]
[null,null,"# Controls Used for Every 96 Tests","Sample Rejection Rate (%)","Tests used quarterly for training (%)"]
[]
[null,null,null,0.05,0.05]
[]
[]
[]
[null,null,"# Samples and # Tests including controls and wastage"]
[null,null,null,{"$f":"D467","v":"0 (0)"},{"$f":"E467","v":"0 (0)"},{"$f":"F467","v":"0 (0)"},{"$f":"G467","v":"0 (0)"},"TOTAL"]
[]
[null,null,"# Samples Collected (including wastage)",{"$f":"E491","v":0},{"$f":"F491","v":0},{"$f":"G491","v":0},{"$f":"H491","v":0},{"$f":"SUM(D155:G155)","v":0}]
[]
[null,null,"# Tests (including controls)"]
[null,null,"Roche Amplicor",{"$f":"D468","v":0},0,0,0,{"$f":"SUM(D158:G158)","v":0}]
[null,null,"Roche CAP/CTM",0,0,0,0,{"$f":"SUM(D159:G159)","v":0}]
[null,null,"Abbott m2000",0,0,0,0,{"$f":"SUM(D160:G160)","v":0}]
[null,null,"Total",{"$f":"SUM(D158:D160)","v":0},{"$f":"SUM(E158:E160)","v":0},{"$f":"SUM(F158:F160)","v":0},{"$f":"SUM(G158:G160)","v":0},{"$f":"SUM(H158:H160)","v":0}]
[]
[null,null,"Average Monthly Consumption - Samples",{"$f":"D155/3","v":0},{"$f":"E155/3","v":0},{"$f":"F155/3","v":0},{"$f":"G155/3","v":0}]
[null,null,"Average Monthly Consumption - Tests",{"$f":"D161/3","v":0},{"$f":"E161/3","v":0},{"$f":"F161/3","v":0},{"$f":"G161/3","v":0}]
[]
[]
[]
[null,"EID Sample Collection Sites"]
[]
[null,null,"Procurement of at least 1 pack of collection test kits per clinic?",null,null,"This is the recommended method of procurement."]
[]
[null,null,"Baseline # of EID Sample Collection Sites",null,null,"The # EID sites is considered as the minimum # of DBS kits to be procured, with the assumption that every EID site should receive at least 1 DBS sample collection kit."]
[]
[null,null,"Target # of EID Sample Collection Sites expected to be operative by next year"]
[]
[null,null,null,null,"Multiplier - 20 kits","Multiplier - 50 kits"]
[]
[null,null,"Collection sites that collect less than 20 samples per quarter"]
[]
[null,null,"Collection sites that collect between 20 and 40 samples per quarter",null,1,0]
[]
[null,null,"Collection sites that collect between 40 and 60 samples per quarter",null,2,0]
[]
[null,null,"Collection sites that collect more than 60 samples per quarter",null,3,1]
[null,null,null,{"$f":"SUM(D178:D184)","v":0},{"$f":"IF(AND(D185<>100%,D185<>0%),\"Error - Sum should be 100%\",\"\")","v":""}]
[]
[null,"Existing  Stock / Pipeline"]
[]
[null,null,"Data on Stock is available?","Yes",{"$f":"IF(AND(E201+E226<>0,D189=\"No\"),\"WARNING! Delete old data stock input in red\",\"\")","v":""}]
[]
[null,null,null,"Existing Stock (# units)","Stock in the Pipeline (# units)",{"$f":"CONCATENATE(\"Stock Expiring later than \", J571,\" (\", J572,\")\")","v":"Stock Expiring later than 0 (0)"}]
[]
[null,null,"DBS Bundles - 20 Test Kits",0,null,{"$f":"IF(F510-$D$203<1,0,ROUND((F510-$D$203)*$D$163/Products!D21,0))","v":{"$error":"#DIV/0!"}}]
[null,null,"DBS Bundles - 50 Test Kits",0,null,{"$f":"IF(F511-$D$203<1,0,ROUND((F511-$D$203)*$D$163/Products!D22,0))","v":{"$error":"#DIV/0!"}}]
[null,null,"Amplicor Reagent Kits 96",0,null,{"$f":"IF(F512-$D$203<1,0,ROUND((F512-$D$203)*$D$164/Products!D23,0))","v":{"$error":"#DIV/0!"}}]
[null,null,"CAP/CTM Reagent Kits 48",0,null,{"$f":"IF(F513-$D$203<1,0,ROUND((F513-$D$203)*$D$164/Products!D24,0))","v":{"$error":"#DIV/0!"}}]
[null,null,"m2000 Reagent Kits 96",0,null,{"$f":"IF(F514-$D$203<1,0,ROUND((F514-$D$203)*$D$164/Products!D25,0))","v":{"$error":"#DIV/0!"}}]
[null,null,"Generic Lab Consumables Bundles Amplicor",0,null,{"$f":"IF(F515-$D$203<1,0,ROUND((F515-$D$203)*$D$164/Products!D26,0))","v":{"$error":"#DIV/0!"}}]
[null,null,"Generic Lab Consumables Bundles CAP/CTM",0,null,{"$f":"IF(F516-$D$203<1,0,ROUND((F516-$D$203)*$D$164/Products!D27,0))","v":{"$error":"#DIV/0!"}}]
[null,null,"Generic Lab Consumables Bundles Abbott",0,null,{"$f":"IF(F517-$D$203<1,0,ROUND((F517-$D$203)*$D$164/Products!D28,0))","v":{"$error":"#DIV/0!"}}]
[null,null,null,{"$f":"SUM(D193:D200)","v":0},{"$f":"SUM(E193:E200)","v":0}]
[]
[null,null,{"$f":"CONCATENATE(\"Number of months from today to \", J571,\" (\", J572,\")\")","v":"Number of months from today to 0 (0)"}]
[]
[null,null,"Required Buffer at Central Stores (# Months)"]
[]
[null,null,"Qtrly Loss from Central Stores to Labs (%)",0.05]
[null,null,"Qtrly Loss from Central Stores to Clinics (%)",0.05]
[]
[null,"Product type - DBS Bundle Pack Size"]
[]
[null,null,null,"% Total Test"]
[]
[null,null,"DBS Bundles - 20 Test Kits"]
[]
[null,null,"DBS Bundles - 50 Test Kits"]
[]
[]
[null,"Prices"]
[]
[null,null,"Price per item or Price per Test available?","Price per item"]
[]
[]
[]
[null,"Output"]
[]
[]
[null,"Procurement Outputs - Volumes"]
[]
[null,null,null,{"$f":"CONCATENATE(\"Procurement for Consumption in \", J$571,\" (\", J$572,\")\")","v":"Procurement for Consumption in 0 (0)"},{"$f":"CONCATENATE(\"Procurement for Consumption in \", K$571,\" (\", K$572,\")\")","v":"Procurement for Consumption in 0 (0)"},{"$f":"CONCATENATE(\"Procurement for consumption in \", L$571,\" (\", L$572,\")\")","v":"Procurement for consumption in 0 (0)"},{"$f":"CONCATENATE(\"Procurement for Consumption in \", M$571,\" (\", M$572,\")\")","v":"Procurement for Consumption in 0 (0)"},"Total Procurement"]
[]
[null,null,"DBS Bundles - 20 Test Kits",{"$f":"(ROUND(D534,0))","v":0},0,0,0,{"$f":"SUM(D232:G232)","v":0}]
[null,null,"DBS Bundles - 50 Test Kits",0,0,0,0,0]
[null,null,"Amplicor Reagent Kits",0,0,0,0,{"$f":"SUM(D234:G234)","v":0}]
[null,null,"CAP/CTM Reagent Kits*",0,0,0,0,0]
[null,null,"m2000 Reagent Kits*",0,0,0,0,0]
[null,null,"Generic Lab Consumables Bundles Amplicor",0,0,0,0,0]
[null,null,"Generic Lab Consumables Bundles CAP/CTM",0,0,0,0,0]
[null,null,"Generic Lab Consumables Bundles Abbott",0,0,0,0,0]
[null,null,"* Reagents are bundled with proprietary lab consumables"]
[]
[null,"Procurement Outputs - Budget"]
[]
[null,null,null,{"$f":"CONCATENATE(\"Procurement for Consumption in \", J$571,\" (\", J$572,\")\")","v":"Procurement for Consumption in 0 (0)"},{"$f":"CONCATENATE(\"Procurement for Consumption in \", K$571,\" (\", K$572,\")\")","v":"Procurement for Consumption in 0 (0)"},{"$f":"CONCATENATE(\"Procurement for Consumption in \", L$571,\" (\", L$572,\")\")","v":"Procurement for Consumption in 0 (0)"},{"$f":"CONCATENATE(\"Procurement for Consumption in \", M$571,\" (\", M$572,\")\")","v":"Procurement for Consumption in 0 (0)"},"Total Procurement"]
[]
[null,null,"DBS Bundles - 20 Test Kits",{"$f":"IF($D$221=\"Price per item\",D232*Products!$E$21,D555*Products!$F$21)","v":0},{"$f":"IF($D$221=\"Price per item\",E232*Products!$E$21,E555*Products!$F$21)","v":0},{"$f":"IF($D$221=\"Price per item\",F232*Products!$E$21,F555*Products!$F$21)","v":0},{"$f":"IF($D$221=\"Price per item\",G232*Products!$E$21,G555*Products!$F$21)","v":0},{"$f":"SUM(D246:G246)","v":0}]
[null,null,"DBS Bundles - 50 Test Kits",{"$f":"IF($D$221=\"Price per item\",D233*Products!$E$22,D556*Products!$F$22)","v":0},{"$f":"IF($D$221=\"Price per item\",E233*Products!$E$22,E556*Products!$F$22)","v":0},{"$f":"IF($D$221=\"Price per item\",F233*Products!$E$22,F556*Products!$F$22)","v":0},{"$f":"IF($D$221=\"Price per item\",G233*Products!$E$22,G556*Products!$F$22)","v":0},0]
[null,null,"Amplicor Reagent Kits",{"$f":"IF($D$221=\"Price per item\",D234*Products!$E$23,D558*Products!$F$21)","v":0},{"$f":"IF($D$221=\"Price per item\",E234*Products!$E$23,E558*Products!$F$21)","v":0},{"$f":"IF($D$221=\"Price per item\",F234*Products!$E$23,F558*Products!$F$21)","v":0},{"$f":"IF($D$221=\"Price per item\",G234*Products!$E$23,G558*Products!$F$21)","v":0},0]
[null,null,"CAP/CTM Reagent Kits",{"$f":"IF($D$221=\"Price per item\",D235*Products!$E$24,D557*Products!$F$24)","v":0},{"$f":"IF($D$221=\"Price per item\",E235*Products!$E$24,E557*Products!$F$24)","v":0},{"$f":"IF($D$221=\"Price per item\",F235*Products!$E$24,F557*Products!$F$24)","v":0},{"$f":"IF($D$221=\"Price per item\",G235*Products!$E$24,G557*Products!$F$24)","v":0},0]
[null,null,"m2000 Reagent Kits",{"$f":"IF($D$221=\"Price per item\",D236*Products!$E$25,D559*Products!$F$25)","v":0},{"$f":"IF($D$221=\"Price per item\",E236*Products!$E$25,E559*Products!$F$25)","v":0},{"$f":"IF($D$221=\"Price per item\",F236*Products!$E$25,F559*Products!$F$25)","v":0},{"$f":"IF($D$221=\"Price per item\",G236*Products!$E$25,G559*Products!$F$25)","v":0},0]
[null,null,"Generic Lab Consumables Bundles Amplicor",{"$f":"IF($D$221=\"Price per item\",D237*Products!$E$26,D558*Products!$F$26)","v":0},{"$f":"IF($D$221=\"Price per item\",E237*Products!$E$26,E558*Products!$F$26)","v":0},{"$f":"IF($D$221=\"Price per item\",F237*Products!$E$26,F558*Products!$F$26)","v":0},{"$f":"IF($D$221=\"Price per item\",G237*Products!$E$26,G558*Products!$F$26)","v":0},0]
[null,null,"Generic Lab Consumables Bundles CAP/CTM",{"$f":"IF($D$221=\"Price per item\",D238*Products!$E$27,D557*Products!$F$27)","v":0},{"$f":"IF($D$221=\"Price per item\",E238*Products!$E$27,E557*Products!$F$27)","v":0},{"$f":"IF($D$221=\"Price per item\",F238*Products!$E$27,F557*Products!$F$27)","v":0},{"$f":"IF($D$221=\"Price per item\",G238*Products!$E$27,G557*Products!$F$27)","v":0},0]
[null,null,"Generic Lab Consumables Bundles Abbott",{"$f":"IF($D$221=\"Price per item\",D239*Products!$E$28,D559*Products!$F$28)","v":0},{"$f":"IF($D$221=\"Price per item\",E239*Products!$E$28,E559*Products!$F$28)","v":0},{"$f":"IF($D$221=\"Price per item\",F239*Products!$E$28,F559*Products!$F$28)","v":0},{"$f":"IF($D$221=\"Price per item\",G239*Products!$E$28,G559*Products!$F$28)","v":0},0]
[]
[null,null,"Total Cost",{"$f":"SUM(D246:D254)","v":0},{"$f":"SUM(E246:E254)","v":0},{"$f":"SUM(F246:F254)","v":0},{"$f":"SUM(G246:G254)","v":0},{"$f":"SUM(H246:H254)","v":0}]
[]
[]
[null,"Note: Click the Plus Sign on the Right to View Calculations"]
[null,"Intermediate Calculation"]
[]
[]
[]
[null,null,"Testing Capacity Utilized"]
[]
[null,null,{"$f":"IF(ISERROR((VLOOKUP(Products!C8,Tool!$D$43:$D$62,1,))),\"\",(VLOOKUP(Products!C8,Tool!$D$43:$D$62,1,)))","v":""},1]
[null,null,{"$f":"IF(ISERROR(VLOOKUP(Products!C9,Tool!$D$43:$D$62,1,)),\"\",(VLOOKUP(Products!C9,Tool!$D$43:$D$62,1,)))","v":""},1]
[null,null,{"$f":"IF(ISERROR(VLOOKUP(Products!C10,Tool!$D$43:$D$62,1,)),\"\",(VLOOKUP(Products!C10,Tool!$D$43:$D$62,1,)))","v":""},1]
[]
[]
[]
[null,null,"Estimates based on consumption data and scale up plan"]
[null,null,"Lab Name",{"$f":"D73","v":" in 0 (0)"},{"$f":"E73","v":" in 0 (0)"},{"$f":"F73","v":" in 0 (0)"},{"$f":"G73","v":" in 0 (0)"},{"$f":"H73","v":"Total "}]
[]
[null,null,{"$f":"C75","v":""},{"$f":"D114","v":""},"","","","Note: Use the estimates above to project the # tests, adjusting the figures to include in the projections any events that is expected to bring an increase/decrease of # tests (eg. new machines, new staff etc.)"]
[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,null,"","","","",""]
[null,null,"","","","",""]
[null,null,"","","","",""]
[null,null,"","","","",""]
[null,null,"","","","",""]
[]
[null,null,"TOTAL",{"$f":"SUM(D274:D293)","v":0},{"$f":"SUM(E274:E293)","v":0},{"$f":"SUM(F274:F293)","v":0},{"$f":"SUM(G274:G293)","v":0},{"$f":"SUM(D295:G295)","v":0}]
[]
[null,null,"Extraordinary events"]
[]
[null,null,"Lab Name","Machine downtime (# days)","Commodities Stock out (# days)","Scale Up plan next year (% growth expected)"]
[]
[null,null,{"$f":"C75","v":""},0,0,{"$f":"$D$105","v":0}]
[null,null,"",0,0,{"$f":"$D$105","v":0}]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",0,0,0]
[null,null,"",null,null,0]
[]
[null,null,"TOTAL",{"$f":"SUM(D301:D320)","v":0},{"$f":"SUM(E301:E320)","v":0}]
[]
[]
[null,null,"Estimates"]
[]
[null,null,"# Tests"]
[null,null,"Lab Name",{"$f":"CONCATENATE(,E571,\" \", E572)","v":"0 0"},{"$f":"CONCATENATE(,F571,\" \", F572)","v":"0 0"},{"$f":"CONCATENATE(,G571,\" \", G572)","v":"0 0"},{"$f":"CONCATENATE(,H571,\" \", H572)","v":"0 0"}]
[]
[null,null,{"$f":"C274","v":""},{"$f":"IF(D274=\"\",0,IF(AND($D$69=\"# Samples received\",$D$15=\"Yes\",$E$15=\"Yes\"),D274+(D274*$D$11),D274))","v":0},{"$f":"IF(E274=\"\",0,IF(AND($D$69=\"# Samples received\",$D$15=\"Yes\",$E$15=\"Yes\"),E274+(E274*$D$11),E274))","v":0},0,0,"Note: Use the estimates above to project the # tests, adjusting the figures to include in the projections any events that are expected to bring an increase/decrease of # tests (eg. new machines, new staff etc.)"]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,null,0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[null,null,"",0,0,0,0]
[]
[null,null,"TOTAL",{"$f":"SUM(D330:D349)","v":0},{"$f":"SUM(E330:E349)","v":0},{"$f":"SUM(F330:F349)","v":0},{"$f":"SUM(G330:G349)","v":0},{"$f":"SUM(D351:G351)","v":0}]
[]
[null,null,"# Samples"]
[null,null,"Lab Name",{"$f":"D328","v":"0 0"},{"$f":"E328","v":"0 0"},{"$f":"F328","v":"0 0"},{"$f":"G328","v":"0 0"}]
[]
[null,null,{"$f":"C330","v":""},{"$f":"IF(D330=\"\",\"\",IF($D$36=\"No\",$D$31/4*E43,IF($D$69=\"# Samples received\",D274,IF(AND($D$69=\"# Tests performed\",$D$15=\"Yes\",$E$15=\"Yes\",$D$20=\"Target # HEI\"),(D330-(D330*$D$11)),IF(AND($D$69=\"# Tests performed\",$D$15=\"Yes\",$E$15=\"No\"),D330,IF(AND($D$69=\"# Tests performed\",$D$15=\"Yes\",$E$15=\"Yes\",$D$20<>\"Target # HEI\"),D330/(1+$D$11)))))))","v":false},{"$f":"IF(E330=\"\",\"\",IF($D$36=\"No\",$D$31/4*E43,IF($D$69=\"# Samples received\",E274,IF(AND($D$69=\"# Tests performed\",$D$15=\"Yes\",$E$15=\"Yes\",$D$20=\"Target # HEI\"),(E330-(E330*$D$11)),IF(AND($D$69=\"# Tests performed\",$D$15=\"Yes\",$E$15=\"No\"),E330,IF(AND($D$69=\"# Tests performed\",$D$15=\"Yes\",$E$15=\"Yes\",$D$20<>\"Target # HEI\"),E330/(1+$D$11)))))))","v":false},{"$f":"IF(F330=\"\",\"\",IF($D$36=\"No\",$D$31/4*E43,IF($D$69=\"# Samples received\",F274,IF(AND($D$69=\"# Tests performed\",$D$15=\"Yes\",$E$15=\"Yes\",$D$20=\"Target # HEI\"),(F330-(F330*$D$11)),IF(AND($D$69=\"# Tests performed\",$D$15=\"Yes\",$E$15=\"No\"),F330,IF(AND($D$69=\"# Tests performed\",$D$15=\"Yes\",$E$15=\"Yes\",$D$20<>\"Target # HEI\"),F330/(1+$D$11)))))))","v":false},{"$f":"IF(G330=\"\",\"\",IF($D$36=\"No\",$D$31/4*E43,IF($D$69=\"# Samples received\",G274,IF(AND($D$69=\"# Tests performed\",$D$15=\"Yes\",$E$15=\"Yes\",$D$20=\"Target # HEI\"),(G330-(G330*$D$11)),IF(AND($D$69=\"# Tests performed\",$D$15=\"Yes\",$E$15=\"No\"),G330,IF(AND($D$69=\"# Tests performed\",$D$15=\"Yes\",$E$15=\"Yes\",$D$20<>\"Target # HEI\"),G330/(1+$D$11)))))))","v":false},"Note: Use the estimates above to project the # tests, adjusting the figures to include in the projections any events that are expected to bring an increase/decrease of # tests (eg. new machines, new staff etc.)"]
[null,null,{"$f":"C331","v":""},false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,0,false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false]
[null,null,"",false,false,false,false,"Total"]
[]
[null,null,"TOTAL",{"$f":"SUM(D356:D375)","v":0},{"$f":"SUM(E356:E375)","v":0},{"$f":"SUM(F356:F375)","v":0},{"$f":"SUM(G356:G375)","v":0},{"$f":"SUM(D377:G377)","v":0}]
[]
[]
[null,"Extraordinary events"]
[]
[null,null,null,"Quarterly growth rate consumption data","Quarterly capacity lost due to machine downtime and stock outs (# samples/tests)"]
[null,null,{"$f":"C356","v":""},{"$f":"(G75-D75)/D75/4","v":{"$error":"#DIV/0!"}},{"$f":"($D301+$E301)/250*$H75/4","v":0}]
[null,null,{"$f":"C357","v":""},{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,0,{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[null,null,"",{"$error":"#DIV/0!"},0]
[]
[null,null,"TOTAL",null,{"$f":"SUM(E383:E402)","v":0}]
[]
[]
[]
[]
[null,"Lab Commodity Calculations (test estimates grouped by platform)"]
[]
[null,null,"Tests per Quarter - No Repeats",{"$f":"CONCATENATE( J$571,\" (\", J$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( K$571,\" (\", K$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( L$571,\" (\", L$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( M$571,\" (\", M$572,\")\")","v":"0 (0)"},"TOTAL"]
[null,null,{"$f":"Products!C8","v":"Roche Amplicor"},{"$f":"SUMIF($D$43:$D$62,$C412,D$330:D$349)","v":0},0,0,0,{"$f":"SUM(D412:G412)","v":0},null,null,null,"Note: If \"# HEIs tested\" estimates were used, this does not include repeat tests - they are added in the next section.  If \"#EID tests done\" estimates were used, repeat tests are included here."]
[null,null,{"$f":"Products!C9","v":"Roche CAP/CTM"},0,0,0,0,{"$f":"SUM(D413:G413)","v":0}]
[null,null,{"$f":"Products!C10","v":"Abbott m2000"},0,0,0,0,{"$f":"SUM(D414:G414)","v":0}]
[null,null,"TOTAL",{"$f":"SUM(D412:D414)","v":0},{"$f":"SUM(E412:E414)","v":0},{"$f":"SUM(F412:F414)","v":0},{"$f":"SUM(G412:G414)","v":0},{"$f":"SUM(H412:H414)","v":0}]
[]
[]
[null,null,"Controls per batch and training per lab"]
[null,null,"Lab Name","# Controls Used for Every 96 Tests","Sample Rejection Rate (%)","Tests used quarterly for training"]
[]
[null,null,{"$f":"IF(ISBLANK($C43),\"\",$C43)","v":""},{"$f":"$C$148","v":0},{"$f":"$D$148","v":0.05},{"$f":"$E$148","v":0.05}]
[null,null,"",{"$f":"$C$148","v":0},{"$f":"$D$148","v":0.05},{"$f":"$E$148","v":0.05}]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[null,null,"",0,0.05,0.05]
[]
[]
[null,null,"Total Controls and training","Platform",{"$f":"D411","v":"0 (0)"},{"$f":"E411","v":"0 (0)"},{"$f":"F411","v":"0 (0)"},{"$f":"G411","v":"0 (0)"},"TOTAL"]
[null,null,"Lab 1",{"$f":"IF(ISBLANK(D43),\"\",D43)","v":""},{"$f":"IF($D421=\"\",0,ROUND($D421/96*D330+D330*$F421,0))","v":0},0,0,0,{"$f":"SUM(E444:H444)","v":0}]
[null,null,"Lab 2","",0,0,0,0,{"$f":"SUM(E445:H445)","v":0}]
[null,null,"Lab 3","",0,0,0,0,0]
[null,null,"Lab 4","",0,0,0,0,0]
[null,null,"Lab 5","",0,0,0,0,0]
[null,null,"Lab 6","",0,0,0,0,0]
[null,null,"Lab 7","",0,0,0,0,0]
[null,null,"Lab 8","",0,0,0,0,0]
[null,null,"Lab 9","",0,0,0,0,0]
[null,null,"Lab 10","",0,0,0,0,0]
[null,null,"Lab 11","",0,0,0,0,0]
[null,null,"Lab 12","",0,0,0,0,0]
[null,null,"Lab 13","",0,0,0,0,0]
[null,null,"Lab 14","",0,0,0,0,0]
[null,null,"Lab 15","",0,0,0,0,0]
[null,null,"Lab 16","",0,0,0,0,0]
[null,null,"Lab 17","",0,0,0,0,0]
[null,null,"Lab 18","",0,0,0,0,{"$f":"SUM(E461:H461)","v":0}]
[null,null,"Lab 19","",0,0,0,0,0]
[null,null,"Lab 20","",0,0,0,0,0]
[null,null,null,"Total",{"$f":"SUM(E444:E463)","v":0},{"$f":"SUM(F444:F463)","v":0},{"$f":"SUM(G444:G463)","v":0},{"$f":"SUM(H444:H463)","v":0},{"$f":"SUM(I444:I463)","v":0}]
[]
[]
[null,null,"Tests per Quarter - ALL TESTS (test estimates + controls)",{"$f":"CONCATENATE( J$571,\" (\", J$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( K$571,\" (\", K$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( L$571,\" (\", L$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( M$571,\" (\", M$572,\")\")","v":"0 (0)"},"TOTAL"]
[null,null,{"$f":"C412","v":"Roche Amplicor"},{"$f":"SUMIF($D$444:$D$463,$C468,E$444:E$463)+D412","v":0},{"$f":"SUMIF($D$444:$D$463,$C468,F$444:F$463)+E412","v":0},0,0,{"$f":"SUM(D468:G468)","v":0}]
[null,null,{"$f":"C413","v":"Roche CAP/CTM"},{"$f":"SUMIF($D$444:$D$463,$C469,E$444:E$463)+D413","v":0},0,0,0,{"$f":"SUM(D469:G469)","v":0}]
[null,null,{"$f":"C414","v":"Abbott m2000"},0,0,0,0,{"$f":"SUM(D470:G470)","v":0}]
[null,null,"Total",{"$f":"SUM(D468:D470)","v":0},{"$f":"SUM(E468:E470)","v":0},{"$f":"SUM(F468:F470)","v":0},{"$f":"SUM(G468:G470)","v":0},{"$f":"SUM(H468:H470)","v":0}]
[]
[null,null,"Active Labs (# labs)",{"$f":"CONCATENATE( J$571,\" (\", J$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( K$571,\" (\", K$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( L$571,\" (\", L$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( M$571,\" (\", M$572,\")\")","v":"0 (0)"}]
[null,null,{"$f":"C468","v":"Roche Amplicor"},{"$f":"IF(AND($C474=$D$43,D$274>0),1,0)+IF(AND($C474=$D$44,D$275>0),1,0)+IF(AND($C474=$D$45,D$276>0),1,0)+IF(AND($C474=$D$46,D$277>0),1,0)+IF(AND($C474=$D$47,D$278>0),1,0)+IF(AND($C474=$D$48,D$279>0),1,0)+IF(AND($C474=$D$49,D$280>0),1,0)+IF(AND($C474=$D$50,D$281>0),1,0)+IF(AND($C474=$D$51,D$282>0),1,0)+IF(AND($C474=$D$52,D$283>0),1,0)+IF(AND($C474=$D$53,D$284>0),1,0)+IF(AND($C474=$D$54,D$285>0),1,0)+IF(AND($C474=$D$55,D$286>0),1,0)+IF(AND($C474=$D$56,D$287>0),1,0)+IF(AND($C474=$D$57,D$288>0),1,0)+IF(AND($C474=$D$58,D$289>0),1,0)+IF(AND($C474=$D$59,D$290>0),1,0)+IF(AND($C474=$D$60,D$291>0),1,0)+IF(AND($C474=$D$61,D$292>0),1,0)+IF(AND($C474=$D$62,D$293>0),1,0)","v":0},0,0,0]
[null,null,{"$f":"C469","v":"Roche CAP/CTM"},0,0,0,0]
[null,null,{"$f":"C470","v":"Abbott m2000"},{"$f":"IF(AND($C476=$D$43,D$274>0),1,0)+IF(AND($C476=$D$44,D$275>0),1,0)+IF(AND($C476=$D$45,D$276>0),1,0)+IF(AND($C476=$D$46,D$277>0),1,0)+IF(AND($C476=$D$47,D$278>0),1,0)+IF(AND($C476=$D$48,D$279>0),1,0)+IF(AND($C476=$D$49,D$280>0),1,0)+IF(AND($C476=$D$50,D$281>0),1,0)+IF(AND($C476=$D$51,D$282>0),1,0)+IF(AND($C476=$D$52,D$283>0),1,0)+IF(AND($C476=$D$53,D$284>0),1,0)+IF(AND($C476=$D$54,D$285>0),1,0)+IF(AND($C476=$D$55,D$286>0),1,0)+IF(AND($C476=$D$56,D$287>0),1,0)+IF(AND($C476=$D$57,D$288>0),1,0)+IF(AND($C476=$D$58,D$289>0),1,0)+IF(AND($C476=$D$59,D$290>0),1,0)+IF(AND($C476=$D$60,D$291>0),1,0)+IF(AND($C476=$D$61,D$292>0),1,0)+IF(AND($C476=$D$62,D$293>0),1,0)","v":0},{"$f":"IF(AND($C476=$D$43,E$274>0),1,0)+IF(AND($C476=$D$44,E$275>0),1,0)+IF(AND($C476=$D$45,E$276>0),1,0)+IF(AND($C476=$D$46,E$277>0),1,0)+IF(AND($C476=$D$47,E$278>0),1,0)+IF(AND($C476=$D$48,E$279>0),1,0)+IF(AND($C476=$D$49,E$280>0),1,0)+IF(AND($C476=$D$50,E$281>0),1,0)+IF(AND($C476=$D$51,E$282>0),1,0)+IF(AND($C476=$D$52,E$283>0),1,0)+IF(AND($C476=$D$53,E$284>0),1,0)+IF(AND($C476=$D$54,E$285>0),1,0)+IF(AND($C476=$D$55,E$286>0),1,0)+IF(AND($C476=$D$56,E$287>0),1,0)+IF(AND($C476=$D$57,E$288>0),1,0)+IF(AND($C476=$D$58,E$289>0),1,0)+IF(AND($C476=$D$59,E$290>0),1,0)+IF(AND($C476=$D$60,E$291>0),1,0)+IF(AND($C476=$D$61,E$292>0),1,0)+IF(AND($C476=$D$62,E$293>0),1,0)","v":0},{"$f":"IF(AND($C476=$D$43,F$274>0),1,0)+IF(AND($C476=$D$44,F$275>0),1,0)+IF(AND($C476=$D$45,F$276>0),1,0)+IF(AND($C476=$D$46,F$277>0),1,0)+IF(AND($C476=$D$47,F$278>0),1,0)+IF(AND($C476=$D$48,F$279>0),1,0)+IF(AND($C476=$D$49,F$280>0),1,0)+IF(AND($C476=$D$50,F$281>0),1,0)+IF(AND($C476=$D$51,F$282>0),1,0)+IF(AND($C476=$D$52,F$283>0),1,0)+IF(AND($C476=$D$53,F$284>0),1,0)+IF(AND($C476=$D$54,F$285>0),1,0)+IF(AND($C476=$D$55,F$286>0),1,0)+IF(AND($C476=$D$56,F$287>0),1,0)+IF(AND($C476=$D$57,F$288>0),1,0)+IF(AND($C476=$D$58,F$289>0),1,0)+IF(AND($C476=$D$59,F$290>0),1,0)+IF(AND($C476=$D$60,F$291>0),1,0)+IF(AND($C476=$D$61,F$292>0),1,0)+IF(AND($C476=$D$62,F$293>0),1,0)","v":0},{"$f":"IF(AND($C476=$D$43,G$274>0),1,0)+IF(AND($C476=$D$44,G$275>0),1,0)+IF(AND($C476=$D$45,G$276>0),1,0)+IF(AND($C476=$D$46,G$277>0),1,0)+IF(AND($C476=$D$47,G$278>0),1,0)+IF(AND($C476=$D$48,G$279>0),1,0)+IF(AND($C476=$D$49,G$280>0),1,0)+IF(AND($C476=$D$50,G$281>0),1,0)+IF(AND($C476=$D$51,G$282>0),1,0)+IF(AND($C476=$D$52,G$283>0),1,0)+IF(AND($C476=$D$53,G$284>0),1,0)","v":0}]
[null,null,"Total",{"$f":"SUM(D474:D476)","v":0},{"$f":"SUM(E474:E476)","v":0},{"$f":"SUM(F474:F476)","v":0},{"$f":"SUM(G474:G476)","v":0}]
[]
[null,"Capacity"]
[null,null,null,{"$f":"CONCATENATE( J$571,\" (\", J$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( K$571,\" (\", K$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( L$571,\" (\", L$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( M$571,\" (\", M$572,\")\")","v":"0 (0)"}]
[null,null,{"$f":"C468","v":"Roche Amplicor"},{"$f":"D474*Products!$D$8*$D$265","v":0},{"$f":"E474*Products!$D$8*$D$265","v":0},{"$f":"F474*Products!$D$8*$D$265","v":0},{"$f":"G474*Products!$D$8*$D$265","v":0}]
[null,null,{"$f":"C469","v":"Roche CAP/CTM"},{"$f":"D475*Products!$D$9*$D$266","v":0},{"$f":"E475*Products!$D$9*$D$266","v":0},{"$f":"F475*Products!$D$9*$D$266","v":0},{"$f":"G475*Products!$D$9*$D$266","v":0}]
[null,null,{"$f":"C470","v":"Abbott m2000"},{"$f":"D476*Products!$D$10*$D$267","v":0},{"$f":"E476*Products!$D$10*$D$267","v":0},{"$f":"F476*Products!$D$10*$D$267","v":0},{"$f":"G476*Products!$D$10*$D$267","v":0}]
[null,null,null,{"$f":"SUM(D481:D483)","v":0},{"$f":"SUM(E481:E483)","v":0},{"$f":"SUM(F481:F483)","v":0},{"$f":"SUM(G481:G483)","v":0}]
[]
[null,"DBS Bundle Calculations (# samples)"]
[null,null,null,"Baseline",{"$f":"CONCATENATE( J$571,\" (\", J$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( K$571,\" (\", K$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( L$571,\" (\", L$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( M$571,\" (\", M$572,\")\")","v":"0 (0)"},"TOTAL"]
[null,null,"Number of EID Testing Sites",{"$f":"D172","v":0},{"$f":"D488+($I488-$D488)/4","v":0},{"$f":"E488+($I488-$D488)/4","v":0},{"$f":"F488+($I488-$D488)/4","v":0},{"$f":"G488+($I488-$D488)/4","v":0},{"$f":"D174","v":0}]
[null,null,"# of DBS Collected - No Wastage",null,{"$f":"D377","v":0},{"$f":"E377","v":0},{"$f":"F377","v":0},{"$f":"G377","v":0},{"$f":"SUM(E489:H489)","v":0}]
[null,null,"DBS Wastage",null,{"$f":"ROUNDUP(E489*($E$421),0)","v":0},{"$f":"ROUNDUP(F489*($E$421),0)","v":0},{"$f":"ROUNDUP(G489*($E$421),0)","v":0},{"$f":"ROUNDUP(H489*($E$421),0)","v":0},{"$f":"SUM(E490:H490)","v":0}]
[null,null,"# of DBS Collected - Including Wastage",null,{"$f":"ROUNDUP(SUM(E489:E490),0)","v":0},{"$f":"ROUNDUP(SUM(F489:F490),0)","v":0},{"$f":"ROUNDUP(SUM(G489:G490),0)","v":0},{"$f":"ROUNDUP(SUM(H489:H490),0)","v":0},{"$f":"SUM(E491:H491)","v":0}]
[]
[]
[null,null,null,null,null,null,null,null,null,{"$f":"H494*I494","v":0}]
[null,"Commodity Demands (stock not included)"]
[null,null,null,{"$f":"CONCATENATE( J$571,\" (\", J$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( K$571,\" (\", K$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( L$571,\" (\", L$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( M$571,\" (\", M$572,\")\")","v":"0 (0)"},"TOTAL"]
[null,null,{"$f":"Products!C21","v":"DBS Bundles - 20 Test Kits"},{"$f":"IF($D$170=\"Yes\",MAX(((E$491*$D$214)/Products!$D$21*(1+$D$208)),(((E488+$D$180*$D$174*$E$180+$D$182*$D$174*$E$182+$D$184*$D$174*$E$184))*$D$214)),((E$491*$D$214)/Products!$D$21*(1+$D$208)))","v":0},{"$f":"IF($D$170=\"Yes\",MAX(((F$491*$D$214)/Products!$D$21*(1+$D$208)),(((F488+$D$180*$E$180+$D$182*$E$182+$D$184*$E$184))*$D$214)),((F$491*$D$214)/Products!$D$21*(1+$D$208)))","v":0},{"$f":"IF($D$170=\"Yes\",MAX(((G$491*$D$214)/Products!$D$21*(1+$D$208)),(((G488+$D$180*$E$180+$D$182*$E$182+$D$184*$E$184))*$D$214)),((G$491*$D$214)/Products!$D$21*(1+$D$208)))","v":0},{"$f":"IF($D$170=\"Yes\",MAX(((H$491*$D$214)/Products!$D$21*(1+$D$208)),(((H488+$D$180*$E$180+$D$182*$E$182+$D$184*$E$184))*$D$214)),((H$491*$D$214)/Products!$D$21*(1+$D$208)))","v":0},{"$f":"SUM(D497:G497)","v":0}]
[null,null,{"$f":"Products!C22","v":"DBS Bundles - 50 Test Kits"},{"$f":"IF($D$170=\"Yes\",MAX((E$491*$D$216)/Products!$D$22*(1+$D$208),(($E$488+$D$184*$F$184)*$D$216)),(E$491*$D$216)/Products!$D$22*(1+$D$208))","v":0},{"$f":"IF($D$170=\"Yes\",MAX((F$491*$D$216)/Products!$D$22*(1+$D$208),(($E$488+$D$184*$F$184)*$D$216)),(F$491*$D$216)/Products!$D$22*(1+$D$208))","v":0},{"$f":"IF($D$170=\"Yes\",MAX((G$491*$D$216)/Products!$D$22*(1+$D$208),(($E$488+$D$184*$F$184)*$D$216)),(G$491*$D$216)/Products!$D$22*(1+$D$208))","v":0},{"$f":"IF($D$170=\"Yes\",MAX((H$491*$D$216)/Products!$D$22*(1+$D$208),(($E$488+$D$184*$F$184)*$D$216)),(H$491*$D$216)/Products!$D$22*(1+$D$208))","v":0},0]
[null,null,{"$f":"Products!C23","v":"Amplicor Reagent Kits 96 + Consumables"},{"$f":"MIN(MAX(ROUNDUP(D468/Products!$D$23*(1+$D$207),0),D474),D481)","v":0},{"$f":"MIN(MAX(ROUNDUP(E468/Products!$D$23,0),0),E481)","v":0},{"$f":"MIN(MAX(ROUNDUP(F468/Products!$D$23,0),0),F481)","v":0},{"$f":"MIN(MAX(ROUNDUP(G468/Products!$D$23,0),0),G481)","v":0},0]
[null,null,{"$f":"Products!C24","v":"CAP/CTM Reagent Kits 48 + Consumables"},{"$f":"MIN(MAX(ROUNDUP(D469/Products!$D$24*(1+$D$207),0),D475),D482)","v":0},{"$f":"MIN(MAX(ROUNDUP(E469/Products!$D$24,0),0),E482)","v":0},{"$f":"MIN(MAX(ROUNDUP(F469/Products!$D$24,0),0),F482)","v":0},{"$f":"MIN(MAX(ROUNDUP(G469/Products!$D$24,0),0),G482)","v":0},0]
[null,null,{"$f":"Products!C25","v":"m2000 Reagent Kits 96 + Consumables"},{"$f":"MIN(MAX(ROUNDUP(D470/Products!$D$25*(1+$D$207),0),D476),D483)","v":0},{"$f":"MIN(MAX(ROUNDUP(E470/Products!$D$25,0),0),E483)","v":0},{"$f":"MIN(MAX(ROUNDUP(F470/Products!$D$25,0),0),F483)","v":0},{"$f":"MIN(MAX(ROUNDUP(G470/Products!$D$25,0),0),G483)","v":0},0]
[null,null,{"$f":"Products!C26","v":"Generic Lab Consumables Amplicor"},{"$f":"MAX(D474,ROUNDUP(D499/10,0))","v":0},{"$f":"MAX(ROUNDUP(E499/10,0),0)","v":0},{"$f":"MAX(ROUNDUP(F499/10,0),0)","v":0},{"$f":"MAX(ROUNDUP(G499/10,0),0)","v":0},0]
[null,null,{"$f":"Products!C27","v":"Generic Lab Consumables CAP/CTM"},{"$f":"MAX(D475,ROUNDUP(D500/20,0))","v":0},{"$f":"MAX(ROUNDUP(E500/20,0),0)","v":0},{"$f":"MAX(ROUNDUP(F500/20,0),0)","v":0},{"$f":"MAX(ROUNDUP(G500/20,0),0)","v":0},0]
[null,null,{"$f":"Products!C28","v":"Generic Lab Consumables Abbott"},{"$f":"MAX(D476,ROUNDUP(D501/10,0))","v":0},{"$f":"MAX(ROUNDUP(E501/10,0),0)","v":0},{"$f":"MAX(ROUNDUP(F501/10,0),0)","v":0},{"$f":"MAX(ROUNDUP(G501/10,0),0)","v":0},0]
[]
[null,"Existing  Stock / Pipeline"]
[]
[null,null,null,"Existing Stock (# months)","Stock in the pipeline  (# months)","Total"]
[]
[null,null,"DBS Bundles - 20 Test Kits",{"$f":"D193*20/$D$163","v":{"$error":"#DIV/0!"}},{"$f":"E193*20/$D$163","v":{"$error":"#DIV/0!"}},{"$f":"D510+E510","v":{"$error":"#DIV/0!"}}]
[null,null,"DBS Bundles - 50 Test Kits",{"$f":"D194*50/$D$163","v":{"$error":"#DIV/0!"}},{"$f":"E194*50/$D$163","v":{"$error":"#DIV/0!"}},{"$f":"D511+E511","v":{"$error":"#DIV/0!"}}]
[null,null,"Amplicor Reagent Kits 96 + Consumables",{"$f":"D195*96/$D$164","v":{"$error":"#DIV/0!"}},{"$f":"E195*96/$D$164","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"}]
[null,null,"CAP/CTM Reagent Kits 48 + Consumables",{"$f":"D196*48/$D164","v":{"$error":"#DIV/0!"}},{"$f":"E196*48/$D164","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"}]
[null,null,"m2000 Reagent Kits 96 + Consumables",{"$f":"D197*96/$D164","v":{"$error":"#DIV/0!"}},{"$f":"E197*96/$D164","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"}]
[null,null,"Generic Lab Consumables Bundles Amplicor",{"$f":"ROUND((D198*960/$D164),0)","v":{"$error":"#DIV/0!"}},{"$f":"ROUND((E198*960/$D164),0)","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"}]
[null,null,"Generic Lab Consumables Bundles CAP/CTM",{"$f":"ROUND((D199*960/$D164),0)","v":{"$error":"#DIV/0!"}},{"$f":"ROUND((E199*960/$D164),0)","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"}]
[null,null,"Generic Lab Consumables Bundles Abbott",{"$f":"D200*1000/$D164","v":{"$error":"#DIV/0!"}},{"$f":"E200*1000/$D164","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"}]
[null,null,null,{"$f":"SUM(D510:D517)","v":{"$error":"#DIV/0!"}},{"$f":"SUM(E510:E517)","v":{"$error":"#DIV/0!"}}]
[]
[null,"Stock Calculations - Leftover Stock at Quarter End (Stock previous quarter+Procurement-Demand)"]
[null,null,null,{"$f":"CONCATENATE( J$571,\" (\", J$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( K$571,\" (\", K$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( L$571,\" (\", L$572,\")\")","v":"0 (0)"},{"$f":"CONCATENATE( M$571,\" (\", M$572,\")\")","v":"0 (0)"},"TOTAL"]
[null,null,{"$f":"C497","v":"DBS Bundles - 20 Test Kits"},{"$f":"MAX(SUM(F193)+D534-D497,0)","v":{"$error":"#DIV/0!"}},{"$f":"MAX(D522+E534-E497,0)","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$f":"MAX(SUM(I193:$I193)-H497,0)","v":0}]
[null,null,"DBS Bundles - 50 Test Kits",{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$f":"MAX(SUM(I194:$I194)-H498,0)","v":0}]
[null,null,"Amplicor Reagent Kits 96 + Consumables",{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$f":"MAX(SUM(I195:$I195)-H499,0)","v":0}]
[null,null,"CAP/CTM Reagent Kits 48 + Consumables",{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$f":"MAX(SUM(I197:$I197)-H500,0)","v":0}]
[null,null,"m2000 Reagent Kits 96 + Consumables",{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$f":"MAX(SUM(I198:$I198)-H501,0)","v":0}]
[null,null,"Generic Lab Consumables Amplicor",{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$f":"MAX(SUM(#REF!)-H503,0)","v":{"$error":"#REF!"}}]
[null,null,"Generic Lab Consumables CAP/CTM",{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$f":"MAX(SUM(I199:$I199)-H504,0)","v":0}]
[null,null,"Generic Lab Consumables Abbott",{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$f":"MAX(SUM(I200:$I200)-H505,0)","v":0}]
[]
[]
[null,"Forecast/Procurement Outputs (Demand-Stock)"]
[null,null,null,{"$f":"CONCATENATE(\"Procurement for Consumption in \", J$571,\" (\", J$572,\")\")","v":"Procurement for Consumption in 0 (0)"},{"$f":"CONCATENATE(\"Procurement for Consumption in \", K$571,\" (\", K$572,\")\")","v":"Procurement for Consumption in 0 (0)"},{"$f":"CONCATENATE(\"Procurement for Consumption in \", L$571,\" (\", L$572,\")\")","v":"Procurement for Consumption in 0 (0)"},{"$f":"CONCATENATE(\"Procurement for Consumption in \", M$571,\" (\", M$572,\")\")","v":"Procurement for Consumption in 0 (0)"},"Total Procurement for Consumption"]
[null,null,{"$f":"C522","v":"DBS Bundles - 20 Test Kits"},{"$f":"IF(D497=0,0,MAX(D497+D545-SUM(F193),0))","v":0},{"$f":"IF(E497=0,0,MAX(E497+E545-D522,0))","v":0},0,0,{"$f":"SUM(D534:G534)","v":0}]
[null,null,{"$f":"C523","v":"DBS Bundles - 50 Test Kits"},0,0,0,0,0]
[null,null,"Amplicor Reagent Kits 96 + Consumables",0,0,0,0,0]
[null,null,"CAP/CTM Reagent Kits 48 + Consumables",0,0,0,0,0]
[null,null,"m2000 Reagent Kits 96 + Consumables",0,0,0,0,0]
[null,null,"Generic Lab Consumables Amplicor",0,0,0,0,0]
[null,null,"Generic Lab Consumables CAP/CTM",0,0,0,0,0]
[null,null,"Generic Lab Consumables Abbott",0,0,0,0,0]
[]
[null,"Buffer Stock"]
[null,null,null,{"$f":"CONCATENATE(\"Procurement for Consumption in \", J$571,\" (\", J$572,\")\")","v":"Procurement for Consumption in 0 (0)"},{"$f":"CONCATENATE(\"Procurement for Consumption in \", K$571,\" (\", K$572,\")\")","v":"Procurement for Consumption in 0 (0)"},{"$f":"CONCATENATE(\"Procurement for Consumption in \", L$571,\" (\", L$572,\")\")","v":"Procurement for Consumption in 0 (0)"},{"$f":"CONCATENATE(\"Procurement for Consumption in \", M$571,\" (\", M$572,\")\")","v":"Procurement for Consumption in 0 (0)"},"Total Procurement for Consumption"]
[null,null,{"$f":"C534","v":"DBS Bundles - 20 Test Kits"},{"$f":"IF(D497=0,0,ROUNDUP((D$163*$D$214*$D$205)/Products!$D$21,0))","v":0},{"$f":"IF(E497=0,0,ROUNDUP((E$163*$D$214*$D$205)/Products!$D$21,0))","v":0},{"$f":"IF(F497=0,0,ROUNDUP((F$163*$D$214*$D$205)/Products!$D$21,0))","v":0},{"$f":"IF(G497=0,0,ROUNDUP((G$163*$D$214*$D$205)/Products!$D$21,0))","v":0},{"$f":"SUM(D545:G545)","v":0}]
[null,null,{"$f":"C535","v":"DBS Bundles - 50 Test Kits"},{"$f":"IF(D498=0,0,ROUNDUP((D$163*$D$216*$D$205)/Products!$D$22,0))","v":0},{"$f":"IF(E498=0,0,ROUNDUP((E$163*$D$216*$D$205)/Products!$D$22,0))","v":0},{"$f":"IF(F498=0,0,ROUNDUP((F$163*$D$216*$D$205)/Products!$D$22,0))","v":0},{"$f":"IF(G498=0,0,ROUNDUP((G$163*$D$216*$D$205)/Products!$D$22,0))","v":0},0]
[null,null,"Amplicor Reagent Kits 96 + Consumables",{"$f":"IF(D499=0,0,(ROUNDUP(D$164*$D$205/Products!$D$23,0)))","v":0},{"$f":"IF(E499=0,0,(ROUNDUP(E$164*$D$205/Products!$D$23,0)))","v":0},{"$f":"IF(F499=0,0,(ROUNDUP(F$164*$D$205/Products!$D$23,0)))","v":0},{"$f":"IF(G499=0,0,(ROUNDUP(G$164*$D$205/Products!$D$23,0)))","v":0},0]
[null,null,"CAP/CTM Reagent Kits 48 + Consumables",{"$f":"IF(D500=0,0,(ROUNDUP(D$164*$D$205/Products!$D$24,0)))","v":0},{"$f":"IF(E500=0,0,(ROUNDUP(E$164*$D$205/Products!$D$24,0)))","v":0},{"$f":"IF(F500=0,0,(ROUNDUP(F$164*$D$205/Products!$D$24,0)))","v":0},{"$f":"IF(G500=0,0,(ROUNDUP(G$164*$D$205/Products!$D$24,0)))","v":0},0]
[null,null,"m2000 Reagent Kits 96 + Consumables",{"$f":"IF(D501=0,0,(ROUNDUP(D$164*$D$205/Products!$D$25,0)))","v":0},{"$f":"IF(E501=0,0,(ROUNDUP(E$164*$D$205/Products!$D$25,0)))","v":0},{"$f":"IF(F501=0,0,(ROUNDUP(F$164*$D$205/Products!$D$25,0)))","v":0},{"$f":"IF(G501=0,0,(ROUNDUP(G$164*$D$205/Products!$D$25,0)))","v":0},0]
[null,null,"Generic Lab Consumables Amplicor",{"$f":"IF(D502=0,0,(ROUNDUP(D$164*$D$205/Products!$D$26,0)))","v":0},{"$f":"IF(E502=0,0,(ROUNDUP(E$164*$D$205/Products!$D$26,0)))","v":0},{"$f":"IF(F502=0,0,(ROUNDUP(F$164*$D$205/Products!$D$26,0)))","v":0},{"$f":"IF(G502=0,0,(ROUNDUP(G$164*$D$205/Products!$D$26,0)))","v":0},0]
[null,null,"Generic Lab Consumables CAP/CTM",{"$f":"IF(D503=0,0,(ROUNDUP(D$164*$D$205/Products!$D$27,0)))","v":0},{"$f":"IF(E503=0,0,(ROUNDUP(E$164*$D$205/Products!$D$27,0)))","v":0},{"$f":"IF(F503=0,0,(ROUNDUP(F$164*$D$205/Products!$D$27,0)))","v":0},{"$f":"IF(G503=0,0,(ROUNDUP(G$164*$D$205/Products!$D$27,0)))","v":0},0]
[null,null,"Generic Lab Consumables Abbott",{"$f":"IF(D504=0,0,(ROUNDUP(D$164*$D$205/Products!$D$28,0)))","v":0},{"$f":"IF(E504=0,0,(ROUNDUP(E$164*$D$205/Products!$D$28,0)))","v":0},{"$f":"IF(F504=0,0,(ROUNDUP(F$164*$D$205/Products!$D$28,0)))","v":0},{"$f":"IF(G504=0,0,(ROUNDUP(G$164*$D$205/Products!$D$28,0)))","v":0},0]
[]
[null,null,null,"Q1","Q2","Q3","Q4","Total"]
[null,null,{"$f":"C534","v":"DBS Bundles - 20 Test Kits"},{"$f":"D232*Products!$D$21","v":0},{"$f":"E232*Products!$D$21","v":0},{"$f":"F232*Products!$D$21","v":0},{"$f":"G232*Products!$D$21","v":0},{"$f":"SUM(D555:G555)","v":0}]
[null,null,{"$f":"C535","v":"DBS Bundles - 50 Test Kits"},{"$f":"D233*Products!$D$22","v":0},{"$f":"E233*Products!$D$22","v":0},{"$f":"F233*Products!$D$22","v":0},{"$f":"G233*Products!$D$22","v":0},{"$f":"SUM(D556:G556)","v":0}]
[null,null,"Number of Tests Roche CAP/CTM",{"$f":"D235*Products!$D$24","v":0},{"$f":"E235*Products!$D$24","v":0},{"$f":"F235*Products!$D$24","v":0},{"$f":"G235*Products!$D$24","v":0},{"$f":"SUM(D557:G557)","v":0}]
[null,null,"Number of Tests Roche Amplicor",{"$f":"D234*Products!$D$23","v":0},{"$f":"E234*Products!$D$23","v":0},{"$f":"F234*Products!$D$23","v":0},{"$f":"G234*Products!$D$23","v":0},{"$f":"SUM(D558:G558)","v":0}]
[null,null,"Number of Tests Abbott",{"$f":"D236*Products!$D$25","v":0},{"$f":"E236*Products!$D$25","v":0},{"$f":"F236*Products!$D$25","v":0},{"$f":"G236*Products!$D$25","v":0},{"$f":"SUM(D559:G559)","v":0}]
[]
[]
[]
[null,"Other Calculations"]
[]
[]
[]
[null,"Other Cacluations"]
[]
[null,null,"Quarter 4","Quarter 1","Quarter 2","Quarter 3","Quarter 4","Quarter 1","Quarter 2","Quarter 3","Quarter 4","Quarter 1","Quarter 2","Quarter 3","Quarter 4","Quarter 1","Quarter 2","Quarter 3","Quarter 4","Quarter 1","Quarter 2","Quarter 3","Quarter 4"]
[]
[null,null,null,null,{"$f":"IF(F571=D569,C569,IF(F571=E569,D569,IF(F571=F569,E569,IF(F571=G569,F569,0))))","v":0},{"$f":"IF(G571=E569,D569,IF(G571=F569,E569,IF(G571=G569,F569,IF(G571=H569,G569,0))))","v":0},{"$f":"IF(H571=F569,E569,IF(H571=G569,F569,IF(H571=H569,G569,IF(H571=I569,H569,0))))","v":0},{"$f":"IF(I571=G569,F569,IF(I571=H569,G569,IF(I571=I569,H569,IF(I571=J569,I569,0))))","v":0},{"$f":"IF(J571=H569,G569,IF(J571=I569,H569,IF(J571=J569,I569,IF(J571=K569,J569,0))))","v":0},{"$f":"D9","v":0},{"$f":"IF(J571=H569,I569,IF(J571=I569,J569,IF(J571=J569,K569,IF(J571=K569,L569,0))))","v":0},0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,null,{"$f":"IF(F571=$L$569,F$572-1,F$572)","v":0},{"$f":"IF(G571=$L$569,G$572-1,G$572)","v":0},{"$f":"IF(H571=$L$569,H$572-1,H$572)","v":0},{"$f":"IF(I571=$L$569,I$572-1,I$572)","v":0},{"$f":"IF(J571=$L$569,J$572-1,J$572)","v":0},{"$f":"E9","v":0},{"$f":"IF(J571=$K$569,J$572+1,J$572)","v":0},0,0,0,0,0,0,0,0,0,0,0,0]

{"sheet":"Products","index":4,"n_rows":28,"n_cols":7,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
[]
[null,"EID"]
[]
[]
[null,"Analyzers"]
[]
[null,null,"Platform","Testing Capacity (# tests per year)"]
[null,null,"Roche Amplicor",25000]
[null,null,"Roche CAP/CTM",50000]
[null,null,"Abbott m2000",50000]
[]
[]
[]
[]
[]
[null,"Products and Pricing"]
[]
[]
[null,null,"Item","Tests Per Pack","Cost per Pack","Cost per Test"]
[]
[null,null,"DBS Bundles - 20 Test Kits",20,{"$f":"F21*D21","v":36.17},1.8085,"This price is part of a global pricing deal with Lasec. The price is ex-works, so taxes, transportation, and distribution margin should be added to this cost if used for budgeting purposes."]
[null,null,"DBS Bundles - 50 Test Kits",50,71.56,1.4312,"This price is part of a global pricing deal with Lasec. The price is ex-works, so taxes, transportation, and distribution margin should be added to this cost if used for budgeting purposes."]
[null,null,"Amplicor Reagent Kits 96 + Consumables",96,0,null,"Use current pricing. If not available, use the price/ test from the global deal +/- 20%."]
[null,null,"CAP/CTM Reagent Kits 48 + Consumables",48,0,null,"Use current pricing. If not available, use the price/ test from the global deal +/- 20%."]
[null,null,"m2000 Reagent Kits 96 + Consumables",96,1200,12.5,"This price is part of a global pricing deal with Abbott. The price is ex-works, so taxes, transportation, and distribution margin should be added to this cost if used for budgeting purposes."]
[null,null,"Generic Lab Consumables Amplicor",960,1795.0000000003201,1.869791666667,"This price is part of a global pricing deal with LabMate. The price is ex-works, so taxes, transportation, and distribution margin should be added to this cost if used for budgeting purposes."]
[null,null,"Generic Lab Consumables CAP/CTM",960,315,0.328125,"This price is part of a global pricing deal with Lasec. The price is ex-works, so taxes, transportation, and distribution margin should be added to this cost if used for budgeting purposes."]
[null,null,"Generic Lab Consumables Abbott",960,965.6899999968,1.00592708333,"This price is part of a global pricing deal with Lasec. The price is ex-works, so taxes, transportation, and distribution margin should be added to this cost if used for budgeting purposes."]

{"sheet":"List","index":5,"n_rows":8,"n_cols":2,"rows_kept":8,"cut":false,"hidden":true,"merged":[]}
["EID","Roche CAP/CTM"]
[null,"Roche Amplicor"]
["Target # HEI","Abbott m2000"]
["Target # Tests"]
[]
[]
["# Samples received"]
["# Tests performed"]

{"sheet":"Forms","index":6,"n_rows":14,"n_cols":8,"rows_kept":14,"cut":false,"hidden":true,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Data to be collected"]
[]
[null,"CD4"]
[]
[null,"Lab","Analyzers","Consumption previous 4 quarters","Stock expiring later than initial forcasting quarter","Wastage (if not included in the consumption data)","Machine downtime (# days)","Stock Out (# days)"]

{"sheet":"Assumtpions and Data Source","index":7,"n_rows":3,"n_cols":2,"rows_kept":3,"cut":false,"hidden":true,"merged":[]}
[]
[]
[null,"Assumptions and data sources"]

{"sheet":"Excercises","index":8,"n_rows":55,"n_cols":6,"rows_kept":55,"cut":false,"hidden":true,"merged":[]}
[]
[]
["A: Complete no consumption"]
[null,"Procurement for Consumption in Quarter 1 (2014)","Procurement for Consumption in Quarter 2 (2014)","Procurement for consumption in Quarter 3 (2014)","Procurement for Consumption in Quarter 4 (2014)","Total Procurement"]
[]
["DBS Bundles - 20 Test Kits",302,235,248,260,1045]
["DBS Bundles - 50 Test Kits",0,0,0,0,0]
["Amplicor Reagent Kits",32,7,7,7,53]
["CAP/CTM Reagent Kits*",0,0,0,0,0]
["m2000 Reagent Kits*",52,27,27,27,133]
["Generic Lab Consumables Bundles Amplicor",4,1,1,1,7]
["Generic Lab Consumables Bundles CAP/CTM",0,0,0,0,0]
["Generic Lab Consumables Bundles Abbott",6,3,3,3,15]
[]
[]
[]
[]
[]
["B: No Min 1 DBS kit per site"]
[null,"Procurement for Consumption in Quarter 1 (2014)","Procurement for Consumption in Quarter 2 (2014)","Procurement for consumption in Quarter 3 (2014)","Procurement for Consumption in Quarter 4 (2014)","Total Procurement"]
[]
["DBS Bundles - 20 Test Kits",189,110,110,110,519]
["DBS Bundles - 50 Test Kits",0,0,0,0,0]
["Amplicor Reagent Kits",32,7,7,7,53]
["CAP/CTM Reagent Kits*",0,0,0,0,0]
["m2000 Reagent Kits*",52,27,27,27,133]
["Generic Lab Consumables Bundles Amplicor",4,1,1,1,7]
["Generic Lab Consumables Bundles CAP/CTM",0,0,0,0,0]
["Generic Lab Consumables Bundles Abbott",6,3,3,3,15]
[]
[]
["C: No wastage/Control/Training etc"]
[null,"Procurement for Consumption in Quarter 1 (2014)","Procurement for Consumption in Quarter 2 (2014)","Procurement for consumption in Quarter 3 (2014)","Procurement for Consumption in Quarter 4 (2014)","Total Procurement"]
[]
["DBS Bundles - 20 Test Kits",175,100,100,100,475]
["DBS Bundles - 50 Test Kits",0,0,0,0,0]
["Amplicor Reagent Kits",28,6,6,6,46]
["CAP/CTM Reagent Kits*",0,0,0,0,0]
["m2000 Reagent Kits*",45,23,23,23,114]
["Generic Lab Consumables Bundles Amplicor",4,1,1,1,7]
["Generic Lab Consumables Bundles CAP/CTM",0,0,0,0,0]
["Generic Lab Consumables Bundles Abbott",6,3,3,3,15]
[]
[]
["D: Consumption Data available"]
[null,"Procurement for Consumption in Quarter 1 (2014)","Procurement for Consumption in Quarter 2 (2014)","Procurement for consumption in Quarter 3 (2014)","Procurement for Consumption in Quarter 4 (2014)","Total Procurement"]
[]
["DBS Bundles - 20 Test Kits",133,79,80,82,374]
["DBS Bundles - 50 Test Kits",0,0,0,0,0]
["Amplicor Reagent Kits",30,11,12,11,64]
["CAP/CTM Reagent Kits*",0,0,0,0,0]
["m2000 Reagent Kits*",34,15,16,16,81]
["Generic Lab Consumables Bundles Amplicor",4,2,2,2,10]
["Generic Lab Consumables Bundles CAP/CTM",0,0,0,0,0]
["Generic Lab Consumables Bundles Abbott",4,2,2,2,10]