{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Scorecard Design","Strategy Map","Four Quadrant","Tabular"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":0,"defined_names":3,"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":"Scorecard Design","index":0,"n_rows":44,"n_cols":2,"rows_kept":44,"cut":false,"hidden":false,"merged":[[39,0,39,1],[2,0,2,2],[4,0,4,2],[31,0,31,1],[5,0,5,2],[21,0,21,1],[20,1,20,2],[17,0,17,2]]}
[]
[]
["KPI Reporting  - Scorecard Design"]
[]
["Please read this in conjunction with the word document in this module and read the instructions below and the word document FIRST"]
["Please note: Please ensure you save a copy of this template onto your hard drive as changes will not be saved."]
["Key Steps"]
[1,"Scorecard Design: Review the three KPI design report formats on the tabs below and choose the one you would prefer to use."]
[2,"Detail Questions: Populate the KPI Headings within the template design you've chosen. Some examples are included in the tables below for your reference. "]
[3,"Also within your chosen spreadsheet, type your specific KPIs"]
[4,"Decide on your reporting period, performance comparators and variance columns"]
[null,"Remember to pay close attention to sequence in which the columns are displayed and revisit if necessary, avoiding excessive number of columns"]
[4.0999999999999996,"Add or replace existing reporting periods"]
[4.2,"Add or replace existing comparator columns"]
[4.3,"Add or replace existing variance columns"]
[5,"Review your final design"]
[6,"Create and check formulas (the limited ones included are for illustration purposes only)."]
["Re-read the instructions above if you are unsure. Otherwise you are ready"]
[]
[]
[null,"Detail Questions: Populate the KPI Headings in the relevant table  below depending upon which template design you've chosen."]
["Tabular"]
[1,"To develop our customer proposition"]
[2,"To expand distribution and improve its effectiveness"]
[3,"To improve our standards of customer service"]
[4,"To manage risk and improve quality"]
[5,"To simplify the business"]
[6,"To improve cost competitiveness"]
[7,"Develop our organisation, our people and our culture"]
[]
[]
["Four Quadrant Approach"]
[1,"FINANCE"]
[2,"CUSTOMER"]
[3,"ORGANISATIONAL DEVELOPMENT"]
[4,"OPERATIONAL EXCELLENCE"]
[5,"SHAREHOLDER VALUE"]
[]
[]
["Strategy Map on One Page Summary"]
[1,"FINANCIAL"]
[2,"CUSTOMER"]
[3,"OPERATIONS"]
[4,"INNOVATION"]

{"sheet":"Strategy Map","index":1,"n_rows":30,"n_cols":7,"rows_kept":30,"cut":false,"hidden":false,"merged":[[15,0,18,0],[20,0,23,0],[25,0,29,0],[0,0,0,6],[2,1,2,6],[6,0,13,0]]}
["Executive Summary"]
[]
["Vision","This is our vision…"]
[]
[null,"CSF Map","KPI","Actual","Target","Trend ","Status"]
[]
[{"$f":"'Scorecard Design'!B41","v":"FINANCIAL"},null,"Gross Written Premiums",null,null,"↑","r"]
[null,null,"C2 (Profit before tax and minority interest)",null,null,"↔","a"]
[null,null,"Net New Assets",null,null,"↓","g"]
[null,null,"Technical Reserves",null,null,null,"r"]
[null,null,"Assets Under Management (Growth)"]
[null,null,"Return on Shareholder Assets"]
[null,null,"Embedded Value (growth)"]
[null,null,"Return on Capital Investment"]
[]
[{"$f":"'Scorecard Design'!B42","v":"CUSTOMER"},null,"Market Share",null,null,"→"]
[null,null,"Customer Satisfaction"]
[null,null,"Sales Margin"]
[null,null,"No. of Contracts/Customers"]
[]
[{"$f":"'Scorecard Design'!B43","v":"OPERATIONS"},null,"Lapse Rates",null,null,"↓"]
[null,null,"Pipeline"]
[null,null,"Expenses Ration"]
[null,null,"Service Quality"]
[]
[{"$f":"'Scorecard Design'!B44","v":"INNOVATION"},null,"Time to Market",null,null,"↑"]
[null,null,"Project Milestones"]
[null,null,"Staff Turnover"]
[null,null,"Education Level"]
[null,null,"Staff Satisfaction"]

{"sheet":"Four Quadrant","index":2,"n_rows":59,"n_cols":17,"rows_kept":59,"cut":false,"hidden":false,"merged":[[0,0,0,7],[0,10,0,17],[45,0,45,7],[45,10,45,16],[51,1,51,7]]}
[{"$f":"'Scorecard Design'!$B$33","v":"FINANCE"},null,null,null,null,null,null,null,null,null,{"$f":"'Scorecard Design'!$B$34","v":"CUSTOMER"}]
[]
[]
[null,null,"YTD Actual","YTD Plan (Target)","Var vs Plan","Last Month Status/ Outlook","This Month Status/ Outlook","Q",null,null,null,"YTD Actual","YTD Plan (Target)","Var vs Plan","Last Month Status/ Outlook","This Month Status/ Outlook","Q"]
[null,"KPI 1",120000,110000,{"$f":"IFERROR(C5/D5-1,\"\")","v":9.0909090909090828E-2},"↑",null,null,null,null,"KPI 1",null,null,{"$f":"IFERROR(L5/M5-1,\"\")","v":""}]
[null,"KPI 2",120000,130000,{"$f":"IFERROR(C6/D6-1,\"\")","v":-7.6923076923076872E-2},"↔",null,null,null,null,"KPI 2",null,null,{"$f":"IFERROR(L6/M6-1,\"\")","v":""}]
[null,"KPI 3",null,null,"","↓",null,null,null,null,"KPI 3",null,null,""]
[null,"KPI 4",null,null,"",null,null,null,null,null,"KPI 4",null,null,""]
[null,"KPI 5",null,null,"",null,null,null,null,null,"KPI 5",null,null,""]
[null,"KPI 6",null,null,"",null,null,null,null,null,"KPI 6",null,null,""]
[null,"KPI 7",null,null,"",null,null,null,null,null,"KPI 7",null,null,""]
[null,"KPI 8",null,null,"",null,null,null,null,null,"KPI 8",null,null,""]
[null,"KPI 9",null,null,"",null,null,null,null,null,"KPI 9",null,null,""]
[null,"KPI 10",null,null,"",null,null,null,null,null,"KPI 10",null,null,""]
[null,"KPI 11",null,null,"",null,null,null,null,null,"KPI 11",null,null,""]
[null,"KPI 12",null,null,"",null,null,null,null,null,"KPI 12",null,null,""]
[null,"KPI 13",null,null,"",null,null,null,null,null,"KPI 13",null,null,""]
[null,"KPI 14",null,null,"",null,null,null,null,null,"KPI 14",null,null,""]
[null,"KPI 15",null,null,"",null,null,null,null,null,"KPI 15",null,null,""]
[null,"KPI 16",null,null,"",null,null,null,null,null,"KPI 16",null,null,""]
[null,"KPI 17",null,null,"",null,null,null,null,null,"KPI 17",null,null,""]
[]
[]
[]
[]
[null,"KPI 1",null,null,{"$f":"IFERROR(C26/D26-1,\"\")","v":""},null,null,null,null,null,"KPI 1",null,null,{"$f":"IFERROR(L26/M26-1,\"\")","v":""}]
[null,"KPI 2",null,null,{"$f":"IFERROR(C27/D27-1,\"\")","v":""},null,null,null,null,null,"KPI 2",null,null,{"$f":"IFERROR(L27/M27-1,\"\")","v":""}]
[null,"KPI 3",null,null,"",null,null,null,null,null,"KPI 3",null,null,""]
[null,"KPI 4",null,null,"",null,null,null,null,null,"KPI 4",null,null,""]
[null,"KPI 5",null,null,"",null,null,null,null,null,"KPI 5",null,null,""]
[null,"KPI 6",null,null,"",null,null,null,null,null,"KPI 6",null,null,""]
[null,"KPI 7",null,null,"",null,null,null,null,null,"KPI 7",null,null,""]
[null,"KPI 8",null,null,"",null,null,null,null,null,"KPI 8",null,null,""]
[null,"KPI 9",null,null,"",null,null,null,null,null,"KPI 9",null,null,""]
[null,"KPI 10",null,null,"",null,null,null,null,null,"KPI 10",null,null,""]
[null,"KPI 11",null,null,"",null,null,null,null,null,"KPI 11",null,null,""]
[null,"KPI 12",null,null,"",null,null,null,null,null,"KPI 12",null,null,""]
[null,"KPI 13",null,null,"",null,null,null,null,null,"KPI 13",null,null,""]
[null,"KPI 14",null,null,"",null,null,null,null,null,"KPI 14",null,null,""]
[null,"KPI 15",null,null,"",null,null,null,null,null,"KPI 15",null,null,""]
[null,"KPI 16",null,null,"",null,null,null,null,null,"KPI 16",null,null,""]
[null,"KPI 17",null,null,"",null,null,null,null,null,"KPI 17",null,null,""]
[null,null,"YTD Actual","YTD Plan (Target)","Var vs Plan","Last Month Status/ Outlook","This Month Status/ Outlook","Q",null,null,null,"YTD Actual","YTD Plan (Target)","Var vs Plan","Last Month Status/ Outlook","This Month Status/ Outlook","Q"]
[]
[]
[{"$f":"'Scorecard Design'!$B$35","v":"ORGANISATIONAL DEVELOPMENT"},null,null,null,null,null,null,null,null,null,{"$f":"'Scorecard Design'!$B$36","v":"OPERATIONAL EXCELLENCE"}]
[]
[]
[]
[]
[]
[null,{"$f":"'Scorecard Design'!$B$37","v":"SHAREHOLDER VALUE"}]
[]
[]
[null,null,"YTD Actual","YTD Plan (Target)","Var vs Plan","Last Month Status/ Outlook","This Month Status/ Outlook","Q"]
[null,"KPI 1",null,null,{"$f":"IFERROR(C56/D56-1,\"\")","v":""}]
[null,"KPI 2",null,null,""]
[null,"KPI 3",null,null,""]
[null,"KPI 4",null,null,{"$f":"IFERROR(C59/D59-1,\"\")","v":""}]

{"sheet":"Tabular","index":3,"n_rows":67,"n_cols":19,"rows_kept":67,"cut":false,"hidden":false,"merged":[[44,2,50,2],[52,2,53,2],[55,2,59,2],[61,2,63,2],[61,0,63,0],[55,0,59,0],[52,0,53,0],[44,0,50,0],[2,0,2,18],[0,0,0,18],[17,2,27,2],[29,2,35,2],[37,2,42,2],[37,0,42,0],[29,0,35,0],[17,0,27,0],[6,2,13,2],[6,0,13,0]]}
["Typical corporate report for ABCcompany"]
[]
["Key Business Results"]
[]
[null,null,"Definition",null,"Key Performance Indicators",null,"Monthly Actual","Monthly Plan/Target","Variance","3 Month Trend",null,"YTD Actual","YTD Plan","YTD Variance",null,"Full Year Plan","Forecast",null,"Issue Analysis"]
[]
["Focus Areas",null,"Key business results",null,"New business premium (£'m)",null,100,100,-0.38760596363636363,"t",null,100,100,-0.28242052732467521,null,100,100,null,"Y"]
[null,null,null,null,"New business value (£'m)",null,100,100,-0.41111111111111109,"t",null,100,100,-0.58253968253968258,null,100,100]
[null,null,null,null,"Expenses (£'m)",null,100,100,0.30803960396039537,"st",null,100,100,0.18349080622347944,null,100,100]
[null,null,null,null,"Monthly NBP run rate required to meet forecast (£'m)",null,100,100,-0.20461126174545452,"t",null,100,100,null,null,100,100]
[null,null,null,null,"Monthly exps run rate required to meet forecast (£'m)",null,100,100,0.11431287128712879,"s",null,100,100,null,null,100,100]
[null,null,null,null,"Embedded value earnings (£'m)",null,100,100,null,null,null,100,100,null,null,100,100]
[null,null,null,null,"IAS before underpinning (£'m)",null,100,100,null,null,null,100,100,null,null,100,100]
[null,null,null,null,"IAS after underpinning (£'m)",null,100,100,null,null,null,100,100,null,null,100,100]
[]
["Achieved by:"]
[]
["CSF 1",null,{"$f":"'Scorecard Design'!B23","v":"To develop our customer proposition"},null,"New business premium (£'m)",null,100,100,-0.38760596363636363,"t",null,100,100,-0.28242052732467521,null,100,100,null,"Y"]
[null,null,null,null," Annual management charge (£'m)",null,100,100,4.905777777777824E-3,"st",null,100,100,-2.6443936507936385E-2,null,100,100]
[null,null,null,null,"12 month rolling performance relative to benchmark",null,100,100,null,null,null,100,100,-2.215E-2,null,100,100]
[null,null,null,null,"%age of business (NBP) in target market",null,100,100,null,"s",null,100,100,null,null,100,100]
[null,null,null,null,"Net policies movement (increase/decrease)",null,100,100,-0.62810999563509395,"t",null,100,100,-0.54654860634782088,null,100,100]
[null,null,null,null,"Persistency rates",null,100,100,null,null,null,100,100,null,null,100,100]
[null,null,null,null,"Monies retained at maturity (£'m)",null,100,100,-0.22169714285714284,"t",null,100,100,0.2380831142857143,null,100,100]
[null,null,null,null,"Group risk sales (£'m)",null,100,100,1.6233000477611941,"s",null,100,100,0.28765839317697223,null,100,100]
[null,null,null,null,"Product x sales",null,100,100,-0.95487999999999984,"t",null,100,100,-0.76805482625482624,null,100,100]
[null,null,null,null,"Product enhancement/development milestones hit",null,100,100,1,"st",null,100,100,-0.25,null,100,100]
[null,null,null,null,"CSF 1 milestones delivered",null,100,100,0,"st",null,100,100,-0.52941176470588236,null,100,100]
[]
["CSF 2",null,{"$f":"'Scorecard Design'!B24","v":"To expand distribution and improve its effectiveness"},null,"Consolidated NBV %age of top 10 distributors",null,null,null,null,null,null,100,100,0.19595504667506253,null,100,100]
[null,null,null,null,"New business value (£'m)",null,null,null,-0.41111111111111109,"t",null,100,100,-0.58253968253968258,null,100,100]
[null,null,null,null,"New distributor sales(£'m)",null,null,null,-0.11920000000000006,"t",null,100,100,-0.39212457142857149,null,100,100]
[null,null,null,null,"Distribution expenses %age of NBP",null,null,null,-0.87420125356224621,"t",null,100,100,-0.27762556304998404,null,100,100]
[null,null,null,null,"Special offer spend (£'000's)",null,null,null,1,"s",null,100,100,0.32382485714285708,null,100,100]
[null,null,null,null,"Agent bad debts/commission fraud costs.",null,null,null,-8.1547999999999998,"t",null,100,100,-57.078542857142857,null,100,100]
[null,null,null,null,"CSF 2 milestones delivered",null,null,null,0,"st",null,100,100,-0.33333333333333331,null,100,100]
[]
["CSF 3",null,{"$f":"'Scorecard Design'!B25","v":"To improve our standards of customer service"},null,"Transaction volumes",null,null,null,null,"t",null,100,100,null,null,100,100]
[null,null,null,null,"Pipeline value as %age of months NBP",null,null,null,null,"s",null,100,100,null,null,100,100]
[null,null,null,null,"Policies issued within turnaround standard",null,null,null,-0.15555555555555556,"s",null,100,100,-0.17499999999999999,null,100,100,null,"Y"]
[null,null,null,null,"%age of applications received \"clean\"",null,null,null,null,"t",null,100,100,null,null,100,100,null,"Y"]
[null,null,null,null,"Ex-gratia payments (£'000's)",null,null,null,7.1333333333333337,"s",null,100,100,-0.56190476190476191,null,100,100]
[null,null,null,null,"CSF 3 milestones delivered",null,null,null,0,"st",null,100,100,-0.5,null,100,100]
[]
["CSF4",null,{"$f":"'Scorecard Design'!B26","v":"To manage risk and improve quality"},null,"Outstanding audit points",null,null,null,null,"s",null,100,100,null,null,100,100]
[null,null,null,null,"Reported regulatory breaches",null,null,null,null,"s",null,100,100,null,null,100,100]
[null,null,null,null,"Risk reviews",null,null,null,0,"s",null,100,100,0,null,100,100]
[null,null,null,null,"Complaints",null,null,null,null,"t",null,100,100,null,null,100,100]
[null,null,null,null,"Agent debt",null,null,null,null,"st",null,100,100,null,null,100,100]
[null,null,null,null,"Admitted business as %age of total sales",null,null,null,null,"s",null,100,100,null,null,100,100]
[null,null,null,null,"CSF 4 milestones delivered",null,null,null,0,"st",null,100,100,-0.33333333333333331,null,100,100]
[]
["CSF 5",null,{"$f":"'Scorecard Design'!B27","v":"To simplify the business"},null,"Headcount",null,null,null,3.7537537537537538E-2,"s",null,100,100,3.7537537537537538E-2,null,100,100]
[null,null,null,null,"CSF 5 milestones delivered",null,null,null,0,"s",null,100,100,-0.4,null,100,100]
[]
["CSF6",null,{"$f":"'Scorecard Design'!B28","v":"To improve cost competitiveness"},null," Solvency : capital adequacy ratio",null,null,null,0,"s",null,100,100,null,null,100,100]
[null,null,null,null,"Expenses against NBP",null,null,null,-0.12992673826222922,"t",null,100,100,-0.13786587391130342,null,100,100]
[null,null,null,null,"YTD expenses (£'m)",null,null,null,0.30803960396039537,"st",null,100,100,0.18349080622347944,null,100,100]
[null,null,null,null,"Unit Cost (£ per policy)",null,null,null,3.6424934227670215E-2,"s",null,100,100,null,null,100,100]
[null,null,null,null,"CSF 6 milestones delivered",null,null,null,0,"s",null,100,100,-0.33333333333333331,null,100,100]
[]
["CSF7",null,{"$f":"'Scorecard Design'!B29","v":"Develop our organisation, our people and our culture"},null,"Staff turnover",null,null,null,0.90976765170313556,"st",null,100,100,0.81665310641665001,null,100,100]
[null,null,null,null,"Absenteeism",null,null,null,3.703703703703707E-2,"st",null,100,100,9.0316561280530033E-2,null,100,100]
[null,null,null,null,"CSF 7 milestones delivered",null,null,null,-0.66666666666666663,"s",null,100,100,-0.52173913043478259,null,100,100]
[]
[null,null,"> 10 % below target",null,"<=10% below target",null,"On target or above target"]
[null,"t","3 month trend decline","st","3 month trend static","s","3 month trend improvement"]