Help with Forecast/Trend function

[rrmando]

Hello party people. I am trying to forecast/trend some safety metrics. The goal is to have 0 (zero) for the year or to be as low as possible. Each category started with zero, we had some incidents, but the metrics have been trending lower since. When I try using the forecast or trend functions, the results are usually higher than historical. I am trying to forecast periods 9 through 12.

Column F is the only metric that looks like it is trending correctly (going lower), all the others are trending higher. Column E for example started out with zero for a few months, had some incidents, but it should be trending down. Should I be using a different formula? Do the forecast/trend formulas take into consideration the zeroes or not? Any suggestions? Thank you.

Excel 2012
	A	B	C	D	E	F	G	H	I
1	Region	Month		Safety Year	WC-LCPH	Auto-LCPH	GL-LCPH	ALL Lines--LCPH	Target
2	207	Aug-15	1	2016	0.000	0.326	0.000	0.326	0.28
3	207	Sep-15	2	2016	0.000	0.150	0.412	0.562	0.28
4	207	Oct-15	3	2016	0.000	0.162	0.830	0.992	0.28
5	207	Nov-15	4	2016	0.000	0.132	0.631	0.763	0.28
6	207	Dec-15	5	2016	0.000	0.131	0.522	0.654	0.28
7	207	Jan-16	6	2016	0.070	0.147	0.455	0.673	0.28
8	207	Feb-16	7	2016	0.061	0.135	0.450	0.647	0.28
9	207	Mar-16	8	2016	0.053	0.124	0.390	0.568	0.28
10	207	Apr-16	9	2016	0.070	0.081	0.552	0.705	0.28
11	207	May-16	10	2016	0.081	0.063	0.572	0.717	0.28
12	207	Jun-16	11	2016	0.092	0.045	0.592	0.730	0.28
13	207	Jul-16	12	2016	0.102	0.026	0.612	0.742	0.28
Sheet1

Cell Formulas
Range	Formula
E10	=TREND($E$2:$E$9,$C$2:$C$9,C10)
E11	=TREND($E$2:$E$9,$C$2:$C$9,C11)
E12	=TREND($E$2:$E$9,$C$2:$C$9,C12)
E13	=TREND($E$2:$E$9,$C$2:$C$9,C13)
F10	=FORECAST(C10,$F$2:$F$9,$C$2:$C$9)
F11	=FORECAST(C11,$F$2:$F$9,$C$2:$C$9)
F12	=FORECAST(C12,$F$2:$F$9,$C$2:$C$9)
F13	=FORECAST(C13,$F$2:$F$9,$C$2:$C$9)
G10	=FORECAST(C10,$G$2:$G$9,$C$2:$C$9)
G11	=FORECAST(C11,$G$2:$G$9,$C$2:$C$9)
G12	=FORECAST(C12,$G$2:$G$9,$C$2:$C$9)
G13	=FORECAST(C13,$G$2:$G$9,$C$2:$C$9)
H10	=FORECAST(C10,$H$2:$H$9,$C$2:$C$9)
H11	=FORECAST(C11,$H$2:$H$9,$C$2:$C$9)
H12	=FORECAST(C12,$H$2:$H$9,$C$2:$C$9)
H13	=FORECAST(C13,$H$2:$H$9,$C$2:$C$9)

 

[DRSteele]

Both functions TREND and FORECAST certainly take the zeros into account. In fact, the zeros are helping to cause the illusion. If you apply your formulas to only E7 to E9 (which is hardly a proper data set, I realise) you will see the forecast figures falling. Both those functions rely on the least-squares method to come up with an equation for line-of-best-fit; all those opening zeros therefore create a LOBF that slopes upward to the right.

I suggest there is an insufficient amount of data (only 9 data points) to extrapolate accurately anyway.

 

[rrmando]

Thank you for the feedback.

 

[DRSteele]

You're welcome.