Every budget season I struggle with coming up with a tool to help me budget unit activity for next year. Today, I’d like to share a little add-in tool called “moving average”.
Follow these steps to add it in (For MS 2007 and higher):
1. Go to Office Button|Excel Options|Adds In|Go…
2. Click theAnalysis ToolPak-VBA check box and click OK.
3. Go to Tools |Data Analysis. expand »
4. Click Moving Average and click OK.
What to do with this? I always like to look at the clinical activity for 36 or more consecutive months for pattern and trend. Sometimes, that is not always the best option. There are a lot of reasons for the variability amongst the data points. Seasonality, time off, demands, supplies, and others are some of the common reasons.
There are many different ways for you to “massage” that data. One of the best and easiest ways is to use the moving average. By using the tool I mentioned above, you will be able to calculate and graph a 3 month moving average in 3 seconds.
Seeing a 3 month moving average chart and graph will help you see patterns and trends that were not there before.