{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Justification ","Life Cycle Cost Analysis","Maintenance Estimate","Insurance Rates","Loan Payment Schedule"],"not_rendered":{"charts":1,"images":3,"macros":1,"external_links":0,"defined_names":72,"comments":4,"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":"Justification ","index":0,"n_rows":45,"n_cols":17,"rows_kept":45,"cut":false,"hidden":false,"merged":[[0,1,0,10],[1,1,1,10]]}
[null,"DFMS Vehicle Justification Calculator"]
[null,"A Vehicle Lifecycle Cost Comparison Tool"]
[]
[null,"Vehicle Acquisition / Usage Data"]
[null," "]
[null,"Enter Acquisition Cost  ",null,null,19146," ",null,null,null,null,null,null,null,null,1,{"$f":"E14*O6","v":0.7}]
[null,"Enter Annual Finance Percentage",null,null,0," ",null,null,null,null,null,null,null,null,10,{"$f":"E14*O7","v":7},{"$f":"D27*O7","v":3.5841963636363641}]
[null,"Enter Fuel Price per Gallon ",null,null,2.99," ",null,null,null,null,null,null,null,null,100,{"$f":"E14*O8","v":70},{"$f":"D27*O8","v":35.841963636363637}]
[null,"Enter Annual Operational Cost  ",null,null,{"$f":"127.32*12","v":1527.84}," ",null,null,null,null,null,null,null,null,500,{"$f":"E14*O9","v":350},{"$f":"D27*O9","v":179.20981818181818}]
[null,"Enter the Estimated MPG",null,null,25," ",null,null,null,null,null,null,null,null,1000,{"$f":"E14*O10","v":700},{"$f":"D27*O10","v":358.41963636363636}]
[null,"Enter Expected Monthly Mileage ",null,null,1486," ",null,null,null,null,null,null,null,null,1500,{"$f":"E14*O11","v":1050},{"$f":"D27*O11","v":537.62945454545456}]
[null,"Enter One-way Commuting Distance ",null,null,10," ",null,null,null,null,null,null,null,null,2000,{"$f":"E14*O12","v":1400},{"$f":"D27*O12","v":716.83927272727271}]
[null,"Enter Commuting Days Per Week  ",null,null,5,null,null,null,null,null,null,null,null,null,2500,{"$f":"E14*O13","v":1750},{"$f":"D27*O13","v":896.04909090909098}]
[null,"Standard IRS Reimbursement Rate ",null,null,0.7,{"$f":"IF(E14<D27,\"Use Reimburstment\",\"\")","v":""},null,null,null,null,null,null,null,null,3000,{"$f":"E14*O14","v":2100},{"$f":"D27*O14","v":1075.2589090909091}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,3500,{"$f":"E14*O15","v":2450},{"$f":"D27*O15","v":1254.4687272727274}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,4000,{"$f":"E14*O16","v":2800},{"$f":"D27*O16","v":1433.6785454545454}]
[null,"Summary",null,null,null,null,null,null,null,null,null,null,null,null,4500,{"$f":"E14*O17","v":3150},{"$f":"D27*O17","v":1612.8883636363637}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,5000,{"$f":"E14*O18","v":3500},{"$f":"D27*O18","v":1792.098181818182}]
[null,"Accumulated Mileage ",null,{"$f":"SUM(Actual_Mileage)","v":110000},null,null,null,null,null,null,null,null,null,null,5500,{"$f":"E14*O19","v":3849.9999999999995},{"$f":"D27*O19","v":1971.3080000000002}]
[null,"Life Cycle Max Months ",null,{"$f":"Actual_Months","v":74},null,null,null,null,null,null,null,null,null,null,6000,{"$f":"E14*O20","v":4200},{"$f":"D27*O20","v":2150.5178181818183}]
[null,"Years in Service ",null,{"$f":"D20/12","v":6.166666666666667},null,null,null,null,null,null,null,null,null,null,6500,{"$f":"E14*O21","v":4550},{"$f":"D27*O21","v":2329.7276363636365}]
[null,"Operational Cost",null,{"$f":"E9/12*D20","v":9421.68},{"$f":"E9/12","v":127.32},"Per Month ",null,null,null,null,null,null,null,null,7000,{"$f":"E14*O22","v":4900},{"$f":"D27*O22","v":2508.9374545454548}]
[null,"Fixed Cost ",null,{"$f":"('Life Cycle Cost Analysis'!C37)","v":26270.16},{"$f":"D23/D19","v":0.23881963636363637},"Per Mile ",null,null,null,null,null,null,null,null,7500,{"$f":"E14*O23","v":5250},{"$f":"D27*O23","v":2688.147272727273}]
[null,"Fuel Cost ",null,{"$f":"'Life Cycle Cost Analysis'!C42","v":13156.000000000002},{"$f":"D24/D19","v":0.11960000000000001},"Per Mile ",null,null,null,null,null,null,null,null,8000,{"$f":"E14*O24","v":5600},{"$f":"D27*O24","v":2867.3570909090909}]
[null,"Total Lifetime cost ",null,{"$f":"'Life Cycle Cost Analysis'!C48","v":39426.160000000003},{"$f":"D25/D21","v":6393.4313513513516},"Per Year ",null,null,null,null,null,null,null,null,8500,{"$f":"E14*O25","v":5950},{"$f":"D27*O25","v":3046.5669090909091}]
[null,"Monthly Cost  ",null,{"$f":"'Life Cycle Cost Analysis'!C50","v":532.78594594594597},{"$f":"D26/20","v":26.639297297297297},"Daily Rate ",null,null,null,null,null,null,null,null,9000,{"$f":"E14*O26","v":6300},{"$f":"D27*O26","v":3225.7767272727274}]
[null,"Total Cost Per Mile",null,{"$f":"'Life Cycle Cost Analysis'!C49","v":0.35841963636363638}," "," ",null,null,null,null,null,null,null,null,9500,{"$f":"E14*O27","v":6650},{"$f":"D27*O27","v":3404.9865454545456}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,10000,{"$f":"E14*O28","v":7000},{"$f":"D27*O28","v":3584.1963636363639}]
[null,null,null,null,null,{"$f":"IF(E14<D27,\"At this accquisition cost and ultilzation level a Commonwealth-Owned Vehicle would NOT be Economical \",\" At this Utlization Level - it is more economical to provide a Commonwealth-Owned Vehicle\" )","v":" At this Utlization Level - it is more economical to provide a Commonwealth-Owned Vehicle"}]
[]
[]
[]
[null,"For Commuting Approval Only"]
[]
[null,null,"Monthly Cost of Commuting  ",null,null,null,null,"Lifetime Commuting Use Costs"]
[null,"Monthly  ","Commuting Mileage ",null,{"$f":"E12*2*E13*4","v":400},null,"Fixed Cost ",null,{"$f":"E38*D20","v":10612.694481830418}]
[null,"Personal ","Commuting Use ",null,{"$f":"E36/E11","v":0.26917900403768508},null,"Fuel Cost ",null,{"$f":"D24*E37","v":3541.3189771197854}]
[null,"Monthly ","Commuting Cost ",null,{"$f":"(D25*E37)/D20","v":143.41479029500564},null,"Total Commuting Cost",null,{"$f":"E38*D20","v":10612.694481830418},null," "]
[null,null,null,null," ",null,null,null,null,null," "]
[null,null,"Monthly Cost of Business ",null,null,null,null,"Lifetime Business Use costs ",null,null," "]
[null,"Monthly ","Business Mileage ",null,{"$f":"E11-E36","v":1086},null,"Fixed Cost ",null,{"$f":"D23-I36","v":15657.465518169582}]
[null,"Commonwealth","Business Use  ",null,{"$f":"(E11-E36)/E11","v":0.73082099596231498},null,"Fuel Cost",null,{"$f":"D24-I37","v":9614.6810228802169}]
[null,"Monthly ","Business Cost ",null,{"$f":"(D25-I38)/D20","v":389.37115565094035},null,"Total Business Cost ",null,{"$f":"SUM(I41:I42)","v":25272.146541049799},null," "]
[null,null,null,null," ",null,null,null,null," "]
[null,null,null,null,null,{"$f":"IF(E42<75%,\"Commuting Assignment not Approved .\",\"Commuting Use Can be Approved \")","v":"Commuting Assignment not Approved ."}]

{"sheet":"Life Cycle Cost Analysis","index":1,"n_rows":79,"n_cols":7,"rows_kept":79,"cut":false,"hidden":true,"merged":[[76,5,76,6],[60,4,60,6],[75,5,75,6],[70,5,70,6],[71,5,71,6],[72,5,72,6],[66,4,66,6],[73,5,73,6],[74,5,74,6],[64,4,64,6],[61,4,61,6],[59,4,59,6],[53,4,53,6],[52,4,52,6],[34,4,34,6],[51,4,51,6],[58,4,58,6],[63,4,63,6],[69,5,69,6],[68,5,68,6],[68,0,68,4],[65,4,65,6],[63,0,63,3],[29,4,29,6],[43,4,43,6],[36,4,36,6],[54,4,54,6],[51,0,51,3],[48,4,48,6],[45,4,45,6],[47,4,47,6],[48,0,48,1],[47,0,47,1],[33,4,33,6],[31,4,31,6],[37,0,37,3],[30,4,30,6],[44,4,44,6],[38,4,38,6],[39,4,39,6],[40,4,40,6],[32,4,32,6],[37,4,37,6],[42,4,42,6],[41,4,41,6],[28,4,28,6],[26,4,26,6],[13,4,13,6],[25,4,25,6],[16,4,16,6],[19,4,19,6],[27,4,27,6],[20,4,20,6],[15,4,15,6],[21,4,21,6],[0,0,0,6],[1,3,1,4],[1,0,1,2],[2,0,2,1],[3,0,3,1],[4,0,4,1],[18,4,18,6],[14,4,14,6],[24,4,24,6],[5,0,5,1],[7,0,7,1],[21,0,21,3],[14,0,14,3],[8,0,8,1],[17,4,17,6],[13,0,13,1],[23,4,23,6],[6,0,6,1],[9,0,9,1],[10,0,10,1],[11,0,11,1]]}
["Lifecycle Cost Analysis - Employer vs. Employee Provided "]
["Variable Data",null,null,"Derived Values",null,null,"FORMULA/SOURCE:"]
["Vehicle:",null," "]
["Target Months in Service:",null,84,"Actual Months:",{"$f":"INT(MIN((MIN((Max_Miles/Monthly_Miles),Max_Months)),Max_Months))","v":74},null,"Actual Months in Service given the Maximum Replacement Mileage and Mileage-per-Month set by policy"]
["Target Replacement Mileage:",null,110000,"Actual Mileage:",{"$f":"INT(MIN((MIN((Monthly_Miles*Max_Months),Max_Miles)),Max_Miles))","v":110000},null,"Actual Miles at Replacement given the Maximum Service Months and Mileage-per-Month set by policy"]
["Expected Mileage-per-Month:",null,{"$f":"'Justification '!E11","v":1486},null,null,null,"Expected Average Miles-per-Month driven; Average Daily Miles driven given a five-day work week and the Mileage-per-Month entered"]
["Lease Management Fee (%):",null,0," ",null,null,"Quoted by leasing company (enter either a % or flat rate--enter \"0\" in the one not used)"]
["Lease Management Fee (Flat Rate):",null,0," ",null,null,"Quoted by leasing company (enter either a % or flat rate--enter \"0\" in the one not used)"]
["Book Depreciation Rate:",null,1.04E-2," ",null,null,"Mutually agreed to with leasing company - Or from actual experience "]
["Projected end of term resale value ",null,0.12,null,null,null,"Resale Value of the vehicle at end of Term - enter a % from 0 to 20 based on actual auction experience (not dealer blue book values)"]
["City Miles-per-Gallon",null,{"$f":"'Justification '!E10","v":25},null,null,null,"Value from EPA Estimated City Mileage or actual experience "]
["Estimated Personal Use:",null,0,null,null,null,"Percentage of personal use which employee will reimburse employer (enter 0 if you will not charge for personal use)"]
[]
[null,null,"Employer","Employee"]
["Acquisition Cost"]
[null,"Factory Invoice Price",{"$f":" 'Justification '!E6","v":19146},17000,"Price Obtained from lessor, dealer or alternate source"]
[null,"Dealer Holdback Adjustment"," "," ","Obtained from dealer or alternate source - incentive portion normally given to dealer"]
[null,"Mfg. Additional Incentive"," ",0,"Obtained from dealer or alternate source - added incentive for early order, etc."]
[null,"Fleet Incentive"," "," ","Obtained from lessor, dealer or alternate source - special fleet discounts"]
[null,"Down Payment ",null,1500,"Down Payment "]
[null,"Net Acquisition Cost",{"$f":"SUM(C16:C19)","v":19146},{"$f":"D16-D20","v":15500},"Factory Invoice Price - Dealer Holdback Adjustment - Mfg.. Additional Incentive - Fleet Incentives [or value from leasing company]"]
["Fixed Costs"]
[null,"Actual Depreciation",{"$f":"C21-C24","v":16848.48},{"$f":"D21-D24","v":13640},"Net Acquisition Cost - Projected Resale Price"]
[null,"     Projected Resale Price",{"$f":"Cap_Cost_A*C10","v":2297.52},{"$f":"Cap_Cost_B*C10","v":1860},"Uses Calculated resale Value - or enter Finance or lease guide or actual fleet history for months in service with mileage adjustment"]
[null,"Interest",{"$f":"SUM(C27:C33)","v":0},{"$f":"SUM(D27:D33)","v":2258.4559960479896},"Total of annual interest from declining balance method/car loan from \"Loan Payment Schedule\" worksheet"]
[null,"     Annual Interest Rate",{"$f":"'Justification '!E7","v":0},0.04,"Annual cost of money or Interest rate "]
[null,"     Year 1",{"$f":"(C21*Interest_Factor_Yr1)*Total_Months_Yr1","v":0},{"$f":"Employee_Y1","v":584.36148474902052},"(Net Vehicle Cost * Year 1 Interest Factor) * Year 1 Total Months) [see table bottom of sheet] or Interest from Loan Payment Schedule"]
[null,"     Year 2",{"$f":"(C21*Interest_Factor_Yr2)*IF(Total_Months_Yr2=0,0,(Total_Months_Yr2-12))","v":0},{"$f":"Employee_Yr2","v":504.5880568968015},"(Net Vehicle Cost * Year 2 Interest Factor) * Year 2 Total Months) [see table in lower left of sheet]or Interest from Load Payment Schedule"]
[null,"     Year 3",{"$f":"(C21*Interest_Factor_Yr3)*IF(Total_Months_Yr3=0,0,(Total_Months_Yr3-24))","v":0},{"$f":"Employee_Yr3","v":421.56453650988249},"(Net Vehicle Cost * Year 3 Interest Factor) * Year 3 Total Months) [see table in lower left of sheet]/Interest from Load Payment Schedule"]
[null,"     Year 4",{"$f":"(C21*Interest_Factor_Yr4)*IF(Total_Months_Yr4=0,0,(Total_Months_Yr4-36))","v":0},{"$f":"Employee_Yr4","v":335.1585098037678},"(Net Vehicle Cost * Year 4 Interest Factor) * Year 4 Total Months) [see table in lower left of sheet]/Interest from Load Payment Schedule"]
[null,"     Year 5",{"$f":"(C21*Interest_Factor_Yr5)*IF(Total_Months_Yr5=0,0,(Total_Months_Yr5-48))","v":0},{"$f":"Employee_Yr5","v":245.23216825207757},"(Net Vehicle Cost * Year 5 Interest Factor) * Year 5 Total Months) [see table in lower left of sheet]/Interest from Load Payment Schedule"]
[null,"     Year 6",{"$f":"(C21*Interest_Factor_Yr6)*IF(Total_Months_Yr6=0,0,(Total_Months_Yr6-60))","v":0},{"$f":"Employee_Yr6","v":151.64208879643942},"(Net Vehicle Cost * Year 6 Interest Factor) * Year 6 Total Months) [see table in lower left of sheet]/Interest from Load Payment Schedule"]
[null,"     Year 7",{"$f":"(C23*Interest_Factor_Yr7)*IF(Total_Months_Yr7=0,0,(Total_Months_Yr7-72))","v":0},{"$f":"Employee_Yr7","v":15.909151039999987},"(Net Vehicle Cost * Year 7 Interest Factor) * Year 7 Total Months) [see table in lower left of sheet]/Interest from Load Payment Schedule"]
[null,"Management Fee",{"$f":"IF(Management_Fee<>0,(Management_Fee*C21)*Actual_Months,Flat_Rate)","v":0},null,"Management Fee * Net Acquisition Cost * Actual Months in Service or Flat Rate Management Fee"]
[null,"Mfg.. Additional Incentive",0,0,"Incentive paid directly to the fleet "]
[null,"Insurance ",{"$f":"'Justification '!D22","v":9421.68},{"$f":"C36","v":9421.68},"Life time Insurance cost (annual rate/12*Actual Months) "]
[null,"Total Fixed Cost:",{"$f":"C23+C25+C34+C35+C36","v":26270.16},{"$f":"D23+D25+D34+D35+D36","v":25320.135996047989},"Actual Depreciation + Interest + Management Fee + Build Time Delay Cost"]
["Operating Costs"]
[null,"Total Fuel Cost",{"$f":"(Actual_Mileage/City_MPG)*C40","v":13156.000000000002},{"$f":"(Actual_Mileage/City_MPG)*D40","v":10692},"(Actual Miles/Estimated Miles-per-Gallon) * Fuel Cost-per-Gallon"]
[null,"     Cost of Fuel",{"$f":" 'Justification '!E8","v":2.99},2.4300000000000002,"Average price of fuel per gallon"]
[null,"Total Maintenance Cost",0,{"$f":"Employee_Maint","v":8811.5},"Values from \"Maintenance Estimate\" worksheet"]
[null,"Total Operating Cost:",{"$f":"C39+C41","v":13156.000000000002},{"$f":"D39+D41","v":19503.5},"Total Fuel Cost + Total Maintenance Cost"]
["Personal Use Costs"]
[null,"Fixed Cost for Personal Use",{"$f":"C37*Personal_Use","v":0},{"$f":"D37*Personal_Use","v":0},"Total Fixed Cost * Estimated Personal Use"]
[null,"Operating Cost for Personal Use",{"$f":"C42*Personal_Use","v":0},{"$f":"D42*Personal_Use","v":0},"Total Operating Cost * Estimated Personal Use"]
[null,"Total Personal Use Cost",{"$f":"SUM(C44:C45)","v":0},{"$f":"SUM(D44:D45)","v":0},"Fixed Cost for Personal Use + Operating Cost for Personal Use (amount to be reimbursed to employer by employee)"]
[]
["Total Lifecycle Cost:",null,{"$f":"C42+C37","v":39426.160000000003},{"$f":"D42+D37","v":44823.635996047989},"Total Fixed Cost + Total Operating Cost"]
["Lifecycle Cost-per-Mile:",null,{"$f":"C48/Actual_Mileage","v":0.35841963636363638},{"$f":"D48/Actual_Mileage","v":0.40748759996407263},"Total Lifecycle Cost / Actual Miles"]
["Lifecycle Cost-per-Month ",null,{"$f":"C48/Actual_Months","v":532.78594594594597},{"$f":"D48/Actual_Months","v":605.72481075740529},"Total Lifecycle Cost / Actual Months "]
[]
["Reimbursement Rates - Fixed and Operating "]
[null,"Break Even "," "," ","Fixed Recovery Cost to Break Even for use of vehicle"]
[null,"County Fixed Monthly",{"$f":"(C37-C44)/Actual_Months","v":355.00216216216216},null,"Monthly Fixed Cost to County - Paid by County "]
[null,"County Operating Per Mile",{"$f":"(C42-C45)/Actual_Mileage","v":0.11960000000000001},null,"Per mile Operating cost for Business use paid by County "]
[null,"Personal Fixed Monthly",{"$f":"C44/Actual_Months","v":0},null,"Fixed Recovery Cost paid by Employee to County for personal use "]
[null,"Personal Operating Per Mile",{"$f":"C45/Actual_Mileage","v":0},null,"Per mile Operating recovery cost Paid by Employee to the County for personal use "]
[null,"Total OC per mile   ",{"$f":"C55+C57","v":0.11960000000000001},{"$f":"'Loan Payment Schedule'!F6","v":211.86649821559266},"Employees personal Monthly Loan payment "]
["Equitable for Employee",null,null,null,"Reimbursements to an employee - when employer covers employees actual expenditures for the official business use "]
[null,"Monthly",null,{"$f":"(D37-D44)/Actual_Months","v":342.16399994659446},"Monthly fixed cost paid to employee for use of personal car on County business"]
[null,"Per Mile",null,{"$f":"(D42-D45)/Actual_Mileage","v":0.17730454545454546},"Per mile Operating Cost paid to employee per official mile driven"]
[null,"Total Extra Cost",{"$f":"(D48-D46)-(C48-C46)","v":5397.475996047986},null,"Amount of added expense to employer to provide equitable reimbursement over the cost of providing a vehicle"]
[]
["Lease Summary"]
[null,"Book Depreciation (Reserve)",{"$f":"C21*(Depreciation_Rate*Actual_Months)","v":14734.7616},null,"Actual Purchase Price * (Book Depreciation Rate * Actual Months)"]
[null,"Total of Lease Payments",{"$f":"C65+C25+C34","v":14734.7616},null,"Reserve For Depreciation + Interest + Management Fee (total cash outlay to leasing company to cover capitalized cost)"]
[null,"Depreciation Adjustment (TRAC)",{"$f":"(C21-C65)-C24","v":2113.7184000000002},null,"Net Acquisition Cost - Book Depreciation - Projected Resale Price (Terminal Rental Adjustment Clause)"]
[]
["Annual Interest Calculations - Step Rate Formula"]
[null,"Holding Period","Total Months","Book Value","Interest Factor"]
[null,"Year 1",{"$f":"IF(C70=0,0,(MIN(IF(OR(Actual_Months>12,Actual_Months<>C70),MIN(Actual_Months,12),0),12)))","v":12},{"$f":"IF(Total_Months_Yr1=0,0,1-(((1-1)+(12-1))/2)*Depreciation_Rate)","v":0.94279999999999997},{"$f":"D71*(Employer_Lease_IR/12)","v":0},"Book Value=1-(((1-1)+(12-1))/2)*Book Depreciation Rate; Interest Factor=Book Value*Monthly Interest"]
[null,"Year 2",{"$f":"IF(C71=0,0,(MIN(IF(OR(Actual_Months>24,Actual_Months<>C71),MIN(Actual_Months,24),0),24)))","v":24},{"$f":"IF(Total_Months_Yr2=0,0,1-(((13-1)+(24-1))/2)*Depreciation_Rate)","v":0.81800000000000006},0,"Book Value=1-(((13-1)+(24-1))/2)*Book Depreciation Rate; Interest Factor=Book Value*Monthly Interest"]
[null,"Year 3",{"$f":"IF(C72=0,0,(MIN(IF(OR(Actual_Months>36,Actual_Months<>C72),MIN(Actual_Months,36),0),36)))","v":36},{"$f":"IF(Total_Months_Yr3=0,0,1-(((25-1)+(36-1))/2)*Depreciation_Rate)","v":0.69320000000000004},0,"Book Value=1-(((25-1)+(36-1))/2)*Book Depreciation Rate; Interest Factor=Book Value*Monthly Interest"]
[null,"Year 4",{"$f":"IF(C73=0,0,(MIN(IF(OR(Actual_Months>48,Actual_Months<>C73),MIN(Actual_Months,48),0),48)))","v":48},{"$f":"IF(Total_Months_Yr4=0,0,1-(((37-1)+(48-1))/2)*Depreciation_Rate)","v":0.56840000000000002},0,"Book Value=1-(((37-1)+(48-1))/2)*Book Depreciation Rate; Interest Factor=Book Value*Monthly Interest"]
[null,"Year 5",{"$f":"IF(C74=0,0,(MIN(IF(OR(Actual_Months>60,Actual_Months<>C74),MIN(Actual_Months,60),0),60)))","v":60},{"$f":"IF(Total_Months_Yr5=0,0,1-(((49-1)+(60-1))/2)*Depreciation_Rate)","v":0.44359999999999999},0,"Book Value=1-(((49-1)+(60-1))/2)*Book Depreciation Rate; Interest Factor=Book Value*Monthly Interest"]
[null,"Year 6",{"$f":"IF(C75=0,0,(MIN(IF(OR(Actual_Months>72,Actual_Months<>C75),MIN(Actual_Months,72),0),72)))","v":72},{"$f":"IF(Total_Months_Yr5=0,0,1-(((61-1)+(72-1))/2)*Depreciation_Rate)","v":0.31880000000000008},0,"Book Value=1-(((61-1)+(72-1))/2)*Book Depreciation Rate; Interest Factor=Book Value*Monthly Interest"]
[null,"Year 7",{"$f":"IF(C76=0,0,(MIN(IF(OR(Actual_Months>84,Actual_Months<>C76),MIN(Actual_Months,84),0),84)))","v":74},{"$f":"IF(Total_Months_Yr5=0,0,1-(((73-1)+(84-1))/2)*Depreciation_Rate)","v":0.19400000000000006},0,"Book Value=1-(((73-1)+(84-1))/2)*Book Depreciation Rate; Interest Factor=Book Value*Monthly Interest"]
[]
[null,null," "]

{"sheet":"Maintenance Estimate","index":2,"n_rows":24,"n_cols":11,"rows_kept":24,"cut":false,"hidden":true,"merged":[[5,8,5,10],[0,0,0,10],[5,5,5,7]]}
["Vehicle Maintenance Estimate"]
[null,"Employer","Employee"]
["Mechanical Hourly Labor Rate",68,85]
["Parts Mark-up (Jobber)",0.15,0.4]
["Accumlated Mileage ",{"$f":"Actual_Mileage","v":110000}," "]
[null,"Mileage","Labor","Parts",null,"Employer",null,null,"Employee"]
["Service/Repair","Interval","Hours","(Jobber)","Freq.","Labor","Parts","Total","Labor","Parts","Total"]
["PM A with safety inspection",6000,1,15,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B8)","v":18},{"$f":"((E8*C8)*Employer_Labor)","v":1224},{"$f":"(E8*D8)+(E8*D8*Employer_PartsMU)","v":310.5},{"$f":"SUM(F8:G8)","v":1534.5},{"$f":"((C8*E8)*Employee_Labor)","v":1530},{"$f":"(D8*E8)+(D8*E8*Employee_PartsMU)","v":378},{"$f":"SUM(I8:J8)","v":1908}]
["PM B (A+trans svc)",50000,2.25,35,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B9)","v":2},{"$f":"((E9*C9)*Employer_Labor)","v":306},{"$f":"(E9*D9)+(E9*D9*Employer_PartsMU)","v":80.5},386.5,{"$f":"((C9*E9)*Employee_Labor)","v":382.5},{"$f":"(D9*E9)+(D9*E9*Employee_PartsMU)","v":98},480.5]
["PM C (A+tune up)",80000,3,85,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B10)","v":1},204,97.75,301.75,255,119,374]
["air conditioning",80000,4,250,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B11)","v":1},272,287.5,559.5,340,350,690]
["alternator",70000,1.5,60,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B12)","v":1},102,69,171,127.5,84,211.5]
["battery",60000,0.5,50,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B13)","v":1},34,57.5,91.5,42.5,70,112.5]
["brakes - pads, disc, drum",30000,2,40,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B14)","v":3},408,138,546,510,168,678]
["brakes - master cylinder, calipers, ABS",80000,4,200,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B15)","v":1},272,230,502,340,280,620]
["cooling system",100000,2,30,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B16)","v":1},136,34.5,170.5,170,42,212]
["engine",180000,5,400,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B17)","v":0},0,0,0,0,0,0]
["emissions",60000,3,100,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B18)","v":1},204,115,319,255,140,395]
["exhaust",120000,3,200,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B19)","v":0},0,0,0,0,0,0]
["front end/suspension",100000,4,200,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B20)","v":1},272,230,502,340,280,620]
["tires",40000,1.5,200,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B21)","v":2},204,460,664,255,560,815]
["transmission",130000,0,1600,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B22)","v":0},0,0,0,0,0,0]
["miscellaneous",20000,3,60,{"$f":"INT(Actual_Mileage/'Maintenance Estimate'!B23)","v":5},1020,345,1365,1275,420,1695]
["Total:",null,null,null,null,null,null,{"$f":"SUM(H8:H23)","v":7113.25},null,null,{"$f":"SUM(K8:K23)","v":8811.5}]

{"sheet":"Insurance Rates","index":3,"n_rows":22,"n_cols":10,"rows_kept":22,"cut":false,"hidden":true,"merged":[[3,0,3,2],[0,0,0,10],[6,7,6,8],[6,9,6,10],[5,3,5,4],[5,5,5,6],[5,7,5,8],[5,9,5,10],[6,1,6,2],[5,1,5,2],[6,3,6,4],[6,5,6,6]]}
["Insurance Rates "]
[null,null,"Enter the Annual Insurance cost from the table below on Justification Sheet cell I8"]
[null," "]
["Current Year Rate Multiplier ",null,null,1.4,"Assigned by Risk Management "]
[null,null," "]
["Type ","AUTO ",null,"LIGHT  ",null,"MEDIUM ",null,"LARGE",null,"TRAILERS"]
["Class Code ","1300 -1399 ",null,"1400-2799",null,"3000-5999",null,"6000-8999",null,9000]
[null,"AUTOS","AUTOS","TRUCKS","TRUCKS","TRUCKS","TRUCKS","TRUCKS","TRUCKS"," "]
["Age ","<5 YRS",">5 YRS","<5 YRS",">5 YRS","<5 YRS",">5 YRS","<5 YRS",">5 YRS"]
["Base Rates",747,726,704,633,864,831,1785,1705,65]
["Annual Cost ",{"$f":"B10*Rate","v":1045.8},1016.4,985.59999999999991,886.19999999999993,1209.5999999999999,1163.3999999999999,2499,2387,91]
["Life Cost ",{"$f":"B11/12*Actual_Months","v":6449.0999999999995},6267.8,6077.8666666666659,5464.9,7459.2,7174.2999999999993,15410.5,14719.833333333332,561.16666666666663]
[]
[null," "]
[null," "]
[]
[null," "]
[]
[null," "]
[null," "]
[null," "]
[null," "]

{"sheet":"Loan Payment Schedule","index":4,"n_rows":94,"n_cols":8,"rows_kept":94,"cut":false,"hidden":true,"merged":[[7,2,8,2],[7,3,8,3],[7,5,8,5],[1,2,1,7],[2,2,2,4]]}
[]
[null,null,"Employee Loan Payment Schedule"]
[null,null,"Calculations for 84 month loan with ",null,null,{"$f":"'Life Cycle Cost Analysis'!D20","v":1500},"Down payment ",{"$f":"F3","v":1500}]
[null,null,null,null,"Loan Amount",{"$f":"'Life Cycle Cost Analysis'!D21","v":15500},"Principal ",{"$f":"SUM(D10:D93)","v":15499.999999999998}]
[null,null,null,null,"Annual Percentage Rate:",{"$f":"Employee_Loan_IR","v":0.04},"Total Interest ",{"$f":"SUM(F10:F93)","v":2296.7858501097826}]
[null,null,null,null,"Monthly Payment:",{"$f":"PMT(Employee_Loan_IR/12,84,Cap_Cost_B,0)*-1","v":211.86649821559266},"Total P+ I =",{"$f":"H4+H5","v":17796.78585010978}]
[null,null," ",null," ",null,"Total cost",{"$f":"H3+H6","v":19296.78585010978}]
[null,null,"Month","Principle","Remaining","Interest","Interest","Annual"]
[null,null,null,null,"Balance",null,"Paid","Interest"]
[null,null,1,{"$f":"PPMT(Employee_Loan_IR/12,C10,84,Cap_Cost_B,0)*-1","v":160.19983154892597},{"$f":"SUM(D11:D93)","v":15339.800168451073},{"$f":"IPMT(Employee_Loan_IR/12,C10,84,Cap_Cost_B,0)*-1","v":51.666666666666671},{"$f":"IF(C10>Actual_Months,0,F10)","v":51.666666666666671}]
[null,null,2,160.73383098742238,{"$f":"SUM(D12:D94)","v":15179.06633746365},51.132667228170256,{"$f":"IF(C11>Actual_Months,0,F11)","v":51.132667228170256}]
[null,null,3,161.26961042404713,15017.796727039604,50.596887791545505,50.596887791545505]
[null,null,4,161.80717579212728,14855.989551247476,50.05932242346536,50.05932242346536]
[null,null,5,162.34653304476771,14693.643018202707,49.519965170824925,49.519965170824925]
[null,null,6,162.88768815491693,14530.755330047792,48.978810060675706,48.978810060675706]
[null,null,7,163.43064711543332,14367.32468293236,48.435851100159304,48.435851100159304]
[null,null,8,163.97541593915145,14203.349266993207,47.891082276441196,47.891082276441196]
[null,null,9,164.5220006589486,14038.82726633426,47.344497556644029,47.344497556644029]
[null,null,10,165.07040732781178,13873.756859006447,46.796090887780863,46.796090887780863]
[null,null,11,165.62064201890448,13708.136216987543,46.245856196688159,46.245856196688159]
[null,null,12,166.17271082563417,13541.963506161908,45.693787389958487,45.693787389958487,{"$f":"SUM(G10:G21)","v":584.36148474902052}]
[null,null,13,166.72661986171963,13375.236886300188,45.139878353873037,45.139878353873037]
[null,null,14,167.28237526125866,13207.95451103893,44.584122954333971,44.584122954333971]
[null,null,15,167.83998317879622,13040.114527860134,44.02651503679644,44.02651503679644]
[null,null,16,168.39944978939218,12871.715078070742,43.467048426200442,43.467048426200442]
[null,null,17,168.96078128869019,12702.754296782052,42.905716926902478,42.905716926902478]
[null,null,18,169.52398389298583,12533.230312889065,42.342514322606846,42.342514322606846]
[null,null,19,170.08906383929576,12363.141249049771,41.777434376296895,41.777434376296895]
[null,null,20,170.65602738542674,12192.485221664341,41.210470830165903,41.210470830165903]
[null,null,21,171.22488081004485,12021.260340854296,40.641617405547812,40.641617405547812]
[null,null,22,171.795630412745,11849.464710441551,40.070867802847673,40.070867802847673]
[null,null,23,172.36828251412078,11677.09642792743,39.498215701471842,39.498215701471842]
[null,null,24,172.94284345583452,11504.153584471596,38.923654759758115,38.923654759758115,{"$f":"SUM(G22:G33)","v":504.5880568968015}]
[null,null,25,173.51931960068731,11330.634264870909,38.347178614905332,38.347178614905332]
[null,null,26,174.0977173326896,11156.536547538222,37.76878088290303,37.76878088290303]
[null,null,27,174.6780430571319,10981.85850448109,37.188455158460741,37.188455158460741]
[null,null,28,175.26030320065567,10806.598201280434,36.606195014936965,36.606195014936965]
[null,null,29,175.84450421132451,10630.753697069109,36.021994004268109,36.021994004268109]
[null,null,30,176.43065255869561,10454.323044510415,35.435845656897037,35.435845656897037]
[null,null,31,177.01875473389126,10277.304289776523,34.847743481701379,34.847743481701379]
[null,null,32,177.60881724967089,10099.695472526853,34.257680965921743,34.257680965921743]
[null,null,33,{"$f":"PPMT(Employee_Loan_IR/12,C42,84,Cap_Cost_B,0)*-1","v":178.20084664050313},9921.4946258863492,{"$f":"IPMT(Employee_Loan_IR/12,C42,84,Cap_Cost_B,0)*-1","v":33.665651575089505},33.665651575089505]
[null,null,34,178.79484946263815,9742.6997764237112,33.071648752954502,33.071648752954502]
[null,null,35,179.39083229418026,9563.3089441295306,32.475665921412372,32.475665921412372]
[null,null,36,179.98880173516088,9383.3201423943701,31.877696480431769,31.877696480431769,{"$f":"SUM(G34:G45)","v":421.56453650988249}]
[null,null,37,180.58876440761142,9202.7313779867563,31.277733807981228,31.277733807981228]
[null,null,38,181.19072695563679,9021.5406510311223,30.675771259955862,30.675771259955862]
[null,null,39,181.79469604548891,8839.7459549856321,30.071802170103737,30.071802170103737]
[null,null,40,182.40067836564052,8657.3452766199916,29.465819849952108,29.465819849952108]
[null,null,41,183.00868062685933,8474.3365959931307,28.857817588733308,28.857817588733308]
[null,null,42,183.61870956228219,8290.7178864308498,28.247788653310444,28.247788653310444]
[null,null,43,184.2307719274898,8106.4871145033612,27.635726288102834,27.635726288102834]
[null,null,44,184.84487450058143,7921.6422400027805,27.021623715011199,27.021623715011199]
[null,null,45,185.46102408225005,7736.1812159205292,26.405474133342597,26.405474133342597]
[null,null,46,186.07922749585754,7550.1019884246725,25.787270719735098,25.787270719735098]
[null,null,47,186.69949158751041,7363.4024968371614,25.167006628082241,25.167006628082241]
[null,null,48,187.32182322613542,7176.0806736110262,24.544674989457203,24.544674989457203,{"$f":"SUM(G46:G57)","v":335.1585098037678}]
[null,null,49,187.9462293035559,6988.1344443074704,23.920268912036754,23.920268912036754]
[null,null,50,188.57271673456773,6799.5617275729019,23.293781481024901,23.293781481024901]
[null,null,51,189.20129245701631,6610.3604351158865,22.665205758576342,22.665205758576342]
[null,null,52,189.83196343187302,6420.5284716840124,22.034534783719618,22.034534783719618]
[null,null,53,190.46473664331259,6230.0637350406996,21.401761572280041,21.401761572280041]
[null,null,54,191.0996190987903,6038.9641159419098,20.766879116802333,20.766879116802333]
[null,null,55,191.73661782911961,5847.2274981127903,20.129880386473033,20.129880386473033]
[null,null,56,192.37573988855002,5654.8517582242403,19.490758327042631,19.490758327042631]
[null,null,57,193.01699235484517,5461.8347658693947,18.849505860747467,18.849505860747467]
[null,null,58,193.66038232936131,5268.1743835400348,18.206115886231316,18.206115886231316]
[null,null,59,194.30591693712586,5073.8684666029085,17.560581278466781,17.560581278466781]
[null,null,60,194.95360332691629,4878.9148632759907,16.912894888676359,16.912894888676359,{"$f":"SUM(G58:G69)","v":245.23216825207757}]
[null,null,61,195.60344867133935,4683.3114146046528,16.263049544253306,16.263049544253306]
[null,null,62,196.25546016691047,4487.055954437742,15.611038048682174,15.611038048682174]
[null,null,63,196.90964503413352,4290.146309403608,14.956853181459142,14.956853181459142]
[null,null,64,197.56601051758062,4092.5802988860273,14.300487698012025,14.300487698012025]
[null,null,65,{"$f":"PPMT(Employee_Loan_IR/12,C74,84,Cap_Cost_B,0)*-1","v":198.22456388597257},3894.3557350000551,{"$f":"IPMT(Employee_Loan_IR/12,C74,84,Cap_Cost_B,0)*-1","v":13.641934329620094},13.641934329620094]
[null,null,66,198.88531243225913,{"$f":"SUM(D76:D158)","v":3695.470422567796},12.981185783333515,{"$f":"IF(C75>Actual_Months,0,F75)","v":12.981185783333515}]
[null,null,67,199.54826347369999,3495.9221590940961,12.318234741892653,12.318234741892653]
[null,null,68,200.21342435194566,3295.7087347421502,11.653073863646988,11.653073863646988]
[null,null,69,200.88080243311882,3094.8279323090314,10.985695782473833,10.985695782473833]
[null,null,70,201.55040510789587,2893.2775272011354,10.316093107696773,10.316093107696773]
[null,null,71,202.22223979158886,2691.0552874095465,9.6442584240037839,9.6442584240037839]
[null,null,72,202.89631392422748,2488.1589734853192,8.9701842913651557,8.9701842913651557,{"$f":"SUM(G70:G81)","v":151.64208879643942}]
[null,null,73,203.57263497064159,2284.5863385146777,8.2938632449510621,8.2938632449510621]
[null,null,74,204.25121042054374,2080.3351280941333,7.6152877950489257,7.6152877950489257]
[null,null,75,204.93204778861218,1875.4030803055214,6.9344504269804448,0]
[null,null,76,205.61515461457424,1669.7879256909468,6.2513436010184043,0]
[null,null,77,206.30053846328951,1463.4873872276573,5.5659597523031579,0]
[null,null,78,206.98820692483378,1256.4991803028238,4.8782912907588596,0]
[null,null,79,207.67816761458323,1048.8210126882407,4.1883306010094135,0]
[null,null,80,208.37042817329851,840.45058451494219,3.4960700422941358,0]
[null,null,81,209.06499626720952,631.38558824773258,2.8015019483831409,0]
[null,null,82,209.76187958810019,421.62370865963237,2.1046186274924419,0]
[null,null,83,210.46108585339388,211.16262280623852,1.4054123621987749,0]
[null,null,84,211.16262280623852,0,0.70387540935412851,0,{"$f":"SUM(G82:G93)","v":15.909151039999987}]
[null,null,"Total "," ",null,null," ",{"$f":"SUM(H93,H81,H69,H57,H45,H33,H21)","v":2258.4559960479892}]