Formula help: Forecast daily sales with multiple variables

[tblackwell]

Question: I need help creating a formula that will add multiple variables to a daily forecast. Might require VBA. I do not know VBA.

Background: Our sales volumes changes based upon multiple variables such as holidays, paydays, and days of the week. We have run regression analysis that determines which of these variables are statistically significant. Our stats package also delivers a coefficient that we can use with some degree of confidence to predict future sales.

Example: The Monday after Easter is a slower day for us. The coefficient is -$317 which means we expect that day to be about -$317 less than it would have been had it not been the Monday after Easter.

If it were that simple, I could write a formula that says: Baseline sales - $317 = Sales forecast.

My problem is that there can be multiple coefficients that affect that same day.

Sticking with the Monday after Easter example, this day is also a Monday, and the coefficient for a Monday is -$183 which means we expect Mondays to be about -$183 less than had it not been a Monday.

So the updated formula would be: Baseline sales - $317 - $183 = Sales Forecast.

What I need is a formula that will (a) look at the business date, (b) see which variables apply to the business date, and (c) apply those variable coefficients to baseline sales to create a sales forecast.

I've tried assign binary digits to the variables (True = 1), but then I get stuck with what to do next?

Any help getting me unstuck would be appreciated.

Sample data below.

Sales Baseline	Business Date	Coefficient	Forecasted Sales
$3,123.34	Monday, April 13, 2020

20210419 Forecast_MrExcel.xlsx
	A	B
1	Variable	Coefficient
2	26-Dec	-755.0729
3	27-Dec	-333.9501
4	28-Dec	-242.7414
5	29-Dec	-71.15019
6	30-Dec	193.7145
7	EasterMON	-316.5817
8	EasterSUN	41.24181
9	FRI	483.6428
10	LaborDayMON	-231.3957
11	MemorialDayMON	-572.9276
12	MemorialDaySAT	-249.8372
13	MemorialDaySUN	-410.881
14	MON	-183.2338
15	MothersDay	295.2422
16	NewYearsDay	-744.8978
17	NewYearsEve	118.8859
18	PaydayFriFRI	202.9545
19	PaydayFriMON	128.4605
20	PaydayFriSAT	137.9061
21	PaydayFriSUN	139.4848
22	PaydayFriTUE	99.60461
23	PaydayMinusOne	74.22771
24	PaydayMonFRI	-80.35609
25	PaydayMonMON	105.7673
26	PaydayMonTHU	67.66514
27	PaydayMonWED	245.584
28	PaydayThuFRI	196.4547
29	PaydayThuSAT	139.8488
30	PaydayThuSUN	99.60177
31	PaydayThuTHU	263.6197
32	PaydayTueTHU	147.143
33	PaydayTueTUE	127.0158
34	PaydayWedFRI	183.1297
35	PaydayWedSAT	240.8867
36	PaydayWedSUN	156.2451
37	PaydayWedTHU	244.2439
38	PaydayWedWED	258.7334
39	SAT	172.8945
40	SUN	395.8903
41	SuperBowl	-387.2396
42	ThanksPopstFRI	-1880.406
43	ThanksPostMON	-148.8666
44	ThanksPostSAT	-937.5562
45	ThanksPostSUN	-520.1458
46	ThanksPreTUE	406.4926
47	ThanksPreWED	399.3952
48	THU	210.27
49	TUE	-91.98303
50	XMASEve	-151.8521
Sheet4

20210419 Forecast_MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1	DOW	2020 Business Date	EasterMON	MON	26-Dec	27-Dec	28-Dec	29-Dec	30-Dec	EasterSUN	FRI	LaborDayMON	MemorialDayMON	MemorialDaySAT	MemorialDaySUN	MothersDay	NewYearsDay	NewYearsEve	PaydayFriFRI	PaydayFriMON	PaydayFriSAT	PaydayFriSUN	PaydayFriTUE	PaydayMinusOne	PaydayMonFRI	PaydayMonMON	PaydayMonTHU	PaydayMonWED	PaydayThuFRI	PaydayThuSAT	PaydayThuSUN	PaydayThuTHU	PaydayTueTHU	PaydayTueTUE	PaydayWedFRI	PaydayWedSAT	PaydayWedSUN	PaydayWedTHU	PaydayWedWED	SAT	SUN	SuperBowl	ThanksPopstFRI	ThanksPostMON	ThanksPostSAT	ThanksPostSUN	ThanksPreTUE	ThanksPreWED	THU	TUE	XMASEve
2	Tuesday	Tuesday, April 7, 2020
3	Wednesday	Wednesday, April 8, 2020
4	Thursday	Thursday, April 9, 2020
5	Friday	Friday, April 10, 2020									1
6	Saturday	Saturday, April 11, 2020
7	Sunday	Sunday, April 12, 2020								1
8	Monday	Monday, April 13, 2020	1	1
Sheet5

Cell Formulas
Range	Formula
A2:A8	A2	=TEXT(B2,"dddd")

 

[Toadstool]

Hi TBlackwell,

I think your challenge arises from trying to use your Variable for multiple meanings. A simpler approach might be a column of coefficients for specific dates (e.g. 25 December 2020 and if you're covering multiple years then repeat for each year) and a column for days of the week. You could then use two SUMIFS added to give the total value.

My problem is understanding what all the PayDay entries are and how you might derive them (e.g. when is PayDayFRIFri?).

 

[tblackwell]

Hi TBlackwell,

I think your challenge arises from trying to use your Variable for multiple meanings. A simpler approach might be a column of coefficients for specific dates (e.g. 25 December 2020 and if you're covering multiple years then repeat for each year) and a column for days of the week. You could then use two SUMIFS added to give the total value.

My problem is understanding what all the PayDay entries are and how you might derive them (e.g. when is PayDayFRIFri?).

Thanks @Toadstool.

Do you mean something like this?

20210420 Atlanta Forecast.xlsx
	E	F	G	H	I	J	K	L	M
1	Date	Coefficient		DOW	Coefficient		DOW	Date	Coeffients
2	26-Dec	-755.0729		Sunday	395.8903		Saturday	12/26/2020	(582.18)
3	27-Dec	-333.9501		Monday	-183.2338
4	28-Dec	-242.7414		Tuesday	-91.98303
5	29-Dec	-71.15019		Wednesday	98.3273
6	30-Dec	193.7145		Thursday	210.27
7				Friday	483.6428
8				Saturday	172.8945
3_Coefficents (2)

Cell Formulas
Range	Formula
K2	K2	=TEXT(L2,"dddd")
M2	M2	=SUMIF(H2:H8,K2,I2:I8)+SUMIF(E2:E6,L2,F2:F6)

Pay Day is going to be tougher. For example PayDayFRIFri means pay day is usually on a Friday and today is Friday. But if you think the above solution works, I can probably get there from here.

Thoughts?

 

[Toadstool]

Yes, that's what I meant.

I'm surprised your coefficients are values and not percentages, unless you're recalculating on a regular basis (i.e. if the baseline is 3,123.34 then your Easter Monday coefficient this year was -499.82 so giving 2,623.52, but if a percentage was used (-499.82/3,123.34=.16 or 16%) then if your baseline changed next year to 4,500 the existing -499.82 gives 4,000.18 but using 16% would give a probably more accurate 3,780, but I don't know your business).

I would also label the fixed dates as you'll need to add new ones each year. New Years Day you'll probably remember but here I've added a few years for Easter, a moveable feast.

Here I've made some ranges absolute so the SUMIF can be copied down (I've just given a 10 day forecast). I've also added a COUNTIF to see how many Coefficients were included (just as a cross check that none are ever zero).

TBBlackwell.xlsx
	D	E	F	G	H	I	J	K	L	M	N
1	Event	Date	Coefficient		DOW	Coefficient		DOW	Date	Coeffients	No. Coeff
2	Easter Sunday	12-Apr-20	41.24181		Sunday	395.8903		Saturday	26-Dec-20	-582.18	2
3	Easter Monday	13-Apr-20	-316.5817		Monday	-183.2338		Sunday	27-Dec-20	61.94	2
4	Christmas +1	26-Dec-20	-755.0729		Tuesday	-91.98303		Monday	28-Dec-20	-425.98	2
5	Christmas +2	27-Dec-20	-333.9501		Wednesday	98.3273		Tuesday	29-Dec-20	-163.13	2
6	Christmas +3	28-Dec-20	-242.7414		Thursday	210.27		Wednesday	30-Dec-20	292.04	2
7	Christmas +4	29-Dec-20	-71.15019		Friday	483.6428		Thursday	31-Dec-20	329.16	2
8	Christmas +5	30-Dec-20	193.7145		Saturday	172.8945		Friday	01-Jan-21	-261.26	2
9	NewYearsEve	31-Dec-20	118.8859					Saturday	02-Jan-21	172.89	1
10	NewYearsDay	01-Jan-21	-744.8978					Sunday	03-Jan-21	395.89	1
11	Easter Sunday	04-Apr-21	41.24181					Monday	04-Jan-21	-183.23	1
12	Easter Monday	05-Apr-21	-316.5817
13	Easter Sunday	17-Apr-22	41.24181
14	Easter Monday	18-Apr-22	-316.5817
15	Easter Sunday	09-Apr-23	41.24181
16	Easter Monday	10-Apr-23	-316.5817
17	Easter Sunday	31-Mar-24	41.24181
18	Easter Monday	01-Apr-24	-316.5817
Coeffocients

Cell Formulas
Range	Formula
K2:K11	K2	=TEXT(L2,"dddd")
M2:M11	M2	=SUMIF($H$2:$H$8,K2,$I$2:$I$8)+SUMIF($E$2:$E$9999,L2,$F$2:$F$9999)
N2:N11	N2	=COUNTIF($H$2:$H$8,K2)+COUNTIF($E$2:$E$9999,L2)
L3:L11	L3	=L2+1

 

[tblackwell]

@Toadstool - all good ideas, especially labeling the fixed dates and count of coefficients - thank you! Yes the coefficients are values, so for example the Monday after Easter is slower than a typical Monday. I think I can take it from here, I am going to go ahead and mark as solution. Thank you!

 

[Toadstool]

You're welcome.
Thanks for the feedback.