VehicleV2.xls
ROI Calculation for changing B/R Lights to LED
Written by: Bryan Hart Version: Date: 2.10 (for free distribution) 6 April 2002
Instructions for use: Enter required data in blue filled cells, and select data in yellow filled cells Typical values have already been entered for you User's Manual Welcome to the latest sales tool to help the heavy-duty trucking customer understand how to choose the best value in vehicular lighting systems. This spreedsheet solution gives you a quick way to show a large or small fleet the value of using Wonder Lite LED lighting products. You may use the default information to generate a quick example, or you can modify the defaults to customize the information. You even have the ability to survey a sample of your customer’s fleet for a completely personalized and accurate analysis of your customer’s fleet costs. You will be able to dramatically demonstrate how you can save him up to 99% of his fleet lighting maintenance costs! In just minutes, you can give your customer a quick report card or a full-scale professional electronic presentation. Filling in the "VARIABLE ENTRY SHEET" Fill in the “FLEET INFORMATION”. You may need to abbreviate the customer’s name somewhat if the company has a long title. Note that the company name will appear on all analysis sheets. Fill in how many trailers and trucks (if any) that this fleet has. You may leave blank if the fleet does not use straight trucks. If you wish to consider tractors instead, enter the number of tractors and correct the default statistics listed below. Enter the LIFE SPAN of an average vehicle. If trailers and trucks / tractors are kept for different lengths of time, select the time frame of the vehicle that you are most interested in speaking about with your customer. Labor / Downtime costs – Costs and repair times are defaults based on accepted industry averages. Not all fleets consider “downtime”. But, remember that it represents the revenue lost while the unit is out of service. Labour is also something that will change from fleet to fleet. You may change either of these by typing in a new value. Lamp cost / Life span – Cost is based on 30% over WD green (standard carton). Fill in another cost if available or desired (do not enter zero). Life span is based on lab observations and generally accepted information with regard to lamp field performance. Again, you may fill in a different number if your customer has better information or is uncomfortable with the default. Average Vehicle Statistics – Note that the defaults reflect a “worse case” scenario using bulb replaceable lamps almost exclusively. All you need to do is “pull down” the menu under “type” to select another type of lamp (i.e., sealed, LED, or B/R). Enter the quantity of each type of lamp. If the fleet uses a uniform lighting specification, you can completely personalize this presentation from your laptop. Merely enter the trailer and truck specs here. Note if you are entering data for a truck or tractor, include back up lamps as “ST/T” lamps. This means that only the cells that are blue Cells that are yellow need to be filled in need to be selected from the dropdown list
NPV for Vehical Lifetime Sheet Nominal Calculation Cost per light per year = Initial Cost(Materials)+number of replacementsX(labour + downtime cost) inflated by inflation rate NPV calculation NPV is calculated for the total lifetime of the vehicle (see financial calculation below) RESULTS This is where the real power of this tool shows. At this point, you’ve done all the hard work. The data is in place and now it’s time to see where you can help the fleet. Results will be VERY dramatic when “Downtime” is considered. However, the results will be very impressive even when the fleet wishes to ignore it. Please note that the answer data is based on the information that you put into the variable screen. If the fleet has many vehicles and they keep them for 10 years, expect to see a HUGE number in expense and savings. Remember that these costs continue to add up over the years that a vehicle is kept. This is the power of this illustration. It helps the customer understand the cost of his total fleet over the full life of the vehicle. You can show the customer where money is being lost where he thinks that he’s only losing nickels and dimes. Results sheets and ROI sheets The “Comparison” box shows the cost of an INDIVIDUAL B/R, sealed, or LED over the LIFE SPAN entered in the variable screen. This is a good place to start educating the customer to the message of “Life Cycle Value Analysis”. Understanding this box makes the rest of the illustration easy to understand. “Lighting Systems” lists the percentage of lamp types used in the customer’s fleet. “Predicted Life Cycle Costs” takes the comparison (individual) information and applies it to the number of vehicles that you listed in the variable screen. Total fleet costs and the cost of an average vehicle are listed. “Cost Drivers” shows to what extent a type of lamp is driving maintenance costs. This gives the customer a look at where the greatest potential savings are. “Current Vs….” The last two boxes compare the fleets CURRENT specification to a completely SEALED fleet or a completely LED fleet. NOTE: If the fleet is currently mostly or entirely sealed, the first box will show no cost savings. This illustration is best used to show the benefits of UPGRADING to SEALED or LED. "ROI FORM" calculates the Return on Investment for upgrades from current configurations. And Payback periods for investements
Notes on the financial calculations NPV for Vehical Lifetime The calculation of the discounted projected cash flows of the investment derives the Net Present Value (NPV). The NPV allows you to determine the value of $1 one or more years from the date of the calculation. What would you rather have: $1 today or $1 one year from now? $1 in today’s currency is worth $1. However, $1 one year from now is worth less than $1 today because of the time value of money. For example, assuming that the discount rate, or investments yield rate, is 10%, $1 today is worth $1.10 one year from now. However, $1 one year from now is worth $0.91 in today’s dollars because the interest earned on the $0.91 over the course of a year at the investments yield rate of 10% would be $0.09. Adding the interest earned during the year, $0.09, to the net present value, $0.91, equals $1 one year from now. The formula for NPV is as follows: NPV = CF1/(1+r)^1 + CF2/(1+r)^2 …. Etc Payback period The payback period calculation determines the number of years that are required for the discounted projected cash flows to equal the initial investment. For example, assuming that the initial investment for a BI project was $1,000,000 and the average annual NPV saving of that BI project was $250,000, then the payback period for the BI project would be 4 years or $1,000,000/$250,000. The payback period formula is as follows: Payback period = Initial Investment/(NPV of Savings / Yearsn) Return on Investment (ROI) The ROI calculation evaluates the NPV projected cash flows derived from the savings generated by the BI project divided by the initial investment. With this financial measure, one can assess the benefit of the project over the initial costs. For example, assuming that the total NPV saving of a BI project was $1,500,000 and the initial investment was $1,000,000. The ROI for the BI project would be 150% of the initial investment. The ROI formula is as follows: ROI = NPV of Savings/Initial Investment x 100 For any problems of queries mail me at: telec@netactive.co.za
Variable Entry Sheet
Input Information Sheet for Enter Your Name in here
Fleet information
Company Name: Vehicles in fleet: Average Vehicle Life span (in years): Currency: Enter Your Name in here Trailers 15 R 80 Straight/Tractor 80 12.0% 14.0%
Lamp Costs/Life Span
Lamp B/R ST/T B/R Bulb Sealed ST/T LED ST/T Current Cost (each) R 20.00 R 2.50 R 6.00 R 110.00 R 0.00 R 35.00 R 1.60 R 40.00 0.16 Yrs 1.00 Yrs 5.00 Yrs 6.25 1.00 0.20 0.16 Yrs 1.00 Yrs 5.00 Yrs Mean Time Between Changes Expected Number of Bulb Replacements per Year 6.25 1.00 0.20
Inflation Rate
Discount Rate/Investment Yeild Rate
Labour/ Downtime Costs
B/R M/C Average Labour/OH (per hr): Downtime Costs (per hr): Repair Time (B/R): Repair Time (Sealed): Repair Time (LED): R R 100.00 B/R Bulb 300.00 Sealed MC 0.50 hr LED M/C 0.25 hr 0.50 hr
Average Vehicle Statistics
Trailer Straight/Tractor M/C Front Type Qty B/R 4 B/R 2 M/C Back Type Qty B/R 4 B/R 2 ST/T Type B/R B/R Qty 6 4 Type B/R B/R Licence Qty 1 1 Centre Turn Type Qty B/R 4 B/R 0 ID Type B/R B/R Qty 0 0
Calculated Metrics
Labour and Overhead per hour X B/R repair time: Sealed LED Cost of Downtime per hour X repair time: B/R Sealed LED R 50.00 R 25.00 R 50.00 R 150.00 R 75.00 R 150.00
Exponential Distribution Curve for Lighting Lifetime
D:\Docstoc\Working\pdf\1960847b-ad87-4fd2-b9ec-8b706718b48b.xls1960847b-ad87-4fd2-b9ec-8b706718b48b.xls
8/14/20082:32 PM
3
Variable Entry Sheet
xpected Number of
D:\Docstoc\Working\pdf\1960847b-ad87-4fd2-b9ec-8b706718b48b.xls1960847b-ad87-4fd2-b9ec-8b706718b48b.xls
8/14/20082:32 PM
4
NPV for Vehical Lifetime
Enter Your Name in here Trailer Results
Life Cycle Cost per lighting type for Avg Lifespan of Vehical
Year 1 B/R Nominal Sealed LED B/R NPV Sealed LED ST/T Marker ST/T Marker ST/T Marker ST/T Marker ST/T Marker ST/T Marker 1,285.63 1,468.75 112.00 103.20 172.00 88.00 1,285.63 1,468.75 112.00 103.20 172.00 88.00 Year 2 1,417.50 1,645.00 118.72 115.58 69.44 98.56 1,243.42 1,442.98 104.14 101.39 60.91 86.46 Year 3 1,587.60 1,842.40 132.97 129.45 77.77 110.39 1,221.61 1,417.67 102.31 99.61 59.84 84.94 Year 4 1,778.11 2,063.49 148.92 144.99 87.11 123.63 1,200.17 1,392.80 100.52 97.86 58.79 83.45 Year 5 1,991.49 2,311.11 166.79 162.39 97.56 138.47 1,179.12 1,368.36 98.75 96.15 57.76 81.99 Year 6 2,230.46 2,588.44 186.81 181.87 109.27 155.09 1,158.43 1,344.35 97.02 94.46 56.75 80.55 Year 7 2,498.12 2,899.05 209.23 203.70 122.38 173.70 1,138.11 1,320.77 95.32 92.80 55.75 79.13 Year 8 2,797.89 3,246.94 234.33 228.14 137.06 194.54 1,118.14 1,297.60 93.65 91.17 54.78 77.75 Year 9 3,133.64 3,636.57 262.45 255.52 153.51 217.88 1,098.53 1,274.83 92.00 89.57 53.81 76.38 Year 10 3,509.68 4,072.96 293.95 286.18 171.93 244.03 1,079.25 1,252.47 90.39 88.00 52.87 75.04 Year 11 3,930.84 4,561.71 329.22 320.52 192.56 273.31 1,060.32 1,230.49 88.81 86.46 51.94 73.72 Year 12 4,402.54 5,109.12 368.73 358.99 215.67 306.11 1,041.72 1,208.91 87.25 84.94 51.03 72.43 Year 13 4,930.84 5,722.21 412.97 402.06 241.55 342.85 1,023.44 1,187.70 85.72 83.45 50.14 71.16 Year 14 5,522.55 6,408.88 462.53 450.31 270.54 383.99 1,005.49 1,166.86 84.21 81.99 49.26 69.91 Year 15 6,185.25 7,177.95 518.03 504.35 303.00 430.07 987.85 1,146.39 82.74 80.55 48.39 68.69
Comparison per light for Lifetime of Vehicle
Bulb Replaceable ST/T: R 16,841.22 Sealed Lighting ST/T: LED ST/T: Marker: LED Marker R 1,414.83 R 934.03 R 1,371.62 R 1,169.59
Marker:
R 19,520.93
D:\Docstoc\Working\pdf\1960847b-ad87-4fd2-b9ec-8b706718b48b.xls1960847b-ad87-4fd2-b9ec-8b706718b48b.xlsNPV for Vehical Lifetime
8/14/20082:32 PM
5
NPV for Vehical Lifetime
D:\Docstoc\Working\pdf\1960847b-ad87-4fd2-b9ec-8b706718b48b.xls1960847b-ad87-4fd2-b9ec-8b706718b48b.xlsNPV for Vehical Lifetime
8/14/20082:32 PM
6
Trailor Results
Enter Your Name in here Trailer Results
Comparison per light for Lifetime of Vehicle
Bulb Replaceable ST/T: R 16,841.22 Sealed Lighting B/R: ST/T: LED ST/T: Marker: LED Marker R 1,414.83 R 934.03 R 1,371.62 R 1,169.59 Sealed: LED: 100% % 0% % 0% % 100% 0% 0%
Lighting System Average Split for Truck Sample
ST/T Lighting Marker Lamps
Marker:
R 19,520.93
Predicted Life Cycle Cost per Vehicle
ST/T Lamps: Marker Lamps R 8,083,787.42 R 20,301,763.43 R 8,083,787.42 R 20,301,763.43 from (B/R) from (B/R)
Cost Drivers
Bulb Replaceable Lamps: % of Lamps % of Cost Sealed/LED Lamps: % of Lamps % of Cost
Total:
R 28,385,550.84
Per Vehicle:
R 354,819.39
Current Configuration vs. If all Vehicle were using sealed lights
Current Specification ST/T: Marker: Total Per Vehicle: Savings pre Vehicle: R 8,083,787.42 R 20,301,763.43 R 28,385,550.84 R 354,819.39 R 328,499.41 Sealed Lighting R 679,118.12 R 1,426,479.65 R 2,105,597.77 R 26,319.97 92.6% %
Current Configuration vs. If all vehicles were using LED lights
Current Specifications ST/T: Marker: Total Per Vehicle: Savings pre Vehicle: R R R R R 8,083,787.42 20,301,763.43 28,385,550.84 354,819.39 334,010.47 LED Lighting R R R R 448,335.50 1,216,378.00 1,664,713.50 20,808.92 94.14%
D:\Docstoc\Working\pdf\1960847b-ad87-4fd2-b9ec-8b706718b48b.xls1960847b-ad87-4fd2-b9ec-8b706718b48b.xls 8/14/20082:32 PM
7
Trailor Results
Marker Lamps % % %
%
D:\Docstoc\Working\pdf\1960847b-ad87-4fd2-b9ec-8b706718b48b.xls1960847b-ad87-4fd2-b9ec-8b706718b48b.xls 8/14/20082:32 PM
8
Trailor ROI
Enter Your Name in here Trailer ROI
Lighting materials comparison cost B/R vs. Sealed vs. LED Trailer
Marker: Tail: 13 6 R R B/R 0.00 20.00 R R Cost 0.00 R 120.00 R B/R 120.00 Sealed 1.60 6.00 R R Sealed Total: R 56.80 Total: R Costs 20.80 R 36.00 R LED 40.00 R 110.00 R Costs 520.00 660.00 LED 1,180.00 NOTE: LED Standard configuration has a Sealed License Lamp
Lighting Package Costs Per Vehicle:
Total:
R
Current lighting materials cost per trailer
Current Markers: Current ST/T lights: B/R 13 6 Costs R 0.00 R 120.00 Sealed 0 0 Costs R 0.00 R 0.00 LED 0 0 Costs R 0.00 R 0.00 Totals R 0.00 R 120.00 R 120.00
Current Lighting Cost per Trailer:
Upgrade Costs
Conversion Cost Per Vehicle: FALSE Downtime Estimated Fleet Conversion Costs: NPV Estimated Fleet Saving: Payback Period: ROI: Years Sealed Spec All Fit Materials: R 56.80 Labour: R 475.00 Downtime: R Total: R 531.80 R 42,544.00 Materials Labour Downtime Total: Sealed Retro Fit R R R R R 56.80 475.00 531.80 42,544.00 LED Spec All Fit Materials R 1180.00 Labour R 950.00 Downtime R Total: R 2130.00 R 170,400.00 Materials Labour Downtime Total: LED Retro Fit R R R R R
R
26279953.08 0.02 61771% Years
R
26,279,953.08 0.02 61771% Years
R
26,720,837.35 0.10 15681% Years
R
D:\Docstoc\Working\pdf\1960847b-ad87-4fd2-b9ec-8b706718b48b.xls1960847b-ad87-4fd2-b9ec-8b706718b48b.xls 8/14/20082:32 PM
9
Trailor ROI
LED Retro Fit 1,180.00 950 2,130.00 170,400.00
26,720,837.35 0.10 15681%
D:\Docstoc\Working\pdf\1960847b-ad87-4fd2-b9ec-8b706718b48b.xls1960847b-ad87-4fd2-b9ec-8b706718b48b.xls 8/14/20082:32 PM
10
Truck Results
Enter Your Name in here Truck Results
Comparison per light for Lifetime of Vehicle
Bulb Replaceable ST/T: R 16,841.22 Sealed Lighting B/R: ST/T: LED ST/T: Marker: LED Marker R 1,414.83 R 934.03 R 1,371.62 R 1,169.59 Sealed: LED: 100% % 0% % 0% %
Lighting System Average Split for Truck Sample
ST/T Lighting
Marker:
R 19,520.93
Predicted Life Cycle Cost per Vehicle
ST/T Lamps: Marker Lamps R 5,389,191.61 R 7,808,370.55 R 5,389,191.61 R 7,808,370.55 from (B/R) from (B/R)
Cost Drivers
Bulb Replaceable Lamps: % of Lamps % of Cost Sealed/LED Lamps: % of Lamps % of Cost
Total:
R 13,197,562.16
Per Vehicle:
R 164,969.53
Current Configuration vs. If all Vehicle were using sealed lights
Current Specification ST/T: Marker: Total Per Vehicle: Savings pre Vehicle: R 5,389,191.61 R 7,808,370.55 R 13,197,562.16 R 164,969.53 R 152,452.13 Sealed Lighting R 452,745.41 R 548,646.02 R 1,001,391.43 R 12,517.39 92.4% %
Current Configuration vs. If all vehicles were using LED lights
Current Specifications ST/T: Marker: Total Per Vehicle: Savings pre Vehicle: R R R R R 5,389,191.61 7,808,370.55 13,197,562.16 164,969.53 155,385.43 LED Lighting R R R R
D:\Docstoc\Working\pdf\1960847b-ad87-4fd2-b9ec-8b706718b48b.xls1960847b-ad87-4fd2-b9ec-8b706718b48b.xls 8/14/20082:32 PM
11
Truck Results
Marker Lamps 100% % 0% % 0% %
sing LED lights
LED Lighting 298,890.33 467,837.69 766,728.03 9,584.10 94.19% %
D:\Docstoc\Working\pdf\1960847b-ad87-4fd2-b9ec-8b706718b48b.xls1960847b-ad87-4fd2-b9ec-8b706718b48b.xls 8/14/20082:32 PM
12