How to spread a Target number into months by Historical Seasonality?

[xa_gta]

Hi Community,

I have month-wise data for the last 4 years and I have the overall year target for the current year.
I am asking you to review whether my approach to dividing this target between the months is logical. Please feel free to add your approach on how to best do this.

• At first, I placed Sales month wise figures.
• Second, I calculate month wise share% in year figures
• Third, I assign the seasonality factor for respective months of that years - 1 = Normal Sales, 1.5 = Festival Sales, 0.875 = Off-Season Sales, 0.75, 0.50, 0.25 = Impact of COVID lockdown & so on.
• Fourth, I calculate weighted average of % Share & Seasonality for each month and multiply this with the current year target to get the monthly sales of this year.

Challenge facing - Because the seasonality & % share are assigned independently, the weighted average % of all months doesn't sum to 100%

Please advise on way forward, please find below the sheet to work with.

Helper Mr excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Sales			Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Total
2	2018-19			28.32	28.55	33.96	44.50	32.86	19.95	37.93	49.66	15.87	44.53	5.81	31.71	373.64
3	2019-20			43.51	20.14	18.92	6.11	43.65	8.51	6.97	27.85	13.26	12.36	28.72	2.47	232.47
4	2020-21			4.43	25.30	18.76	30.95	35.86	1.35	45.20	10.28	2.78	39.45	3.02	35.97	253.36
5	2021-22			9.28	19.56	23.18	19.40	6.59	47.59	48.56	40.59	29.89	15.94	49.80	40.43	350.80
6	2022-23 (Value in CR)			0.11	0.49	30.50	41.15	19.77	48.29	27.81	35.39	28.70	1.91	30.56	31.27	295.94
7	%spread x Seasonality weighted avg.
8	2018-19			10%	8%	8%	8%	11%	6%	11%	10%	5%	9%	7%	10%	103%
9	2019-20			10%	8%	8%	8%	11%	6%	11%	10%	5%	9%	7%	10%	103%
10	2020-21			10%	8%	8%	8%	11%	6%	11%	10%	5%	9%	7%	10%	103%
11	2021-22			10%	8%	8%	8%	11%	6%	11%	10%	5%	9%	7%	10%	103%
12	% Spread
13	2018-19			8%	8%	9%	12%	9%	5%	10%	13%	4%	12%	2%	8%	100%
14	2019-20			19%	9%	8%	3%	19%	4%	3%	12%	6%	5%	12%	1%	100%
15	2020-21			2%	10%	7%	12%	14%	1%	18%	4%	1%	16%	1%	14%	100%
16	2021-22			3%	6%	7%	6%	2%	14%	14%	12%	9%	5%	14%	12%	100%
17	2022-23			0%	0%	10%	14%	7%	16%	9%	12%	10%	1%	10%	11%
18	Seasonality
19	2018-19			1.000	1.000	1.000	0.750	1.000	1.000	1.500	0.875	1.000	1.000	0.875	1.000	12.00
20	2019-20			1.000	1.000	1.000	0.750	1.000	1.000	1.500	0.875	1.000	1.000	0.875	0.500	11.50
21	2020-21			0.000	0.250	0.750	0.750	0.750	1.000	1.500	1.000	1.000	1.000	1.000	1.000	10.00
22	2021-22			1.000	0.500	1.000	1.000	1.000	1.250	1.500	1.000	1.000	1.000	1.000	1.000	12.25
Sheet1

Cell Formulas
Range	Formula
D2:O6	D2	=RAND()*50
P2:P6,P19:P22,P13:P16,P8:P11	P2	=SUM(D2:O2)
D8:O11	D8	=SUMPRODUCT(D$13:D$16,D$19:D$22)/SUM(D$19:D$22)
D13:O17	D13	=D2/$P2

 

[sijpie]

I think this should give you a decent way to predict. But:
I would either leave out the heavily covid affected years, or use a kind of rolling average.

What i mean with the last is that there could be a shifting trend, so if you took the weighted average of the last two years (depending on how many years' worth of data you have you could increase last 2 to say last 5 years), then your target could be more realistic.
So D8 would just be D13*D19
D9 would be sumproduct(D13:D14,D19:D20)/sum(D19:D20)
D10 would be sumproduct(D14:D15,D20:D21)/sum(D20:D21)
etc