Forecast Formula

[EXCELBENCH]

Hi

Anyone can share their own Forecast formula.
e.g.
Current Data points (These are scores from Trainees):
7, 10, -1, 3, 4, 25 from April to September
test file1 image.PNG

What would be your next 5 data points (Oct - Mar) based on the current data points? Predictions for the next 6 months?

Appreciate the help.

[AliGW]

Sharing is caring.

We know. There are many, many helpers here, all doing it for FREE. Altruism abounds here, so you don't need to tell us how to do it.

So, what happened when you put those data points into Excel and tried the FORECAST formula?

[EXCELBENCH]

Hi Maam - How have you been. Long time since I used the forum and I learned a lot from the past.

Goal is to have a prediction of what Scores the Trainees will get on the future months based from their current scores.
Current Data points:
7, 10, -1, 3, 4, 25
This is their scores from April to September. The forecast formula will give us the predicted scores from the next 6 months.

[AliGW]

So, I'll rephrase my question: what was the problem you encountered when you tried the FORECAST function?

Please attach a workbook to show the issue.

[EXCELBENCH]

I was hoping to have some suggestions based on experience in using other formulas and not the forecast function. I attached file.

[AliGW]

OK - in that case, your thread title is NOT detailed enough. Please update it. Two words are not enough.

[AliGW]

Change your headers to real dates:

AliGW on MS365 Insider (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Trainees	01/04/2021	01/05/2021	01/06/2021	01/07/2021	01/08/2021	01/09/2021	01/10/2021	01/11/2021	01/12/2021	01/01/2022	01/02/2022	01/03/2022
2	Trainee 1	7	10	-1	3	4	25	15.621251	17.832721	19.972854	22.184325	24.395795	26.393253
3	Trainee 2
4	Trainee 3
5	Trainee 4

Sheet: Sheet2

=FORECAST.LINEAR(H1,$B$2:$G$2,$B$1:$G$1)

[EXCELBENCH]

Great! I'll use this. Change the dates. Thank you as always. I appreciate your help Maam.

[davsth]

From your data, predicting 6 points in the future from 6 in the past where there does not seem to be a clear pattern, is very unreliable. who could guess it would change from 10 to -1 or 3 to 4 and bang 25!

There are formulas that will give you an answer, but I would not trust the answer and it would be very unreliable. If you had to predict September from the previous 5 points, you may have gone around 4 (the previous value) arguments could be made why it might by higher or lower. But 25 (150% higher than the previous highest value) would not even be entertained as an option.

In short the data provided is not suitable to try and apply this kind of approach, it is too volatile and insufficient.
.

[EXCELBENCH]

Can we only use this for daily numbers? The scores are monthly in frequency.
Attachment 751615

[EXCELBENCH]

I agree the data could be high in variance but that's the actual numbers. Forecasting can't be 100% reliable I agree. But a visual trend on how the scores would look like in the future based on the previous scores are helpful especially in terms of coaching.

[curiouscat408]

@ExcelBench.... First, the attachment link in posting #10 does not work (for me?). I notified the administrator, per instructions in the error message.

Second, you acknowledge that forecasting is a crapshoot ("not 100% reliable"). So, the "correctness" of any forecast is in the eye of the beholder.

Yes, your historical data seems to demonstrate an upward linear trend. But obviously, the historical data does not track the linear trendline closely. So, to my "eye", simply using FORECAST does not yield a forecast that "looks like the future".

Moreover, you might know things about the trainees' scores that we do not. For example, perhaps Trainee 1's scores bounce around an average, not an upward linear trend.

And in any case, since these are "scores", I suspect that there is a minimum and maximum score, which might or might not be the same in the future. That would put limits on any forecasted number.

With the limited historical data, a simple (and arguably overly-simplistic) approach is to assume that future scores differ from the linear trendline by the same amount that the past did, month-for-month. But differ how?

Initially, I assumed a percentage difference from the trendline. But to my "eye", that resulted in too much variability as the score increased.

So I settle for an absolute difference from the trendline. See the image below. Also see the attached Excel file.

The formula in H3 is (forecast data):

=MIN($B$8, MAX($B$9, ROUND(FORECAST(H2, $B$3:$G$3, $B$2:$G$2) + B13, 0)))

The formula in B13 is (historical variance):

=B3 - FORECAST(B2, $B$3:$G$3, $B$2:$G$2)

Note that B2:M2 are time indexes (1 through 12), not the dates in B1:M1. The reason is: I presume that you want "monthly" data, treating each month the same. Using actual dates adds an unintended degree of accuracy between data points.

And to reiterate: the forecast is arbitrary. It is no more nor less "correct" than any other (reasonable) forecast. It all depends on what __your__ eyes behold.

-----