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 | 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") |