Among the many ways to understand data collected over time is to present and analyze it using what are called “moving averages.” Somewhat like smoothing data rather than using a single linear trend line, the topic of an earlier column, moving averages get away from overall metrics such as annual figures and instead show trends during shorter time periods, such as monthly or quarterly, and how they are varying within those periods. As each month or quarter contributes its data, the moving average changes.
We frequently hear the term “seasonally adjusted economic indicators.” This is not some complicated formula, but merely moving averages that show the bulges and declines peculiar to certain months as the year progresses. Likewise, stock market closes are often shown as moving averages to give a better sense of direction.
Why are moving averages an improvement over generic averages? A general counsel could tell the CEO, “We have averaged $800,000 per month in outside counsel fees during the last two years.” A more informative statement could use moving averages: “Our three-month rolling average of outside counsel fees paid has moved from $1 million down to $750,000 for the latest three months.”
The second statement more fully explains the direction in which outside counsel spend is trending (and certainly sounds more financially and mathematically sophisticated!). It gives up-to-date metrics rather than metrics that might have been skewed by earlier periods.
Here is a quick tutorial on how to find the rolling average. Enter a column of numbers into your spreadsheet program, for example, your legal fees paid per month. Then, you have two choices in Excel to produce moving averages. If you create a chart that portrays the monthly figures, you can insert a trend line. One trend line selection in Excel 2007 is “moving average” and you can choose the interval. If on this example you chose “3” you would produce a quarterly moving average that will recalculate each time a new month is added and the formula copied down to include it.
The other way to do it is to calculate the average of the first three months—for example, (=average(A1:A3)) in the cell to the right of the third month at A3—and then drag that formula down the column until you run out of months. Each of those new cells calculates the rolling average for the previous three months in the first column. If you want four months or six months, expand the initial range.
Many people go further and create a graph that shows the progression of moving averages, with one data point for each month after the first of three (or whatever the interval). As a final note, rolling right along so to speak, some matter management systems may have the ability to calculate and display moving averages for spending numbers. The moving average for total external spending could play a prominent role in a dashboard.
