FUTURE ORDERS

[alm395]

Hi!

I am new to creating future estimates and am looking for the best way to show the prediction. I tried using Forecast.Linear, but think I did something wrong, as I got the same number for all future predictions. I was able to calculate the monthly averages for this year based on the historical values.

Total Orders = Total number of orders we received
Arrive < 1 HR = Of the total number of orders, these are the amount we were able to deliver on < 1 hour

I am looking for the estimated number of Total Orders and Arrive < 1 HR for May 2021 - Jan 2022. What is the best way to go about this?

Thank you!

05.06.21 - WD DATA.xlsx
	A	B	C	D	E	F	G	H
2	FY	MMYY	Total Orders	TO AVG	TO PREDICT	ARRIVE < 1HR	<60 AVG	<60 PREDICT
3	FY 18	Feb-17	445			174
4		Mar-17	217			190
5		Apr-17	253			213
6		May-17	396			244
7		Jun-17	250			222
8		Jul-17	237			207
9		Aug-17	569			426
10		Sep-17	188			167
11		Oct-17	159			144
12		Nov-17	118			111
13		Dec-17	175			129
14		Jan-18	148			135
15	FY19	Feb-18	142			133
16		Mar-18	235			215
17		Apr-18	225			216
18		May-18	305			292
19		Jun-18	241			236
20		Jul-18	380			366
21		Aug-18	274			273
22		Sep-18	308			300
23		Oct-18	221			214
24		Nov-18	177			175
25		Dec-18	242			240
26		Jan-19	204			204
27	FY 20	Feb-19	128			128
28		Mar-19	167			167
29		Apr-19	385			360
30		May-19	335			333
31		Jun-19	1493			755
32		Jul-19	317			311
33		Aug-19	221			218
34		Sep-19	301			297
35		Oct-19	324			317
36		Nov-19	230			227
37		Dec-19	139			137
38		Jan-20	159			159
39	FY 21	Feb-20	162			162
40		Mar-20	213			213
41		Apr-20	305			303
42		May-20	628			582
43		Jun-20	284			283
44		Jul-20	249			248
45		Aug-20	300			298
46		Sep-20	263			260
47		Oct-20	194			193
48		Nov-20	141			140
49		Dec-20	221			221
50		Jan-21	151			151
51	FY22	Feb-21	162	219		155	149
52		Mar-21	282	208		278	196
53		Apr-21	319	292		294	273
54		May-21		416			363
55		Jun-21		567			374
56		Jul-21		296			283
57		Aug-21		341			304
58		Sep-21		265			256
59		Oct-21		225			217
60		Nov-21		167			163
61		Dec-21		194			182
62		Jan-22		166			162
DATA (2)

Cell Formulas
Range	Formula
D51:D62,G51:G62	D51	=AVERAGE(C3,C15,C27,C39)

 

[johnnyL]

For a decent explanation of Forecast.Linear you can look --> Here.

This appears to be available only to versions 2016 and newer!!! Older versions used Forecast

Example:

=FORECAST.LINEAR(7,Y1:Y6,X1:X6)

Basically, the 7 would be your estimated future value of X, The result returned from the formula will be the future estimate of what y will be based on the x value you are estimating for the future.

The 1st part of the formula is your estimated value of what x will be, this must be a value, or a cell address that contains a value
The 2nd part of the formula is your range of known y values that have already occurred, This can be a named range also.
The 3rd part of the formula is your range of known x values that have already occurred, This can be a named range also.

These two ranges must be of equal size. <---

So the formula takes your two known ranges of x & y values, looks at what x value you have chosen to be in the future, and as a result it will spit out the future y value.

 

[DRSteele]

This solution relies on your having Excel 365 because it uses the new function FILTER.

Copy downwards the formulas in row3 all the way to row62.

MrExcel posts18.xlsx
	A	B	C	D	E	F	G	H
2	FY	Date	Total Orders	Total Orders Monthly Average	Total Orders Monthly Forecast	ARRIVE < 1HR	Arrive < 1HR Monthly Average	Arrive < 1HR Monthly Forecast
3	FY 18	2/1/2017	445			174
Sheet73

Cell Formulas
Range	Formula
D3	D3	=IF(ISNUMBER(C3),"",AVERAGE(FILTER($C$3:$C$62,($B$3:$B$62<B3)*(MONTH($B$3:$B$62)=MONTH(B3)))))
E3	E3	=IF(ISNUMBER(C3),"",FORECAST.LINEAR(B3,FILTER($C$3:$C$62,($B$3:$B$62<B3)*(MONTH($B$3:$B$62)=MONTH(B3))),FILTER($B$3:$B$62,($B$3:$B$62<B3)*(MONTH($B$3:$B$62)=MONTH(B3)))))
G3	G3	=IF(ISNUMBER(F3),"",AVERAGE(FILTER($F$3:$F$62,($B$3:$B$62<B3)*(MONTH($B$3:$B$62)=MONTH(B3)))))
H3	H3	=IF(ISNUMBER(F3),"",FORECAST.LINEAR(B3,FILTER($F$3:$F$62,($B$3:$B$62<B3)*(MONTH($B$3:$B$62)=MONTH(B3))),FILTER($B$3:$B$62,($B$3:$B$62<B3)*(MONTH($B$3:$B$62)=MONTH(B3)))))

 

[alm395]

I used those formulas & they worked. Only one problem...column H should be < column E. That is stating, of the total number of orders (column E), this amount arrived at the location under 60 minutes (column H). We then divide H by E to get our month-end percentage.