# What can you do with 'crazy' data?

This question is more about an approach to a complicated data situation rather than particular statistical methods.

I'm modeling our organization's electricity bills, and I have monthly billing data from 2008 to the present. I have a fairly accurate model for electricity usage, based on the month's average temperature. And I have a fairly good model for the basics of cost, based on usage, demand, and peak-season surcharges. (The cost model is what I really need, for budgeting purposes.)

BUT the problem is the data. To give an example, in 2010 we received two different sets of credits, based on two different sets of overcharges in 2009, while at the same time we were evidently experiencing the hottest summer and coldest winter in a century, which means that for the next two years (2011-2013) we will be paying a surcharge to make up for that.

It's a messy situation and I'm not sure how to handle it. My first attempt was to modify the data by shifting the refunds to the appropriate period in the past. Unfortunately, one of the refunds is calculated based on data I can't get, so it's just a guess on my part. And who knows how much the two-year surcharge will actually be.

So I'm wondering what the proper approach would be, if any?

1. Is it even worth trying to adjust the data? Should I just accept that the bills are the bills and not care about how money is shifting around from year to year? I would think it would result in much higher variance and would mess up trends.
2. Could I try to use indicator variables to indicate when various surcharges and refunds hit? I can't believe this would work with only 3-4 years of data.
3. Should I try to model the various parts of the bill separately? Some (like the usage and demand charges) might be fairly stable, while others (like refunds or fuel charges) would be appropriately volatile.
4. Is it a mistake to try to model monthly bills at all? Should I model at the year level to hopefully smooth out things a bit?

I'd appreciate any ideas or suggestions.

I have realized that at a higher level the issue isn't so much to address error as it is to minimize the spread relative to the mean. One measure of spread is the standard deviation, so this works out to be the coefficient of variation. Ultimately, your organization would really like a lower mean cost for members and you'd like more certainty (less spread) around the expected cost (which isn't the actual, realized cost).

There are three ways to look at this:

1. A good modeler will reduce the CV by reducing the SD
2. A bad modeler will reduce the CV by increasing the mean ("just to be safe")
3. A good negotiator will reduce the mean and, hopefully, the SD. This may reduce the CV, but a good negotiator might just say that having some bands (and penalties for exceeding those bands) for the expected costs is an adequate compromise for reducing the mean.

We could help you reduce CV through models (not to be confused with the "CV" of this site, by the way ;-)), but it would be better to negotiate this in such a way that you reduce the costs and the uncertainty.

You may consider the response as a sum of random variables, some that can be decomposed into models based on other variables, some that cannot.

If you are estimating monthly costs, then you report having a decent model for the covariates you know, and then you may add to this costs that you cannot decompose as a function of other covariates. Some might call this a random effects model. Others may invoke the name of Bayes. I do neither, but acknowledge that some people have these cravings.

At an annual level, your estimates may be the sum of the monthly estimates, but I doubt that errors by month are independent of each other. I'll assume you know what to do here. If billing corrections are applied at an annual level, then this is simply a random variable term that you may add to the model, with its own assumptions about its distribution. You may be able to get information from the utility or utility regulator(s) about its past corrections for other large customers (I assume you're a large customer if you have such concern). This may help in deciding on the assumptions for the distribution to use for the random variable for billing corrections.

I know this is very general, but it's pragmatic. Just because a random variable isn't decomposed into a model isn't bad - that's real the essence of so-called error terms: those elements of a stochastic phenomenon for which we accept that we cannot model them. It doesn't mean they cannot be modeled, but due to some limitations of our expertise, model, data, and so on, we simply push them into a separate catch-all. We may add some assumptions about them, such as normality (which can and should be tested), but that doesn't mean we've said more or less about them. We might even be more honest about the error term than about the model, because all too often people do not test their model itself, just the residuals.

• Thanks! In terms of being large enough to get more info from the utility, we're a condo association so we're medium-sized and fall into that awkward gap of: large enough that we're talking a fair amount of money, yet not large enough that the utility especially cares about us and not as financially flexible as a company. We're talking roughly \$200,000 for electricity annually, and the cost models I've created (using lmer) have 95% credible intervals (by simulation) of \$20,000-40,000 which too large to budget against. – Wayne Aug 9 '11 at 18:03
• Well, you're a condo association which is presumably a member of a larger statewide condo organization. :) Think in terms of scale, and then you're talking clout. Just ping the largest representative group you can, and see if they can get the data for you. It never hurts to see who can pull some levers for data. A few breadcrumbs such as a little report that they can send their members could go a long way in convincing them to check on this. – Iterator Aug 9 '11 at 19:57
• Also, I'm not sure that a range of $20-40K (+/- 10-20%) is that unreasonable. 1. Consider overall #s of people - although it's a slight stretch, an apt bldg could easily have 10% vacancy or more during certain stretch of the economy. 2. Sums can be affected by outliers. It may only take a few extreme people to push total usage up a bit. – Iterator Aug 9 '11 at 20:03 • The one good thing is that the bill is for the Association itself, not individual units. (I.e. our hallway lights, ventilation, and air conditioning, our elevators, pumps, and other infrastructure.) I am calculating my model's accuracy by cross-validation and my usage model is around +/- 6%, while my cost models are +/- 10-20% (or even 30%), hence my frustration. No way we can say to our fellow owners, "Hey, we may charge$25,000 extra this year for electricity, to play it safe, and so may have a boatload of extra cash at the end of the year." Think torches and pitchforks. – Wayne Aug 9 '11 at 20:17
• I understand. I have some ideas on hedging with energy futures contracts, but that will likely lead to sorrow. :) It would be a form of insurance to cap upper costs. On the other hand, a few utilities have shown interest in giving more consistent energy price caps under some circumstances. Well, I guess it's worth asking for better updates from the utilities. It's not like they don't have actuaries and statisticians. :) – Iterator Aug 9 '11 at 20:52