Calendarize Sales Data stated in years from date

nterhaar89

New Member
Joined
Aug 22, 2019
Messages
6
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
ABCDEFGHIJKLMNOPQ
1Customer IDStart DatePRODUCT_NUMY1 From StartY2 From StartY3 From StartY4 From StartY5 From StartY6 From StartFY 2020FY 2021FY 2022FY 2023FY 2024FY 2025FY 2026FY 2027
2A8/23/20211$2,737.94$2,737.94$2,737.94$2,737.94$2,737.94$2,737.94
3A8/23/20212$2,497.50$2,497.50$2,497.50$2,497.50$2,497.50$2,497.50
4A8/23/20213$3,779.06$3,779.06$3,779.06$3,779.06$3,779.06$3,779.06
5A8/23/20214$2,497.50$2,497.50$2,497.50$2,497.50$2,497.50$2,497.50
6A8/23/20215$3,679.83$3,679.83$3,679.83$3,679.83$3,679.83$3,679.83
7A8/23/20216$3,779.06$3,779.06$3,779.06$3,779.06$3,779.06$3,779.06
8A8/23/20217$3,991.82$3,991.82$3,991.82$3,991.82$3,991.82$3,991.82
9A8/23/20218$3,991.82$3,991.82$3,991.82$3,991.82$3,991.82$3,991.82
10A8/23/20219$3,991.82$3,991.82$3,991.82$3,991.82$3,991.82$3,991.82
11A8/23/202110$3,991.82$3,991.82$3,991.82$3,991.82$3,991.82$3,991.82
12A8/23/202111$2,632.50$2,632.50$2,632.50$2,632.50$2,632.50$2,632.50
13B6/16/20211$815.17$815.17$815.17$815.17$815.17$0.00
14B6/16/20212$3,820.92$3,820.92$3,820.92$3,820.92$3,820.92$0.00
15B6/16/20213$3,823.00$3,823.00$3,823.00$3,823.00$3,823.00$0.00
16B6/16/20214$2,720.00$2,720.00$2,720.00$2,720.00$2,720.00$0.00
17B6/16/20215$6,700.00$6,700.00$6,700.00$6,700.00$6,700.00$0.00
18B6/16/20216$2,463.25$2,463.25$2,463.25$2,463.25$2,463.25$0.00
19C3/13/20201$1,000.00$1,100.00$1,210.00$1,331.00$1,464.10$1,610.51
20C3/13/20202$1,000.00$1,100.00$1,210.00$1,331.00$1,464.10$1,610.51
21C3/13/20203$1,000.00$1,100.00$1,210.00$1,331.00$1,464.10$1,610.51
22C3/13/20204$1,000.00$1,100.00$1,210.00$1,331.00$1,464.10$1,610.51
23C3/13/20205$1,000.00$1,100.00$1,210.00$1,331.00$1,464.10$1,610.51
24C3/13/20206$1,000.00$1,100.00$1,210.00$1,331.00$1,464.10$1,610.51
25C3/13/20207$1,000.00$1,100.00$1,210.00$1,331.00$1,464.10$1,610.51
26C3/13/20208$1,000.00$1,100.00$1,210.00$1,331.00$1,464.10$1,610.51
Sheet1
Cell Formulas
RangeFormula
E19:I26E19=D19*1.1
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
Your profile doesn't say which version of Excel you are using. If you have 365 you can try:

Book1
ABCDEFGHIJKLMNOPQ
1Customer IDStart DatePRODUCT_NUMY1 From StartY2 From StartY3 From StartY4 From StartY5 From StartY6 From StartFY 2020FY 2021FY 2022FY 2023FY 2024FY 2025FY 2026FY 2027
2A8/23/20211.002737.942737.942737.942737.942737.942737.940.00975.152737.942737.942737.942737.942737.941762.78
3A8/23/20212.002497.502497.502497.502497.502497.502497.500.00889.522497.502497.502497.502497.502497.501607.98
4A8/23/20213.003779.063779.063779.063779.063779.063779.060.001345.963779.063779.063779.063779.063779.062433.09
5A8/23/20214.002497.502497.502497.502497.502497.502497.500.00889.522497.502497.502497.502497.502497.501607.98
6A8/23/20215.003679.833679.833679.833679.833679.833679.830.001310.623679.833679.833679.833679.833679.832369.21
7A9/30/20226.001.002.003.004.005.006.000.000.000.251.252.253.254.255.25
8A10/1/20217.001.002.003.004.005.006.000.000.251.252.253.254.255.254.50
9A9/30/20208.0010.0020.0030.0040.0050.0060.002.4912.4922.4932.4942.4952.4945.04#N/A
10A9/30/20199.00100.00200.00300.00400.00500.00600.00125.21225.21325.21425.21525.21448.77#N/A#N/A
11A8/23/202110.003991.823991.823991.823991.823991.823991.820.001421.743991.823991.823991.823991.823991.822570.07
Sheet2
Cell Formulas
RangeFormula
J2:Q11J2=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) )
 
Upvote 0
Solution
Your profile doesn't say which version of Excel you are using. If you have 365 you can try:

Book1
ABCDEFGHIJKLMNOPQ
1Customer IDStart DatePRODUCT_NUMY1 From StartY2 From StartY3 From StartY4 From StartY5 From StartY6 From StartFY 2020FY 2021FY 2022FY 2023FY 2024FY 2025FY 2026FY 2027
2A8/23/20211.002737.942737.942737.942737.942737.942737.940.00975.152737.942737.942737.942737.942737.941762.78
3A8/23/20212.002497.502497.502497.502497.502497.502497.500.00889.522497.502497.502497.502497.502497.501607.98
4A8/23/20213.003779.063779.063779.063779.063779.063779.060.001345.963779.063779.063779.063779.063779.062433.09
5A8/23/20214.002497.502497.502497.502497.502497.502497.500.00889.522497.502497.502497.502497.502497.501607.98
6A8/23/20215.003679.833679.833679.833679.833679.833679.830.001310.623679.833679.833679.833679.833679.832369.21
7A9/30/20226.001.002.003.004.005.006.000.000.000.251.252.253.254.255.25
8A10/1/20217.001.002.003.004.005.006.000.000.251.252.253.254.255.254.50
9A9/30/20208.0010.0020.0030.0040.0050.0060.002.4912.4922.4932.4942.4952.4945.04#N/A
10A9/30/20199.00100.00200.00300.00400.00500.00600.00125.21225.21325.21425.21525.21448.77#N/A#N/A
11A8/23/202110.003991.823991.823991.823991.823991.823991.820.001421.743991.823991.823991.823991.823991.822570.07
Sheet2
Cell Formulas
RangeFormula
J2:Q11J2=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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top