Day of First Sale Excel Formula

[acool]

Hi Everyone, I am currently trying to create an excel formula that provides revenue figures in the model below based on the adjusted first sale date. For example, because cell A3 has an adjusted first sale date of 2/1/2023, I would like revenue to begin showing in that specific month (Value for 1/1/23 would be 0). I would then like to apply the seasonality factor to the total revenue figure to have it broken out by month. Any help would be greatly appreciated. Thank You!

 

[dreid1011]

Does this look like what you are after?

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	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$99,000,000
4	4/1/2023	1	$13,000,000		$0	$0	$0	$13,000,000	$13,000,000	$13,000,000	$13,000,000	$13,000,000	$13,000,000	$13,000,000	$13,000,000	$13,000,000	$117,000,000
5	4/1/2023	1	$4,000,000		$0	$0	$0	$4,000,000	$4,000,000	$4,000,000	$4,000,000	$4,000,000	$4,000,000	$4,000,000	$4,000,000	$4,000,000	$36,000,000
6	5/1/2023	1	$5,000,000		$0	$0	$0	$0	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$40,000,000
7	6/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$35,000,000
8	7/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$30,000,000
9	8/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$25,000,000
10	9/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$20,000,000
11	10/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$5,000,000	$5,000,000	$5,000,000	$15,000,000
12	11/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$5,000,000	$5,000,000	$10,000,000
13	12/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$5,000,000	$5,000,000
14	1/1/2024	1	$4,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
15					$0	$630,000	$720,000	$2,340,000	$3,100,000	$3,960,000	$4,920,000	$2,300,000	$3,570,000	$2,240,000	$7,320,000	$6,600,000	$0
Sheet2

Cell Formulas
Range	Formula
E3:P14	E3	=IF($A3<=E$2,$C3,0)
Q3:Q14	Q3	=SUM($E3:$P3)
E15:Q15	E15	=SUM(E$3:E$14)*E$1

Or this for your sum formulas in E15:P15

=SUM(E$3:E$14)*(1+E$1)

 

[acool]

Does this look like what you are after?

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	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$9,000,000	$99,000,000
4	4/1/2023	1	$13,000,000		$0	$0	$0	$13,000,000	$13,000,000	$13,000,000	$13,000,000	$13,000,000	$13,000,000	$13,000,000	$13,000,000	$13,000,000	$117,000,000
5	4/1/2023	1	$4,000,000		$0	$0	$0	$4,000,000	$4,000,000	$4,000,000	$4,000,000	$4,000,000	$4,000,000	$4,000,000	$4,000,000	$4,000,000	$36,000,000
6	5/1/2023	1	$5,000,000		$0	$0	$0	$0	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$40,000,000
7	6/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$35,000,000
8	7/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$30,000,000
9	8/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$25,000,000
10	9/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$5,000,000	$5,000,000	$5,000,000	$5,000,000	$20,000,000
11	10/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$5,000,000	$5,000,000	$5,000,000	$15,000,000
12	11/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$5,000,000	$5,000,000	$10,000,000
13	12/1/2023	1	$5,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$5,000,000	$5,000,000
14	1/1/2024	1	$4,000,000		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
15					$0	$630,000	$720,000	$2,340,000	$3,100,000	$3,960,000	$4,920,000	$2,300,000	$3,570,000	$2,240,000	$7,320,000	$6,600,000	$0
Sheet2

Cell Formulas
Range	Formula
E3:P14	E3	=IF($A3<=E$2,$C3,0)
Q3:Q14	Q3	=SUM($E3:$P3)
E15:Q15	E15	=SUM(E$3:E$14)*E$1

Or this for your sum formulas in E15:P15

=SUM(E$3:E$14)*(1+E$1)

Thank You!

 

[dreid1011]

Thank You!

You're welcome, happy to help.