Calculate Software Automation ROI

Software automation ROI" refers to the Return on Investment (ROI) associated with implementing software automation in a business or organizational context. ROI is a measure used to evaluate the financial benefits of an investment relative to its cost. In the case of software automation, ROI assesses the value gained from automating specific processes or tasks compared to the expenses incurred in implementing and maintaining the automation.

Here are the following calculation parameters: (used google sheets)
 

Considering 2 years(12 months) of ROI projection:

Data Calculation parameters: 

• Number of test-cases can be automated: (B5 -(B5*B6)/100) 
• Automation of the test-cases needs: =(($B$7*$B$11)+B9)/B12
• Time-to-market acceleration: =(B10*B7*B8)/8

Statistics calculation parameters: 

Saved manual testing time (accumulated), hrs = 

• =$B$10*F21*$B$8*0
• =H21+($B$10*F22*$B$8) ...

Automation costs (accumulated), hrs =

• =D21
• =D22+I21 ...

Autotests run and support cost, hrs= 

• 0.00
• =$B$13 ...

Test cases automated = 

• =IF((E21/$B$11)*B12 >= $B$7, $B$7, (E21/$B$11)*B12)
• =IF(((E22/$B$11)*$B$12) + $F21 >= $B$7, $B$7, (($E22/$B$11)*$B$12) + F21) ...

Autotests development cost, hrs using standard working hrs/month =

• =IF($B$14 <= $B$18 * $B$12, $B$14, $B$18*$B$12)
• =IF(SUM($E$21:E21)+$B$18 >= $B$14, MAX(0, $B$14 - SUM($E$21:E21)), $B$18*$B$12) ...

Automation costs, hrs =

• =E21+G21
• =E22+G22 ...

Saved manual testing time, hrs = 

• =$B$10*F21*$B$8*0
• =$B$10*F22*$B$8 ...

ROI, hrs = 

• =H21-I21
• =H22-I22 ...

Total saved in $ (- ve means in loss) = 

• =(B45*B17)