moeller roi spreadsheet xls - ehscpirr raw data settings sheet roi annual roi quarterly executive...
TRANSCRIPT
DriveCam ROI Calculator Assuming 5 Year Agreement
Confidential and Proprietary Date Produced: 062205 DriveCam
Year 1 Year 2 Year 3 Year 4 Year 5NUMBER OF VEHICLES EQUIPPED WITH DRIVECAMTotal Fleet Size 38,000 38,000 38,000 38,000 38,000DriveCam Units Committed 10,000 10,000 10,000 4,000 4,000 38,000Cumulative % DriveCam Units 26% 53% 79% 89% 100%
Property & Casualty Claims without DriveCam $ 77,000,000 $ 79,310,000 $ 81,689,300 $ 84,139,979 $ 86,664,178
Investment in DriveCam solution $ (7,912,000) $ (15,112,000) $ (22,312,000) $ (24,772,000) $ (27,652,000)Reduction in Property & Casualty Claims (due to DriveCam) $ 14,184,211 $ 29,219,474 $ 45,144,087 $ 52,698,197 $ 60,664,925
Net Annual Fleet Savings $ 6,272,211 $ 14,107,474 $ 22,832,087 $ 27,926,197 $ 33,012,925 IRR By Year 79% 93% 102% 113% 119%
Grand Total (Fleet Savings) $ 104,150,893 NPV @ 10% $ 48,295,801 Internal Rate of Return 107%
Turquoise colored cells may be modified to accommodate your specific requirements. If the cell turns red, the number of plants using GSQA is going down.
Auto Liability
Personal Injury
Workers Comp
$- $20.00 $40.00 $60.00 $80.00 $100.00 $120.00 $140.00
Net Fleet Savings(over 5 year period)
Dollars in (mil)
Cat
egor
y
DriveCam ROI Calculator Assuming 5 Year Agreement
Confidential and Proprietary Date Produced: 062205 DriveCam
NUMBER OF VEHICLES EQUIPPED WITH DRIVECAM 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 5th Qtr 6th Qtr 7th QtrTotal Fleet Size 38000 38000 38000 38000 38000 38000 38000DriveCam Units Deployed 10,000 0 0 0 5000 5000 0Cumulative % DriveCam Units 26% 26% 26% 26% 39% 53% 53%
Property & Casualty Claims (without DriveCam) $ 19,250,000 $ 19,250,000 $ 19,250,000 $ 19,250,000 $ 19,827,500 $ 19,827,500 $ 19,827,500
Investment in DriveCam solution $ (2,503,000) $ (1,803,000) $ (1,803,000) $ (1,803,000) $ (3,053,000) $ (3,953,000) $ (3,603,000)Reduction in Property & Casualty Claims (due to DriveCam) $ 3,546,053 $ 3,546,053 $ 3,546,053 $ 3,546,053 $ 5,478,651 $ 7,304,868 $ 7,304,868
Net Quarterly Fleet Savings $ 1,043,053 $ 1,743,053 $ 1,743,053 $ 1,743,053 $ 2,425,651 $ 3,351,868 $ 3,701,868 IRR By Quarter 42% 97% 97% 97% 79% 85% 103%
Grand Total (Fleet Savings) $ 41,577,467 NPV @ 10% $ 6,700,420 Internal Rate of Return 88%
Turquoise colored cells may be modified to accommodate your specific requirements. If the cell turns red, the number of plants using GSQA is going down.
1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 5th Qtr 6th Qtr 7th Qtr 8th Qtr $-
$1,000,000 $2,000,000 $3,000,000 $4,000,000 $5,000,000 $6,000,000 $7,000,000 $8,000,000
Net Fleet Savings By Quarter
Workers Comp Personal Injury Auto Liability
Quarters
DriveCam Quarterly ROI Breakdown Quarters 1-8
Year 1 Year 2NUMBER VEHICLES EQUIPPED DRIVECAM 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 5th Qtr 6th Qtr 7th Qtr 8th QtrTotal Fleet Size 38000 38000 38000 38000 38000 38000 38000 38000DriveCam Units Deployed 10000 0 0 0 5000 5000 0 0Cumulative % DriveCam Units 26% 26% 26% 26% 39% 53% 53% 53%
QUARTERLY INVESTMENT DRIVECAM SOLUTION 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 5th Qtr 6th Qtr 7th Qtr 8th QtrQuarterly Initial Setup (not unit dependent) $ (3,000) $ (3,000) $ (3,000) $ (3,000) $ (3,000) $ (3,000) $ (3,000) $ (3,000)Quarterly Equipment Install Cost $ (700,000) $ - $ - $ - $ (350,000) $ (350,000) $ - $ - Quarterly Service Fee $ (1,800,000) $ (1,800,000) $ (1,800,000) $ (1,800,000) $ (2,700,000) $ (3,600,000) $ (3,600,000) $ (3,600,000)Quarterly Investment (Setup + Service + Install) $ (2,503,000) $ (1,803,000) $ (1,803,000) $ (1,803,000) $ (3,053,000) $ (3,953,000) $ (3,603,000) $ (3,603,000)Quarterly Investment Per Unit $ (250) $ (180) $ (180) $ (180) $ (204) $ (198) $ (180) $ (180)Quarterly Investment DriveCam Solution $ (2,503,000) $ (1,803,000) $ (1,803,000) $ (1,803,000) $ (3,053,000) $ (3,953,000) $ (3,603,000) $ (3,603,000)
REDUCTION IN LOSS-RELATED COSTS 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 5th Qtr 6th Qtr 7th Qtr 8th QtrAuto Liability $ 460,526 $ 460,526 $ 460,526 $ 460,526 $ 711,513 $ 948,684 $ 948,684 $ 948,684 Personal Injury / General Liability $ 2,072,368 $ 2,072,368 $ 2,072,368 $ 2,072,368 $ 3,201,809 $ 4,269,079 $ 4,269,079 $ 4,269,079 Workers Comp $ 1,013,158 $ 1,013,158 $ 1,013,158 $ 1,013,158 $ 1,565,329 $ 2,087,105 $ 2,087,105 $ 2,087,105 Quarterly Costs Avoided With DriveCam $ 3,546,053 $ 3,546,053 $ 3,546,053 $ 3,546,053 $ 5,478,651 $ 7,304,868 $ 7,304,868 $ 7,304,868 Net Quarter Cost $ 1,043,053 $ 1,743,053 $ 1,743,053 $ 1,743,053 $ 2,425,651 $ 3,351,868 $ 3,701,868 $ 3,701,868 IRR By Quarter 42% 97% 97% 97% 79% 85% 103% 103%
Grand Total Savings $ 41,577,467 NPV @ 10% $ 6,700,420 Internal Rate of Return 88%
1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 5th Qtr 6th Qtr 7th Qtr 8th Qtr $-
$1,000,000 $2,000,000 $3,000,000 $4,000,000 $5,000,000 $6,000,000 $7,000,000 $8,000,000
Costs Avoided By Quarter
Workers Comp Personal Injury Auto Liability Quarters
DriveCam Quarterly ROI Breakdown Quarters 1-8
1st Qtr 2nd Qtr 3rd Qtr 4th Qtr 5th Qtr 6th Qtr 7th Qtr 8th Qtr Auto Liability $ 460,526 $ 460,526 $ 460,526 $ 460,526 $ 711,513 $ 948,684 $ 948,684 $ 948,684 Personal Injury $ 2,072,368 $ 2,072,368 $ 2,072,368 $ 2,072,368 $ 3,201,809 $ 4,269,079 $ 4,269,079 $ 4,269,079 Workers Comp $ 1,013,158 $ 1,013,158 $ 1,013,158 $ 1,013,158 $ 1,565,329 $ 2,087,105 $ 2,087,105 $ 2,087,105
DriveCam ROI Calculator Assuming 5 Year Agreement
Confidential and Proprietary Date Produced: 062205 DriveCam
Year 1 Year 2 Year 3 Year 4 Year 5NUMBER OF VEHICLES EQUIPPED WITH DRIVECAMTotal Fleet Size 38000 38000 38000 38000 38000DriveCam Units Committed 10000 10000 10000 4000 4000Cumulative % DriveCam Units 26% 53% 79% 89% 100%
FLEET LOSS-RELATED COSTS (without DriveCam)Auto Liability $ 10,000,000 $ 10,000,000 $ 10,300,000 $ 880,000 $ 1,408,000 $ 1,877,333 Personal Injury / General Liability $ 45,000,000 $ 45,000,000 $ 46,350,000 $ 1,650,000 $ 2,640,000 $ 3,520,000 Workers Comp $ 22,000,000 $ 22,000,000 $ 22,660,000 $ 1,210,000 $ 1,936,000 $ 2,581,333 Annual Costs (without DriveCam) $ 77,000,000 $ 77,000,000 $ 79,310,000 $ 81,689,300 $ 84,139,979 $ 86,664,178
IndustryPER VEHICLE LOSS-RELATED COSTS StandardAuto Liability 1800 $ 450 $ 263 $ 271 $ 279 $ 288 $ 296 Personal Injury / General Liability 500 $ 1,250 $ 1,184 $ 1,220 $ 1,256 $ 1,294 $ 1,333 Workers Comp $ 625 $ 579 $ 596 $ 614 $ 633 $ 652 Annual Cost/Vehicle (without DriveCam) $ 2,325 $ 2,026 $ 2,087 $ 2,150 $ 2,214 $ 2,281 Type of Fleet 1=Pass Carrying, 2=Service, 3=Distribution 2Setup (lump sum) $ 60,000
Reduction in Loss Events with DriveCam 50% 70.0% 70.0% 70.0% 70.0% 70.0%
MONTHLY INVESTMENT IN DRIVECAM SOLUTION Year 1 Year 2 Year 3 Year 4 Year 5Monthly Initial Setup (Amortized Over 60 Months) $ (1,000) $ (1,000) $ (1,000) $ (1,000) $ (1,000)Monthly Equipment Install Cost $ (58,333) $ (58,333) $ (58,333) $ (23,333) $ (23,333)Monthly Service Fee $ (600,000) $ (1,200,000) $ (1,800,000) $ (2,040,000) $ (2,280,000)Monthly Investment (Setup + Service) $ (659,333) $ (1,259,333) $ (1,859,333) $ (2,064,333) $ (2,304,333)Investment Per Unit Per Month $ (66) $ (63) $ (62) $ (61) $ (61)
Annual Investment in DriveCam Solution $ (7,912,000) $ (15,112,000) $ (22,312,000) $ (24,772,000) $ (27,652,000)
REDUCTION IN LOSS-RELATED COSTSAuto Liability $ 1,842,105 $ 3,794,737 $ 5,862,868 $ 6,843,922 $ 7,878,562 Personal Injury / General Liability $ 8,289,474 $ 17,076,316 $ 26,382,908 $ 30,797,648 $ 35,453,528 Workers Comp $ 4,052,632 $ 8,348,421 $ 12,898,311 $ 15,056,628 $ 17,332,836 Total Costs Avoided With DriveCam $ 14,184,211 $ 29,219,474 $ 45,144,087 $ 52,698,197 $ 60,664,925 Net Annual Cost $ 6,272,211 $ 14,107,474 $ 22,832,087 $ 27,926,197 $ 33,012,925 IRR By Year 79% 93% 102% 113% 119%
Grand Total (Savings) $ 104,150,893 NPV @ 10% $ 48,295,801 Internal Rate of Return 107%
Turquoise colored cells may be modified to accommodate your specific requirements. If the cell turns red, the number of plants using GSQA is going down.
Turquoise colored cells may be modified to accommodate your specific requirements. If the cell turns red, the number of plants using GSQA is going down.
Auto Liability
Personal Injury
Workers Comp
$- $20.00 $40.00 $60.00 $80.00 $100.00 $120.00 $140.00
Net Fleet Savings(over 5 Year Period)
Dollars in (mil)
Cat
egor
y
DriveCam ROI Calculator
Confidential and Proprietary Information Date Produced: 062205 DriveCam
Year 1 Year 2 Year 3 Year 4 Year 5NUMBER OF VEHICLES EQUIPPED WITH DRIVECAMTotal Fleet Size 38000 38000 38000 38000 38000DriveCam Units Committed 10000 10000 10000 4000 4000Cumulative % DriveCam Units 26% 53% 79% 89% 100%
FLEET LOSS-RELATED COSTS (without DriveCam)Auto Liability $ 10,000,000 $ 10,000,000 $ 10,300,000 $ 10,609,000 $ 10,927,270 $ 11,255,088 Personal Injury / General Liability $ 45,000,000 $ 45,000,000 $ 46,350,000 $ 47,740,500 $ 49,172,715 $ 50,647,896 Workers Comp $ 22,000,000 $ 22,000,000 $ 22,660,000 $ 23,339,800 $ 24,039,994 $ 24,761,194 Annual Costs (without DriveCam) $ 77,000,000 $ 77,000,000 $ 79,310,000 $ 81,689,300 $ 84,139,979 $ 86,664,178
Industry StdPER VEHICLE LOSS-RELATED COSTS Per VehicleAuto Liability $ 450 $ 263 $ 271 $ 279 $ 288 $ 296 Personal Injury / General Liability $ 1,250 $ 1,184 $ 1,220 $ 1,256 $ 1,294 $ 1,333 Workers Comp $ 625 $ 579 $ 596 $ 614 $ 633 $ 652 Annual Cost/Vehicle (without DriveCam) $ 2,325 $ 2,026 $ 2,087 $ 2,150 $ 2,214 $ 2,281 Type of Fleet 1=Pass Carrying, 2=Service, 3=Distribution 2
Reduction in Loss Events with DriveCam 50% 70.0% 70.0% 70.0% 70.0% 70.0%
MONTHLY INVESTMENT IN DRIVECAM SOLUTION Year 1 Year 2 Year 3 Year 4 Year 5Monthly Initial Setup (Amortized Over 60 Months) $ (1,000) $ (1,000) $ (1,000) $ (1,000) $ (1,000)Monthly Equipment Install Cost $ (58,333) $ (58,333) $ (58,333) $ (23,333) $ (23,333)Monthly Service Fee $ (600,000) $ (1,200,000) $ (1,800,000) $ (2,040,000) $ (2,280,000)Monthly Investment (Setup + Service) $ (659,333) $ (1,259,333) $ (1,859,333) $ (2,064,333) $ (2,304,333)Monthly Investment Per Unit $ (66) $ (63) $ (62) $ (61) $ (61)
Annual Investment in DriveCam Solution $ (7,912,000) $ (15,112,000) $ (22,312,000) $ (24,772,000) $ (27,652,000)
REDUCTION IN LOSS-RELATED COSTSAuto Liability $ 1,842,105 $ 3,794,737 $ 5,862,868 $ 6,843,922 $ 7,878,562 Auto Liability $ 26.22 Personal Injury / General Liability $ 8,289,474 $ 17,076,316 $ 26,382,908 $ 30,797,648 $ 35,453,528 Personal Injury $ 118.00 Workers Comp $ 4,052,632 $ 8,348,421 $ 12,898,311 $ 15,056,628 $ 17,332,836 Workers Comp $ 57.69 Total Costs Avoided With DriveCam $ 14,184,211 $ 29,219,474 $ 45,144,087 $ 52,698,197 $ 60,664,925 Net Annual Cost $ 6,272,211 $ 14,107,474 $ 22,832,087 $ 27,926,197 $ 33,012,925 IRR By Year 79% 93% 102% 113% 119%
Grand Total (Savings) $ 104,150,893 NPV @ 10% $ 48,295,801 Internal Rate of Return 107%
DriveCam Pricing Overview 1<=U<= 500 500<U<= 1001 1001<U<=1500 1500<U<=2000 2001+ Units(allows for setup price variance by volume) $70 Per Unit $70 Per Unit $70 Per Unit $70 Per Unit $70 Per Unit(allows for unit price variance by volume) $60 Per Unit $60 Per Unit $60 Per Unit $60 Per Unit $60 Per UnitCustom Pricingno
yes no
$ 500 $ 500 $ 500 $ 500 $ 500 Minimum Monthly $ 15,000 $ 15,000 $ 15,000 $ 15,000 $ 15,000 $ 15,000 DriveCam Data Storage Per Month $ 500 Total Monthly Pricing, Regardless of Units $ 15,000
Setup Fee (amortized over 60 months) $ 60,000
Industry Standard per Vehicle Pass Carrying Service Distribution Category 3 Category 4 Category 5
Bodily Injury $ 400 $ 450 $ 500 $ 100 $ 200 $ 300 Auto PD $ 1,700 $ 1,250 $ 1,100 $ 500 $ 600 $ 700 Workers Comp $ 650 $ 625 $ 600 $ 900 $ 1,000 $ 1,100 Percent saved 50.0% 50.0% 50.0% 35.0% 70.0% 80.0%
Inflation (on vehicle loss lines 15, 16, 17) 1.00 1.03 1.03 1.03 1.03 1.03
Year 1 Year 2 Year 3 Year 4 Year 5 All Years $ (7,912,000) $ 14,184,211 $ (15,112,000) $ 29,219,474 $ (22,312,000) $ 45,144,087 $ (24,772,000) $ 52,698,197 $ (27,652,000) $ 60,664,925 $ (97,760,000)
$ 201,910,893 IRR Calculation Annual 79% 93% 102% 113% 119% 107%
Quarterly DriveCam Investment $ (2,503,000) $ (1,803,000) $ (1,803,000) $ (1,803,000) $ (3,053,000) $ (3,953,000) $ (3,603,000) $ (3,603,000) $ (22,124,000) Return net of Investment $ 3,546,053 $ 3,546,053 $ 3,546,053 $ 3,546,053 $ 5,478,651 $ 7,304,868 $ 7,304,868 $ 7,304,868 $ 41,577,467 IRR 42% 97% 97% 97% 79% 85% 103% 103% 88%
Turquoise colored cells may be modified to accommodate your specific requirements. If the cell turns red, the number of plants using GSQA is going down.