I'm looking for some advice on how to determine how accurate a forecast is.

Basically, what I have is 23 years of competitive results for a particular sport (10000+ matches). The matches are broken down into team, division, home, away, and neutral matches, as well as score. From this I was able to determine each team's for and against scores for each year, depending on the division of their opponents and venue. However, I'm trying to determine the best method of forecasting the scores going forward (about 12 months).

My first thought was to use forecast the averages and backtest then vis-a-vis the the historical results. This leads to some differences (obviously), but I can't determine how significant these differences are.

As an example, I used 10 teams, and forecast using the proceeding 5 year period. When I got the average of the average differences between actuality vs forecast, I got 0.44 (e.g. average of the average of 18 data points per team for 10 teams). If I change it to a 4 year period, it (obviously) changes, in this case to 0.75.

Is this the correct way of determining the best forecast model or if there is a a more correct method, that is widely accepted, for determining which forecast model is most accurate?

BTW: doing this in Excel, so answers with Python, R, or other programs, while useful, are not great.

  • 5
    $\begingroup$ I would advise learning a statistics package - R or Python or SAS or Matlab or whatever - and not using Excel for complicated analyses. $\endgroup$ – Peter Flom - Reinstate Monica Dec 13 '18 at 11:54
  • 2
    $\begingroup$ There is no "one correct" method of assessing forecast accuracy. I describe some background in the proposed duplicate. If this does not answer your question, it may help you clarify what specifically you are looking for. (And I agree with @PeterFlom's comment.) $\endgroup$ – Stephan Kolassa Dec 13 '18 at 13:15
  • $\begingroup$ While I appreciate the view that Python, R, etc might be better, I'm currently not in a position to learn them (not can I install them on employer's machines). Will look at the other question and see if I can determine how it could help. Is there anything that Excel can do in the meantime? $\endgroup$ – Clauric Dec 13 '18 at 14:06

Browse other questions tagged or ask your own question.