tblackwell
New Member
 Joined
 Oct 24, 2018
 Messages
 28
 Office Version

 365
 Platform

 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.
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  26Dec  755.0729  
3  27Dec  333.9501  
4  28Dec  242.7414  
5  29Dec  71.15019  
6  30Dec  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  26Dec  27Dec  28Dec  29Dec  30Dec  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") 