Formula help: Forecast daily sales with multiple variables

tblackwell

New Member
Joined
Oct 24, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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 DateCoefficientForecasted Sales
$3,123.34Monday, April 13, 2020


20210419 Forecast_MrExcel.xlsx
AB
1VariableCoefficient
226-Dec-755.0729
327-Dec-333.9501
428-Dec-242.7414
529-Dec-71.15019
630-Dec193.7145
7EasterMON-316.5817
8EasterSUN41.24181
9FRI483.6428
10LaborDayMON-231.3957
11MemorialDayMON-572.9276
12MemorialDaySAT-249.8372
13MemorialDaySUN-410.881
14MON-183.2338
15MothersDay295.2422
16NewYearsDay-744.8978
17NewYearsEve118.8859
18PaydayFriFRI202.9545
19PaydayFriMON128.4605
20PaydayFriSAT137.9061
21PaydayFriSUN139.4848
22PaydayFriTUE99.60461
23PaydayMinusOne74.22771
24PaydayMonFRI-80.35609
25PaydayMonMON105.7673
26PaydayMonTHU67.66514
27PaydayMonWED245.584
28PaydayThuFRI196.4547
29PaydayThuSAT139.8488
30PaydayThuSUN99.60177
31PaydayThuTHU263.6197
32PaydayTueTHU147.143
33PaydayTueTUE127.0158
34PaydayWedFRI183.1297
35PaydayWedSAT240.8867
36PaydayWedSUN156.2451
37PaydayWedTHU244.2439
38PaydayWedWED258.7334
39SAT172.8945
40SUN395.8903
41SuperBowl-387.2396
42ThanksPopstFRI-1880.406
43ThanksPostMON-148.8666
44ThanksPostSAT-937.5562
45ThanksPostSUN-520.1458
46ThanksPreTUE406.4926
47ThanksPreWED399.3952
48THU210.27
49TUE-91.98303
50XMASEve-151.8521
Sheet4


20210419 Forecast_MrExcel.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
1DOW2020 Business DateEasterMONMON26-Dec27-Dec28-Dec29-Dec30-DecEasterSUNFRILaborDayMONMemorialDayMONMemorialDaySATMemorialDaySUNMothersDayNewYearsDayNewYearsEvePaydayFriFRIPaydayFriMONPaydayFriSATPaydayFriSUNPaydayFriTUEPaydayMinusOnePaydayMonFRIPaydayMonMONPaydayMonTHUPaydayMonWEDPaydayThuFRIPaydayThuSATPaydayThuSUNPaydayThuTHUPaydayTueTHUPaydayTueTUEPaydayWedFRIPaydayWedSATPaydayWedSUNPaydayWedTHUPaydayWedWEDSATSUNSuperBowlThanksPopstFRIThanksPostMONThanksPostSATThanksPostSUNThanksPreTUEThanksPreWEDTHUTUEXMASEve
2TuesdayTuesday, April 7, 2020
3WednesdayWednesday, April 8, 2020
4ThursdayThursday, April 9, 2020
5FridayFriday, April 10, 20201
6SaturdaySaturday, April 11, 2020
7SundaySunday, April 12, 20201
8MondayMonday, April 13, 202011
Sheet5
Cell Formulas
RangeFormula
A2:A8A2=TEXT(B2,"dddd")
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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?).
 
Upvote 0
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
EFGHIJKLM
1DateCoefficientDOWCoefficientDOWDateCoeffients
226-Dec-755.0729Sunday395.8903Saturday12/26/2020(582.18)
327-Dec-333.9501Monday-183.2338
428-Dec-242.7414Tuesday-91.98303
529-Dec-71.15019Wednesday98.3273
630-Dec193.7145Thursday210.27
7Friday483.6428
8Saturday172.8945
3_Coefficents (2)
Cell Formulas
RangeFormula
K2K2=TEXT(L2,"dddd")
M2M2=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?
 
Upvote 0
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
DEFGHIJKLMN
1EventDateCoefficientDOWCoefficientDOWDateCoeffientsNo. Coeff
2Easter Sunday12-Apr-2041.24181Sunday395.8903Saturday26-Dec-20-582.182
3Easter Monday13-Apr-20-316.5817Monday-183.2338Sunday27-Dec-2061.942
4Christmas +126-Dec-20-755.0729Tuesday-91.98303Monday28-Dec-20-425.982
5Christmas +227-Dec-20-333.9501Wednesday98.3273Tuesday29-Dec-20-163.132
6Christmas +328-Dec-20-242.7414Thursday210.27Wednesday30-Dec-20292.042
7Christmas +429-Dec-20-71.15019Friday483.6428Thursday31-Dec-20329.162
8Christmas +530-Dec-20193.7145Saturday172.8945Friday01-Jan-21-261.262
9NewYearsEve31-Dec-20118.8859Saturday02-Jan-21172.891
10NewYearsDay01-Jan-21-744.8978Sunday03-Jan-21395.891
11Easter Sunday04-Apr-2141.24181Monday04-Jan-21-183.231
12Easter Monday05-Apr-21-316.5817
13Easter Sunday17-Apr-2241.24181
14Easter Monday18-Apr-22-316.5817
15Easter Sunday09-Apr-2341.24181
16Easter Monday10-Apr-23-316.5817
17Easter Sunday31-Mar-2441.24181
18Easter Monday01-Apr-24-316.5817
Coeffocients
Cell Formulas
RangeFormula
K2:K11K2=TEXT(L2,"dddd")
M2:M11M2=SUMIF($H$2:$H$8,K2,$I$2:$I$8)+SUMIF($E$2:$E$9999,L2,$F$2:$F$9999)
N2:N11N2=COUNTIF($H$2:$H$8,K2)+COUNTIF($E$2:$E$9999,L2)
L3:L11L3=L2+1
 
Upvote 0
Solution
@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!
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,595
Members
452,657
Latest member
giadungthienduyen

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