{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Front","Example","Resolved","Notes"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":3,"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":"Front","index":0,"n_rows":10,"n_cols":4,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[null,"A simple illustration of Excel's built in auditing tools"]
[]
[null,"As posted on: ",null,"www.Chandoo.org"]

{"sheet":"Example","index":1,"n_rows":33,"n_cols":21,"rows_kept":33,"cut":false,"hidden":false,"merged":[]}
[null,"SALES REPORT"]
[null,null,"Jan","Feb","Mar","Q1","Apr","May","Jun","Q2","Jul","Aug","Sep","Q3","Oct","Nov","Dec","Q4","Total"]
[]
[null,"Region: North"]
[null,"James Bourne",4521,2863,3802,11925,{"$f":"5498+200","v":5698},{"$f":"4487-350","v":4137},{"$f":"3362+U5","v":3627},{"$f":"SUM(G5:I5)","v":13462},5100,2280,4207,{"$f":"SUM(K5:M5)","v":11587},3843,3400,4200,{"$f":"SUM(O5:Q5)","v":11443},{"$f":"SUM(F5,J5,N5,R5)","v":48417},null,265]
[null,"Chris Hewitt",3510,4882,3915,12056,{"$f":"4043-200","v":3843},{"$f":"1063+350","v":1413},{"$f":"4452-U5","v":4187},{"$f":"SUM(G6:I6)","v":9443},2021,1205,2468,{"$f":"SUM(K6:M6)","v":5694},3825,2500,3500,{"$f":"SUM(O6:Q6)","v":9825},{"$f":"SUM(F6,J6,N6,R6)","v":37018}]
[null,"Pat Hill",5213,4557,2187,10562,1070,4424,{"$f":"1240+U7","v":1240},{"$f":"SUM(G7:I7)","v":6734},4128,2331,3849,{"$f":"SUM(K7:M7)","v":10308},2928,3000,5700,{"$f":"SUM(O7:Q7)","v":11628},{"$f":"SUM(F7,J7,N7,R7)","v":39232}]
[null,"Jasmine Hunt",4175,2681,2257,9112,3588,4666,{"$f":"3666-U7","v":3666},{"$f":"SUM(G8:I8)","v":11920},5410,2719,1934,{"$f":"SUM(K8:M8)","v":10063},1440,2000,4700,{"$f":"SUM(O8:Q8)","v":8140},{"$f":"SUM(F8,J8,N8,R8)","v":39235}]
[null,"Total North",{"$f":"SUM(C5:C8)","v":17419},{"$f":"SUM(D5:D8)","v":14983},12161,43655,14199,14640,12720,41559,{"$f":"SUM(K5:K8)","v":16659},{"$f":"SUM(K5:K8)","v":16659},12458,37652,12036,10900,18100,41036,163902]
[]
[]
[null,"Region: South"]
[null,"Mark Watts",3400,5329,4734,{"$f":"SUM(C13:E13)","v":13463},1450,4191,{"$f":"3109+#REF!","v":{"$error":"#REF!"}},{"$f":"SUM(G13:I13)","v":{"$error":"#REF!"}},2097,2354,1837,{"$f":"SUM(K13:M13)","v":6288},3525,{"$f":"AVERAGE(L13:M13,O13)","v":2572},3565.6666666666665,{"$f":"SUM(O13:Q13)","v":9662.6666666666661},{"$f":"SUM(F13,J13,N13,R13)","v":{"$error":"#REF!"}}]
[null,"Jane Hill",3608,4922,4711,{"$f":"SUM(C14:E14)","v":13241},1463,1204,2342,{"$f":"SUM(G14:I14)","v":5009},3402,5269,3625,{"$f":"SUM(K14:M14)","v":12296},4125,4339.666666666667,6753.5555555555557,{"$f":"SUM(O14:Q14)","v":15218.222222222223},{"$f":"SUM(F14,J14,N14,R14)","v":45764.222222222219}]
[null,"Sarah Allen",2303,1253,3651,{"$f":"SUM(C15:E15)","v":7207},4595,2863,3802,{"$f":"SUM(G15:I15)","v":11260},1478,5410,0,{"$f":"SUM(K15:M15)","v":6888},0,1803.3333333333333,2897.1111111111113,{"$f":"SUM(O15:Q15)","v":4700.4444444444443},{"$f":"SUM(F15,J15,N15,R15)","v":30055.444444444445}]
[null,"Roger West",2359,5273,5438,{"$f":"SUM(C16:E16)","v":13070},2280,4882,3915,{"$f":"SUM(G16:I16)","v":11077},5498,2806,3061,{"$f":"SUM(K16:M16)","v":11365},4757,3541.3333333333335,5989.1111111111104,{"$f":"SUM(O16:Q16)","v":14287.444444444445},{"$f":"SUM(F16,J16,N16,R16)","v":49799.444444444445}]
[null,"Gill Smith",4487,2638,5100,{"$f":"SUM(C17:E17)","v":12225},1205,4557,2187,{"$f":"SUM(G17:I17)","v":7949},4043,2252,5076,{"$f":"SUM(K17:M17)","v":11371},1101,2809.6666666666665,6418.8888888888896,{"$f":"SUM(O17:Q17)","v":10329.555555555557},{"$f":"SUM(F17,J17,N17,R17)","v":41874.555555555555}]
[null,"Total South",{"$f":"SUM(C13:C17)","v":16157},{"$f":"SUM(D13:D17)","v":19415},23634,59206,10993,17697,{"$error":"#REF!"},{"$error":"#REF!"},16518,18091,13599,48208,13508,15066,25624.333333333336,54198.333333333336,{"$error":"#REF!"}]
[]
[]
[null,"Region: Wales"]
[null,"Gareth Jones",1063,3362,"2021",{"$f":"SUM(C22:E22)","v":4425},4175,2681,2257,{"$f":"SUM(G22:I22)","v":9113},1070,1722,2176,{"$f":"SUM(K22:M22)","v":4968},3445,{"$f":"AVERAGE(L22:M22,O22)","v":2447.6666666666665},3197.2222222222222,{"$f":"SUM(O22:Q22)","v":9089.8888888888887},{"$f":"SUM(F22,J22,N22,R22)","v":27595.888888888891}]
[null,"Tim Stevens",4424,4452,"4128",{"$f":"SUM(C23:E23)","v":8876},3400,5329,4734,{"$f":"SUM(G23:I23)","v":13463},3588,1732,4534,{"$f":"SUM(K23:M23)","v":9854},5123,3796.3333333333335,6061.4444444444443,{"$f":"SUM(O23:Q23)","v":14980.777777777777},{"$f":"SUM(F23,J23,N23,R23)","v":47173.777777777781}]
[null,"Mike Payne",4666,1240,"5410",{"$f":"SUM(C24:E24)","v":5906},3608,4922,4711,{"$f":"SUM(G24:I24)","v":13241},1450,3910,2705,{"$f":"SUM(K24:M24)","v":8065},2936,3183.6666666666665,4651.2222222222217,{"$f":"SUM(O24:Q24)","v":10770.888888888887},{"$f":"SUM(F24,J24,N24,R24)","v":37982.888888888891}]
[null,"Total Wales",{"$f":"SUM(C23:C24)","v":9090},{"$f":"SUM(D23:D24)","v":5692},0,14782,7008,10251,9445,26704,5038,5642,7239,17919,8059,6980,10712.666666666666,25751.666666666664,85156.666666666672]
[]
[]
[null,"Region: Scotland"]
[null,"Simon Campbell",4191,3109,3402,{"$f":"SUM(C29:E29)","v":10702},2303,1253,3651,{"$f":"SUM(G29:I29)","v":7207},1463,2845,2303,{"$f":"SUM(K29:M29)","v":6611},4051,{"$f":"AVERAGE(L29:M29,O29)","v":3066.3333333333335},{"$f":"AVERAGE(M29:N29,P29)","v":3993.4444444444448},{"$f":"SUM(O29:Q29)","v":11110.777777777779},{"$f":"SUM(F29,J29,N29,R29)","v":35630.777777777781}]
[null,"Glen Wilks",1204,2342,1478,{"$f":"SUM(C30:E30)","v":5024},2359,5273,5438,{"$f":"SUM(G30:I30)","v":13070},4595,2857,5138,{"$f":"SUM(K30:M30)","v":12590},3261,{"$f":"AVERAGE(L30:M30,O30)","v":3752},{"$f":"AVERAGE(M30:N30,P30:Q30)","v":0},{"$f":"SUM(O30:Q30)","v":7013},{"$f":"SUM(F30,J30,N30,R30)","v":37697}]
[null,"Total Scotland",{"$f":"SUM(C29:C30)","v":5395},{"$f":"SUM(D29:D30)","v":5451},4880,15726,4662,6526,9089,20277,6058,5702,7441,19201,7312,6818.3333333333339,3993.4444444444448,18123.777777777781,73327.777777777781]
[]
[null,"Total",{"$f":"SUM(C9,C18,C25,C31)","v":48061},{"$f":"SUM(D9,D18,D25,D31)","v":45541},40675,133369,36862,49114,{"$error":"#REF!"},{"$error":"#REF!"},44273,46094,40737,122980,40915,39764.333333333336,58430.444444444445,139109.77777777778,{"$f":"SUM(S9,S18,S25,S31)","v":{"$error":"#REF!"}}]

{"sheet":"Resolved","index":2,"n_rows":33,"n_cols":21,"rows_kept":33,"cut":false,"hidden":false,"merged":[]}
[null,"SALES REPORT 2011 £'000"]
[null,null,"Jan","Feb","Mar","Q1","Apr","May","Jun","Q2","Jul","Aug","Sep","Q3","Oct","Nov","Dec","Q4","Total"]
[null,null,"ACT","ACT","ACT",null,"ACT","ACT","ACT",null,"ACT","ACT","ACT",null,"ACT","FOR","FOR"]
[null,"Region: North"]
[null,"James Bourne",4521,2863,3802,{"$f":"SUM(C5:E5)","v":11186},{"$f":"5498+200","v":5698},{"$f":"4487-350","v":4137},{"$f":"3362+U5","v":3627},{"$f":"SUM(G5:I5)","v":13462},5100,2280,4207,{"$f":"SUM(K5:M5)","v":11587},3843,3400,4200,{"$f":"SUM(O5:Q5)","v":11443},{"$f":"SUM(F5,J5,N5,R5)","v":47678},null,265]
[null,"Chris Hewitt",3510,4882,3915,12307,{"$f":"4043-200","v":3843},{"$f":"1063+350","v":1413},{"$f":"4452-U5","v":4187},{"$f":"SUM(G6:I6)","v":9443},2021,1205,2468,{"$f":"SUM(K6:M6)","v":5694},3825,2500,3500,{"$f":"SUM(O6:Q6)","v":9825},{"$f":"SUM(F6,J6,N6,R6)","v":37269}]
[null,"Pat Hill",5213,4557,2187,11957,1070,4424,{"$f":"1240+U7","v":1240},{"$f":"SUM(G7:I7)","v":6734},4128,2331,3849,{"$f":"SUM(K7:M7)","v":10308},2928,3000,5700,{"$f":"SUM(O7:Q7)","v":11628},{"$f":"SUM(F7,J7,N7,R7)","v":40627}]
[null,"Jasmine Hunt",4175,2681,2257,9113,3588,4666,{"$f":"3666-U7","v":3666},{"$f":"SUM(G8:I8)","v":11920},5410,2719,1934,{"$f":"SUM(K8:M8)","v":10063},1440,2000,4700,{"$f":"SUM(O8:Q8)","v":8140},{"$f":"SUM(F8,J8,N8,R8)","v":39236}]
[null,"Total North",{"$f":"SUM(C5:C8)","v":17419},{"$f":"SUM(D5:D8)","v":14983},12161,44563,14199,14640,12720,41559,{"$f":"SUM(K5:K8)","v":16659},{"$f":"SUM(L5:L8)","v":8535},12458,37652,12036,10900,18100,41036,164810]
[]
[]
[null,"Region: South"]
[null,"Mark Watts",3400,5329,4734,{"$f":"SUM(C13:E13)","v":13463},1450,4191,{"$f":"3109","v":3109},{"$f":"SUM(G13:I13)","v":8750},2097,2354,1837,{"$f":"SUM(K13:M13)","v":6288},3525,{"$f":"AVERAGE(L13:M13,O13)","v":2572},{"$f":"AVERAGE(M13,O13:P13)","v":2644.6666666666665},{"$f":"SUM(O13:Q13)","v":8741.6666666666661},{"$f":"SUM(F13,J13,N13,R13)","v":37242.666666666664}]
[null,"Jane Hill",3608,4922,4711,{"$f":"SUM(C14:E14)","v":13241},1463,1204,2342,{"$f":"SUM(G14:I14)","v":5009},3402,5269,3625,{"$f":"SUM(K14:M14)","v":12296},4125,{"$f":"AVERAGE(L14:M14,O14)","v":4339.666666666667},{"$f":"AVERAGE(M14,O14:P14)","v":4029.8888888888891},{"$f":"SUM(O14:Q14)","v":12494.555555555557},{"$f":"SUM(F14,J14,N14,R14)","v":43040.555555555555}]
[null,"Sarah Allen",2303,1253,3651,{"$f":"SUM(C15:E15)","v":7207},4595,2863,3802,{"$f":"SUM(G15:I15)","v":11260},1478,5410,0,{"$f":"SUM(K15:M15)","v":6888},0,{"$f":"AVERAGE(L15:M15,O15)","v":1803.3333333333333},{"$f":"AVERAGE(M15,O15:P15)","v":601.11111111111109},{"$f":"SUM(O15:Q15)","v":2404.4444444444443},{"$f":"SUM(F15,J15,N15,R15)","v":27759.444444444445}]
[null,"Roger West",2359,5273,5438,{"$f":"SUM(C16:E16)","v":13070},2280,4882,3915,{"$f":"SUM(G16:I16)","v":11077},5498,2806,3061,{"$f":"SUM(K16:M16)","v":11365},4757,{"$f":"AVERAGE(L16:M16,O16)","v":3541.3333333333335},3786.4444444444448,{"$f":"SUM(O16:Q16)","v":12084.777777777779},{"$f":"SUM(F16,J16,N16,R16)","v":47596.777777777781}]
[null,"Gill Smith",4487,2638,5100,{"$f":"SUM(C17:E17)","v":12225},1205,4557,2187,{"$f":"SUM(G17:I17)","v":7949},4043,2252,5076,{"$f":"SUM(K17:M17)","v":11371},1101,2809.6666666666665,2995.5555555555552,{"$f":"SUM(O17:Q17)","v":6906.2222222222217},{"$f":"SUM(F17,J17,N17,R17)","v":38451.222222222219}]
[null,"Total South",{"$f":"SUM(C13:C17)","v":16157},{"$f":"SUM(D13:D17)","v":19415},23634,59206,10993,17697,15355,44045,16518,18091,13599,48208,13508,15066,14057.666666666666,42631.666666666664,194090.66666666666]
[]
[]
[null,"Region: Wales"]
[null,"Gareth Jones",1063,3362,2021,{"$f":"SUM(C22:E22)","v":6446},4175,2681,2257,{"$f":"SUM(G22:I22)","v":9113},1070,1722,2176,{"$f":"SUM(K22:M22)","v":4968},3445,{"$f":"AVERAGE(L22:M22,O22)","v":2447.6666666666665},{"$f":"AVERAGE(M22,O22:P22)","v":2689.5555555555552},{"$f":"SUM(O22:Q22)","v":8582.2222222222208},{"$f":"SUM(F22,J22,N22,R22)","v":29109.222222222219}]
[null,"Tim Stevens",4424,4452,4128,{"$f":"SUM(C23:E23)","v":13004},3400,5329,4734,{"$f":"SUM(G23:I23)","v":13463},3588,1732,4534,{"$f":"SUM(K23:M23)","v":9854},5123,3796.3333333333335,4484.4444444444443,{"$f":"SUM(O23:Q23)","v":13403.777777777777},{"$f":"SUM(F23,J23,N23,R23)","v":49724.777777777781}]
[null,"Mike Payne",4666,1240,5410,{"$f":"SUM(C24:E24)","v":11316},3608,4922,4711,{"$f":"SUM(G24:I24)","v":13241},1450,3910,2705,{"$f":"SUM(K24:M24)","v":8065},2936,3183.6666666666665,2941.5555555555552,{"$f":"SUM(O24:Q24)","v":9061.2222222222208},{"$f":"SUM(F24,J24,N24,R24)","v":41683.222222222219}]
[null,"Total Wales",{"$f":"SUM(C22:C24)","v":10153},{"$f":"SUM(D22:D24)","v":9054},11559,{"$f":"SUM(F22:F24)","v":30766},11183,12932,11702,35817,6108,7364,9415,22887,11504,9427.6666666666661,10115.555555555555,31047.222222222219,120517.22222222222]
[]
[]
[null,"Region: Scotland"]
[null,"Simon Campbell",4191,3109,3402,{"$f":"SUM(C29:E29)","v":10702},2303,1253,3651,{"$f":"SUM(G29:I29)","v":7207},1463,2845,2303,{"$f":"SUM(K29:M29)","v":6611},4051,{"$f":"AVERAGE(L29:M29,O29)","v":3066.3333333333335},{"$f":"AVERAGE(M29,O29:P29)","v":3140.1111111111113},{"$f":"SUM(O29:Q29)","v":10257.444444444445},{"$f":"SUM(F29,J29,N29,R29)","v":34777.444444444445}]
[null,"Glen Wilks",1204,2342,1478,{"$f":"SUM(C30:E30)","v":5024},2359,5273,5438,{"$f":"SUM(G30:I30)","v":13070},4595,2857,5138,{"$f":"SUM(K30:M30)","v":12590},3261,{"$f":"AVERAGE(L30:M30,O30)","v":3752},{"$f":"AVERAGE(M30,O30:P30)","v":4050.3333333333335},{"$f":"SUM(O30:Q30)","v":11063.333333333334},{"$f":"SUM(F30,J30,N30,R30)","v":41747.333333333336}]
[null,"Total Scotland",{"$f":"SUM(C29:C30)","v":5395},{"$f":"SUM(D29:D30)","v":5451},4880,15726,4662,6526,9089,20277,6058,5702,7441,19201,7312,6818.3333333333339,7190.4444444444453,21320.777777777781,76524.777777777781]
[]
[null,"Total",{"$f":"SUM(C9,C18,C25,C31)","v":49124},{"$f":"SUM(D9,D18,D25,D31)","v":48903},52234,150261,41037,51795,48866,141698,45343,39692,42913,127948,44360,42212,49463.666666666664,136035.66666666666,{"$f":"SUM(S9,S18,S25,S31)","v":555942.66666666663}]

{"sheet":"Notes","index":3,"n_rows":29,"n_cols":5,"rows_kept":29,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,null,"Issue","Range","Comments"]
[]
[null,null,"Layout and structure"]
[null,1,"Lack of report description",null,"Very little information is provided about the information presented. What year is this for, what is the currency, is this actual amounts or in 1,000s?"]
[null,2,"Lack of data labels","Col P&Q","November and December are forecasts based on an average of the last three months. This is not easily seen."]
[null,3,"Lack of seperation within model",null,"Data entry, calculation and presentation all on one sheet"]
[null,4,"Lack of clarity over cell types",null,"It is not clear what is an input cell and what is a calculation cell"]
[null,5,"Inconsitency over cell types","Col P&Q","November and December are forecasts based on an average of the last three months. However for the North region these are constants."]
[null,6,"Inconsitency over cell types","G5,G6,H5,H6","These cells contain formula which is inconsistent"]
[null,7,"Inconsitency over cell types","I5:I8","These cells contain formula which is inconsistent"]
[null,8,"Inconsitency over cell types","I13","This cells contain formula which is inconsistent"]
[null,9,"Inconsitency over cell types","F5:F8","These cells contain constants which is inconsistent"]
[null,10,"Hidden row","Row 15","Row 15 is hidden but contains values (not obvious as the headers were removed)"]
[null,11,"Masked cell","U5","This cell has a white on white format (white font, white background). REVIEW."]
[]
[null,null,"Formulae"]
[null,1,"Circular reference","Q30","The formula is incorrect and is a circular reference"]
[null,2,"Formula returning an error","I13, J13, J33, S13, S18 & S33","These formulae are returning the #REF error"]
[null,3,"Incorrect formula","Col Q","The December forecast formula is incorrect as it is referencing the wrong ranges"]
[null,4,"Formulae with no precedents","G5,G6,H5,H6","Not necessarily an issue but worth reviewing. These cell contain formulae with all values hard-coded"]
[null,5,"Mixed cell entry","I5:I6","Formulae with a mix of constants and cell references. Not necessarily an issue but not best practice."]
[null,6,"Mixed cell entry refering to a blank cell","I7:I8","Formulae with a mix of constants and cell reference to blank cell. Not necessarily an issue needs to be reviewed and not best practice."]
[null,7,"Incorrect formulae","Row 25","The total formula is missing the top row for the Wales region"]
[null,8,"Incorrect formula","L9","The formula has been incorrectly copied from K9 and sums K5:K8 rather than L5:L8."]
[null,9,"Missing formulae","F5:F8","Formulae have been overwritten with constants"]
[null,10,"Number formatted as text","E22:E24","These cells contain numbers formatted as text"]
[null,11,"Incorrect formulae usage","P15,Q15","These formulae are returning values for the forecast. Is this correct?"]