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.

So… am I a crazy fool for using the utility companies’ budget pay service, in which they bill me the same amount every month, and adjust the amount a couple times a year? (I’m in the midwest too, so the regular amounts go from practically $0 to $250.)

alicia – You’re only a crazy fool if you like spending your free time in front of a spreadsheet doing budget calculations.

So how does this all work out in Excel? Is a moving average formula in Excel different than the normal average?

No, the moving average is calculated as you replace the oldest value in the spreadsheet. You’re simply using the normal AVERAGE function in Excel and making it a moving average on your own.

Trent, unless your new home is significantly more energy efficient than your old apartment or you very rarely use HVAC, I would pad the budgeted number by the ratio of square footage of the new home to the old, just to make sure that the additional conditioned space is taken into account.

Like alica, I use the utility companiesâ€™ budget pay service. It varies by +/- $5 per month, and it is less work. For my budget, I use the high-water mark for the utility bill (electricity, water, gas) for the past 12 months.

I’ve not signed up for my utility’s budget pay service because they always remind me about it at the beginning of a period of low electric bills–meaning *they* get the interest on the float, not me. I also would rather feel the full pain of the high bills, as an incentive to myself to minimize electricity usage.

I use the time-of-use plan, and have programmable thermostats to shift usage to off-peak.

Any suggestions on how to handle this if you are moving to a new construction home? The space difference between our current place and the new place is 11 sq. ft. so I was just going to use our current values, but they are in difference cities so I’m not sure if the rates will change.

Aw, there’s nothing wrong with being an Excel geek. Math is an especially refreshing salve for a bad case of OCD. And I’ve wondered if I should be checking up to make sure the utility companies calculate the payments correctly. Overall, the amounts have seemed right and have gone down or up when I expected they would. (People’s Energy recalculates every six months, but I think ComEd only does it once a year.)

@alicia: Well, some factors to consider are the building envelope, efficiency of the HVAC units, and volume. Vaulted spaces and high ceilings (more and more common in new construction) take more energy to condition than the traditional 8 ft. ceiling height.

If you’re significantly increasing the volume that you’re conditioning, you might want to build some excess into the budget. For example, my parents moved into a home with 12 foot ceilings on the ground floor. That factor increased their heating and cooling bills significantly.

I, on the other hand, live in a 1970s home with 7.5 foot ceilings (the builder was cutting costs) and my 1500 square feet cost about the same amount to condition as did my previous 1000 square foot apartment with 9 foot ceilings.

The efficiency of the new HVAC system might actually cause your bills to drop in the new construction home. But since I’m a conservative budgeter, I’d personally stick with the higher number until I was proven wrong.

Sorry, that should have been to Andrea, not alicia. Oops!

I use the moving average for every category in my budget. It’s particularly useful for things like gifts (which are very seasonal) clothes (I do the vast majority of my shopping during the January and July sales) and occasional expenses like home repairs. I even set up my spreadsheet to average some expenses (like food) over three-month intervals, and others over 12 or even 24 month intervals.

Yes, I am an Excel geek.

You mentioned that your checking account earns you interest? I’d be very interested in knowing what bank you’re with…I certainly don’t earn interest on the money in my checking account!

Living in the Midwest like Trent, we also see quite a shift in utilities costs over the course of the year; in our case, though, our furnace uses natural gas, so our electric bill is lowest in the winter and highest in the summer… when you graph it, our electric bill & gas bill are very “wavy”, inversely related, of course. Because of this, a moving average doesn’t work as well. Instead, every month we look at what we spent in electricity & gas during that month the previous year and pad a little to be safe. We’ve been budgeting for two and a half years, and it’s worked out pretty well to this point.

Ooh, good idea. I use a moving average to track my weight, but never thought about using it in budgeting.

Excel is good, but if you really want to geek it out, try R (http:/r-project.org/).

You can start the “equal payment plan” anytime you like with my electric utility.

I started it in June – so I’m using much more power than I’m paying for for the first 4 months – we settle up in May each year.

So the utility carries my debt.

What about tracking all of your utilities into one monthly average, instead of individual ones. This would compensate for seasonal differences, where electric might be more in the summer due to ac, while gas is more in the winter due to heating.