12 month moving average

[jesssaymsu]

I need help calculating a 12 month rolling average for employee turnover for the month of January. After I get the turnover, then I need to display this in a graph or sort.

I'm lost. I know how to do the rolling average for each the turnover and the headcount, then calculate Jan turnover, BUT i'm not sure I'm showing my director what they are expecting. What's expected to show the Jan 2014 vs. the Jan 2015 turnover for the rolling average.

Could someone please help!! I have attached my data.. i'm so lost

Data:

[TABLE="class: outer_border, width: 567, align: center"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Terminations [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD]Termination Date[/TD]
[TD]Total[/TD]
[TD]12 Month Rolling Sum[/TD]
[TD]12 Month /12[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Jan[/TD]
[TD]63[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]56[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]49[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD]69[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]99[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD]93[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]84[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD]102[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD]78[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD]136[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD]76[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD]64[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]Jan[/TD]
[TD]46[/TD]
[TD="align: right"]952[/TD]
[TD="align: right"]79.3333333[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]38[/TD]
[TD="align: right"]934[/TD]
[TD="align: right"]77.8333333[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]51[/TD]
[TD="align: right"]936[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD]46[/TD]
[TD="align: right"]913[/TD]
[TD="align: right"]76.0833333[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]91[/TD]
[TD="align: right"]905[/TD]
[TD="align: right"]75.4166667[/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD]48[/TD]
[TD="align: right"]860[/TD]
[TD="align: right"]71.6666667[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]63[/TD]
[TD="align: right"]839[/TD]
[TD="align: right"]69.9166667[/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD]78[/TD]
[TD="align: right"]815[/TD]
[TD="align: right"]67.9166667[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD]84[/TD]
[TD="align: right"]821[/TD]
[TD="align: right"]68.4166667[/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD]77[/TD]
[TD="align: right"]762[/TD]
[TD="align: right"]63.5[/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD]52[/TD]
[TD="align: right"]738[/TD]
[TD="align: right"]61.5[/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD]56[/TD]
[TD="align: right"]730[/TD]
[TD="align: right"]60.8333333[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Jan[/TD]
[TD]84[/TD]
[TD="align: right"]768[/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]16[/TD]
[TD="align: right"]746[/TD]
[TD="align: right"] 62.1666667[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Grand Total[/TD]
[TD]1799[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




Headcount

[TABLE="class: outer_border, width: 624, align: center"]
<colgroup><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 5"]Headcount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Years[/TD]
[TD]HC Month[/TD]
[TD]Total[/TD]
[TD]12 monthrolling Sum[/TD]
[TD]12 month / 12[/TD]
[TD][/TD]
[TD]% of Turnover[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]Jan[/TD]
[TD]2919[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]2933[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]2957[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD]3010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]3047[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD]3041[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]3091[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD]3135[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD]3161[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD]3178[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD]3164[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD]3166[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Jan[/TD]
[TD]3168[/TD]
[TD="align: right"]37051[/TD]
[TD="align: right"]3087.58333[/TD]
[TD][/TD]
[TD="align: right"]30%[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]3154[/TD]
[TD="align: right"]37272[/TD]
[TD="align: right"]3106[/TD]
[TD][/TD]
[TD="align: right"]30%[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]3182[/TD]
[TD="align: right"]37497[/TD]
[TD="align: right"]3124.75[/TD]
[TD][/TD]
[TD="align: right"]29%[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD]3213[/TD]
[TD="align: right"]37700[/TD]
[TD="align: right"]3141.66667[/TD]
[TD][/TD]
[TD="align: right"]28%[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]3251[/TD]
[TD="align: right"]37904[/TD]
[TD="align: right"]3158.66667[/TD]
[TD][/TD]
[TD="align: right"]28%[/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD]3274[/TD]
[TD="align: right"]38137[/TD]
[TD="align: right"]3178.08333[/TD]
[TD][/TD]
[TD="align: right"]26%[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]3299[/TD]
[TD="align: right"]38345[/TD]
[TD="align: right"]3195.41667[/TD]
[TD][/TD]
[TD="align: right"]25%[/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD]3388[/TD]
[TD="align: right"]38598[/TD]
[TD="align: right"]3216.5[/TD]
[TD][/TD]
[TD="align: right"]24%[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD]3407[/TD]
[TD="align: right"]38844[/TD]
[TD="align: right"]3237[/TD]
[TD][/TD]
[TD="align: right"]24%[/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD]3435[/TD]
[TD="align: right"]39101[/TD]
[TD="align: right"]3258.41667[/TD]
[TD][/TD]
[TD="align: right"]22%[/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD]3385[/TD]
[TD="align: right"]39322[/TD]
[TD="align: right"]3276.83333[/TD]
[TD][/TD]
[TD="align: right"]22%[/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD]3420[/TD]
[TD="align: right"]39576[/TD]
[TD="align: right"]3298[/TD]
[TD][/TD]
[TD="align: right"]21%[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Jan[/TD]
[TD]3520[/TD]
[TD="align: right"]39928[/TD]
[TD="align: right"]3327.33333[/TD]
[TD][/TD]
[TD="align: right"]22%[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Grand Total[/TD]
[TD]79898[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jumbledore]

could you pls put this data in a workbook and upload it. you can upload it through a 3rd party website like upload.com and paste the link

 

[MARK858]

@jumbledore, the data the OP provided copies and pastes perfectly well into an Excel spreadsheet

 

[MARK858]

@ jesssaymsu I am just about to go to work and so don't have time to look at this now but will take a look tonight if nobody else has responded (but expect questions).

 

[jesssaymsu]

@ jesssaymsu I am just about to go to work and so don't have time to look at this now but will take a look tonight if nobody else has responded (but expect questions).

Thank you! I appreciate any support

 

[jumbledore]

@jumbledore, the data the OP provided copies and pastes perfectly well into an Excel spreadsheet

thanks

@jesssaymsu you need to correct the way the years are displayed. it should be start with the year 2013 and then 2014 and so on. your calculations appear to be correct. I am not sure how you calculated the % of Turnover. But I think you just need to show a graph of the 12 month rolling average for the months of 2014 and till feb 2015.