Calendarize Sales Data stated in years from date

[nterhaar89]

Hi,

I have a set of data with customers A-C, products 1-12, a "start date" for sales, values for sales that fall 1-7 years from the start date. I am trying to come up with a formula or way to calendarize those sales based on the start date, by prorating them into the years.

ie, start date is 2/15/2021, and there are sales values of $2,000 for Product 1 for Y1-Y5.
2021 expected sales are $1,747.9 ((12/31/2021 - 2/15/2021) * 365) * $2,000
2022-2025 expected sales are $2000
2026 expected sales are $252.1 ($2,000-$1,747.9)

The actual math from 1 formula might be a little different, but looking for a way to pro-rate the expected sales into the calendar year based on that close date. Minisheet below.

Sample Workbook 9.16.21.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Customer ID	Start Date	PRODUCT_NUM	Y1 From Start	Y2 From Start	Y3 From Start	Y4 From Start	Y5 From Start	Y6 From Start	FY 2020	FY 2021	FY 2022	FY 2023	FY 2024	FY 2025	FY 2026	FY 2027
2	A	8/23/2021	1	$2,737.94	$2,737.94	$2,737.94	$2,737.94	$2,737.94	$2,737.94
3	A	8/23/2021	2	$2,497.50	$2,497.50	$2,497.50	$2,497.50	$2,497.50	$2,497.50
4	A	8/23/2021	3	$3,779.06	$3,779.06	$3,779.06	$3,779.06	$3,779.06	$3,779.06
5	A	8/23/2021	4	$2,497.50	$2,497.50	$2,497.50	$2,497.50	$2,497.50	$2,497.50
6	A	8/23/2021	5	$3,679.83	$3,679.83	$3,679.83	$3,679.83	$3,679.83	$3,679.83
7	A	8/23/2021	6	$3,779.06	$3,779.06	$3,779.06	$3,779.06	$3,779.06	$3,779.06
8	A	8/23/2021	7	$3,991.82	$3,991.82	$3,991.82	$3,991.82	$3,991.82	$3,991.82
9	A	8/23/2021	8	$3,991.82	$3,991.82	$3,991.82	$3,991.82	$3,991.82	$3,991.82
10	A	8/23/2021	9	$3,991.82	$3,991.82	$3,991.82	$3,991.82	$3,991.82	$3,991.82
11	A	8/23/2021	10	$3,991.82	$3,991.82	$3,991.82	$3,991.82	$3,991.82	$3,991.82
12	A	8/23/2021	11	$2,632.50	$2,632.50	$2,632.50	$2,632.50	$2,632.50	$2,632.50
13	B	6/16/2021	1	$815.17	$815.17	$815.17	$815.17	$815.17	$0.00
14	B	6/16/2021	2	$3,820.92	$3,820.92	$3,820.92	$3,820.92	$3,820.92	$0.00
15	B	6/16/2021	3	$3,823.00	$3,823.00	$3,823.00	$3,823.00	$3,823.00	$0.00
16	B	6/16/2021	4	$2,720.00	$2,720.00	$2,720.00	$2,720.00	$2,720.00	$0.00
17	B	6/16/2021	5	$6,700.00	$6,700.00	$6,700.00	$6,700.00	$6,700.00	$0.00
18	B	6/16/2021	6	$2,463.25	$2,463.25	$2,463.25	$2,463.25	$2,463.25	$0.00
19	C	3/13/2020	1	$1,000.00	$1,100.00	$1,210.00	$1,331.00	$1,464.10	$1,610.51
20	C	3/13/2020	2	$1,000.00	$1,100.00	$1,210.00	$1,331.00	$1,464.10	$1,610.51
21	C	3/13/2020	3	$1,000.00	$1,100.00	$1,210.00	$1,331.00	$1,464.10	$1,610.51
22	C	3/13/2020	4	$1,000.00	$1,100.00	$1,210.00	$1,331.00	$1,464.10	$1,610.51
23	C	3/13/2020	5	$1,000.00	$1,100.00	$1,210.00	$1,331.00	$1,464.10	$1,610.51
24	C	3/13/2020	6	$1,000.00	$1,100.00	$1,210.00	$1,331.00	$1,464.10	$1,610.51
25	C	3/13/2020	7	$1,000.00	$1,100.00	$1,210.00	$1,331.00	$1,464.10	$1,610.51
26	C	3/13/2020	8	$1,000.00	$1,100.00	$1,210.00	$1,331.00	$1,464.10	$1,610.51
Sheet1

Cell Formulas
Range	Formula
E19:I26	E19	=D19*1.1

 

[nterhaar89]

Just re-reading this and the first "example" formula should read "/ 365" - I am getting the % of days in the first "stub" year and multiplying by the sales value.

 

[JGordon11]

Your profile doesn't say which version of Excel you are using. If you have 365 you can try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Customer ID	Start Date	PRODUCT_NUM	Y1 From Start	Y2 From Start	Y3 From Start	Y4 From Start	Y5 From Start	Y6 From Start	FY 2020	FY 2021	FY 2022	FY 2023	FY 2024	FY 2025	FY 2026	FY 2027
2	A	8/23/2021	1.00	2737.94	2737.94	2737.94	2737.94	2737.94	2737.94	0.00	975.15	2737.94	2737.94	2737.94	2737.94	2737.94	1762.78
3	A	8/23/2021	2.00	2497.50	2497.50	2497.50	2497.50	2497.50	2497.50	0.00	889.52	2497.50	2497.50	2497.50	2497.50	2497.50	1607.98
4	A	8/23/2021	3.00	3779.06	3779.06	3779.06	3779.06	3779.06	3779.06	0.00	1345.96	3779.06	3779.06	3779.06	3779.06	3779.06	2433.09
5	A	8/23/2021	4.00	2497.50	2497.50	2497.50	2497.50	2497.50	2497.50	0.00	889.52	2497.50	2497.50	2497.50	2497.50	2497.50	1607.98
6	A	8/23/2021	5.00	3679.83	3679.83	3679.83	3679.83	3679.83	3679.83	0.00	1310.62	3679.83	3679.83	3679.83	3679.83	3679.83	2369.21
7	A	9/30/2022	6.00	1.00	2.00	3.00	4.00	5.00	6.00	0.00	0.00	0.25	1.25	2.25	3.25	4.25	5.25
8	A	10/1/2021	7.00	1.00	2.00	3.00	4.00	5.00	6.00	0.00	0.25	1.25	2.25	3.25	4.25	5.25	4.50
9	A	9/30/2020	8.00	10.00	20.00	30.00	40.00	50.00	60.00	2.49	12.49	22.49	32.49	42.49	52.49	45.04	#N/A
10	A	9/30/2019	9.00	100.00	200.00	300.00	400.00	500.00	600.00	125.21	225.21	325.21	425.21	525.21	448.77	#N/A	#N/A
11	A	8/23/2021	10.00	3991.82	3991.82	3991.82	3991.82	3991.82	3991.82	0.00	1421.74	3991.82	3991.82	3991.82	3991.82	3991.82	2570.07
Sheet2

Cell Formulas
Range	Formula
J2:Q11	J2	=LET(yr,YEAR($B2),yrdiff, RIGHT(J$1,4)-yr, pctyr, ($B2- DATE(yr,1,1)+1)/365,IFS(yrdiff<0,0, yrdiff=0, INDEX($D2:$I2,1,yrdiff+1)*(1-pctyr),yrdiff<6,INDEX($D2:$I2,1,yrdiff)*pctyr + INDEX($D2:$I2,1,yrdiff+1)*(1-pctyr),yrdiff=6,INDEX($D2:$I2,1,yrdiff)*pctyr) )

 

[nterhaar89]

Your profile doesn't say which version of Excel you are using. If you have 365 you can try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Customer ID	Start Date	PRODUCT_NUM	Y1 From Start	Y2 From Start	Y3 From Start	Y4 From Start	Y5 From Start	Y6 From Start	FY 2020	FY 2021	FY 2022	FY 2023	FY 2024	FY 2025	FY 2026	FY 2027
2	A	8/23/2021	1.00	2737.94	2737.94	2737.94	2737.94	2737.94	2737.94	0.00	975.15	2737.94	2737.94	2737.94	2737.94	2737.94	1762.78
3	A	8/23/2021	2.00	2497.50	2497.50	2497.50	2497.50	2497.50	2497.50	0.00	889.52	2497.50	2497.50	2497.50	2497.50	2497.50	1607.98
4	A	8/23/2021	3.00	3779.06	3779.06	3779.06	3779.06	3779.06	3779.06	0.00	1345.96	3779.06	3779.06	3779.06	3779.06	3779.06	2433.09
5	A	8/23/2021	4.00	2497.50	2497.50	2497.50	2497.50	2497.50	2497.50	0.00	889.52	2497.50	2497.50	2497.50	2497.50	2497.50	1607.98
6	A	8/23/2021	5.00	3679.83	3679.83	3679.83	3679.83	3679.83	3679.83	0.00	1310.62	3679.83	3679.83	3679.83	3679.83	3679.83	2369.21
7	A	9/30/2022	6.00	1.00	2.00	3.00	4.00	5.00	6.00	0.00	0.00	0.25	1.25	2.25	3.25	4.25	5.25
8	A	10/1/2021	7.00	1.00	2.00	3.00	4.00	5.00	6.00	0.00	0.25	1.25	2.25	3.25	4.25	5.25	4.50
9	A	9/30/2020	8.00	10.00	20.00	30.00	40.00	50.00	60.00	2.49	12.49	22.49	32.49	42.49	52.49	45.04	#N/A
10	A	9/30/2019	9.00	100.00	200.00	300.00	400.00	500.00	600.00	125.21	225.21	325.21	425.21	525.21	448.77	#N/A	#N/A
11	A	8/23/2021	10.00	3991.82	3991.82	3991.82	3991.82	3991.82	3991.82	0.00	1421.74	3991.82	3991.82	3991.82	3991.82	3991.82	2570.07
Sheet2

Cell Formulas
Range	Formula
J2:Q11	J2	=LET(yr,YEAR($B2),yrdiff, RIGHT(J$1,4)-yr, pctyr, ($B2- DATE(yr,1,1)+1)/365,IFS(yrdiff<0,0, yrdiff=0, INDEX($D2:$I2,1,yrdiff+1)*(1-pctyr),yrdiff<6,INDEX($D2:$I2,1,yrdiff)*pctyr + INDEX($D2:$I2,1,yrdiff+1)*(1-pctyr),yrdiff=6,INDEX($D2:$I2,1,yrdiff)*pctyr) )

This is incredible, thank you so much.