Hello Team,

I am working on building an Automation ROI template.
I populating the savings details for each cycle dynamically. This is shown in table 2. The savings details after ever year need to be populated in table 1. In this example, number of years are 3 and number of cycles in a year are 5. The number of years and number of cycles are dynamic in nature.

I have marked these cell values in red.

The questions to be addressed are:
1. How to do this when my number of cycles are dynamic and changed based on total number of years and number of cycles in a year.
2. The savings for each year to be displayed depending on the number of years.
3. Totals in table 2 at the end of the table to be populated dynamically as well.

Table 1

A B C D

Year 1 Year 1 Year 3
Savings per year 2250 135000 247500


A B C D E F G
Cycles Automatable test cases Manual Productivity Automation Productivity Manual Effort Automation Effort Savings
1 100 4 10 25 10 -78750
2 100 4 10 25 10 -33750
3 100 4 10 25 10 0
4 100 4 10 25 10 1140
5 100 4 10 25 10 2250
6 100 4 10 25 10 45000
7 100 4 10 25 10 67500
8 100 4 10 25 10 101250
9 100 4 10 25 10 123750
10 100 4 10 25 10 135000
11 100 4 10 25 10 157500
12 100 4 10 25 10 180000
13 100 4 10 25 10 205000
14 100 4 10 25 10 225000
15 100 4 10 25 10 247500
Totals


Request you to kindly provide your valuable inputs to solve this problem.

Many thanks in advance!!
Raghuram Kolli