{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Marks","TEMPLATE"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":36,"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":"Marks","index":0,"n_rows":52,"n_cols":29,"rows_kept":52,"cut":false,"hidden":true,"merged":[[5,3,5,10],[6,3,6,10],[8,2,8,14]]}
["SUBJECT CAA Module 5",null,null,"May 2018 exam",null,null,null,null,null,"Assignment No :",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Count","% total"]
["Scripts base path",null,"F:\\CAA Module 5\\May 2018\\Sorted",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"MAX",{"$f":"MAX(X13:X52)","v":0},null,"F","<55",{"$f":"COUNTIF($X$13:$X$52,\"<\"&55)","v":0},{"$f":"IF($AB$7>0,AB2/$AB$7,\"N/A\")","v":"N/A"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"MIN",{"$f":"MIN(X13:X52)","v":0},null,"BL/F","<59",{"$f":"COUNTIF($X$13:$X$52,\"<\"&59)-SUM(AB2:AB2)","v":0},"N/A"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AVE",{"$f":"IF( AB7 > 0,  AVERAGE(X13:X52),  0)","v":0},null,"BL","59-61",{"$f":"COUNTIF($X$13:$X$52,\"<\"&61)-SUM(AB2:AB3)","v":0},"N/A"]
[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,"BL/P",">61",{"$f":"COUNTIF($X$13:$X$52,\"<\"&65)-SUM(AB2:AB4)","v":0},"N/A"]
[null,"RESULTS :",null,"Type results date here",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"P",">65",{"$f":"COUNTIF($X$13:$X$52,\"<\"&100)-SUM(AB2:AB5)","v":0},"N/A"]
[null,"ASSISTANT EXAMINER :",null,"Type name here",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Σ",null,{"$f":"SUM(AB2:AB6)","v":0},"N/A"]
[]
[null,null,"MODELLING",null,null,null,null,null,null,null,null,null,null,null,null,null,"AUDIT TRAIL",null,null,null,null,null,null,"Total",null," "]
[null,"Aspect :","I","II","III","IV","V","VI","VII","VIII","IX","X","XI",null,"Total","Fellow Analyst student can review, check & modify the model","Written in clear English","Written in a logical order","All model steps accurately covered","All check clearly recorded","All steps clearly explained","Clear signposting & labelling","Total"]
[null,"Max Marks :",2,9,3,3,2,4,2,3,8,2,3,null,{"$f":"SUM(C11:N11)","v":41},8,5,3,20,8,8,7,{"$f":"SUM(P11:V11)","v":59},{"$f":"O11+W11","v":100}]
[null,"ARN"]
[1]
[{"$f":"A13+1","v":2}]
[{"$f":"A14+1","v":3}]
[4]
[5]
[6]
[7]
[8]
[9]
[10]
[11]
[12]
[13]
[14]
[15]
[16]
[17]
[18]
[19]
[20]
[21]
[22]
[23]
[24]
[25]
[26]
[27]
[28]
[29]
[30]
[31]
[32]
[33]
[34]
[35]
[36]
[37]
[38]
[39]
[40]

{"sheet":"TEMPLATE","index":1,"n_rows":171,"n_cols":13,"rows_kept":171,"cut":false,"hidden":false,"merged":[]}
[" ","CAA Module 119",null,null,null,"Part 1",{"$f":"part1","v":42},{"$f":"C5","v":"of 42"}]
[null,"ARN :",{"$f":"MID(CELL(\"filename\",C2),FIND(\"]\",CELL(\"filename\",C2))+1,LEN(CELL(\"filename\",C2)))","v":"TEMPLATE"},null,null,"Part 2",{"$f":"part2","v":58},"out of 58"]
[null,"Go to Marks sheet",null,null,null,"TOTAL",{"$f":"G1+G2","v":100},"out of 100"]
[]
[null,{"$f":"i+ii+iii+iv+v+vi+vii+viii+ix+x+xi+reasonableness_checks","v":42},"of 42","PART 1"]
[null,null,null,null,null,null,null,null,null,null,null,null,1]
[null,null,null,null,null,null,null,null,null,null,null,null,0.5]
[null,{"$f":"MIN(SUM(B9:B10),2)","v":2},null,"PART (i) (max 2)",null,null,null,null,null,null,null,null,0]
[null,1,1,"Spreadsheet has separate worksheets for parameters and calculations",null,null,null,null,null,null,null,null,0.5]
[null,1,1,"All worksheets have meaningful/sensible titles",null,null,null,null,null,null,null,null,1]
[null,null,null,null,null,null,null,null,null,null,null,null,1.5]
[null,{"$f":"MIN(SUM(B13:B22),4)","v":4},null,"PART (ii) (max 4)",null,null,null,null,null,null,null,null,2]
[null,1,1,"Check years are valid (e.g. within expected range, 4 years between olympic years)"]
[null,1,1,"Check ID number increases by 1 each time"]
[null,1,1,"Check equal number of M and F data points"]
[null,1,1,"Check equal number of Gold, Silver and Bronze data points"]
[null,1,1,"Check sex = M for all men's events and sex = F for all women's events"]
[null,1,1,"Check no blanks"]
[null,1,1,"Check men's distance greater than women's"]
[null,1,1,"Check long jump distance is greater than high jump"]
[null,1,1,"Check for no outliers"]
[null,1,1,"Any other valid check"]
[]
[null,{"$f":"MIN(SUM(B25:B28),3)","v":3},null,"PART (iii) (max 3)"]
[null,1,1,"Total funding identified as a parameter"]
[null,1,1,"Option 1 parameters identified"]
[null,1,1,"Option 2 parameters identified"]
[]
[]
[null,{"$f":"MIN(SUM(B31:B34),4)","v":4},null,"PART (iv) (max 4)"]
[null,1,1,"Medal numbers calculated correctly (11 gold, 8 silver, 9 bronze, 28 total)"]
[null,1,1,"Correct data shown on a chart"]
[null,1,1,"Suitable chart type used (e.g. bar chart)"]
[null,1,1,"Chart has title and axes labels"]
[]
[null,{"$f":"MIN(4,SUM(B37:B40))","v":4},null,"PART (v) (max 4)"]
[null,1,1,"Results shown in a single table"]
[null,1,1,"Results shown for gold, silver and bronze separately"]
[null,1,1,"Results shown for Country A, Country C, Country D, Country E and Country F separately"]
[null,1,1,"Correct results (see marking schedule)"]
[]
[null,{"$f":"MIN(3,SUM(B43:B46))","v":3},null,"PART (vi) (max 3)"]
[null,1,1,"Correct data plotted on chart"]
[null,1,1,"Suitable chart type used (e.g. line chart)"]
[null,1,1,"Chart has title and axes labels"]
[]
[]
[null,{"$f":"MIN(4,SUM(B49:B52))","v":4},null,"PART (vii) (max 4)"]
[null,1,1,"Total change in distance over period calculated for each event (8.7%, 7.4%, 4.2%, 3.6% OR 0.19m, 0.14m, 0.34m, 0.24m)"]
[null,1,1,"Number of periods calculated (14)"]
[null,2,2,"Correct % change per Olympics (0.60%, 0.51%, 0.30%, 0.25%)"]
[null,null,null,"Full marks available for correct result without setting out intermediate calculations"]
[]
[null,{"$f":"MIN(2,SUM(B55:B58))","v":2},null,"PART (viii) (max 2)"]
[null,2,2,"Correct results (2.38m, 2.05m, 8.43m, 7.02m)"]
[null,null,null,"Full marks available if part (vii) calculated incorrectly but applied correctly in part (viii)"]
[]
[]
[]
[null,{"$f":"MIN(2,SUM(B61:B64))","v":2},null,"PART (ix) (max 2)"]
[null,2,2,"Correct results (M HJ - 1, M LJ - 3, w HJ - 1, W LJ - 0)"]
[]
[]
[]
[]
[null,{"$f":"MIN(SUM(B67:B70),5)","v":5},null,"PART (x) (max 5)"]
[null,2,2,"Correct split between men's and women's events (M = $20m, W = $30m)"]
[null,2,2,"Correct results (M HJ = 13.33, M LJ = 6.67, W HJ = 20, W LJ = 10)"]
[null,1,1,"Total funds allocated = $50m"]
[null,null,null,"Full marks available if student uses Solver or other method and gets correct result"]
[]
[null,{"$f":"MIN(5,SUM(B73:B76))","v":5},null,"PART (xi) (max 5)"]
[null,2,2,"Calculated x = 3.45"]
[null,2,2,"Correct results (M HJ = 10.34, M LJ = 3.45, W HJ = 15.52, W LJ = 20.69)"]
[null,1,1,"Total funds allocated = $50m"]
[null,null,null,"Full marks available if student uses Solver or other method and gets correct result"]
[]
[null,{"$f":"MIN(4,SUM(B79:B86))","v":4},null,"Reasonableness checks (max 4)"]
[null,1,1,"Total Country B medals (G+S+B) = total calculated directly from data"]
[null,1,1,"Total other country medals (G+S+B) = total calculated directly from data"]
[null,1,1,"Applying % increase in distance to 1964 distance gives 2020 distance"]
[null,1,1,"2024 expected distance is greater than 2020 distance"]
[null,1,1,"Country A medal count across 4 events = medal count by G/S/B"]
[null,1,1,"Funding split calculated for option 1 sums to $50m"]
[null,1,1,"Funding split calculated for option 2 sums to $50m"]
[null,1,1,"Any other distinct, valid check"]
[]
[null,{"$f":"audit_approach+audit_content","v":58},"of 58","PART 2"]
[]
[null,{"$f":"B92+B100+B105","v":15},"of 15","Audit Approach"]
[]
[null,{"$f":"MIN(8,SUM(B93:B98))","v":8},"of 8","Fellow Analyst student can review, check and modify the model"]
[null,2,2,"For a newcomer, the calculations are easy to follow i.e. the marker does not have to look at the audit trail to understand what has been done"]
[null,1,1,"All the steps are correctly and clearly described"]
[null,1,1,"There is sufficient technical detail"]
[null,2,2,"The workbook is well labelled and is easy to navigate through"]
[null,1,1,"Where there are, or could be errors, the audit trail would enable the student to identify and correct errors"]
[null,1,1,"Danger areas in the spreadsheet are appropriately flagged (e.g. goal seek, formulae that don't copy down) "]
[]
[null,{"$f":"MIN(4,SUM(B101:B103))","v":4},"of 4","Written in clear English"]
[null,2,2,"The audit trail is written in clear, crisp and flowing English"]
[null,1,1,"Accurate spelling"]
[null,1,1,"The audit trail is laid out well, with good formatting to aid clarity"]
[]
[null,{"$f":"MIN(3,SUM(B106:B108))","v":3},"of 3","Written in a logical order"]
[null,1,1,"Data is introduced (e.g. what is it, what is the source) before description of how data is used"]
[null,1,1,"Assumptions are stated before explanation of model method"]
[null,1,1,"The methodology is described in a logical order i.e. nothing is introduced which would require that the reader has read ahead"]
[]
[]
[null,{"$f":"B113+B129+B150+B159","v":43},"of 43","Audit Content"]
[]
[null,{"$f":"MIN(21,SUM(B114:B127))","v":21},"of 21","All model steps  accurately covered (1 or 2 each, max of 21)"]
[null,2,2,"Overview"]
[null,2,2,"Description of the data used, including source"]
[null,1,1,"Clear statement that no changes have been made to the data"]
[null,1,1,"List of parameters used"]
[null,2,2,"Description of calculation of US medal numbers"]
[null,2,2,"Clear reporting of which numbers are plotted in the chart and type of chart used (US medals)"]
[null,1,1,"Description of calculation of other country medal numbers"]
[null,2,2,"Clear reporting of which numbers are plotted in the chart and type of chart used (winning distances)"]
[null,2,2,"Description of how average increase in distance is calculated"]
[null,2,2,"Description of how 2024 winning distance is calculated"]
[null,2,2,"Description of how funding split is calculated using option 1"]
[null,2,2,"Description of how funding split is calculated using option 2"]
[null,2,2,"Any other useful description"]
[]
[]
[null,{"$f":"MIN(8,SUM(B130:B148))","v":8},"of 8","All checks clearly recorded / described"]
[null,null,null,"(1 each - maximum of 8)"]
[null,1,1,"Description of the check that the years for each data set are in a valid range (1980-2020 and 1964-2020)"]
[null,1,1,"Description of the check that there are no blank values"]
[null,1,1,"Description of the check that the ID numbers look correct"]
[null,1,1,"Description of the check that half of the data points are male/female"]
[null,1,1,"Description of the check that a third of the data points are gold/silver/bronze"]
[null,1,1,"Description of the check that all men's event medals are for males (and vice versa)"]
[null,1,1,"Description of the check that men's distances are longer than women's, and/or long jump longer than high jump"]
[null,1,1,"Description of the check that there are no outliers in the data"]
[null,1,1,"Description of the check that gold plus silver plus bronze medals for US match the overall total from the data"]
[null,1,1,"As above for the other countries"]
[null,1,1,"Description of the check that the percentage increase in distance applied to the 1964 value reproduces the 2020 value"]
[null,1,1,"Description of the check that the expected 2024 distance is greater than the 2020 distance"]
[null,1,1,"Description of the check that the chart appears consistent with the values calculated"]
[null,1,1,"Description of the check that the total Country A medals for the four events matches the total across the three medal types"]
[null,1,1,"Description of the check that twice as much funding is allocated to high jump for option 1"]
[null,1,1,"Description of the check that total funding sums to $50m for option 1 and/or option 2"]
[null,2,2,"Description of any other valid check performed"]
[]
[]
[null,{"$f":"MIN(8,SUM(B151:B157))","v":8},"of 8","All steps clearly explained"]
[null,1,1,"The level of detail in the audit trail is appropriate for a newcomer to understand what has been done"]
[null,2,2,"All the methodology steps are set out clearly"]
[null,2,2,"Data source and format is described adequately and clearly."]
[null,1,1,"All reasonableness checks applied are adequately documented"]
[null,1,1,"Areas where manual intervention or caution is required are well flagged within the audit trail (eg goalseeks or non-standard model areas)"]
[null,1,1,"The marker does not need to look directly at the model to understand what has been performed"]
[]
[]
[null,{"$f":"MIN(6,SUM(B160:B165))","v":6},"of 6","Clear signposting/labelling"]
[null,1,1,"The audit trail allows the user to follow the model through"]
[null,2,2,"The audit trail allows the user to understand each calculation easily"]
[null,1,1,"There is adequate signposting in the audit trail to describe the purpose of each tab"]
[null,1,1,"There is adequate signposting in the audit trail to describe the general direction of the model"]
[null,1,1,"There is adequate signposting of named ranges and parameters"]
[null,1,1,"Model labelling (e.g. sheet names) is consistent with the audit trail "]
[]
[]
[null,"Overall Marks & P/F Assessment"]
[]
[null,null,"Total Marks:",{"$f":"part1+part2","v":100},{"$f":"D170/100","v":1}]
[null,null,null,{"$f":"D170=P1_TOTAL","v":true}]