{"text_format":"1","workbook":"xlsx","n_sheets":12,"sheets":["Cover","Dealership name","Labour rates","Labour Sales","Productive employees","Non Productive Employees","Facility","Relative Size","Labour Gross","Sublet","Calculations","Report"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":2,"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":"Cover","index":0,"n_rows":33,"n_cols":4,"rows_kept":33,"cut":false,"hidden":false,"merged":[[6,1,6,8]]}
[]
[]
[]
[]
[]
[]
[null,"GRANT THORNTON SERVICE ANALYSIS TOOL"]
[]
[null,null,"Welcome to Grant Thornton's Service Analysis Tool. In 8 quick steps"]
[null,null,"you will be able to utilise this tool to understand the following key "]
[null,null,"aspects of your service department:"]
[]
[null,null,"1.","What is my capacity based on my current technician numbers?"]
[]
[null,null,"2.","How much additional gross margin can I achieve through operating "]
[null,null,null,"at the benchmark Performance Index?"]
[]
[null,null,"3.","Do I have the correct number of non-productive employees?"]
[]
[null,null,"4.","How much of my facility am I utilising?"]
[]
[null,null,"5.","What is my average prime labour cost?"]
[]
[null,null,"6.","Based on my prime labour cost what is my target gross profit?"]
[]
[null,null,"7.","Is the size of my service deparment relative to the number of new "]
[null,null,null,"and used vehicles the dealership sells"]
[]
[null,null,"8.","Do I have the capacity for business that is currently sublet?"]
[]
[null,null,"If you have any questions on how you can utilise this tool for your "]
[null,null,"dealership or tips on how to maximise your service department  "]
[null,null,"profitability please contact a Grant Thornton Automotive retail partner."]

{"sheet":"Dealership name","index":1,"n_rows":11,"n_cols":7,"rows_kept":11,"cut":false,"hidden":false,"merged":[[6,4,6,8]]}
[]
[]
[]
[]
[]
[]
[null,"Please enter the dealership name?",null,null,"Sample Motors"]
[]
[null,"What is the period being reviewed?",null,null,"Jan","to","Jun"]
[]
[null,"Number of months being reviewed?",null,null,6]

{"sheet":"Labour rates","index":2,"n_rows":11,"n_cols":6,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,"What are the dealerships average labour rates during the period?"]
[]
[null,"Retail (avg inclusive of fleet)",null,null,null,100]
[null,"Internal ",null,null,null,90]
[null,"Warranty ",null,null,null,90]

{"sheet":"Labour Sales","index":3,"n_rows":11,"n_cols":6,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,"What were the dealerships total labour sales during the period?"]
[]
[null,"Retail (avg inclusive of fleet)",null,null,null,{"$f":"35000*6","v":210000}]
[null,"Internal ",null,null,null,{"$f":"6000*6","v":36000}]
[null,"Warranty ",null,null,null,{"$f":"8500*6","v":51000}]

{"sheet":"Productive employees","index":4,"n_rows":16,"n_cols":6,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,"How many productive employees does the service department have?"]
[]
[null,"Qualified Technicians",null,null,null,4]
[null,"4th Year Apprentice",null,null,null,1]
[null,"3rd Year Apprentice",null,null,null,1]
[null,"2nd Year Apprentice",null,null,null,1]
[null,"1st Year Apprentice"]
[null,"Foreman ",null,null,null,1]
[]
[null,"What is the Foreman's average productivity percentage?",null,null,null,0.4]

{"sheet":"Non Productive Employees","index":5,"n_rows":13,"n_cols":5,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,"How many non productive employees does the service department have?"]
[]
[null,"Manager/s",null,null,1]
[null,"Service Advisors",null,null,3]
[null,"Warranty Clerk",null,null,1]
[null,"Costing Clerk"]
[null,"Other",null,null,1]

{"sheet":"Facility","index":6,"n_rows":11,"n_cols":6,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,"How big is the service facility?"]
[]
[null,"Number of bays with hoists",null,null,null,8]
[null,"Number of additional bays",null,null,null,3]
[null,"Total",null,null,null,{"$f":"F9+F10","v":11}]

{"sheet":"Relative Size","index":7,"n_rows":10,"n_cols":9,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,"What are the total vehicle sales during the period being reviewed?"]
[]
[null,"Total new vehicle sales ",null,null,null,null,null,null,132]
[null,"Total used vehicle sales (excluding wholesale)",null,null,null,null,null,null,108]

{"sheet":"Labour Gross","index":8,"n_rows":7,"n_cols":10,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,"What is the average labour gross during the period being reviewed?",null,null,null,null,null,null,0.7,"enter as a %"]

{"sheet":"Sublet","index":9,"n_rows":10,"n_cols":7,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,"What are the total sublet sales during the period being reviewed?"]
[]
[null,"Sublet sales (total for the period)",null,null,null,180000]
[null,"Sublet gross (average)",null,null,null,0.06,"enter as a %"]

{"sheet":"Calculations","index":10,"n_rows":39,"n_cols":7,"rows_kept":39,"cut":false,"hidden":true,"merged":[]}
[]
[null,"Analysing Your Service Business - These Numbers Don’t Lie"]
[]
[null,{"$f":"'Dealership name'!E7","v":"Sample Motors"}]
[]
[null,"Based on the information provided where are the key improvement opportunites in this business."]
[]
[]
[null,"Labour Rates",null,null,"Sales Mix"]
[null,null,"$ per hour (ex GST)",null,null,"$ per month","% Mix"]
[null,"Retail (avg incl fleet)",{"$f":"'Labour rates'!F9","v":100},null,"Retail",{"$f":"'Labour Sales'!F9/'Dealership name'!$E$11","v":35000},{"$f":"F11/F15","v":0.70707070707070707}]
[null,"Internal",{"$f":"'Labour rates'!F10","v":90},null,"Internal",{"$f":"'Labour Sales'!F10/'Dealership name'!$E$11","v":6000},{"$f":"F12/F15","v":0.12121212121212122}]
[null,"Warranty (avg)",{"$f":"'Labour rates'!F11","v":90},null,"Warranty",{"$f":"'Labour Sales'!F11/'Dealership name'!$E$11","v":8500},{"$f":"F13/F15","v":0.17171717171717171}]
[]
[null,"Average Rate (based on Mix)",{"$f":"(C11*G11)+(C12*G12)+(C13*G13)","v":97.070707070707073},null,"Total",{"$f":"SUM(F11:F13)","v":49500}]
[]
[]
[null,"Department Head Count","No of Employees",null,null,{"$f":"C18","v":"No of Employees"}]
[]
[null,"Qualified Technicians",{"$f":"'Non Productive Employees'!E9","v":1},{"$f":"C20","v":1},"Manager/s",{"$f":"'Non Productive Employees'!E9","v":1}]
[null,"4th Year Apprentice",{"$f":"'Non Productive Employees'!E10","v":3},{"$f":"C21","v":3},"Service Advisors",{"$f":"'Non Productive Employees'!E10","v":3}]
[null,"3rd Year Apprentice",{"$f":"'Non Productive Employees'!E11","v":1},{"$f":"C22*0.75","v":0.75},"Warranty Clerk",{"$f":"'Non Productive Employees'!E11","v":1}]
[null,"2nd Year Apprentice",{"$f":"'Non Productive Employees'!E12","v":0},{"$f":"C23*0.5","v":0},"Costing Clerk",{"$f":"'Non Productive Employees'!E12","v":0}]
[null,"1st Year Apprentice",{"$f":"'Non Productive Employees'!E13","v":1},{"$f":"C24*0.25","v":0.25},"Other",{"$f":"'Non Productive Employees'!E13","v":1}]
[null,"Foreman (add % productivity)",{"$f":"'Non Productive Employees'!E14","v":0},{"$f":"'Productive employees'!F16","v":0.4}]
[null,null,null,{"$f":"D25*C25","v":0}]
[null,"Total Chargeable (adjusted)",{"$f":"SUM(D20:D24)+D26","v":5},null,"Total Non Chargeable",{"$f":"SUM(F20:F24)","v":6}]
[]
[null,"Facility Details",null,null,"Vehicle Sales"]
[]
[null,"Number of Hoists",{"$f":"Facility!F9","v":8},null,"Total New per month",{"$f":"'Relative Size'!I9/'Dealership name'!$E$11","v":22}]
[null,"Number of Additional Bays",{"$f":"Facility!F10","v":3},null,"Total Used (excl whl)",{"$f":"'Relative Size'!I10/'Dealership name'!$E$11","v":18}]
[]
[null,"Total Bays",{"$f":"C32+C31","v":11},null,"Total Cars Sold",{"$f":"F32+F31","v":40}]
[]
[null,"Gross Profit Details",null,null,"Sublet Details"]
[]
[null,"Total Labour Gross %",{"$f":"'Labour Gross'!I7","v":0.7},null,"Monthly Sublet Sales",{"$f":"Sublet!F9/'Dealership name'!$E$11","v":30000}]
[null,"Monthly $ Labour Gross",{"$f":"F15*C38","v":34650},null,"Sublet Gross %",{"$f":"Sublet!F10","v":0.06}]

{"sheet":"Report","index":11,"n_rows":53,"n_cols":14,"rows_kept":53,"cut":false,"hidden":false,"merged":[[40,13,43,17],[25,7,28,10],[6,1,6,4],[11,13,20,17],[22,13,38,17]]}
[]
[]
[]
[]
[]
[]
[null,{"$f":"'Dealership name'!E7","v":"Sample Motors"}]
[null,"Service Analyis for the",null,{"$f":"'Dealership name'!E11","v":6},"Month"]
[null,"Period Ended ",null,{"$f":"'Dealership name'!G9","v":"Jun"}]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"The information contained herein is of a general nature and is not intended to address the circumstances of any particular individual or entity. Although we endeavour to provide accurate and timely information, there can be no guarantee that such information is accurate as of the date it is received or that it will continue to be accurate in the future. No one is entitled to rely on this information and no one should act on such information without appropriate professional advice obtained after a thorough examination of the particular situation."]
[null,null,"Productivity and Efficiency",null,"B'mark",null,null,null,"Prime Labour Cost"]
[]
[null,null,"Monthly Labour Sales per Technician",{"$f":"Calculations!F15/Calculations!C27","v":9900},16000,null,null,null,{"$f":"Report!C15","v":"Monthly Labour Sales per Technician"},{"$f":"Report!D15","v":9900}]
[null,null,"Available Monthly Sales per Technician",{"$f":"Calculations!C15*150","v":14560.60606060606},null,null,null,null,"Monthly Labour Gross per Tech",{"$f":"Calculations!C39/Calculations!C27","v":6930}]
[]
[null,null,"Performance Index",{"$f":"D15/D16","v":0.67991675338189383},1,null,null,null,"Monthly Cost of Sales",{"$f":"J15-J16","v":2970}]
[null,null,null,null,null,null,null,null,"Avg Weekly technician wage*",{"$f":"J18/4.33333","v":685.38514260395584}]
[null,null,"Average Labour Rate",{"$f":"Calculations!C15","v":97.070707070707073},null,null,null,null,"Prime Labour Cost per hour",{"$f":"J19/38","v":18.036451121156734}]
[null,null,"Average Labour Rate Recovered",{"$f":"D15/150","v":66},{"$f":"D20*0.95","v":92.217171717171709}]
[null,null,"Hours Sold per Technician per month",{"$f":"D15/D20","v":101.98751300728408},150,null,null,null,"Target GP %",{"$f":"1-(J20/Report!D20)","v":0.81419264713896811}]
[null,null,"Unrecovered Hours per Tech per month",{"$f":"(D16-D15)/D20","v":48.012486992715914},null,null,null,null,"Current GP %",{"$f":"Calculations!C38","v":0.7},null,null,null,"“Grant Thornton” refers to the brand under which the Grant Thornton member firms provide assurance, tax and advisory services to their clients and/or refers to one or more member firms, as the context requires. Grant Thornton Australia Ltd is a member firm of Grant Thornton International Ltd (GTIL). GTIL and the member firms are not a worldwide partnership. GTIL and each member firm is a separate legal entity. Services are delivered by the member firms. GTIL does not provide services to clients. GTIL and its member firms are not agents of, and do not obligate one another and are not liable for one another’s acts or omissions. In the Australian context only, the use of the term 'Grant Thornton' may refer to Grant Thornton Australia Limited ABN 41 127 556 389 and its Australian subsidiaries and related entities. GTIL is not an Australian related entity to Grant Thornton Australia Limited."]
[null,null,"Your current level of labour sales"]
[null,null,"                           could be achieved with",{"$f":"Calculations!C27-(Calculations!F15/D16)","v":1.6004162330905305},"less technicians"]
[null,null,null,null,null,null,null,"If this number does not seem correct please confirm your technician numbers.  If these are accurate you may want to look further into what makes up your labour COS and WIP accounts."]
[null,null,"Benefit to dealership if benchmark reached",{"$f":"(Calculations!C27*D16)-Calculations!F15","v":23303.030303030304},"per month"]
[null,null,null,{"$f":"D27*12","v":279636.36363636365},"per annum"]
[]
[]
[]
[]
[null,null,"Employee Numbers",null,"B'mark",null,null,null,"Relative Size",null,"B'mark"]
[]
[null,null,"Productive to Non Productive Employee Ratio",{"$f":"Calculations!C27/Calculations!F27","v":0.83333333333333337},"2 to 1",null,null,null,"Total monthly labour sales",{"$f":"Calculations!F15","v":49500}]
[null,null,"Technician to Service Advisor Ratio",{"$f":"Calculations!C27/Calculations!F21","v":1.6666666666666667},"5 to 1",null,null,null,"Retail monthly labour sales",{"$f":"Calculations!F11","v":35000}]
[null,null,null,null,null,null,null,null,"Number of Cars Sold",{"$f":"Calculations!F34","v":40}]
[]
[null,null,null,null,null,null,null,null,"Total Labour sold per vehicle",{"$f":"J35/J37","v":1237.5},1500]
[null,null,null,null,null,null,null,null,"Total retail Labour sold per vehicle",{"$f":"J36/J37","v":875},1000]
[null,null,"Facility",null,null,null,null,null,null,null,null,null,null,"Liability limited by a scheme approved under Professional Standards Legislation. Liability is limited in those States where a current scheme applies."]
[]
[null,null,{"$f":"Calculations!B31","v":"Number of Hoists"},{"$f":"Calculations!C31","v":8}]
[null,null,{"$f":"Calculations!B32","v":"Number of Additional Bays"},{"$f":"Calculations!C32","v":3}]
[null,null,{"$f":"Calculations!B34","v":"Total Bays"},{"$f":"Calculations!C34","v":11},null,null,null,null,"Sublet Analysis"]
[]
[null,null,"% of Bays With Hoists",{"$f":"D43/D45","v":0.72727272727272729},"Min 60%",null,null,null,"Current monthly sublet sales",{"$f":"Calculations!F38","v":30000}]
[null,null,null,null,null,null,null,null,"Current monthly sublet COS",{"$f":"J47*(1-Calculations!F39)","v":28200}]
[null,null,"Available Labour Throughput",{"$f":"D45*Report!D16","v":160166.66666666666}]
[null,null,"Current Sales",{"$f":"Report!J35","v":49500},null,null,null,null,"Equivalent hours if bought back in house*",{"$f":"(J48/Calculations!C15)*0.6","v":174.30593132154004}]
[null,null,null,null,null,null,null,null,"Current unrecovered Hours",{"$f":"Report!D23*Calculations!C27","v":240.06243496357956}]
[null,null,"Facility Utilisation",{"$f":"D50/D49","v":0.30905306971904267}]
[null,null,null,null,null,null,null,null,"*assume 40% of sublet cost is parts"]