{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Part1. Bench_Indicator","Part2. Bench_Deflator","Part3. Spline","Spline_Solution","Multi_Bench_Solution","XLPBM_Solution"],"not_rendered":{"charts":7,"images":0,"macros":1,"external_links":1,"defined_names":91,"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":"Part1. Bench_Indicator","index":0,"n_rows":48,"n_cols":14,"rows_kept":48,"cut":false,"hidden":false,"merged":[]}
["Example of the use of XLPBM to benchmark quarterly indicators to annual 'benchmark' data"]
[]
[null,"Annual benchmark",null,null,"Indicator data",null,null,null,null,"Benchmak series","BM","BM","BM","BM"]
["Year","Y","2. Check A=SumQ",null,"Year","Q","X","3. Growth X",null,"Year Q","1. Denton B/I=0","3. Growth of 1.","4. Denton, B/I=5","5. Cholette-Dagum"]
[2006,954.21021305876263,{"$f":"SUMIF($E$5:$E$48,A5,$K$5:$K$48)","v":954.21021305876275},null,{"$f":"A5","v":2006},1,77.134930053836669,null,null,{"$f":"E5&\" Q\"&F5","v":"2006 Q1"},{"$f":"Bench($G$5:$G$48,$B$5:$B$15,1,0)","v":217.549484217345},null,{"$f":"Bench($G$5:$G$48,$B$5:$B$15,1,5)","v":217.54949039329128},{"$f":"Bench(G5:G48,B5:B15,2,0)","v":217.62886421075831}]
[{"$f":"A5+1","v":2007},959.09882056550077,{"$f":"SUMIF($E$5:$E$48,A6,$K$5:$K$48)","v":959.09882056550066},null,{"$f":"E5","v":2006},2,87.458463857824043,{"$f":"(G6-G5)/G5","v":0.1338373392804274},null,{"$f":"E6&\" Q\"&F6","v":"2006 Q2"},246.6543622593264,{"$f":"(K6-K5)/K5","v":0.13378509329355104},246.65436669483103,246.59470658370006]
[{"$f":"A6+1","v":2008},900.90758270884305,900.90758270884317,null,{"$f":"E6","v":2006},3,83.192889629928061,{"$f":"(G7-G6)/G6","v":-4.8772572027222766E-2},null,"2006 Q3",234.60132412811708,{"$f":"(K7-K6)/K6","v":-4.8866105674372988E-2},234.60132313685693,234.63093758533671]
[2009,922.0047187095314,922.0047187095314,null,2006,4,90.583587608032929,8.8838096752996018E-2,null,"2006 Q4",255.40504245397423,8.8676900708779122E-2,255.40503283378348,255.3557046789675]
[2010,944.4689737415099,944.46897374151024,null,{"$f":"E5+1","v":2007},1,80.633233135546575,-0.10984721112551694,null,"2007 Q1",227.30354196388308,-0.11002719531332371,227.30352301844314,227.41728089751689]
[2011,957.16129303377704,957.16129303377693,null,{"$f":"E6+1","v":2007},2,88.069227866701468,9.2219974841574195E-2,null,"2007 Q2",248.26383913227198,9.2212804900855205E-2,248.26382155675401,248.26973749479399]
[2012,980.28188951915706,980.28188951915718,null,2007,3,83.033797521013796,-5.7175820291159189E-2,null,"2007 Q3",234.11763852095234,-5.6980511784411408E-2,234.11763977606617,234.15129986782969]
[2013,1031.2296657461998,1031.2296657461998,null,2007,4,88.422714871932058,6.4900287735900092E-2,null,"2007 Q4",249.41380094839329,6.5335369535055421E-2,249.41383621423734,249.26050230536021]
[2014,1088.4186930832047,1088.4186930832047,null,2008,1,74.309782509781783,-0.15960754408627789,null,"2008 Q1",209.73602778806861,-0.15908411246470874,209.73609931538212,209.856323742873]
[2015,1136.2220742508855,1136.2220742508857,null,2008,2,82.277586405407902,0.10722415846900474,null,"2008 Q2",232.31005851043324,0.1076306772872372,232.31012772727641,232.26017499362035]
[2016,null,null,null,2008,3,78.774641695851429,-4.2574713996790671E-2,null,"2008 Q3",222.43830594246435,-4.2493866306376631E-2,222.43830290919743,222.41293737344691]
[null,null,null,null,2008,4,83.742853345590447,6.3068667058128469E-2,null,"2008 Q4",236.42319046787702,6.2870846215804133E-2,236.42305275698726,236.37814659890282]
[null,null,null,null,2009,1,74.055565332621882,-0.11567898185879832,null,"2009 Q1",208.97494672595138,-0.11609793306488286,208.97464884702467,209.0712609364478]
[null,null,null,null,2009,2,83.817587956548095,0.13182024308476908,null,"2009 Q2",236.47412877988333,0.13159080782058638,236.47382852838041,236.49856375081583]
[null,null,null,null,2009,3,80.88933086382589,-3.4936069673589795E-2,null,"2009 Q3",228.23646193984175,-3.4835382976331555E-2,228.23646578520612,228.27459642046537]
[null,null,null,null,2009,4,87.971605879411001,8.755511932108638E-2,null,"2009 Q4",248.31918126385494,8.7990845780401963E-2,248.31977554892018,248.16029760180234]
[null,null,null,null,2010,1,77.091406930939399,-0.12367853058617427,null,"2010 Q1",217.7646794820771,-0.12304527433711107,217.76597546161557,218.04668896488917]
[null,null,null,null,2010,2,86.156735680874874,0.11759194845226324,null,"2010 Q2",243.54535817415839,0.11838778792500713,243.54663919586326,243.61889788627371]
[null,null,null,null,2010,3,82.016994851794763,-4.8048951673537621E-2,null,"2010 Q3",232.00564676259074,-4.7382185799310722E-2,232.00560716673121,231.94296758174914]
[null,null,null,null,2010,4,88.72477603693585,8.1785259229042603E-2,null,"2010 Q4",251.15328932268389,8.2530933308217078E-2,251.15075191730011,250.86041930859787]
[null,null,null,null,2011,1,75.857321989071181,-0.1450266162690339,null,"2011 Q1",214.87482083132113,-0.14444751485914992,214.86947114886667,215.16759785997922]
[null,null,null,null,2011,2,87.396399381415037,0.15211553861611801,null,"2011 Q2",247.76076127700324,0.15304697087565183,247.75524285073675,247.91542893975551]
[null,null,null,null,2011,3,84.40979174350791,-3.41731199345294E-2,null,"2011 Q3",239.52355141583485,-3.3246627991907772E-2,239.52363943914895,239.43901158208016]
[null,null,null,null,2011,4,89.765415792250352,6.3447900274607075E-2,null,"2011 Q4",255.00215950961777,6.4622489113442719E-2,255.01293959502459,254.63925465196218]
[null,null,null,null,2012,1,76.794437090788975,-0.14449861995271002,null,"2012 Q1",218.42925780411088,-0.14342192935086689,218.45210302020789,218.69540041316975]
[null,null,null,null,2012,2,88.885465670881302,0.15744667241714275,null,"2012 Q2",253.12052891834853,0.15882154003997515,253.14433726642434,253.30517136795422]
[null,null,null,null,2012,3,85.492699872728664,-3.8170085205101213E-2,null,"2012 Q3",243.72829035714119,-3.7105795414314573E-2,243.72817094119063,243.58874549122777]
[null,null,null,null,2012,4,92.860016751213507,8.617480661451124E-2,null,"2012 Q4",265.00381243955655,8.7291967835329276E-2,264.95727829133426,264.69257224680518]
[null,null,null,null,2013,1,82.783105591019208,-0.10851722315743188,null,"2013 Q1",236.46939032896455,-0.10767551548753769,236.36581369556771,236.37814818897994]
[null,null,null,null,2013,2,91.352733015869788,0.10351903765471035,null,"2013 Q2",261.07992365502741,0.10407492188238784,260.97848489144786,261.03909255083153]
[null,null,null,null,2013,3,89.980676000736025,-1.5019331878066625E-2,null,"2013 Q3",257.16348401017547,-1.5000922284728622E-2,257.16469123437645,257.08829040725459]
[null,null,null,null,2013,4,96.796774414567423,7.5750691334833309E-2,null,"2013 Q4",276.51686775203234,7.5257122201265153E-2,276.72067592480778,276.72413459913361]
[null,null,null,null,2014,1,86.712976286226791,-0.10417493960235799,null,"2014 Q1",247.46973331950053,-0.10504651910992983,247.92404608558587,246.97332461936708]
[null,null,null,null,2014,2,96.758336327054877,0.11584609906214993,null,"2014 Q2",275.86886768262673,0.11475801093809297,276.3212143465442,275.73003656332031]
[null,null,null,null,2014,3,95.270515352376719,-1.5376669661300739E-2,null,"2014 Q3",271.36141819906152,-1.633910169505174E-2,271.35992815247789,271.4689621576818]
[null,null,null,null,2014,4,103.22091314357822,8.345076923112453E-2,null,"2014 Q4",293.71867388201605,8.2389220366448732E-2,292.81350449859679,294.24636974283561]
[null,null,null,null,2015,1,93.120793844151123,-9.7849544165318797E-2,null,"2015 Q1",264.71816629243568,-9.8735661598518557E-2,262.67650656593202,264.47907035710887]
[null,null,null,null,2015,2,101.62921844979455,9.136976022652149E-2,null,"2015 Q2",288.70247431117798,9.0603181317925469E-2,286.74018150135709,288.69098534003029]
[null,null,null,null,2015,3,98.543740365074555,-3.036014771917428E-2,null,"2015 Q3",279.82650492316651,-3.0744348170859477E-2,279.8778807534195,279.90992461523928]
[null,null,null,null,2015,4,106.70624734097973,8.2831308672327339E-2,null,"2015 Q4",302.97492872410538,8.2724200151429048E-2,306.92750543017701,303.14209393850712]
[null,null,null,null,2016,1,94.35,-0.11579685022091871,null,"2016 Q1",267.95118464773543,{"$f":"(K45-K44)/K44","v":-0.11559947954724407},276.59695494682813,267.49749621258115]
[null,null,null,null,2016,2,101.74,7.8325384207737159E-2,null,"2016 Q2",288.98803467008611,7.8510009388489815E-2,302.572262982403,288.44934037803932]
[null,null,null,null,2016,3,97.4,-4.2657755061922445E-2,null,"2016 Q3",276.69212981163986,-4.254814519391252E-2,292.42537587855304,276.14473906841982]
[null,null,null,null,2016,4,104.2,6.9815195071868549E-2,null,"2016 Q4",296.02706235789987,6.9878867026042263E-2,314.37626023455562,295.42383789455181]

{"sheet":"Part2. Bench_Deflator","index":1,"n_rows":32,"n_cols":11,"rows_kept":32,"cut":false,"hidden":false,"merged":[[9,7,10,17]]}
["Example of the use of XLPBM to benchmark a quarterly price index to an annual deflator "]
[]
["A) We have an annual deflator for Hotels and Restaurants"]
[null,"Def","1. Annual CPI Index",{"$f":"K12","v":"2. Benchmarked Price"}]
[2006,106.46589318503754,{"$f":"AVERAGEIF($A$13:$A$32,A5,$C$13:$C$32)","v":103.94922961428198},{"$f":"AVERAGEIF($A$13:$A$32,A5,$K$13:$K$32)","v":106.46589318503753}]
[{"$f":"A5+1","v":2007},102.61394374033897,{"$f":"AVERAGEIF($A$13:$A$32,A6,$C$13:$C$32)","v":100.30001112845488},{"$f":"AVERAGEIF($A$13:$A$32,A6,$K$13:$K$32)","v":102.61394374033897}]
[{"$f":"A6+1","v":2008},102.75704348610645,{"$f":"AVERAGEIF($A$13:$A$32,A7,$C$13:$C$32)","v":101.13361161045746},102.75704348610647]
[2009,99.114984293263504,{"$f":"AVERAGEIF($A$13:$A$32,A8,$C$13:$C$32)","v":97.315130486749155},99.114984293263504]
[2010,101.75045472117323,{"$f":"AVERAGEIF($A$13:$A$32,A9,$C$13:$C$32)","v":97.607644153159157},101.75045472117323]
[null,null,null,null,null,null,null,"C) We can use XLPBM to benchmark the price to the deflator  (multiplying the result by 4)"]
["B) ...and a quarterly price index for 'Meals Out' (based on the CPI)"]
["Year","Q","CPI Price",null,null,null,null,"Year","Q",null,"2. Benchmarked Price"]
[2006,1,100.66164633631833,null,null,null,null,{"$f":"A13","v":2006},{"$f":"B13","v":1},{"$f":"H13&\" Q\"&I13","v":"2006 Q1"},{"$f":"Bench(C13:C32,B5:B9)*4","v":103.08968150418578}]
[2006,2,103.84881980909985,null,null,null,null,{"$f":"A14","v":2006},{"$f":"B14","v":2},{"$f":"H14&\" Q\"&I14","v":"2006 Q2"},106.35733374492462]
[2006,3,102.6056014323681,null,null,null,null,2006,3,"2006 Q3",105.09131477781668]
[2006,4,108.68085087934161,null,null,null,null,2006,4,"2006 Q4",111.32524271322309]
[2007,1,101.87870852225063,null,null,null,null,2007,1,"2007 Q1",104.37218774937929]
[2007,2,101.25174711727117,null,null,null,null,2007,2,"2007 Q2",103.67790089271406]
[2007,3,98.74764919558956,null,null,null,null,2007,3,"2007 Q3",100.99866310593231]
[2007,4,99.321939678708176,null,null,null,null,2007,4,"2007 Q4",101.40702321333019]
[2008,1,100.66484206010711,null,null,null,null,2008,1,"2008 Q1",102.5322334956001]
[2008,2,100.69155942835167,null,null,null,null,2008,2,"2008 Q2",102.36057981650799]
[2008,3,102.22586955938367,null,null,null,null,2008,3,"2008 Q3",103.76623767224763]
[2008,4,100.95217539398745,null,null,null,null,2008,4,"2008 Q4",102.36912296007011]
[2009,1,99.61755840578526,null,null,null,null,2009,1,"2009 Q1",100.95963652198984]
[2009,2,97.204396640843584,null,null,null,null,2009,2,"2009 Q2",98.691403366385771]
[2009,3,95.098915311448735,null,null,null,null,2009,3,"2009 Q3",96.948992262893498]
[2009,4,97.339651588919082,null,null,null,null,2009,4,"2009 Q4",99.859905021784925]
[2010,1,96.251075604564136,null,null,null,null,2010,1,"2010 Q1",99.587402979820823]
[2010,2,97.102042677025693,null,null,null,null,2010,2,"2010 Q2",101.10961837629351]
[2010,3,97.888877525285551,null,null,null,null,2010,3,"2010 Q3",102.362334784216]
[2010,4,99.188580805761234,null,null,null,null,2010,4,"2010 Q4",103.94246274436257]

{"sheet":"Part3. Spline","index":2,"n_rows":36,"n_cols":43,"rows_kept":36,"cut":false,"hidden":false,"merged":[[2,0,2,4],[2,6,2,10]]}
["Example of a simple and a seasonal spline"]
[]
["A.  Assume we have an annual series for ISIC 1074: Manufacture of macaroni, noodles, couscous and similar farinaceous products",null,null,null,null,null,"B.  We can create a simple spline for this series which is as smooth as possible…"]
[null,"Annual Series","Sum Spline",null,null,null,null,null,"Quarterly Spline","For Simple Spline","For Seasonal Spline"]
[2004,4560,null,null,null,2004,1,{"$f":"F5&\" Q\"&G5","v":"2004 Q1"},{"$f":"Bench(K5:K36,B5:B12,1,0)","v":1105.5385435096512},1,0.98,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,{"$f":"F5&G5","v":"20041"},{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"I5","v":1105.5385435096512}]
[{"$f":"A5+1","v":2005},4866.73980461275,null,null,null,{"$f":"F5","v":2004},2,{"$f":"F6&\" Q\"&G6","v":"2004 Q2"},1146.4534111833545,1,1.01,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,"20042",{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"I6","v":1146.4534111833545}]
[{"$f":"A6+1","v":2006},4966.3700324421898,null,null,null,{"$f":"F6","v":2004},3,"2004 Q3",1172.3068024358959,1,1.02,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,"20043",{"$f":"#REF!","v":{"$error":"#REF!"}},1172.3068024358959]
[2007,5272.6705015354701,null,null,null,2004,4,"2004 Q4",1135.7012428710989,1,0.97,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,"20044",{"$f":"#REF!","v":{"$error":"#REF!"}},1135.7012428710989]
[2008,5672.5713620330698,null,null,null,{"$f":"F5+1","v":2005},{"$f":"G5","v":1},"2005 Q1",1175.276797286981,1,{"$f":"K5","v":0.98},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,"20051",{"$f":"#REF!","v":{"$error":"#REF!"}},1175.276797286981,null,{"$f":"AM9/AM5-1","v":{"$error":"#REF!"}},6.3080798210741973E-2]
[2009,5870.7774323014301,null,null,null,{"$f":"F6+1","v":2005},{"$f":"G6","v":2},"2005 Q2",1232.3344929934126,1,{"$f":"K6","v":1.01},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,"20052",{"$f":"#REF!","v":{"$error":"#REF!"}},1232.3344929934126,null,{"$error":"#REF!"},7.4910223976230039E-2]
[2010,5773.8523107127903,null,null,null,2005,3,"2005 Q3",1257.8719715788131,1,1.02,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,"20053",{"$f":"#REF!","v":{"$error":"#REF!"}},1257.8719715788131,null,{"$error":"#REF!"},7.2988716746439142E-2]
[2011,5973.20939621053,null,null,null,2005,4,"2005 Q4",1201.256542753543,1,0.97,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,"20054",{"$f":"#REF!","v":{"$error":"#REF!"}},1201.256542753543,null,{"$error":"#REF!"},5.772231059351296E-2]
[null,null,null,null,null,2006,1,"2006 Q1",1211.3996582747789,1,0.98,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,"20061",{"$f":"#REF!","v":{"$error":"#REF!"}},1211.3996582747789,null,{"$error":"#REF!"},3.0735619958791149E-2]
[null,null,null,null,null,2006,2,"2006 Q2",1252.2449984896152,1,1.01,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,"20062",{"$f":"#REF!","v":{"$error":"#REF!"}},1252.2449984896152,null,{"$error":"#REF!"},1.6156737971229518E-2]
[null,null,null,null,null,2006,3,"2006 Q3",1274.7614424731112,1,1.02,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,"20063",{"$f":"#REF!","v":{"$error":"#REF!"}},1274.7614424731112,null,{"$error":"#REF!"},1.3427019025711528E-2]
[null,null,null,null,null,2006,4,"2006 Q4",1227.9639332046836,1,0.97,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,"20064",{"$f":"#REF!","v":{"$error":"#REF!"}},1227.9639332046836,null,{"$error":"#REF!"},2.22328782409138E-2]
[null,null,null,null,null,2007,1,"2007 Q1",1262.3067180956114,1,0.98,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,"20071",{"$f":"#REF!","v":{"$error":"#REF!"}},1262.3067180956114,null,{"$error":"#REF!"},4.2023340086897454E-2]
[null,null,null,null,null,2007,2,"2007 Q2",1324.7362087389154,1,1.01,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,"20072",{"$f":"#REF!","v":{"$error":"#REF!"}},1324.7362087389154,null,{"$error":"#REF!"},5.7888999626059423E-2]
[null,null,null,null,null,2007,3,"2007 Q3",1363.3744734258323,1,1.02,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,"20073",{"$f":"#REF!","v":{"$error":"#REF!"}},1363.3744734258323,null,{"$error":"#REF!"},6.9513422668955771E-2]
[null,null,null,null,null,2007,4,"2007 Q4",1322.253101275111,1,0.97,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,"20074",{"$f":"#REF!","v":{"$error":"#REF!"}},1322.253101275111,null,{"$error":"#REF!"},7.6784965356723411E-2]
[null,null,null,null,null,2008,1,"2008 Q1",1363.2436122907541,1,0.98,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,"20081",{"$f":"#REF!","v":{"$error":"#REF!"}},1363.2436122907541,null,{"$error":"#REF!"},7.996225699204218E-2]
[null,null,null,null,null,2008,2,"2008 Q2",1430.1225649154981,1,1.01,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,"20082",{"$f":"#REF!","v":{"$error":"#REF!"}},1430.1225649154981,null,{"$error":"#REF!"},7.955271055579094E-2]
[null,null,null,null,null,2008,3,"2008 Q3",1466.5041563863226,1,1.02,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,"20083",{"$f":"#REF!","v":{"$error":"#REF!"}},1466.5041563863226,null,{"$error":"#REF!"},7.5642961615197457E-2]
[null,null,null,null,null,2008,4,"2008 Q4",1412.7010284404944,1,0.97,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,"20084",{"$f":"#REF!","v":{"$error":"#REF!"}},1412.7010284404944,null,{"$error":"#REF!"},6.8404397825318153E-2]
[null,null,null,null,null,2009,1,"2009 Q1",1442.6069460650008,1,0.98,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,"20091",{"$f":"#REF!","v":{"$error":"#REF!"}},1442.6069460650008,null,{"$error":"#REF!"},5.8216545493939176E-2]
[null,null,null,null,null,2009,2,"2009 Q2",1494.3875429642464,1,1.01,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,"20092",{"$f":"#REF!","v":{"$error":"#REF!"}},1494.3875429642464,null,{"$error":"#REF!"},4.4936692578195547E-2]
[null,null,null,null,null,2009,3,"2009 Q3",1508.3511867311624,1,1.02,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,"20093",{"$f":"#REF!","v":{"$error":"#REF!"}},1508.3511867311624,null,{"$error":"#REF!"},2.8535227917769301E-2]
[null,null,null,null,null,2009,4,"2009 Q4",1425.4317565410204,1,0.97,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,"20094",{"$f":"#REF!","v":{"$error":"#REF!"}},1425.4317565410204,null,{"$error":"#REF!"},9.0116223066529777E-3]
[null,null,null,null,null,2010,1,"2010 Q1",1423.1856710670113,1,0.98,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,"20101",{"$f":"#REF!","v":{"$error":"#REF!"}},1423.1856710670113,null,{"$error":"#REF!"},-1.3462624071625906E-2]
[null,null,null,null,null,2010,2,"2010 Q2",1459.0705941056249,1,1.01,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,"20102",{"$f":"#REF!","v":{"$error":"#REF!"}},1459.0705941056249,null,{"$error":"#REF!"},-2.3633058924305117E-2]
[null,null,null,null,null,2010,3,"2010 Q3",1475.935773261891,1,1.02,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,"20103",{"$f":"#REF!","v":{"$error":"#REF!"}},1475.935773261891,null,{"$error":"#REF!"},-2.1490627484121116E-2]
[null,null,null,null,null,2010,4,"2010 Q4",1415.660272278262,1,0.97,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,"20104",{"$f":"#REF!","v":{"$error":"#REF!"}},1415.660272278262,null,{"$error":"#REF!"},-6.8551049307825629E-3]
[null,null,null,null,null,2011,1,"2011 Q1",1451.8441171541808,1,0.98,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,"20111",{"$f":"#REF!","v":{"$error":"#REF!"}},1451.8441171541808,null,{"$error":"#REF!"},2.0136828714473642E-2]
[null,null,null,null,null,2011,2,"2011 Q2",1513.0599147898838,1,1.01,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,"20112",{"$f":"#REF!","v":{"$error":"#REF!"}},1513.0599147898838,null,{"$error":"#REF!"},3.700254182516316E-2]
[null,null,null,null,null,2011,3,"2011 Q3",1539.2760107503357,1,1.02,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,"20113",{"$f":"#REF!","v":{"$error":"#REF!"}},1539.2760107503357,null,{"$error":"#REF!"},4.291530745166483E-2]
[null,null,null,null,null,2011,4,"2011 Q4",1469.029353516129,1,0.97,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,"20114",{"$f":"#REF!","v":{"$error":"#REF!"}},1469.029353516129,null,{"$error":"#REF!"},3.7699073911270142E-2]

{"sheet":"Spline_Solution","index":3,"n_rows":36,"n_cols":14,"rows_kept":36,"cut":false,"hidden":true,"merged":[[1,13,2,19],[2,0,2,4],[2,6,2,11]]}
["Example of a simple spline for a 'missing' series"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"NB the starting values for this splines can be more or less any value, but choosing roughly 1/4 of the original annual series is a reasonable start"]
["1.  Assume we have an annual series for ISIC 1074: Manufacture of macaroni, noodles, couscous and similar farinaceous products",null,null,null,null,null,"2.  We can create a simple spline for this series which is as smooth as possible…"]
[null,"Annual Series","Sum Spline","Set=0",null,null,null,null,"Solver Spline","XLPBM Spline ","Spline Guide"]
[2004,2075.6940517745484,{"$f":"SUM(IF($G$5:$G$36=A5,$I$5:$I$36))","v":2075.6940517745484},{"$f":"C5-B5","v":0},null,null,2004,"Q1",495.02899451396405,{"$f":"[1]!XLPBM(K5:K36,B5:B12)","v":492.32708203150327},1,"Minimize",{"$f":"SUM((I6:I36/I5:I35-1)^2)","v":2.6724851472404089E-2}]
[{"$f":"A5+1","v":2005},2481.35235359632,{"$f":"SUM(IF($G$5:$G$36=A6,$I$5:$I$36))","v":2481.3523535963195},{"$f":"C6-B6","v":0},null,null,{"$f":"G5","v":2004},"Q2",504.49571035324072,502.96565439635674,1]
[{"$f":"A6+1","v":2006},2344.3999878529917,{"$f":"SUM(IF($G$5:$G$36=A7,$I$5:$I$36))","v":2344.3999878529921},0,null,null,{"$f":"G6","v":2004},"Q3",523.3894309623463,524.24279912606391,1,null,"Solver Model"]
[2007,2467.8937120373384,{"$f":"SUM(IF($G$5:$G$36=A8,$I$5:$I$36))","v":2467.8937120373384},0,null,null,2004,"Q4",552.77991594499724,556.15851622062462,1,null,{"$f":"MIN($M$5)","v":2.6724851472404089E-2}]
[2008,2666.8937120372998,{"$f":"SUM(IF($G$5:$G$36=A9,$I$5:$I$36))","v":2666.8937120372998},0,null,null,{"$f":"G5+1","v":2005},{"$f":"H5","v":"Q1"},594.29093341912221,598.71280568003897,1,null,{"$f":"COUNT($I$5:$I$36)","v":32}]
[2009,2576.8937120373398,{"$f":"SUM(IF($G$5:$G$36=A10,$I$5:$I$36))","v":2576.8937120373389},0,null,null,{"$f":"G6+1","v":2005},{"$f":"H6","v":"Q2"},623.36301810141651,624.38463455142085,1,null,{"$f":"$D$5:$D$12=0","v":true}]
[2010,2812.2037340000002,{"$f":"SUM(IF($G$5:$G$36=A11,$I$5:$I$36))","v":2812.2037340000006},0,null,null,2005,"Q3",635.61619133682086,633.17400283477104,1,null,{"$f":"{100,100,0.000001,0.05,FALSE,FALSE,FALSE,1,1,1,0.0001,FALSE}","v":100}]
[2011,3113.8937120373398,{"$f":"SUM(IF($G$5:$G$36=A12,$I$5:$I$36))","v":3113.8937120373403},0,null,null,2005,"Q4",628.08221073895982,625.08091053008866,1]
[null,null,null,null,null,null,2006,"Q1",600.14164925115278,600.10535763737414,1]
[null,null,null,null,null,null,2006,"Q2",584.17745512142278,584.51299221063778,1]
[null,null,null,null,null,null,2006,"Q3",578.27400204485184,578.30381424987979,1]
[null,null,null,null,null,null,2006,"Q4",581.80688143556449,581.47782375509996,1]
[null,null,null,null,null,null,2007,"Q1",594.42396457992129,594.0350207262984,1]
[null,null,null,null,null,null,2007,"Q2",608.43875442561557,608.23326242799237,1]
[null,null,null,null,null,null,2007,"Q3",623.95232389837895,624.07254886018154,1]
[null,null,null,null,null,null,2007,"Q4",641.07866913342286,641.55288002286613,1]
[null,null,null,null,null,null,2008,"Q1",659.96867840584787,660.67425591604672,1]
[null,null,null,null,null,null,2008,"Q2",670.63906278573108,670.74247511063004,1]
[null,null,null,null,null,null,2008,"Q3",672.18081634999089,671.75753760661689,1]
[null,null,null,null,null,null,2008,"Q4",664.10515449572995,663.71944340400614,1]
[null,null,null,null,null,null,2009,"Q1",646.38511330993231,646.62819250279847,1]
[null,null,null,null,null,null,2009,"Q2",638.71186991678701,638.8297863449302,1]
[null,null,null,null,null,null,2009,"Q3",640.48842591669836,640.32422493040042,1]
[null,null,null,null,null,null,2009,"Q4",651.30830289392145,651.11150825921072,1]
[null,null,null,null,null,null,2010,"Q1",671.34034773832639,671.19163633135986,1]
[null,null,null,null,null,null,2010,"Q2",692.01894627609772,691.96740642767566,1]
[null,null,null,null,null,null,2010,"Q3",713.40240116614279,713.43881854815766,1]
[null,null,null,null,null,null,2010,"Q4",735.44203881943406,735.60587269280654,1]
[null,null,null,null,null,null,2011,"Q1",757.93286142992122,758.46856886162175,1]
[null,null,null,null,null,null,2011,"Q2",775.41101225319676,775.61559098823295,1]
[null,null,null,null,null,null,2011,"Q3",787.27730219674709,787.04693907264073,1]
[null,null,null,null,null,null,2011,"Q4",793.27253615747475,792.76261311484427,1]

{"sheet":"Multi_Bench_Solution","index":4,"n_rows":20,"n_cols":29,"rows_kept":20,"cut":false,"hidden":true,"merged":[[0,17,1,25]]}
["Example of 'simultaneous' benchmarking - solution",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Using a proportional Denton method, generate series for each of these components consistent with the 'definitive' series from an annual survey."]
[null,"Given these quarterly series based on short term indicators",null,null,null,null,null,null,null,null,null,"…and"]
[null,null,"Agriculture","Forestry & Fishing","Mining and quarrying","Manufacturing Formal","Manufacturing informal","Electricity supply","Water supply","Construction",null,null,"Benchmark annual totals","Sum Bench series","Set=0",null,null,null,"Agriculture","Forestry & Fishing","Mining and quarrying","Manufacturing Formal","Manufacturing informal","Electricity supply","Water supply","Construction"]
[null,"200604",332.27898037056252,682.76995821818218,13.90267478743235,251.11256200204659,78.20909928515546,51.22912409252271,90.300815031510936,743.73810743512206,null,2007,9203.5868577010551,{"$f":"SUM(IF(L4=$Q$5:$Q$20,$S$5:$Z$20))","v":9203.5871941186597},{"$f":"N4-M4","v":3.3641760455793701E-4},null,{"$f":"VALUE(LEFT(R4,4))","v":2006},"200604",{"$f":"C4","v":332.27898037056252},{"$f":"D4","v":682.76995821818218},13.90267478743235,251.11256200204659,78.20909928515546,51.22912409252271,90.300815031510936,743.73810743512206,null,"Min",{"$f":"SUM((S5:Z20/S4:Z19-C5:J20/C4:J19)^2)","v":1.7017994705004205E-4}]
[null,"200701",321.05600823184108,660.0897860725637,21.111264082530678,264.2758303783267,100.20900053235941,51.878664643002701,90.90709023646275,780.12814815540821,null,2008,9702.7734842561567,{"$f":"SUM(IF(L5=$Q$5:$Q$20,$S$5:$Z$20))","v":9702.773484334055},{"$f":"N5-M5","v":7.7898221206851304E-8},null,{"$f":"VALUE(LEFT(R5,4))","v":2007},"200701",320.0572208675747,657.21907600889631,21.108344965386927,263.72045045640476,100.15486239975031,51.844323904399076,90.794930317885772,777.19758847466358]
[null,"200702",369.05974370118957,752.54386058306818,21.133758684256058,266.64093032512716,98.266777789849883,52.204532768718359,91.558921553952288,727.14432093456173,null,2009,10041.490018123157,{"$f":"SUM(IF(L6=$Q$5:$Q$20,$S$5:$Z$20))","v":10041.490018078759},-4.4397893361747265E-8,null,2007,"200702",367.38350661215333,747.953571603618,21.127063458883327,265.68225012792357,98.126033174995072,52.151738259919298,91.37420226146844,722.80611246514945]
[null,"200703",336.44640439084827,683.42667620530358,19.488788768762848,272.44486321500813,93.894020317637086,53.828268691353514,93.097064588532902,687.93383370050742,null,2010,10467.00563851589,{"$f":"SUM(IF(L7=$Q$5:$Q$20,$S$5:$Z$20))","v":10467.005638551454},3.5564880818128586E-8,null,2007,"200703",334.54636780415569,679.07134058159295,19.480118661100054,271.23438542883815,93.699857503143505,53.761078158103835,92.863910786456884,683.83054810718261]
[null,"200704",338.26457929153838,697.71679472608935,11.52360575004975,257.76423989420556,64.975541113630584,55.683820799565972,93.660138122594063,817.64106129105539,null,null,null,null,null,null,2007,"200704",336.40501827479181,694.28506099809044,11.519279320948947,256.56530013359355,64.824006159940893,55.615430061023069,93.406920568129067,813.77729621249387]
[null,"200801",324.21378463095112,673.85096152784945,17.605604105698891,275.44341975020012,83.349040208490734,58.13723783311849,94.313383696846657,880.22745404876127,null,null,null,null,null,null,2008,"200801",322.81866208564048,672.82539103697889,17.600959393842707,274.32686228790084,83.164805682327085,58.069867151997165,94.067357730152537,878.85710908456383]
[null,"200802",371.35465212116702,756.65797733542649,24.784134370771479,288.99575957992135,102.12325863671745,59.392653795477933,94.832626410392933,808.49770355857993,null,null,null,null,null,null,2008,"200802",369.89644728074211,756.62794946553254,24.780505581890626,287.88901314245703,101.88374750715818,59.328531443639768,94.571039017082228,808.9227090134076]
[null,"200803",338.60040445236729,679.56659218802383,26.76823898141545,304.61290109662707,110.5952880288939,60.131436641512934,96.660024780192572,747.74943397100935,null,null,null,null,null,null,2008,"200803",337.25175059105021,679.66154610316516,26.763561235620688,303.40631279054355,110.30247791579195,60.069990386196181,96.357434205668028,748.18534316514456]
[null,"200804",339.85114877243547,686.35817705714214,18.720266706932758,291.21285221669422,81.72366861929936,60.450115379977625,97.947069222717346,863.67101256119167,null,null,null,null,null,null,2008,"200804",338.32786369854216,685.54682096393969,18.711295560957797,289.93480990366868,81.437027197032037,60.373269644540905,97.566180647217053,863.24684341966361]
[null,"200901",324.87006418950301,670.94735289603295,22.6845972211419,305.33810544079,98.596574302821494,61.605694538731697,98.411696344100903,914.245715256224,null,null,null,null,null,null,2009,"200901",323.00592366776601,668.32286703064506,22.668854695810474,303.71928256259071,98.197195222298987,61.503394107847079,97.950642609447542,911.29909576088005]
[null,"200902",373.52538667642699,768.84682443213001,22.7553956141242,316.51614968869899,96.316077729927997,61.987584354583298,98.360540410685999,841.11571219678103,null,null,null,null,null,null,2009,"200902",371.30054566731991,764.94747663243663,22.73586623363261,314.74736608746213,95.875728523302897,61.866791730182776,97.850917648302129,837.03462086334662]
[null,"200903",340.83865971213999,696.42548475508204,22.630941278528201,328.40461597048198,99.886767712568201,62.541743388448701,98.309531711736597,826.51023905046395,null,null,null,null,null,null,2009,"200903",338.92150591473694,692.88966927119884,22.609171360351304,326.65720683300657,99.401195983070181,62.413240144565549,97.764260651209,822.60382103756285]
[null,"200904",338.68359064104999,699.84864263225302,17.879878279800302,313.91352424742001,79.688171453069998,62.752609430089102,98.258669761146706,924.17639936838395,null,null,null,null,null,null,2009,"200904",337.10730001685715,697.19335374005857,17.864014403847168,312.52982953282657,79.295919586865011,62.619605304289522,97.719626625229665,920.87372862981169]
[null,"201001",325.662071314635,680.12186761176201,23.806604017392001,330.41898537646102,102.745349405946,64.069582378369205,98.207954074715104,963.98650188317504,null,null,null,null,null,null,2010,"201001",324.70443962848913,679.3877444892405,23.786620305694594,329.44144337061113,102.25307915306776,63.944810633996717,97.694266827368338,963.09866716158194]
[null,"201002",371.90908388002799,778.02133914785895,24.041231175475598,342.51520911360598,100.36889344023101,64.489475853215296,98.157384170136098,896.59970053103405,null,null,null,null,null,null,2010,"201002",371.15384361803632,778.29598524605603,24.024614183797002,341.89581660795074,99.90196481926904,64.384765083411665,97.647118355519481,897.38145669803851]
[null,"201003",340.22462887544998,705.59999947081201,23.949543205273599,355.38020989966299,104.089831946267,65.053645242070701,98.106959566990795,881.99422738471696,null,null,null,null,null,null,2010,"201003",339.9042552297858,706.72937659452828,23.93418678529974,355.06953344948198,103.61748638391123,64.958033624898505,97.600395271099046,883.41674104706249]
[null,"201004",336.80690021399198,709.02315734798299,19.205033602101501,339.698800541276,83.041313325145396,65.267148061581395,98.0566797867383,979.66038770263594,null,null,null,null,null,null,2010,"201004",336.65475046089449,710.50851459494277,19.192807097192599,339.59586490237092,82.67033675241187,65.180269911989299,97.552653907153541,981.42379635630255]

{"sheet":"XLPBM_Solution","index":5,"n_rows":48,"n_cols":11,"rows_kept":48,"cut":false,"hidden":true,"merged":[]}
["Example of the use of XLPBM to benchmark quarterly indicators to annual 'benchmark' data"]
[]
[null,"Annual benchmark",null,null,"Indicator data",null,null,null,"Benchmak series"]
["Year","Y",null,null,"Year","Q","X",null,"Year","Q","X hat"]
[2004,954.21021305876263,null,null,{"$f":"A5","v":2004},1,77.134930053836669,null,{"$f":"E5","v":2004},{"$f":"F5","v":1},{"$f":"[1]!XLPBM(G5:G56,B5:B14)","v":217.54948414564635}]
[{"$f":"A5+1","v":2005},959.09882056550077,null,null,{"$f":"E5","v":2004},2,87.458463857824043,null,{"$f":"E6","v":2004},2,246.65436220783317]
[{"$f":"A6+1","v":2006},900.90758270884305,null,null,{"$f":"E6","v":2004},3,83.192889629928061,null,2004,3,234.60132413962489]
[2007,922.0047187095314,null,null,2004,4,90.583587608032929,null,2004,4,255.40504256565816]
[2008,944.4689737415099,null,null,{"$f":"E5+1","v":2005},1,80.633233135546575,null,2005,1,227.30354218382698]
[2009,957.16129303377704,null,null,{"$f":"E6+1","v":2005},2,88.069227866701468,null,2005,2,248.26383933631209]
[2010,980.28188951915706,null,null,2005,3,83.033797521013796,null,2005,3,234.11763850638133]
[2011,1031.2296657461998,null,null,2005,4,88.422714871932058,null,2005,4,249.4138005389805]
[2012,1088.4186930832047,null,null,2006,1,74.309782509781783,null,2006,1,209.73602695768446]
[2013,1136.2220742508855,null,null,2006,2,82.277586405407902,null,2006,2,232.31005770687216]
[2014,1126.5540000000001,null,null,2006,3,78.774641695851429,null,2006,3,222.43830597767865]
[null,null,null,null,2006,4,83.742853345590447,null,2006,4,236.42319206660787]
[null,null,null,null,2007,1,74.055565332621882,null,2007,1,208.97495018412559]
[null,null,null,null,2007,2,83.817587956548095,null,2007,2,236.47413226560144]
[null,null,null,null,2007,3,80.88933086382589,null,2007,3,228.23646189519965]
[null,null,null,null,2007,4,87.971605879411001,null,2007,4,248.31917436460475]
[null,null,null,null,2008,1,77.091406930939399,null,2008,1,217.76466443662562]
[null,null,null,null,2008,2,86.156735680874874,null,2008,2,243.54534330235717]
[null,null,null,null,2008,3,82.016994851794763,null,2008,3,232.00564722227207]
[null,null,null,null,2008,4,88.72477603693585,null,2008,4,251.15331878025506]
[null,null,null,null,2009,1,75.857321989071181,null,2009,1,214.87488293753927]
[null,null,null,null,2009,2,87.396399381415037,null,2009,2,247.76082534222365]
[null,null,null,null,2009,3,84.40979174350791,null,2009,3,239.52355039394334]
[null,null,null,null,2009,4,89.765415792250352,null,2009,4,255.00203436007089]
[null,null,null,null,2010,1,76.794437090788975,null,2010,1,218.4289925865049]
[null,null,null,null,2010,2,88.885465670881302,null,2010,2,253.1202525194274]
[null,null,null,null,2010,3,85.492699872728664,null,2010,3,243.72829174348001]
[null,null,null,null,2010,4,92.860016751213507,null,2010,4,265.00435266974466]
[null,null,null,null,2011,1,82.783105591019208,null,2011,1,236.4705927840707]
[null,null,null,null,2011,2,91.352733015869788,null,2011,2,261.08110129090159]
[null,null,null,null,2011,3,89.980676000736025,null,2011,3,257.16346999511387]
[null,null,null,null,2011,4,96.796774414567423,null,2011,4,276.5145016761137]
[null,null,null,null,2012,1,86.712976286226791,null,2012,1,247.46445905360278]
[null,null,null,null,2012,2,96.758336327054877,null,2012,2,275.86361624185383]
[null,null,null,null,2012,3,95.270515352376719,null,2012,3,271.36143549750102]
[null,null,null,null,2012,4,103.22091314357822,null,2012,4,293.72918229024708]
[null,null,null,null,2013,1,93.120793844151123,null,2013,1,264.74186858978237]
[null,null,null,null,2013,2,101.62921844979455,null,2013,2,288.72525521195973]
[null,null,null,null,2013,3,98.543740365074555,null,2013,3,279.82590848430391]
[null,null,null,null,2013,4,106.70624734097973,null,2013,4,302.92904196483943]
[null,null,null,null,2014,1,94.35,null,2014,1,267.85081306487052]
[null,null,null,null,2014,2,101.74,null,2014,2,288.83033090853149]
[null,null,null,null,2014,3,97.4,null,2014,3,276.50947739818139]
[null,null,null,null,2014,4,104.2,null,2014,4,295.81404050195584]