{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Marks","TEMPLATE"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":38,"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":160,"n_cols":13,"rows_kept":160,"cut":false,"hidden":false,"merged":[]}
[" ","CAA Module X19",null,null,null,"Part 1",{"$f":"part1","v":0},{"$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":0},"out of 59"]
[null,"Go to Marks sheet",null,null,null,"TOTAL",{"$f":"G1+G2","v":0},"out of 100"]
[]
[null,{"$f":"i+ii+iii+iv+v+vi+vii+viii+ix+x+xi+xii+xiii+reasonableness_checks","v":0},"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":0},null,"PART (i) (max 2)",null,null,null,null,null,null,null,null,0]
[null,null,1,"Spreadsheet has separate worksheets for parameters and calculations",null,null,null,null,null,null,null,null,0.5]
[null,null,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:B20),5)","v":0},null,"PART (ii) (max 5)",null,null,null,null,null,null,null,null,2]
[null,null,1,"Count number of data points"]
[null,null,1,"... and check that this is equal to 300"]
[null,null,1,"Calculate min and max salary values"]
[null,null,1,"... and confirm none greater than reasonable upper limit or less than 20,000"]
[null,null,1,"Calculate mean and median salary values"]
[null,null,1,"Visual Check that data appears to be spread with no obvious outliers"]
[null,null,1,"Check on the Min / Max number of exams "]
[null,null,1,"Any other valid check"]
[]
[null,{"$f":"MIN(SUM(B23:B25),3)","v":0},null,"PART (iii) (max 3)"]
[null,null,1,"Correct calculation of number of trainees in data who have passed only one exam."]
[null,null,1,"Correct calculation of total salaries for trainees in data who have passed only one exam."]
[null,null,1,"Correct calculation of average salary for trainees in data who have passed only one exam"]
[]
[null,{"$f":"MIN(SUM(B28:B30),3)","v":0},null,"PART (iv) (max 3)"]
[null,null,1,"Correct calculation of number of trainees in data who have passed each exam."]
[null,null,1,"Correct calculation of total salaries for trainees in data who have passed each exam."]
[null,null,1,"Correct calculation of average salary for trainees in data who have passed each exam"]
[]
[null,{"$f":"MIN(3,SUM(B33:B35))","v":0},null,"PART (v) (max 3)"]
[null,null,1,"Correct calculation of mean payrise for passing all exams (mean increase for passing all exams - starting salary)"]
[null,null,1,"Correct calculation of mean payrise for passing technical, specialist and summative exams"]
[null,null,1,"Correct calculation of proportion of total increase attributable to each exam type (technical, specialist and summative)"]
[]
[null,{"$f":"MIN(3,SUM(B38:B40))","v":0},null,"PART (vi) (max 3)"]
[null,null,1,"Correctly starting salary taken from parameters tab"]
[null,null,1,"Correct number of pay raises applied for each exam type (i.e. 5 technical exam, 2 specialist and 1 summative  pay raise)"]
[null,null,1,"Correct salary calculated after each pay raise"]
[]
[null,{"$f":"MIN(2,SUM(B43:B44))","v":0},null,"PART (vii) (max 2)"]
[null,null,1,"Correct calculation of pay raises for each exam type"]
[null,null,1,"Correct calculation of proportion of total pay raises for each exam type"]
[]
[null,{"$f":"MIN(4,SUM(B47:B50))","v":0},null,"PART (viii) (max 4)"]
[null,null,1,"Calculations in Base tab copied into new tab"]
[null,null,1,"Calculation of pay raises updated to include addition of fixed pay raise parameter "]
[null,null,1,"Correct determination of fixed increase to pay raisesusing goalseek, or otherwise (e.g. trial and error)"]
[null,null,1,"Check on goalseek or other approach adopted (e.g. trial and error)"]
[]
[null,{"$f":"MIN(1,SUM(B53:B53))","v":0},null,"PART (ix) (max 1)"]
[null,null,1,"Correct calculation of proportion of total pay raises for each exam type"]
[]
[null,{"$f":"MIN(SUM(B56:B60),5)","v":0},null,"PART (x) (max 5)"]
[null,null,1,"Calculations in Base tab copied into new tab"]
[null,null,1,"Calculation of pay raises updated to use percentage of salary pay raise parameter "]
[null,null,1,"Calculation of pay raises updated to usecorrect multiple of salary pay raise parameter (1, 2 or 4)"]
[null,null,1,"Correct determination of fixed increase to pay raises using goalseek, or otherwise (e.g. trial and error)"]
[null,null,1,"Check on goalseek or other approach adopted (e.g. trial and error)"]
[]
[null,{"$f":"MIN(1,SUM(B63:B63))","v":0},null,"PART (xi) (max 1)"]
[null,null,1,"Correct calculation of proportion of total pay raises for each exam type"]
[]
[null,{"$f":"MIN(3,SUM(B66:B68))","v":0},null,"PART (xii) (max 3)"]
[null,null,1,"Construction of chart - Future expected salaries under each of the three scenarios considered relative to the mean salary "]
[null,null,1,"Construction of chart - Sensible type of chart selected"]
[null,null,1,"Construction of chart - Meaningful axis headings and title included"]
[]
[null,{"$f":"MIN(3,SUM(B71:B73))","v":0},null,"PART (xiii) (max 3)"]
[null,null,1,"Construction of chart - Proportion of the total salary increase awarded for passing all 8 exams between the technical, specialist and summative exams for each of the three scenarios considered relative to the industry data."]
[null,null,1,"Construction of chart - Sensible type of chart selected"]
[null,null,1,"Construction of chart - Meaningful axis headings and title included"]
[]
[null,{"$f":"MIN(4,SUM(B76:B80))","v":0},null,"reasonableness checks (max 4)"]
[null,null,1,"Proportion of total pay rise attributed to each of the exam types adds to 100%"]
[null,null,1,"Setting fixed addition to pay rises to zerp produces results equal to the base scenario"]
[null,null,1,"Salary increases after each exam for all scenarios"]
[null,null,1,"Final salary after passing all exams equal for all scenarios"]
[null,null,1,"any other valid check"]
[]
[null,{"$f":"audit_approach++audit_content","v":0},"of 58","PART 2"]
[]
[null,{"$f":"B86+B94+B99","v":0},"of 15","Audit Approach"]
[]
[null,{"$f":"MIN(8,SUM(B87:B92))","v":0},"of 8","Fellow Analyst student can review, check and modify the model"]
[null,null,2,"For a newcomer, the audit trail is easy to follow i.e. the marker does not have to look at the model directly to understand what has been done"]
[null,null,2,"All the steps are correctly and clearly described"]
[null,null,1,"There is sufficient technical detail"]
[null,null,1,"The workbook is well labelled and is easy to navigate through"]
[null,null,1,"Where there are, or could be errors, the audit trail would enable the student to identify and correct errors"]
[null,null,1,"Danger areas in the spreadsheet are appropriately flagged (e.g. goal seek)"]
[]
[null,{"$f":"MIN(4,SUM(B95:B97))","v":0},"of 4","Written in clear English"]
[null,null,2,"The audit trail is written in clear, crisp and flowing English"]
[null,null,1,"Accurate spelling"]
[null,null,1,"The audit trail is laid out well, with good formatting to aid clarity"]
[]
[null,{"$f":"MIN(3,SUM(B100:B102))","v":0},"of 3","Written in a logical order"]
[null,null,1,"Data is introduced before referring to it"]
[null,null,1,"Assumptions are stated before using them"]
[null,null,1,"The methodology is described in a logical order, ie nothing is introduced which would require that the reader has read ahead"]
[]
[]
[null,{"$f":"B107+B126+B139+B148","v":0},"of 43","Audit Content"]
[]
[null,{"$f":"MIN(21,SUM(B108:B124))","v":0},"of 21","All model steps  accurately covered (1 or 2 each, max of 23)"]
[null,null,2,"Description of objective, brief overview of model"]
[null,null,2,"Description of data used, including source"]
[null,null,1,"List of assumptions (1/2 mark for each max 1)"]
[null,null,1,"Clear statement that no changes have been made to the data"]
[null,null,1,"List of parameters used"]
[null,null,1,"Range names specified for parameters (providing clear link between list of parameters and use of these in calculations)"]
[null,null,2,"Calculation of the mean salary for trainee investment analysts who have passed 0 to 8 exams"]
[null,null,1,"Calculation of proportion of total pay rises for each exam type based on survey data"]
[null,null,1,"Calculation of the trainee investment analyst salary after each exam pass from 0 to 8 based on the current pay rise information provided by management."]
[null,null,1,"Calculation of proportion of total pay raises for each exam type based on current pay rise structure"]
[null,null,1,"Adjustment to base calculations to include fixed addition to fixed pay raises."]
[null,null,2,"Calculation of fixed increase, using goal seek or trial and error"]
[null,null,1,"Calculation of proportion of total pay raises for each exam type based on revised  fixed pay rise structure"]
[null,null,1,"Adjustment to base calculations to use salary related pay rises"]
[null,null,1,"Calculation of percentage increase, using goal seek or trial and error"]
[null,null,1,"Calculation of proportion of total pay rises for each exam type based on revised  salary related pay raise structure"]
[null,null,1,"Construction of charts"]
[]
[null,{"$f":"MIN(8,SUM(B127:B137))","v":0},"of 8","All checks clearly recorded / described"]
[null,null,null,"(1 each - maximum of 8)"]
[null,null,1,"Total number of data items is 300"]
[null,null,1,"Minimum and maximum values (checked no values lower than 20,000 and none greater than 50,000 or other sensible upper bound)"]
[null,null,1,"Simulated values plotted on a chart to verify that values spread evenly between max and min"]
[null,null,1,"Check mean salary increases with each pay raise"]
[null,null,1,"Check pay raises allocated to each exam type add to mean pay raise for passing all exams"]
[null,null,1,"Check proportion of pay raises allocated to each exam sum to 100%"]
[null,null,1,"Check starting salary for all projections equal to 20,000"]
[null,null,1,"Check final salary for both scenarios equal to mean final salary in data"]
[null,null,1,"Check on number of exams"]
[null,null,1,"Any other distinct, valid check"]
[]
[null,{"$f":"MIN(8,SUM(B140:B146))","v":0},"of 8","All steps clearly explained"]
[null,null,1,"The level of detail in the audit trail is appropriate for a newcomer to understand what has been done"]
[null,null,2,"All the methodology steps are set out clearly"]
[null,null,1,"Data source and format is described adequately and clearly."]
[null,null,1,"Items for double-checking are highlighted, with justification (given that no data adjustments are required)"]
[null,null,1,"All reasonableness checks applied are adequately documented"]
[null,null,1,"Areas where manual intervention or caution is required are well flagged (eg goalseeks or non-standard model areas)"]
[null,null,1,"The marker does not need to look directly at the model to understand what has been performed"]
[]
[null,{"$f":"MIN(6,SUM(B149:B154))","v":0},"of 6","Clear signposting/labelling"]
[null,null,1,"The audit trail allows the user to follow the model through"]
[null,null,1,"The audit trail allows the user to understand each calculation easily"]
[null,null,1,"There is adequate signposting in the audit trail to describe the purpose of each tab"]
[null,null,1,"There is adequate signposting in the audit trail to describe the general direction of the model"]
[null,null,1,"There is adequate signposting to indicate where in each sheet to look for the information / calculations / checks"]
[null,null,1,"Model labelling is consistent with the audit trail (data, parameters, analysis, results)"]
[]
[]
[null,"Overall Marks & P/F Assessment"]
[]
[null,null,"Total Marks:",{"$f":"part1+part2","v":0},{"$f":"D159/100","v":0}]
[null,null,null,{"$f":"D159=P1_TOTAL","v":true}]