itsraghus
02-18-2019, 04:19 AM
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
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