#5275 – looking under the sofa cushions

today at work i told my manager (my boss’ boss) that i found an error. basically, i looked under the sofa cushions at work and found us a half million dollars.

part (well, most) of my job is to submit payments to different enterprise and government customers who – as part of their contract with us – get a monthly, quarterly or a yearly payment.

sometimes their payment is based on how much they were billed during that period, sometimes it’s based on how much they paid to us during that period (the difference being we might bill them for one amount, and they might pay us a sum of money less than that billed amount, and we would pay them a percentage based on the amount of money they actually gave us), and sometimes it’s a fixed amount per line.

these customers get these payments because they end up having hundreds or thousands or hundreds of thousands of lines of service with us, and because they have so many lines of service with us, these payments to the customer help reimburse the customer for their administrative costs they incurred while managing all of their lines of service within their agency.

my team is made up of two other analysts besides myself (one in my office and one in little rock), my supervisor here, and my manager (my supervisor’s boss) in huntsville. each month/quarter/year, we run the appropriate SQL queries for that period, find out how much we owe each agency, send it to compliance for them to double-check, then send it to accounts payable to mail a check to the customer. and while my team and i are good at what we do, i’m definitely the most SQL-skilled employee in my team. there’s a separate team that also does reporting and is a lot more elbows-deep in SQL coding than my team and me, but they handle more advanced reporting requests. meanwhile, i’m trying to make our queries a lot more user-friendly since my teammates aren’t as familiar with SQL as i am.

the problem that my team has is that we have different queries for each different agency. while that’s not really a problem in itself, so many of our reports are based on the same 3 or 4 sets of information. the step-by-step process for processing a payment is different for each agency, even though the information we need might be the same. it shouldn’t be as complicated as it is.

well, last week i figured that there really isn’t any reason why we shouldn’t have a few core queries that we could modify to return the specific agency results that we’re looking for, so i started by looking at our contracts and writing down the key terms in each contract so i could start grouping them together and rewriting things to make them easier to read, easier to understand, and since compliance usually finds a few errors to be fixed with each payment, i wanted to make these new queries match what the contract says rather have the queries reflect the step-by-step instructions that had been handed down from department to team to department to team over the past 2 years or so.

i started by rewriting one query in particular since it said that we pay a fixed amount for each line that has a voice and data combination plan, and a different fixed amount for each line that has a data only plan. it’s pretty simple compared to our other reports, but while i was putting this together, i saw this key sentence buried in the section of the contract about our payment: “This fee will only be paid on collected revenue.”

now, what does that mean, “collected revenue”? it means that we should only be paying this fee to the customer for each month that they paid towards their bill. if they didn’t make a payment towards a bill one month, then we don’t pay their fee for that month. it’s a bit trickier than that if you get into the nitty-gritty, but that’s the key part. we should be only be paying this fee when we received a payment for that account that month. if the customer didn’t pay their bill, then we don’t have to pay that fee for that account that month.

when i looked at the history for this payment and compared it to my rewritten query, it turns out that since 10/2008 when this payment went into effect, each quarter we were paying out for about twice the number of lines that were actually eligible for payment; we were overpaying our obligation each quarter since we were paying the customer’s fee even when the customer didn’t make a payment towards their bill that month.

when i added up the total payments we had made from 10/2008 through 6/2010, and compared it to the results i got for that same time period, it turned out that we had overpaid by just over $536,000. that’s right, over half a million dollars that we get to tell the customer we paid to them in error and want back since it wasn’t paid out according to the terms of the contract. ka-ching!

so i told my supervisor about it on friday when i found out about how much we had overpaid (at that time it was “only” $435,000 since we didn’t have all the payment data we needed), and we figured that it was good that my manager and one of the other analysts were coming up from huntsville and little rock respectively this week to visit and so we could have some team meetings and so on. told my manager about it today (and half-jokingly asked “can i have a raise?” when she saw how much it was), did some more work, and ended up finding it’s closer to $536,000 than the $435,000 i had originally expected. later on my manager told her boss (my associate director) about my find to toot my horn a bit.

i also discovered that if i didn’t catch this error then we’d be paying out about $306,000 per year in error. now, the original process and my current query checks for the type of device (PDA or blackberry vs. data card) instead of the type of plan (voice and data vs. data only), so i need to modify my query to match that part of the contract, but even still i don’t expect the sum to change much if at all.

and this is just the first of several queries i’m looking at, and probably the most basic of those payments as well! who knows if the other queries might turn up money, too? in any case, this is definitely résumé material, and i’m curious to see how this impacts my yearly bonus (since that’s subject to a multiplier based on how well the company / your department / you did) and yearly raise. unfortunately, i don’t think that any increase this would bring to my salary would be as much as i’d like it to be to meet my goal of moving out and starting to pay off student loans next year, which is unfortunate.

i started to edit my photos from the trip to the virgin islands tonight, but it took forever to geotag them with my gps and import them into aperture (one of the reasons for me writing this post was because i didn’t have much else to do while importing), i’m ready now to just go to bed now instead. i don’t know why, but the last week or so i’ve been really tired as soon as i get home from work.