Forecast based on same period last year

[charlesfarrell]

Hi Everyone,

I was wondering if anyone might be able to help with some forecasting I am working on, file attached.

I have a daily running total of new students for Term 2 last year 2017, along with the % to target for that term.

I would like to forecast this years Term 2 2018 based on last years data and also factoring in the actual data so far this year, I'm not sure if I've explained that clearly enough.

I can forecast based on the data so far this term but I am not sure how to forecast based on the same period last year, the forecast goes through the roof.

Any help anyone could offer or suggest would be greatly appreciated.

Many thanks,
Charlie

Capture.PNG

[AliGW]

Not sure, but shouldn't the formula be this (in F21 for example)?

=IF(E21="",FORECAST.ETS($B21,$F$2:$F19,$B$2:$B19),E21)

EDIT: no, forget that!

[JeteMc]

Perhaps this will help.
A new column (E) is inserted which monitors the daily percentage change of the number of students in the 2017 term using: =(C3-C2)/C2
The "T218 New Students Forecast" column is populated using: =IF(F2<>"",F2,G1*(1+E2))
Let us know if you have any questions.