Finding current value only using moving averages of last 12 months

[gcefaloni]

Hi guys,

I'm trying to find the latest monthly value but I only have access to data that represents a moving average of the last 12 months. What operation should I do to extrapolate what the last monthly data point is?

Example:

12 Mo Yield 2016-01	12 Mo Yield 2016-02	12 Mo Yield 2016-03	12 Mo Yield 2016-04	12 Mo Yield 2016-05	12 Mo Yield 2016-06	12 Mo Yield 2016-07	12 Mo Yield 2016-08	12 Mo Yield 2016-09	12 Mo Yield 2016-10	12 Mo Yield 2016-11	12 Mo Yield 2016-12	12 Mo Yield 2017-01	12 Mo Yield 2017-02	12 Mo Yield 2017-03	12 Mo Yield 2017-04	12 Mo Yield 2017-05	12 Mo Yield 2017-06	12 Mo Yield 2017-07	12 Mo Yield 2017-08	12 Mo Yield 2017-09	12 Mo Yield 2017-10	12 Mo Yield 2017-11	12 Mo Yield 2017-12
4.17	4.11	4.05	4.00	3.92	3.83	3.76	3.70	3.63	3.58	3.54	3.71	3.64	3.63	3.63	3.62	3.61	3.64	3.66	3.65	3.67	3.66	3.65	3.56

I would like to be able to know what the actual yield for one specific month (say December 2017) was by extrapolating it from the 12 months moving average of yields showing above.

What operation would I need to do to do that? Know the current yield for a specific month, not a moving average of 12 months at a specific month.

Thank you!

[sandy666]

In attached Excel file try not use:

• merged cells
• unnecessary formatting like: colours, borders, aligning another than default, etc...
• unnecessary zoom

You have words to logically describe your problem

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

[gcefaloni]

In attached Excel file try not use:

• merged cells
• unnecessary formatting like: colours, borders, aligning another than default, etc...
• unnecessary zoom

You have words to logically describe your problem

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

Thanks, Fixed Any ideas how to solve my moving average issue?

[sandy666]

In short:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

and don't quote whole post!

[MrShorty]

Unless there is more information, I don't think this problem can be solved -- at least not with a single unique solution. This kind of problem usually ends up being a "solve a system of equations" type of problem, where you have 12 equations in 24 unknowns (see here if you need a quick refresher on solving systems of equations: http://www.purplemath.com/modules/systlin1.htm ). With a little algebra, you can find equations that relate the different months, but you will not be able to find actual values for any of the months.