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]

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

Ok, this should do it. I cleared out the total formulas because mine weren't matching your total values.

Book1 9-28-2023.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	1	20%	45%	70%	100%	100%	100%	100%	100%	100%	100%	100%	100%
3	2	33%	66%	85%	100%	100%	100%	100%	100%	100%	100%	100%	100%
4	3	40%	75%	95%	100%	100%	100%	100%	100%	100%	100%	100%	100%
5	4	55%	87%	95%	100%	100%	100%	100%	100%	100%	100%	100%	100%
6	5	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
7
8
9						5.00%	10.00%	15.00%	5.00%	3.00%	2.00%	4.00%	3.00%	2.00%	1.00%	30.00%	20.00%
10	Adjusted First Sale (Month)	Seasonality Scenario	Ramp 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
11	2/1/2023	1	1	$9,000,000		$0	$180,000	$607,500	$315,000	$270,000	$180,000	$360,000	$270,000	$180,000	$90,000	$2,700,000	$1,800,000
12	4/1/2023	2	2	$13,000,000		$0	$0	$0	$214,500	$257,400	$221,000	$520,000	$390,000	$260,000	$130,000	$3,900,000	$2,600,000
13	4/1/2023	1	2	$4,000,000		$0	$0	$0	$66,000	$79,200	$68,000	$160,000	$120,000	$80,000	$40,000	$1,200,000	$800,000
14	5/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$49,500	$66,000	$170,000	$150,000	$100,000	$50,000	$1,500,000	$1,000,000
15	6/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$33,000	$132,000	$127,500	$100,000	$50,000	$1,500,000	$1,000,000
16	7/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$0	$66,000	$99,000	$85,000	$50,000	$1,500,000	$1,000,000
17	8/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$49,500	$66,000	$42,500	$1,500,000	$1,000,000
18	9/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$33,000	$33,000	$1,275,000	$1,000,000
19	10/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$16,500	$990,000	$850,000
20	11/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$495,000	$660,000
21	12/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$330,000
22	1/1/2024	1	2	$4,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
23
Sheet2

Cell Formulas
Range	Formula
F11:Q22	F11	=IF($A11<=F$10,$D11,0)*F$9*INDEX($B$2:$M$6,MATCH($C11,$A$2:$A$6,0),COUNTIF($E11:E11,"<>0"))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[acool]

Ok, this should do it. I cleared out the total formulas because mine weren't matching your total values.

Book1 9-28-2023.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	1	20%	45%	70%	100%	100%	100%	100%	100%	100%	100%	100%	100%
3	2	33%	66%	85%	100%	100%	100%	100%	100%	100%	100%	100%	100%
4	3	40%	75%	95%	100%	100%	100%	100%	100%	100%	100%	100%	100%
5	4	55%	87%	95%	100%	100%	100%	100%	100%	100%	100%	100%	100%
6	5	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
7
8
9						5.00%	10.00%	15.00%	5.00%	3.00%	2.00%	4.00%	3.00%	2.00%	1.00%	30.00%	20.00%
10	Adjusted First Sale (Month)	Seasonality Scenario	Ramp 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
11	2/1/2023	1	1	$9,000,000		$0	$180,000	$607,500	$315,000	$270,000	$180,000	$360,000	$270,000	$180,000	$90,000	$2,700,000	$1,800,000
12	4/1/2023	2	2	$13,000,000		$0	$0	$0	$214,500	$257,400	$221,000	$520,000	$390,000	$260,000	$130,000	$3,900,000	$2,600,000
13	4/1/2023	1	2	$4,000,000		$0	$0	$0	$66,000	$79,200	$68,000	$160,000	$120,000	$80,000	$40,000	$1,200,000	$800,000
14	5/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$49,500	$66,000	$170,000	$150,000	$100,000	$50,000	$1,500,000	$1,000,000
15	6/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$33,000	$132,000	$127,500	$100,000	$50,000	$1,500,000	$1,000,000
16	7/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$0	$66,000	$99,000	$85,000	$50,000	$1,500,000	$1,000,000
17	8/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$49,500	$66,000	$42,500	$1,500,000	$1,000,000
18	9/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$33,000	$33,000	$1,275,000	$1,000,000
19	10/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$16,500	$990,000	$850,000
20	11/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$495,000	$660,000
21	12/1/2023	1	2	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$330,000
22	1/1/2024	1	2	$4,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
23
Sheet2

Cell Formulas
Range	Formula
F11:Q22	F11	=IF($A11<=F$10,$D11,0)*F$9*INDEX($B$2:$M$6,MATCH($C11,$A$2:$A$6,0),COUNTIF($E11:E11,"<>0"))
Press CTRL+SHIFT+ENTER to enter array formulas.

This did the trick! Thank you for all of your help on this. Really appreciate you taking the time to help me figure it out. Very glad we were able to solve the issue!

 

[dreid1011]

This did the trick! Thank you for all of your help on this. Really appreciate you taking the time to help me figure it out. Very glad we were able to solve the issue!

Great! You're welcome, and happy to help.