ROI Example Spread Sheet

The populated initial project spread sheet can be downloaded here.

The populated ongoing project spread sheet can be downloaded here.

The populated life of project spread sheet can be downloaded here.

       
         
User data entry or item description        
         
Formula cells: Totals are calculated and filled in automatically.      
Formula cells: Cells are filled in and calculated automatically but feel free to modify    
Formula cells: Totals are calculated automatically.  Do Not Change.      
         
Investment overview        
Project name:    <Project name>        
Project sponsor: <Project sponsor>        
Date of request: <Date>        
General description of benefits: <Description of benefits>    
   
   
   
         
Analysis Inputs        
Equipment Unit Replacement Cost ($) $60,000      
Equipment Units Not Requiring Replacement (per year) 2      
Cost of Replacement Board ($) $5,752      
Initial Investment (Number of Replacement Boards) 5      
Initial Integration Manpower (Troubleshoot/Debug) $0      
Initial Integration Material Costs (Troubleshoot/Debug) $0      
Initial Investment Additional Charge to Meet Minimum $0      
Total Initial Investment Required ($10K Minimum) $28,760      
Annual Cost of Replaced Equipment $18,000      
         
ROI measures: Circuit Board Replacement Year 0 Year 1 Year 2 Year 3
Cost of capital 15%      
Net present value (of Year 0 Investment) $42,774        
Return on investment   46% 131% 249%
Payback (in years) 1.42      
ROI measures: Equipment Replacement Year 0 Year 1 Year 2 Year 3
  15%      
Net present value (of Year 0 Investment) ($78,380)        
Return on investment    0% 0% 0%
Payback (in years) After Year 3      
        PAGE 1 OF 3
         
Cash flow and ROI statement: Circuit Board Replacement      
BENEFIT DRIVERS  YEAR 
0 1 2 3
Value of  Money Not Spent Replacing Equipment Per Year   18,000 36,000 54,000
Eliminated Maintenance Personel Cost        
Eliminated Maintenance Supplies/Services Cost        
<Benefit driver>  Money Not Spent Not Replacing Equipment        
<Benefit driver>        
<Benefit driver>        
Total annual benefits   $18,000 $36,000 $54,000
Implementation filter 85% 90% 95%
Total benefits realized $15,300 $32,400 $51,300
         
Costs: Circuit Board Replacement Year 0 Year 1 Year 2 Year 3
Total $28,760 $0 $0 $0
         
Benefits: Circuit Board Replacement Year 0 Year 1 Year 2 Year 3
Annual benefit flow (28,760) 15,300 32,400 51,300
Cumulative benefit flow (28,760) (13,460) 18,940 70,240
         
Discounted benefit flow: Circuit Board Replacement Year 0 Year 1 Year 2 Year 3
Discounted costs 28,760 0 0 0
Discounted benefits 0 13,304 24,499 33,731
Total discounted benefit flow (28,760) 13,304 24,499 33,731
Total cumulative discounted benefit flow (28,760) (15,456) 9,043 42,774
ROI MEASURES        
Initial investment: Circuit Board Replacement Year 0 Year 1 Year 2 Year 3
Initial investment $28,760 $0 $0 $0
<Cost Driver>        
<Cost Driver>        
<Cost Driver>        
Total costs $28,760 $0 $0 $0
         
ROI measures: Circuit Board Replacement Year 0 Year 1 Year 2 Year 3
Initial investment $28,760      
Cost of capital 15%      
Net present value (of Year 0 Investment) $42,774        
Return on investment   46% 131% 249%
Payback (in years) 1.42      
        PAGE 2 OF3
         
Cash flow and ROI statement: Equipment Replacement      
BENEFIT DRIVERS  YEAR 
0 1 2 3
<Benefit driver>        
<Benefit driver>        
<Benefit driver>        
<Benefit driver>        
<Benefit driver>        
<Benefit driver>        
Total annual benefits   $0 $0 $0
Implementation filter 85% 90% 95%
Total benefits realized $0 $0 $0
         
Costs: Equipment Replacement Year 0 Year 1 Year 2 Year 3
Total $1 $18,000 $36,000 $54,000
         
Benefits: Equipment Replacement Year 0 Year 1 Year 2 Year 3
Annual benefit flow (1) (18,000) (36,000) (54,000)
Cumulative benefit flow (1) (18,001) (54,001) (108,001)
         
Discounted benefit flow: Equipment Replacement Year 0 Year 1 Year 2 Year 3
Discounted costs 1 0 0 0
Discounted benefits 0 0 0 0
Total discounted benefit flow (1) 0 0 0
Total cumulative discounted benefit flow (1) (1) (1) (1)
         
Initial investment: Equipment Replacement Year 0 Year 1 Year 2 Year 3
Initial investment $1 $0 $0 $0
<Cost Driver>        
<Cost Driver>        
<Cost Driver>        
Total costs $1 $0 $0 $0
         
ROI measures: Equipment Replacement Year 0 Year 1 Year 2 Year 3
Costs: Equipment Replacement $1 $18,000 $36,000 $54,000
Cost of capital 15%      
Net present value (of Year 0 Investment) ($78,380)        
Return on investment   0% 0% 0%
Payback (in years) After Year 3      
        PAGE 3 OF 3