{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Title Page","Tab A ","Tab B","Tab C","Tab D","Tab F"],"not_rendered":{"charts":0,"images":17,"macros":1,"external_links":0,"defined_names":4,"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":"Title Page","index":0,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Tab A ","index":1,"n_rows":41,"n_cols":30,"rows_kept":41,"cut":false,"hidden":false,"merged":[[3,24,3,27],[2,4,2,8],[2,11,2,15],[2,18,2,22]]}
[]
[]
[null,null,null,null,"Service Department Sales And Gross  (Labor Only)              page  A-3",null,null,null,null,null,null,"Parts To Labor Ratios      page A-5",null,null,null,null,null,null,"Service Department Profit Centering     pageA-7"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Fixed Absorption     page A-9"]
[null,null,null,null,"Category","Sales","Gross","Gross as % of Sales","%Sales Contribution",null,null,null,"Category","Parts Sales","Labor Sales","P/L Ratio",null,null,null,"Expense Category","Dollar Amount",null,null,null,null,null,null,null,null,"% Adj Ovhd Exp"]
[null,null,null,null,"Customer Car",null,null,{"$f":"IF(ISERROR(G6/F6),\"0%\",(G6/F6))","v":"0%"},{"$f":"IF(ISERROR(F6/$F$14),\"0%\",(F6/$F$14))","v":"0%"},null,null,null,"Customer Car",null,{"$f":"F6","v":0},{"$f":"IF(ISERROR(N6/O6),\"0.00\",(N6/O6))","v":"0.00"},null,null,null,"Department Gross",null,"% of Gross","Profile",null,null,null,"Parts Department Total Gross",null,null,{"$f":"IF(ISERROR(AB6/$AB$23),\"0.00%\",AB6/$AB$23)","v":"0.00%"}]
[null,null,null,null,"Customer Truck",null,null,{"$f":"IF(ISERROR(G7/F7),\"0%\",(G7/F7))","v":"0%"},{"$f":"IF(ISERROR(F7/$F$14),\"0%\",(F7/$F$14))","v":"0%"},null,null,null,"Customer Truck",null,0,{"$f":"IF(ISERROR(N7/O7),\"0.00\",(N7/O7))","v":"0.00"},null,null,null,"Variable Expense",null,{"$f":"IF(ISERROR(U7/$U$6),\"0.00%\",U7/$U$6)","v":"0.00%"}]
[null,null,null,null,"Customer Other",null,null,"0%","0%",null,null,null,"Customer Other",null,0,"0.00",null,null,null,"Selling Expense",null,"0.00%",null,null,null,null,"Service Department Total Gross",null,null,{"$f":"IF(ISERROR(AB8/$AB$23),\"0.00%\",AB8/$AB$23)","v":"0.00%"}]
[null,null,null,null,"Warranty",null,null,"0%","0%",null,null,null,"Warranty",null,0,"0.00",null,null,null,"Personnel Expense",null,"0.00%"]
[null,null,null,null,"Warranty Other",null,null,"0%","0%",null,null,null,"Warranty Other",null,0,"0.00",null,null,null,"Semi-Fixed Expense",null,"0.00%",null,null,null,null,"Body Shop Department Total Gross",null,null,{"$f":"IF(ISERROR(AB10/$AB$23),\"0.00%\",AB10/$AB$23)","v":"0.00%"}]
[null,null,null,null,"Internal",null,null,"0%","0%",null,null,null,"Internal ",null,0,"0.00",null,null,null,"Fixed Expense",null,"0.00%"]
[null,null,null,null,"NVI / Road Ready",null,null,"0%","0%",null,null,null,"Total",{"$f":"SUM(N6:N11)","v":0},{"$f":"SUM(O6:O11)","v":0},"0.00",null,null,null,"Unallocated Expense",null,"0.00%",null,null,null,null,"Total Fixed Gross Profit ",{"$f":"AB6+AB8+AB10","v":0}]
[null,null,null,null,"Adj. Cost Of Labor",null,null,"0%","0%",null,null,null,null,null,null,null,null,null,null,"Dealer's Salary",null,"0.00%"]
[null,null,null,null,"Total",{"$f":"SUM(F6:F13)","v":0},{"$f":"SUM(G6:G13)","v":0},{"$f":"IF(ISERROR(G14/F14),\"0.00%\",G14/F14)","v":"0.00%"},{"$f":"SUM(I6:I13)","v":0},null,null,null,null,null,null,null,null,null,null,"Total Expenses",{"$f":"SUM(U7:U13)","v":0},"0.00%"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Net Profit",{"$f":"U6-U14","v":0},"0.00%",null,null,null,null,"Total Dealership Expense"]
[]
[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,null,"New & Used Sales Commission Expense     -"]
[null,null,null,null,null,null,null,null,null,null,null,null,"The Picture"]
[null,null,null,null,null,null,null,null,null,null,null,null,"Customer Pay Gross Profit %",null,{"$f":"G21","v":"0.00%"},null,null,null,null,null,null,null,null,null,null,null,"New & Used Policy Expense                         - "]
[null,null,null,null,"The Picture"]
[null,null,null,null,"Customer Pay Gross Profit %",null,{"$f":"IF(ISERROR((G6+G7+G8)/(F6+F7+F8)),\"0.00%\",(G6+G7+G8)/(F6+F7+F8))","v":"0.00%"},null,null,null,null,null,"Total Service Dept. G.P. %",null,{"$f":"H14","v":"0.00%"},null,null,null,null,"The Picture",null,null,null,null,null,null,"New & Used Get Ready / Delivery Expense  -"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Customer Pay Gross Profit %",null,{"$f":"O19","v":"0.00%"}]
[null,null,null,null,"Total Service Dept. G.P. %",null,{"$f":"H14","v":"0.00%"},null,null,null,null,null,"Parts / Labor Ratio (Cust. Pay Only) ",null,{"$f":"IF(ISERROR((N6+N7+N8)/(O6+O7+O8)),\"0.00\",(N6+N7+N8)/(O6+O7+O8))","v":"0.00"},null,null,null,null,null,null,null,null,null,null,null,"Adjusted Overhead Expense",{"$f":"AB15-AB17-AB19-AB21","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total Service Dept. G.P. %",null,{"$f":"O21","v":"0.00%"}]
[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,null,"Total Fixed Gross Profit ",{"$f":"AB12","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Parts / Labor Ratio (Cust. Pay Only) ",null,{"$f":"O23","v":"0.00"}]
[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,null,"Adjusted Overhead Expense",{"$f":"AB23","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total Service Dept. Expenses",null,{"$f":"U14","v":0}]
[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,null,"Total Absorption Percentage",{"$f":"IF(ISERROR(AB25/AB27),\"0.00%\",(AB25/AB27))","v":"0.00%"},null,"Guideline 75%"]
[]
[]
[]
[]
[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,null,"The Picture"]
[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,null,"Customer Pay Gross Profit %",{"$f":"G21","v":"0.00%"}]
[]
[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,null,"Total Service Dept. G.P. %",{"$f":"O21","v":"0.00%"}]
[]
[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,null,"Parts / Labor Ratio (Cust. Pay Only) ",{"$f":"O23","v":"0.00"}]
[]
[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,null,"Total Service Dept. Expenses",{"$f":"V28","v":0}]

{"sheet":"Tab B","index":2,"n_rows":44,"n_cols":47,"rows_kept":44,"cut":false,"hidden":false,"merged":[[43,3,43,4],[37,3,37,4],[39,3,39,4],[5,20,5,28],[5,30,5,36],[5,38,5,45],[18,38,18,40],[41,3,41,4],[4,3,4,9],[25,3,25,10],[33,3,33,4],[35,3,35,4],[5,13,5,18],[17,13,17,18]]}
[]
[]
[]
[]
[null,null,null,"NADA ACTUAL SERVICE ANALYSIS     page B-1",null,null,null,null,null,null,null,null,null,null,null,null,"page B-3",null,null,null,null,null,null,null,null,null,null,"page B-5",null,null,null,null,null,null,null,"page B-7",null,null,null,null,null,null,null,null,"page B-9"]
[null,null,null,"Performance",null,null,null,null,null,null,null,null,null,"FACILITY POTENTIAL",null,null,null,null,null,null,"NADA \"QUICK\" SERVICE  ANALYSIS",null,null,null,null,null,null,null,null,null,"OWNER BASE POTENTIAL",null,null,null,null,null,null,null,"PROFIT ON LABOR SALES"]
[null,null,null,null,"Labor Sales / Month",null,"Hourly Labor Rate",null,"Hours Billed",null,null,null,null,"Number of Bays"]
[null,null,null,"Customer Car*",{"$f":"'Tab A '!F6","v":0},"÷",null,"=",{"$f":"IF(ISERROR(E8/G8),\"0.00\",E8/G8)","v":"0.00"},null,null,null,null,null,"x",null,null,null,null,null,null,null,{"$f":"'Tab A '!F14","v":0},null,null,null,{"$f":"W8","v":0},null,null,null,null,null,"x",null,"=",{"$f":"AF8*AH8","v":0}]
[null,null,null,"Customer Truck*",{"$f":"'Tab A '!F7","v":0},"÷",null,"=","0.00",null,null,null,null,"Number of Days",null,null,null,null,null,null,null,null,"Labor Sales",null,null,null,"Labor Sales",null,null,null,null,"5 Year Owner Base ",null,"Annual Hours Purchased",null,"Market Potential / Hours",null,null,null,null,null,null,"Total Expenses"]
[null,null,null,"Customer Other*",{"$f":"'Tab A '!F8","v":0},"÷",null,"=","0.00",null,null,null,null,null,"x",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,"divided by",null,"divided by",null,"divided by"]
[null,null,null,"Warranty",{"$f":"'Tab A '!F9+'Tab A '!F10","v":0},"÷",null,"=","0.00",null,null,null,null,"Number of Hours",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"AJ8","v":0},"x",{"$f":"W14","v":"0.00"},"=",{"$f":"AF11*AH11","v":0},null,null,null,null,{"$f":"'Tab A '!H14","v":"0.00%"},null,{"$f":"IF(AO11=\"0.00%\",\"0.00%\",AO11-0.1)","v":"0.00%"},null,{"$f":"IF(AO11=\"0.00%\",\"0.00%\",AO11-0.2)","v":"0.00%"}]
[null,null,null,"Internal",{"$f":"'Tab A '!F11","v":0},"÷",null,"=","0.00",null,null,null,null,null,"x",null,null,null,null,null,null,null,"Divided by Hours Billed",null,null,null,"-Labor Gross",null,null,null,null,"Market Potentail/ Hours",null,"Effective Labor Rate",null,"5 Yr. O.B Sales Potential",null,null,null,null,"Current Labor Gross Profit %",null,"Current Labor Gross Profit % Minus 10",null,"Current Labor Gross Profit % Minus 20"]
[null,null,null,"New Vehicle Prep",{"$f":"'Tab A '!F12","v":0},"÷",null,"=","0.00",null,null,null,null,"Effective Labor Rate"]
[null,null,null,"Total",{"$f":"SUM(E8:E13)","v":0},null,null,null,{"$f":"SUM(I8:I13)","v":0},null,null,null,null,null,"equals",null,null,null,null,null,null,null,{"$f":"IF(ISERROR(W8/W11),\"0.00\", (W8/W11))","v":"0.00"},null,null,null,{"$f":"AA8-AA11","v":0},null,null,null,null,null,"x",null,"=",{"$f":"AF14*AH14","v":0},null,null,null,null,{"$f":"IF(ISERROR(AQ8/AO11),\"$0.00\",(AQ8/AO11))","v":"$0.00"},null,{"$f":"IF(ISERROR(AQ8/AQ11),\"0.00%\",(AQ8/AQ11))","v":"0.00%"},null,{"$f":"IF(ISERROR(AQ8/AS11),\"0.00%\",AQ8/AS11)","v":"0.00%"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"FACILITY POTENTIAL",null,{"$f":"O7*O9*O11*O13","v":0},null,null,null,null,null,null,"= OELR",null,null,null,"=Labor Cost",null,null,null,null,"Avg. Mos. Labor Sales (excluding internal PDI and NVI)",null,"Annualized",null,"Current Labor Sales Trend",null,null,null,null,"Sales to Breakeven",null,"Sales To Net 10%",null,"Sales To Net 20%"]
[null,null,null,"POTENTIAL"]
[null,null,null,null,{"$f":"E14","v":0},"÷",{"$f":"I14","v":0},"=",{"$f":"IF(ISERROR(E17/G17),\"$0.00\",E17/G17)","v":"$0.00"},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"AJ14","v":0},"÷",{"$f":"AJ11","v":0},"=",{"$f":"IF(ISERROR(AF17/AH17),\"0.00\"%,AF17/AH17)","v":0}]
[null,null,null,null,"Total labor sales for month",null,"Total hours billed",null,"Effective Labor Rate",null,null,null,null,"FACILITY UTILIZATION",null,null,null,null,null,null,null,null,null,null,{"$f":"AA14","v":0},null,null,null,null,null,null,"Labor Sales Trend",null,"5 Yr. O.B. Sales Potential",null,"Ouch",null,null,"The Picture"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Total Labor Sales",{"$f":"'Tab A '!F14","v":0},null,null,null,null,null,null,null,null,null,"Labor Cost",null,null,null,null,null,null,"*Note: The industry average of 35% is very poor performance.",null,null,null,null,null,null,"Customer Pay Gross Profit %",null,null,{"$f":"'Tab A '!G21","v":"0.00%"},null,"Customer Pay E.L.R.",null,null,{"$f":"G38","v":0}]
[null,null,null,null,null,"x",null,"x",null,"=",{"$f":"E20*G20*I20","v":0},null,null,null,"÷"]
[null,null,null,null,"# Service mechanical technicians",null,"# Hours/Day",null,"Working Days/Month",null,"Clock Hour Aval",null,null,"Facility Potential",{"$f":"P15","v":0},null,null,null,null,null,null,null,null,null,{"$f":"W11","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,"Total Service Dept. G.P.%",null,null,{"$f":"'Tab A '!G23","v":"0.00%"},null,"Total (overall) E.L.R.",null,null,{"$f":"G42","v":"$0.00"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"equals",null,null,null,null,null,null,null,null,null,"/ Hours Billed"]
[null,null,null,null,{"$f":"K20","v":0},"x",{"$f":"I17","v":"$0.00"},"=",{"$f":"E23*G23","v":0},null,null,null,null,"FACILITY UTILIZATION",null,{"$f":"IF(ISERROR(O19/O21),\"0.00%\",O19/O21)","v":"0.00%"},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Parts / Labor Ratio (Cust Pay Only)",null,null,{"$f":"'Tab A '!V26:V26","v":"0.00"},null,"Warranty Labor Rate",null,null,{"$f":"G40","v":0}]
[null,null,null,null,"Clock Hours Available",null,"Effective Labor Rate",null,"Labor sales potential",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(ISERROR(Y18/Y21),\"$0.00\",Y18/Y21)","v":"$0.00"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"=Real Cost",null,null,null,null,null,null,null,null,null,null,null,null,null,"Total Service Dept Expense",null,null,{"$f":"'Tab A '!$U$14","v":0},null,"Overall Tech Proficiency",null,null,{"$f":"G44","v":"0.00%"}]
[null,null,null,"How proficient are your technicians ?"]
[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,null,null,null,null,null,null,null,null,null,null,null,null,null,"Hours Per R.O (recap)",null,null,{"$f":"G34","v":0}]
[null,null,null,null,null,"÷",null,"=",{"$f":" IF(ISERROR(E28/G28),\"0.00%\",(E28/G28))","v":"0.00%"}]
[null,null,null,null,"Hours Produced",null,"Hours Available",null,"Tech Proficiency",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"÷",0.3,"=",{"$f":"W29/Y29","v":0},null,null,null,null,null,null,null,null,null,null,null,"Percent Of One Item R.O.'s",null,null,{"$f":"G36","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Real Cost",null,null,null,"E.L.R. Needed to earn 70%"]
[]
[]
[]
[null,null,null,"Hours Per RO (Recap Sheet)"]
[]
[null,null,null,"Percent of One Item R.O.'s (Recap Sheet)"]
[]
[null,null,null,"Customer Pay Effective Labor Rate (Recap Sheet)"]
[]
[null,null,null,"Warranty Labor Rate (Recap Sheet)"]
[]
[null,null,null,"Total Overall Effective Labor Rate",null,null,{"$f":"I17","v":"$0.00"}]
[]
[null,null,null,"Overall Technician Proficiency",null,null,{"$f":"I28","v":"0.00%"}]

{"sheet":"Tab C","index":3,"n_rows":53,"n_cols":54,"rows_kept":53,"cut":false,"hidden":false,"merged":[[3,3,3,12],[8,5,8,6],[14,5,14,6],[18,6,28,6],[17,19,17,20],[14,16,14,20],[9,19,9,20],[17,16,17,17],[17,22,17,23],[20,16,20,18],[43,16,43,19],[31,16,31,20],[44,19,44,20],[34,16,34,17],[36,16,36,17],[38,16,38,17],[40,16,40,17],[52,19,52,20],[46,16,46,17],[48,16,48,17],[50,16,50,17],[52,16,52,17],[46,19,46,20],[48,19,48,20],[50,19,50,20],[6,49,6,54],[7,49,7,54],[15,19,15,20],[7,29,7,45],[11,30,11,32],[11,36,11,38],[11,43,11,45],[8,16,8,24],[11,16,11,17],[11,22,11,23],[9,22,9,23],[11,19,11,20]]}
[]
[]
[]
[null,null,null,"Technician Value   page C-7"]
[]
[null,null,null,"Calculate using daily available hours per technician",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,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"page C-32"]
[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,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Exercise to See What Happens When You "]
[null,null,null,null,"Hours",null,null,"Days",null,"Labor Rate",null,"Sales Value",null,null,null,null,"STAFFING REQUIREMENTS",null,null,null,"page C-9",null,null,null,null,null,null,null,null,"Service Advisor Performance",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Increase Your Hours Per Repair Order"]
[null,null,null,null,null,"X",null,null,"X",{"$f":"'Tab B'!I17","v":"$0.00"},"=",{"$f":"E9*H9*J9","v":0},null,null,null,"A.","Sales To Break Even"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total Expenses for One Month",null,"¸","Current Gross Profit Percent",null,"=","Sales To Break Even",null,null,null,null,null,null,null,"How To Set Advisor Sales Objectives To:  Beak Even,  Net 10%, &  Net 20%",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Number of customer R.O.'s for the month"]
[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,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,null,"x"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"¸",{"$f":"'Tab B'!AO11","v":"0.00%"},null,"=",{"$f":"IF(ISERROR(Q12/T12),\"$0.00\",Q12/T12)","v":"$0.00"},null,null,null,null,null,null,null,"Break Even",null,null,null,null,null,"Net 10 %",null,null,null,null,null,null,"Net 20 %",null,null,null,null,null,null,"Multiply by .3 hours",null,null,"0.3 hours"]
[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,null,null,null,null,null,null,null,null,"           "]
[null,null,null,null,"Sales Value",null,null,"Gross Margin",null,"Profit Value",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,1,"Department's Average Monthly Expenses (Including unallocated)",null,null,null,null,1,"Department's Average Monthly Expenses (Including unallocated)",null,{"$f":"AG14","v":0},null,null,null,1,"Department's Average Monthly Expenses (Including unallocated)",null,{"$f":"AM14","v":0},null,null,null,null,"Additional customer labor hours generated","=",null,{"$f":"BB10*0.3","v":0}]
[null,null,null,null,null,"X",null,{"$f":"'Tab A '!H14","v":"0.00%"},"=",{"$f":"E15*H15","v":0},null,null,null,null,null,"B.","Sales To Generate 20% Net",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,null,"¸",null,null,null,null,null,null,"x"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total Expenses for One Month",null,"¸","Current Gross Profit Percent (Minus 20)",null,"=","Sales To Generate 20% Net",null,null,null,null,null,null,2,"Divide by current labor gross profit % to break even","¸",{"$f":"'Tab A '!H14","v":"0.00%"},null,null,2,"Divide by current labor gross profit % minus 10 to net 10%",null,{"$f":"IF(AG16=\"0.00%\",\"0.00%\",AG16-0.1)","v":"0.00%"},null,null,null,2,"Divide by current labor gross profit % minus 20 to net 20%",null,{"$f":"IF(AM16=\"0.00%\",\"0.00%\",AM16-0.1)","v":"0.00%"},null,null,null,null,"Multiply by Customer Labor Rate",null,null,{"$f":"'Tab B'!$AU$19","v":0}]
[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,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,null,null,null,null,null,null,null,null,null,"¸",{"$f":"IF(T12=\"0.00%\",\"0.00%\",T12-0.2)","v":"0.00%"},null,"=",{"$f":"IF(ISERROR(Q18/T18),\"$0.00\",Q18/T18)","v":"$0.00"},null,null,null,null,null,null,3,"Equals New Sales Objective",null,{"$f":"IF(ISERROR(AG14/AG16),\"$0.00\",AG14/AG16)","v":"$0.00"},null,null,3,"Equals New Sales Objective",null,{"$f":"IF(ISERROR(AM14/AM16),\"$0.00\",AM14/AM16)","v":"$0.00"},null,null,null,3,"Equals New Sales Objective",null,{"$f":"IF(ISERROR(AT14/AT16),\"$0.00\",AT14/AT16)","v":"$0.00"},null,null,null,null,"Equals additional Customer Labor Sales Generated","=",null,{"$f":"BB14*BB16","v":0}]
[null,null,null,null,{"$f":"J15","v":0},0.7,"proficiency",{"$f":"E19*0.7","v":0},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,null,null,null,"¸",null,null,null,null,null,null,"¸",null,null,null,null,null,null,"x"]
[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,null,null,null,null,4,"Number of Advisors",null,null,null,null,4,"Number of Advisors",null,{"$f":"AG20","v":0},null,null,null,4,"Number of Advisors",null,{"$f":"AM20","v":0},null,null,null,null,"Multiply by customer Labor Gross Profit %",null,null,{"$f":"'Tab A '!AB35","v":"0.00%"}]
[null,null,null,null,{"$f":"E19","v":0},0.8,null,{"$f":"E21*0.8","v":0},null,null,null,null,null,null,null,"C.","Technician Value",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,"="]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Daily Work Hours","X","Average Proficiency Rate","X","Overall Effective Labor Rate","X","Work Days Per Month","=","Technician Value",null,null,null,null,5,"Equals Sales Objective per Advisor",null,{"$f":"IF(ISERROR(AG18/AG20),\"$0.00\",AG18/AG20)","v":"$0.00"},null,null,5,"Equals Sales Objective per Advisor",null,{"$f":"IF(ISERROR(AM18/AM20),\"$0.00\",AM18/AM20)","v":"$0.00"}," ",null,null,5,"Equals Sales Objective per Advisor",null,{"$f":"IF(ISERROR(AT18/AT20),\"$0.00\",AT18/AT20)","v":"$0.00"},null,null,null,null,"Equals additional Labor Gross Profit $ generated","=","(A)",{"$f":"BB18*BB20","v":0}]
[null,null,null,null,{"$f":"E21","v":0},0.9,null,{"$f":"E23*0.9","v":0},null,null,null,null,null,null,null,null,null,null,0.8,null,{"$f":"'Tab B'!AU21","v":"$0.00"},null,null,null,{"$f":"Q23*S23*U23*W23","v":0},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,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,6,"Number of work days per month",null,null,null,null,6,"Number of work days per month",null,{"$f":"AG24","v":0},null,null,null,6,"Number of work days per month",null,{"$f":"AM24","v":0}]
[null,null,null,null,{"$f":"E23","v":0},1,null,{"$f":"E25*1","v":0},null,null,null,null,null,null,null,null,{"$f":"Q23","v":0},null,0.9,null,{"$f":"U23","v":"$0.00"},null,{"$f":"W23","v":0},null,{"$f":"Q25*S25*U25*W25","v":0},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,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,7,"Equals daily sales objective per advisor",null,{"$f":"IF(ISERROR(AG22/AG24),\"$0.00\",AG22/AG24)","v":"$0.00"},null,null,7,"Equals daily sales objective per advisor",null,{"$f":"IF(ISERROR(AM22/AM24),\"$0.00\",AM22/AM24)","v":"$0.00"},null,null,null,7,"Equals daily sales objective per advisor",null,{"$f":"IF(ISERROR(AT22/AT24),\"$0.00\",AT22/AT24)","v":"$0.00"}]
[null,null,null,null,{"$f":"E25","v":0},1.1000000000000001,null,{"$f":"E27*1.1","v":0},null,null,null,null,null,null,null,null,{"$f":"Q23","v":0},null,1,null,{"$f":"U23","v":"$0.00"},null,{"$f":"W23","v":0},null,{"$f":"Q27*S27*U27*W27","v":0},null,null,null,null,null,null,"¸",null,null,null,null,null,"¸",null,null,null,null,null,null,"¸",null,null,null,null,null,"Divide Parts Sales R.O. by Labor Sales R.O. to calculate $ parts sales per 1$ of Labor Sales","=",null,{"$f":"'Tab A '!AB39","v":"0.00"}]
[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,null,null,null,null,8,"Current overall effective labor rate",null,{"$f":"'Tab B'!$AU$21","v":"$0.00"},null,null,8,"Current overall effective labor rate",null,{"$f":"AG28","v":"$0.00"},null,null,null,8,"Current overall effective labor rate",null,{"$f":"AM28","v":"$0.00"}]
[null,null,null,null,{"$f":"E27","v":0},1.2,null,{"$f":"E29*1.2","v":0},null,null,null,null,null,null,null,null,{"$f":"Q23","v":0},null,1.2,null,{"$f":"U23","v":"$0.00"},null,{"$f":"W23","v":0},null,{"$f":"Q29*S29*U29*W29","v":0},null,null,null,null,null,null,"=",null,null,null,null,null,"=",null,null,null,null,null,null,"=",null,null,null,null,null,null,"x"]
[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,null,null,null,null,9,"Equals daily sales objective per advisor (FRH's)",null,{"$f":"IF(ISERROR(AG26/AG28),\"$0.00\",AG26/AG28)","v":"$0.00"},null,null,9,"Equals daily sales objective per advisor (FRH's)",null,{"$f":"IF(ISERROR(AM26/AM28),\"$0.00\",AM26/AM28)","v":"$0.00"},null,null,null,9,"Equals daily sales objective per advisor (FRH's)",null,{"$f":"IF(ISERROR(AT26/AT28),\"$0.00\",AT26/AT28)","v":"$0.00"},null,null,null,null,"Multiply by Customer Labor Sales",null,null,{"$f":"BB18","v":0}]
[null,null,null,null,"Profit Value",null,null,"Adjusted Profit Value"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"D.","Staffing To Break Even",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,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,"Sales To Break Even",null,"¸",null,"Technician Value",null,"=",null,"Staffing"]
[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,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,"Equals additional Customer Parts Sales generated",null,null,{"$f":"BB27*BB30","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"W12","v":"$0.00"},null,"¸",null,{"$f":"Y23","v":0},"@ 80%","=",null,{"$f":"IF(ISERROR(Q35/U35),\"0.0\",Q35/U35)","v":"0.0"},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,null,"x"]
[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,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,"Multiply  by  Customer Parts Sales Gross Profit %"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Q35","v":"$0.00"},null,"¸",null,{"$f":"Y25","v":0},"@ 90%","=",null,{"$f":"IF(ISERROR(Q37/U37),\"0.0\",Q37/U37)","v":"0.0"}]
[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,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,"Equals additional Parts Gross Profit $ Generated","=","(B)",{"$f":"BB34*BB36","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Q37","v":"$0.00"},null,"¸",null,{"$f":"Y27","v":0},"@ 100%","=",null,{"$f":"IF(ISERROR(Q39/U39),\"0.0\",Q39/U39)","v":"0.0"}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Q39","v":"$0.00"},null,"¸",null,{"$f":"Y29","v":0},"@ 120%","=",null,{"$f":"IF(ISERROR(Q41/U41),\"0.0\",Q41/U41)","v":"0.0"},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,"Add Gross Profit from Labor (A) and Parts (B)","=",null,{"$f":"BB38+BB22","v":0}]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"E.","Staffing To Generate 20% Net"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Sales To Generate 20% Net",null,"¸","Technician Value",null,null,"=",null,"Staffing"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"W18","v":"$0.00"},null,"¸",{"$f":"Y23","v":0},null,"@ 80%","=",null,{"$f":"IF(ISERROR(Q47/T47),\"0.0\",Q47/T47)","v":"0.0"}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Q47","v":"$0.00"},null,"¸",{"$f":"Y25","v":0},null,"@ 90%","=",null,{"$f":"IF(ISERROR(Q49/T49),\"0.0\",Q49/T49)","v":"0.0"}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Q49","v":"$0.00"},null,"¸",{"$f":"Y27","v":0},null,"@ 100%","=",null,{"$f":"IF(ISERROR(Q51/T51),\"0.0\",Q51/T51)","v":"0.0"}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Q51","v":"$0.00"},null,"¸",{"$f":"Y29","v":0},null,"@ 120%","=",null,{"$f":"IF(ISERROR(Q53/T53),\"0.0\",Q53/T53)","v":"0.0"}]

{"sheet":"Tab D","index":4,"n_rows":35,"n_cols":25,"rows_kept":35,"cut":false,"hidden":false,"merged":[[1,3,1,11],[3,4,3,8],[5,4,5,5],[27,4,27,5],[1,16,1,25],[2,17,2,25],[3,17,3,21],[27,19,27,20],[27,22,27,23]]}
[]
[null,null,null,"Labor Rate Calculations",null,null,null,null,null,null,null,null,null,null,null,null,"Calculating Mark-Up"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,3,"Using the following formula, mark-up a part costing $6.72 to attain a 35%"]
[null,null,null,1,"Calculate the Labor Rate for the following operation.",null,null,null,null,null,null,null,null,null,null,null,null,"gross profit ( round to the nearest cent)"]
[]
[null,null,null,null,"A/C Charge and Check",null,null,null,null,null,null,null,null,null,null,null,null,1,null,null,null,null,"=",null,{"$f":"(IF(ISBLANK(V6),\"0.00\",(R6/(R6-V6))))","v":"0.00"}]
[null,null,null,null,null,null,"Labor Price",120,null,null,null,null,null,null,null,null,null,1,null,null,null,"Desired Gross Profit percent ",null,null,"Mark-Up Factor"]
[null,null,null,null,null,null,"Units",1]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"X",null,{"$f":"Y6","v":"0.00"},"=",null,{"$f":"V9*R9","v":0}]
[null,null,null,null,null,"¸",null,"=",{"$f":"IF(ISERROR(E10/G10),\"$0.00\",E10/G10)","v":"$0.00"},null,null,null,null,null,null,null,null,"Part Cost",null,null,null,"Mark-Up Factor",null,null,"Retail Price"]
[null,null,null,null,"Price",null,"Units",null,"Labor Rate"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,4,"Calculate the \"Weighted Average\" price at a 40% Gross Profit for the following "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"parts (round to the nearest cent)"]
[]
[null,null,null,2,"Calculate the Effective Labor Rate for the following \"Repair\" operations.",null,null,null,null,null,null,null,null,null,null,null,null,"Item",null,"Cost",null,"Annual Turnover",null,null,"Total Cost"]
[]
[null,null,null,null,"Labor Operations",null,"Labor Price",null,"Labor Units",null,"Labor Rate",null,null,null,null,null,null,"Filter #1",null,4.3600000000000003,"X",112,"="]
[null,null,null,null,"Clean Fuel Injectors",null,117.6,"¸",1.2,"="]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Filter #2",null,4.01,"X",56,"="]
[null,null,null,null,"R&R Rear Hub Bearing.",null,96,"¸",0.8,"="]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Filter #3",null,3.56,"X",85,"="]
[null,null,null,null,"Replace Trans. Pan gasket",null,107.8,"¸",1.1000000000000001,"=",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,null,null,null,null,null,null,"Filter #4",null,3.86,"X",202,"="]
[null,null,null,null,"R&R Headlight unit (1)",null,108,"¸",0.9,"="]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Filter #5",null,3.51,"X",36,"="]
[]
[null,null,null,null,"Total Price",null,null,"Total Units",null,null,null,null,null,null,null,null,null,null,null,"Total Items",null,{"$f":"V18+V20+V22+V24+V26","v":491},"Total Cost",null,{"$f":"Y18+Y20+Y22+Y24+Y26","v":0}]
[]
[null,null,null,null,null,null,null,"¸",null,"=",{"$f":"IF(ISERROR(G30/I30),\"$0.00\",G30/I30)","v":"$0.00"}]
[null,null,null,null,null,null,"Total Price",null,"Total Units",null,"Effective Labor Rate ",null,null,null,null,null,null,{"$f":"Y28","v":0},"¸",{"$f":"V28","v":491},"=",{"$f":"R31/T31","v":0}]
[null,null,null,null,null,null,null,null,null,null,"(For This R.O.)",null,null,null,null,null,null,"Total Cost",null,"Total Items",null,"Weighted Average Cost"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"V31","v":0},"X",null,"=",{"$f":"R34*T34","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Weighted Average Cost",null,"Mark-Up Factor",null,"Weighted Average Price"]

{"sheet":"Tab F","index":5,"n_rows":39,"n_cols":7,"rows_kept":39,"cut":false,"hidden":false,"merged":[[4,3,4,6],[17,3,18,3],[29,3,30,3]]}
[]
[]
[]
[]
[null,null,null,"Cost Of A Come-Back"]
[]
[null,null,null,"Lost Customers"]
[]
[null,null,null,"Average Hours per R.O.","X"]
[]
[null,null,null,null,"=",{"$f":"F7*F9","v":0}]
[]
[null,null,null,"Effective Labor Rate","X",{"$f":"'Tab B'!AU21","v":"$0.00"}]
[]
[null,null,null,null,"=",{"$f":"F11*F13","v":0},"(A) Service Labor Sales"]
[]
[]
[null,null,null,"Service Department Gross Profit % (Excluding Sublet)","X",{"$f":"'Tab A '!G23","v":"0.00%"}]
[]
[]
[null,null,null,null,"=",{"$f":"F15*F18","v":0},"(B) Service Labor Gross"]
[]
[]
[null,null,null,"Service Labor Sales (A)",null,{"$f":"F15","v":0}]
[]
[null,null,null,"Parts / Labor Ratio","X",{"$f":"'Tab A '!P6","v":"0.00"}]
[]
[null,null,null,null,"=",{"$f":"F24*F26","v":0}]
[]
[null,null,null,"Parts Dept Gross Profit % R.O.Sales","X"]
[]
[null,null,null,null,"=",{"$f":"F28*F30","v":0},"(C) Service Parts Gross"]
[]
[null,null,null,null,null,null," "]
[null,null,null,"(B) Service Labor Gross",null,{"$f":"F21","v":0}]
[]
[null,null,null,"(C) Service Parts Gross","+",{"$f":"F32","v":0}]
[]
[null,null,null,"Lost Gross","=",{"$f":"F35+F37","v":0}]