Average forecast performance x number of days out from actual

[aussiecraigo]

I want to be able to measure the performance of a forecast model that I have created trying to predict the number of visitors we will get each day.

To explain my below worksheet:
* As of midnight on 1 June 2022, I had predicted 665 visitors to visit during the day on 1 June. We ended with an actual of 655 so on the day of, my forecast was 1.5% over the actual.
* As of midnight on 1 June 2022, I had predicted 565 visitors for 2 June 2022 and we ended up with an actual of 662 so 1 day out my forecast was -14.6% under the actual.
* As of midnight on 2 June 2022, I had predicted 585 visitors for 2 June 2022 and we ended up with an actual of 662 so on the day of my forecast was -11.6% under the actual.

What I'd like to be able to do is average my forecast % +/- performance on the day of, 1 day out, 2 days out, 3 days out and so on, up until 7 days out.
Essentially, if you look at the % screenshot, I'd like to be able to average automatically, the light blues, the light greens, the yellows etc. The will be data added almost everyday and all new data would need to be included in the averages.
In the last screenshot, is a table on how I'd like to report the average % +/- (I have just manually average the appropriate cells).

I hope that I'm making sense and there might be someone out there who can help me.

Any assistance or advice would be appreciated.

Thank you....

 

[aussiecraigo]

I should also mention that in the percentage calculation (the 2nd screenshot), I have a formula reporting 'blank' if there isn't a forecast or an actual (we are closed every Tuesday).