Compare modeled (fitted) paired data to actual data in forecasting problem (Excel sheet included) In my endeavor to learn forecasting and improve my statistical knowledge, I've decided to forecast the population of a certain area. I've attached the excel file that I used. I know I'm using excel which is not very common around here. I'm familiar with R but prefer excel for fast analysis such as this.
The collected data is for 1921 to 2009. The forecast period is between 2010 and 2019. I did:


*

*Arima over all the data

*Arima over the last 10 years or so of the data

*Linear regression over the whole time period

*Linear regression over the last 10 years

*Fitted model using Eureqa software

*Average of above models without the overall linear regression.


The forecast worksheet includes all the raw data, while the forecasting simplified worksheet includes the simplified chart to better view the results.
The question, what are some of the ways I can validate and/or calibrate my forecasts, numbers, assumptions, and improve my analysis from an amateur level to something that is more professional.
Thanks
Excel File
 A: Quick three tips after reviewing your excel sheet: 
1) If you use a derivative on your metric (population in this case) and try to fit the models through it, you will get a smoother fit as it will eliminate the initial 'jump' in your forecasts. For example, in the "FORECASTS" sheet create a new metric which calculates ((B3/B2)-1) and fit your model through the new metric.
2) Try to find patterns in the data before choosing your period. For example, I see you chose the last 10 years for the prediction model. That is great since the growth significantly slowed down around these years, so probably better for prediction.
3) This is more of a goal based approach. Try to determine what is this forecast going to be used for. If it was for planning food supply for a city, perhaps you prefer to overestimate. If its for tax revenues, you would prefer to underestimate, etc. This will also help you choose the appropriate forecast period, and build a 'range' of forecasts instead of a particular expectation.
Hope this is helpful! And good luck with your work,
Erad
