Finding current month sales using moving averages only.

[gcefaloni]

Hi guys,

I'm trying to find what operation I need to do to find out how much sales I did in a specific month by only using 5 month moving averages of sales. So, if May sales of 36 units roll off the moving average and the moving average goes from 51 in Aug to 66 in Sep. What operation do I need to do to figure out that the sales in September were 100 units if I don't have access to the B column (Sales) and only know the C column of moving averages. I want to figure how my sales for the month of September in this example.

Thank you!

[TABLE="width: 465"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Sales[/TD]
[TD]Moving Average last 5 months[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-17[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Feb-17[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Mar-17[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Apr-17[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]May-17[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="align: right"]Jun-17[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]Jul-17[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD="align: right"]Aug-17[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD="align: right"]Sep-17[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD="align: right"]Oct-17[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]83[/TD]
[/TR]
[TR]
[TD="align: right"]Nov-17[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]102[/TD]
[/TR]
[TR]
[TD="align: right"]Dec-17[/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]123[/TD]
[/TR]
</tbody>[/TABLE]

 

[Legacy 143009]

Why don't you just try this and pull down?
C6 =AVARAGE(B2:B6)

 

[gcefaloni]

Why don't you just try this and pull down?
C6 =AVARAGE(B2:B6)

Because, thats the whole problem. I don't have access to the B column. I want to estimate the value in the B column at a specific month FROM the differences in the C column. I just included the B column to illustrate how I got the moving average values. I want to estimate the Sales in September 2017 by only having access to moving average from May to August and rolling off older data.

 

[Legacy 143009]

Sorry my formula is not right.

It seems pretty impossible without access to Column B. last 4 moths could be very small moves or either very large moves. And even for a slightest avarage change in semptember, you may either need a big jump or a small step. I couldn't figure it out.