Using A Moving Average In Budgeting

My wife and I use a simple mathematical construct called a moving average in order to help us budget and estimate upcoming bills.

What’s a moving average? Let’s say our telephone bills over the last twelve months were as follows:

May 2006 – $55
June 2006 – $55
July 2006 – $58
August 2006 – $55
September 2006 – $80
October 2006 – $55
November 2006 – $55
December 2006 – $55
January 2007 – $85
February 2007 – $55
March 2007 – $55
April 2007 – $57

Our basic cost is $55, but on occasion we use our phones outside of our calling area and incur some hefty bills. This happens roughly at random.

If we average the costs of these twelve bills, we come up with an average of exactly $60, which is what we estimate our next cell phone bill to be. Most of the time, it’s slightly under this, so we take that extra $5 and leave it in our checking account, where it earns interest. Then, when we get a higher bill, we can pay it off easily with the money we’ve been holding onto.

Easy, right? Here’s where it gets interesting. Let’s say our next bill, for May 2007, is $75. When we recalculate, we don’t just add on the new number, we drop the oldest one. Why? It reflects our situation far enough in the past that it doesn’t really reflect the actual use right now. So, when we re-average the numbers, dropping the oldest one and adding the new one, we get a new average of $61.67, which is how much we budget for for the June bill.

It’s really easy to keep track of all of this in Excel. We just have twelve rows (one for each month), and we just overwrite the bill from a year ago with the newest bill and Excel recalculates our budget for us. Over time, as we become more careful with our cell phone minutes, this moving average slowly drops and frees up more space in our budget. We can actually see frugality at work.

What about when the bill varies quite a bit? Let’s say our electric bills over the last year were as follows:

May 2006 – $50
June 2006 – $60
July 2006 – $65
August 2006 – $70
September 2006 – $45
October 2006 – $40
November 2006 – $50
December 2006 – $80
January 2007 – $85
February 2007 – $95
March 2007 – $70
April 2007 – $60

This is roughly typical for the Midwest, as fall and spring electricity usage is always much lower than summer (when you need air conditioning) and winter (when you need heating).

If we average the cost of these twelve electric bills, it comes out to $64.17, which we use as an estimate for the upcoming electricity bill. However, we do know that in the winter and summer it’s going to be more than that, and in the spring and fall, it’s going to be less than that. So, we budget in $64.17 for electricity for May, knowing that almost assuredly our bill will be less than that, and we leave the excess in our interest-bearing checking account. Then, in August, we use that money we left in checking to pay the inevitably higher bill, and we’ve earned a bit of interest in the process.

Much like the cell phone example, when we initiate cost-saving measures (like installing CFLs), it causes all of the bills to drop, and over time we begin to see more breathing room in our budget (which usually means more money in the bank). Excel makes this all automatic, thankfully.

What happens when you move and all of the numbers change? First thing, we take the utilities estimates from the previous owner and add 20% (to give us plenty of leeway). Then, we enter this number in for all twelve months, so the moving average is actually just equal to the estimate plus 20%. When we start receiving bills, then we start entering real numbers – almost assuredly, the real bills will be lower than our padded estimates, so over time our budget will gain breathing room rather than losing it.

Loading Disqus Comments ...