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.
