spreadsheet error costs time and money, yet again

Back in November, I got my first water, sewage, and gas bill from a company called ISTA. My apartment management had taken a while to set up the billing after the previous billing company went of business (or dropped the contract, I don’t remember exactly what happened). So I hadn’t actually paid water and sewage for two or three months.

So when the bill came for $173, I wasn’t too surprised. I didn’t really remember what I’d paid the previous year, but this seemed reasonable for a few months of water, sewage and gas. I wrote the check, and forgot about ISTA.

Forty five days later, I got the next bill, but this time something seemed wrong: $463 and it had only been a month and a half. What the hell was going on? I looked back on my old bills and noticed that my average 30 day bill was about $30, even in the winter. Either the company was trying to extort money from me or somebody had made an accounting error.

I looked more closely at the bill, which had three columns: previous usage, current usage, and usage. The difference between the first two columns was exactly 1000. The value in the third column was 10,000. Was there some hidden multiplier I didn’t understand? Maybe there was some rate that just happened to be 10, and I had just kept my apartment and showers really warm this winter.

So I called ISTA and disputed the bill. They immediately escalated it to their dispute manager, who called me back after a few days. They said that there had been a misread meter and that they had corrected the reading, and that after the bill was now only about $270, after they had applied the credit. When I got the call, I had a meeting to be at, so I didn’t think about it much.

After I got home that night, it still didn’t seem right. $270 for 45 days? What happened to the rates? They must have gone up by a factor of 10! So the next day, I called ISTA back, and spoke to a nice lady about my problem. Rather than call the dispute manager again, she told me she was opening my spreadsheet. She proceeded to walk through the calculations with me, describing the rates and the formulas. I jotted them down on paper as we went. Finally, we got to the final total calculation, and she said, “so this times the multiplier is … wait, it shouldn’t be.” She immediately put me on hold.

A few minutes later, she came back, saying that she needed to have the accounting department look at my spreadsheet. My spreadsheet, implying that every customer has their own. She said that the dispute manager would, yet again, call me back in a few days.

Four days later, the dispute manager called me back and explained that there was some sort of disagreement between billing and accounting, regarding the cause of the problem. Billing thought it was the spreadsheet and accounting thought it was the meter readings. She said she’d call back in a few more business days, after they’d worked out their differences.

When she did call back, she leveled with me: accounting was wrong, there was an error in the spreadsheet, and after fixing the multiplier cell, my bill was reduced by a factor of 10. After the credit calculators, they determined that I had overpaid from the previous bill by about $100, and that I probably wouldn’t have a bill for the next two cycles. She apologized for how long it took to resolve the issue, but reassured me that it wouldn’t happen to me again.

But I wasn’t thinking about me at this point. I was thinking about all of the other customers, whose spreadsheets probably had the same error. Would the accountants audit all of the spreadsheets that copied the error? How many customers would call about the bills? How many would insist, like I did, that there was a spreadsheet error, and demand that it be properly diagnosed? And how much of this feedback would ever make it to the accountants writing the buggy spreadsheets?

Oh, end-user programming. Your manifestations in society abound.