Help with Formula Placement for 12 Month Centered Moving Average

Hi all, been lurking for awhile soaking up information, but have ran into something I'm not sure of...hope someone can help!

I'm creating a sales forecasting tool in Excel. Im wanting to compare a 12, 6, and 4 month centered moving average. I just need a bit of help in terms of laying out the formula, and where to place it. I've performed a BUNCH of other calculations in my actual file. All of these are reliant on the CMA placement due to references, and so I would love some help to make sure my formulas are in the right places (?period 6 or 7?, 12 or 11? etc). Its a bit hard for me to "visualize" where they need to be on a spreadsheet - I've seen examples where people create a "pd x.5" cell, but if possible I'd like to avoid that.

Example Data attached. The dates are correct (Correct starting and ending month/year), with June being the first month I'd like to forecast. The actual values were randomly generated & pasted in.

Ignore the t, Month Code, and Year - they are there for other functions to de-trend + deseasonalize the data, and put them back together later on in the forecast which I've done already in my actual file - the other calcs I mentioned previously that rely on the CMA



Example File:
12mCMA.xlsx
Feel free to edit it up as you wish. Adding comments to the file would be greatly appreciated, so I can learn!!


Actual File Available on request if needed.


Also, bonus points and a huge level of gratitude to *anyone* who can give me some information/help me on any of the following to make this a more efficient tool:

1) Setting this up so that I can continually plug in numbers to perpetuity/on and on, without having to change my formulas & functions each and every time I get new data.

2) Setting it up so that I could change my moving/centered moving average periods on the fly and see the outcomes




Sorry for the length of this post, I just wanted to provide as much info and/or clarification as posisble to hopefully make it easier on anyone willing to help. A big pre-emptive thanks to anyone that can help me out. It is definitely appreciated!

Thanks a TON! Just a few questions to make sure I understand everything properly to make sure I use it correctly.

1) Im assuming that as new data becomes available, I simply fill the formulas down?
2) Are there any cells with formulas that I can't "move around" (referring mostly to the period selector that you have highlighted)
3) Do I need to leave any cells blank at the end (as in 3 for a 6pd, 6 for 12pd, etc)?

Thanks so much for your help. I'll have this marked as solved soon!

Awesome, thanks so much! Marking as solved, and +Rep'd you. Much obliged, really.