Formula for Scenario Analysis

[acool]

Hi Everyone,

Apologies for all of the questions, but was wondering if you could help me out on one last piece of the puzzle I am working on! I would like to create an scenario in the table below which takes a percentage of revenue based on the Ramp Up Period (Table on the Right) starting on the first month where sales occur. I would like to apply these percentages for the first months where sales take place until total revenue is recognized and carried out (100%) in the following months. For example, 2/1/2023 Revenue would be 495,984*.33, 3/1/2023 would be 578,507*.66, 4/1/2023 would be 595,010*.85 and the remainder of the months would be multiplied out by the 100%. I would like to do this for each line item when revenue is first recognized. Any help would be greatly appreciated. Thank You!

 

[dreid1011]

Hi Everyone,

Apologies for all of the questions, but was wondering if you could help me out on one last piece of the puzzle I am working on! I would like to create an scenario in the table below which takes a percentage of revenue based on the Ramp Up Period (Table on the Right) starting on the first month where sales occur. I would like to apply these percentages for the first months where sales take place until total revenue is recognized and carried out (100%) in the following months. For example, 2/1/2023 Revenue would be 495,984*.33, 3/1/2023 would be 578,507*.66, 4/1/2023 would be 595,010*.85 and the remainder of the months would be multiplied out by the 100%. I would like to do this for each line item when revenue is first recognized. Any help would be greatly appreciated. Thank You!

View attachment 99470

Maybe this, in E3 copied across and down:

=IF($A3<=E$2,$C3,0)*LOOKUP(COUNTIF($D3:D3,"<>0"),{1,2,3,4},{1.33,1.66,1.85,2})

 

[acool]

@dreid1011 Essentially what I am trying to do is multiply the percentages by the revenue figures where there is first month of revenue. Depending on the scenario, I would like to multiply by Column B values each time there is a date match. The issue is getting the value from Column B each time and keeping them variable from that point on-I've only been able to make the references absolute or variable. I have not been able to figure out a way to start at column B every time and then work down the columns. So for example, I would like to have Cell D11 Multiply by C2, then cell E11 by D2 Etc. Would you happen to know a fix?

 

[dreid1011]

@dreid1011 Essentially what I am trying to do is multiply the percentages by the revenue figures where there is first month of revenue. Depending on the scenario, I would like to multiply by Column B values each time there is a date match. The issue is getting the value from Column B each time and keeping them variable from that point on-I've only been able to make the references absolute or variable. I have not been able to figure out a way to start at column B every time and then work down the columns. So for example, I would like to have Cell D11 Multiply by C2, then cell E11 by D2 Etc. Would you happen to know a fix?
View attachment 99484

That is essentially what I did. I can change the formula so it makes more sense.

LOOKUP(COUNTIF($D3:D3,"<>0"),{$A$2:$A$6},{$B2:$B$6})

This part counts how many cells in the range are not zero, while extending the range as you drag it across the columns. And then looks up the count (1,2,3 etc) to return the proper %. However, it works better with an empty column between the adjusted first sale column and the first cloumn of data.

See this example of how the countif is returning the numbers based on the layout of your data above:

Book1
	E	F	G	H	I	J	K	L	M	N
18	1	1	2	3	4	5	6	7	8	9
19	1	1	1	1	2	3	4	5	6	7
20	1	1	1	1	2	3	4	5	6	7
21	1	1	1	1	1	2	3	4	5	6
22	1	1	1	1	1	1	2	3	4	5
23	1	1	1	1	1	1	1	2	3	4
24	1	1	1	1	1	1	1	1	2	3
25	1	1	1	1	1	1	1	1	1	2
26	1	1	1	1	1	1	1	1	1	1
Sheet2

Cell Formulas
Range	Formula
E18:N26	E18	=COUNTIF($D3:D3,"<>0")

 

[dreid1011]

Compare the two green sections and see if that is what you would expect for each row as far as count and matching that up with your ramp up schedule.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1					5%	7%	8%	9%	10%	11%	12%	5%	7%	4%	12%	10%
2	Adjusted First Sale (Month)	Seasonality Scenario	Revenue		1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023	6/1/2023	7/1/2023	8/1/2023	9/1/2023	10/1/2023	11/1/2023	12/1/2023	Total
3	2/1/2023	1	$9,000,000		$0	$598,500	$1,045,800	$1,332,000	$1,620,000	$1,800,000	$1,980,000	$2,160,000	$900,000	$1,260,000	$720,000	$2,160,000	$15,576,300
4	4/1/2023	1	$13,000,000		$0	$0	$0	$1,383,200	$1,942,200	$2,405,000	$2,860,000	$3,120,000	$1,300,000	$1,820,000	$1,040,000	$3,120,000	$18,990,400
5	4/1/2023	1	$4,000,000		$0	$0	$0	$425,600	$597,600	$740,000	$880,000	$960,000	$400,000	$560,000	$320,000	$960,000	$5,843,200
6	5/1/2023	1	$5,000,000		$0	$0	$0	$0	$598,500	$830,000	$1,017,500	$1,200,000	$500,000	$700,000	$400,000	$1,200,000	$6,446,000
7	6/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$665,000	$913,000	$1,110,000	$500,000	$700,000	$400,000	$1,200,000	$5,488,000
8	7/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$731,500	$996,000	$462,500	$700,000	$400,000	$1,200,000	$4,490,000
9	8/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$798,000	$415,000	$647,500	$400,000	$1,200,000	$3,460,500
10	9/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$332,500	$581,000	$370,000	$1,200,000	$2,483,500
11	10/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$465,500	$332,000	$1,110,000	$1,907,500
12	11/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$266,000	$996,000	$1,262,000
13	12/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$798,000	$798,000
14	1/1/2024	1	$4,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
15					$0	$640,395	$1,129,464	$3,423,472	$5,234,130	$7,148,400	$9,387,840	$10,861,200	$5,146,700	$7,731,360	$5,205,760	$16,658,400	$66,745,400
16
17
18					1	1	2	3	4	5	6	7	8	9	10	11	12
19					1	1	1	1	2	3	4	5	6	7	8	9	10
20					1	1	1	1	2	3	4	5	6	7	8	9	10
21					1	1	1	1	1	2	3	4	5	6	7	8	9
22					1	1	1	1	1	1	2	3	4	5	6	7	8
23					1	1	1	1	1	1	1	2	3	4	5	6	7
24					1	1	1	1	1	1	1	1	2	3	4	5	6
25					1	1	1	1	1	1	1	1	1	2	3	4	5
26					1	1	1	1	1	1	1	1	1	1	2	3	4
27					1	1	1	1	1	1	1	1	1	1	1	2	3
28					1	1	1	1	1	1	1	1	1	1	1	1	2
29					1	1	1	1	1	1	1	1	1	1	1	1	1
Sheet2

Cell Formulas
Range	Formula
E3:P14	E3	=(IF($A3<=E$2,$C3,0)*D$1)*LOOKUP(COUNTIF($D3:D3,"<>0"),{1,2,3,4},{1.33,1.66,1.85,2})
Q3:Q14	Q3	=SUM($E3:$P3)
E15:Q15	E15	=SUM(E$3:E$14)*(1+E$1)
E18:Q29	E18	=COUNTIF($D3:D3,"<>0")

 

[acool]

@dreid1011 I am still getting an error for some reason-would you be able to write out the formula based on the updated photo that I provided. Thank you for all of your help and thank you for bearing with me here!

 

[dreid1011]

@dreid1011 I am still getting an error for some reason-would you be able to write out the formula based on the updated photo that I provided. Thank you for all of your help and thank you for bearing with me here!

Of course, but I need a few answers first.

-In the updated image, where did the revenue column go?
-Can you put an empty column before the first month (between col B and col C)?
-For each month, what is the calculation? (show me the formula ie: revenue * ramp up %)
-Please provide an example of one row with revenue value and your expected values for each month.

 

[acool]

I have provided another update photo to reflect revenue. And yes, it is fine to have an empty column before the first month. For each month the calculation is Rev Opportunity* Seasonality Factor*the Ramp up Scenario. As mentioned, I am trying to get my scenario number to match column A, provide the corresponding value in column B and draw that out, beginning when there is first revenue recognized. Each Scenario spans from column B-M. I have provided an example of above. In cell F11 I Multiplied $D$11*F$9*B$2. In H12 I multiplied D12*H$9*B$3. I hope this info helps!

 

[dreid1011]

View attachment 99485

I have provided another update photo to reflect revenue. And yes, it is fine to have an empty column before the first month. For each month the calculation is Rev Opportunity* Seasonality Factor*the Ramp up Scenario. As mentioned, I am trying to get my scenario number to match column A, provide the corresponding value in column B and draw that out, beginning when there is first revenue recognized. Each Scenario spans from column B-M. I have provided an example of above. In cell F11 I Multiplied $D$11*F$9*B$2. In H12 I multiplied D12*H$9*B$3. I hope this info helps!

Does the Seasonality Factor work like the Ramp Scenario? All of them * 5% except row 12 * 10%? Or is each column * the factor at the top of each column?

 

[acool]

Does the Seasonality Factor work like the Ramp Scenario? All of them * 5% except row 12 * 10%? Or is each column * the factor at the top of each column?

Seasonality are the numbers above the dates! Each month has its own seasonality %