It was a dark and stormy day, the numbers fell in torrents – except at occasional intervals, when they were checked by the violent gust of a turned page which swept up the totals (for it is in Livermore that our scene lies), rattling our customers, and fiercely agitating the webmaster.

Ok, 6-years ago, our statements sucked. Here’s how:

1. Suppose you run a data set (invoice and payment detail) wouldn’t it make sense to produce a total based on that data? In our case, the ERP vendor decided to total a statement based on the data generated from the last aging. What this meant is that if you didn’t run an aging prior to running statements you had almost no chance of getting the totals correct on a statement unless you restricted the ability to run an aging, which we didn’t. The only answer I can find to this problem is that the vendor was desperately scrambling for speed and “cheese balled” the totals to speed up the printing of statements, or, they didn’t know how to total an aging during the printing process. Either way, this was dumb.
2. Unapplied payments were not reflected in the aging detail. Sorry, Mr. Customer, if you’ve given us money but we’re not sure what invoice to apply it to and post it as unapplied it won’t show up on the statement anywhere except the total.
3. Because of the above it was possible to add up the items on a statement, get one total, add up the 30, 60, 90… columns, get another total, and then there was the total on the statement which possibly didn’t match either of the those.
4. Warehouse totals added to the confusion. Customers would buy from different locations and we needed a way to dissect that and the statements did a poor job.
5. Reconciling what invoice a payment was applied against was very confusing. Our credit people could do it, sometimes with a lot of work, but a customer had no chance.
6. Statements were run at individual warehouses meaning they went out on different dates instead on the 1st.
7. Finally, statements had become radioactive. No one wanted to touch them. One group had one idea, another had another, and some people wanted to do away with them entirely.

In other words, our statements were confusing, awkward to run, they didn’t add up and no wanted to be a part of them. So, what was the solution to the problem? Stick the new guy with it.

Research

The first thing I did was go to the data in the openitem table and try to figure that out. To this day I’m unsure if they guy who designed the table is brilliant or something else entirely. Data in that table would roll Codd over in his grave. It has running totals, data is flagged to type and then does different things in the table based on the type of record and you basically need to react to the data differently depending on the type. But, somehow, it all works, and is actually fairly clean, once you know the game.

The second thing I did was look at the statement options. We were pretty much stuck because there weren’t very many and none of them worked well. Basically we were screwed no matter what we did because we would still face some of the problems listed earlier no matter what we did.

Execution

After a few months I’d done the following:

1. Implemented a web-based statement on our websites. The web-based statement provided correct totals, allowed drill-downs so you could see where a payment was applied, showed invoice detail and well, it worked.
2. We pulled statement building from the warehouses but we still allowed them to print the statements. The new process consisted of running an aging then printing statements to our imaging system and the individual credit managers printed the statements from there.
3. Created a new fast aging (built once a day) so our credit managers could see an aging and make notes as they needed. It’s worth noting that it could take hours to run an aging while the new aging would run in under a minute. We moved all of the processing to the back-end late at night.
4. We changed the options on the statements to use the best options available to us on the default statement. Well, at least the best options according to the people I could get to go on record as to what the best options were.

So, here’s the point where I pat myself on the back and tell myself that I’d done good. We’d solved or provided alternatives that fixed or at least improved most of the problems above and the statements were immeasurable better, “hey, they totaled correctly”. I felt, and still feel, that this was some damn good work.

5 Years Later – Our Statements Suck!

At least that’s what was determined by a credit meeting. Honestly, that’s probably true. We never managed to produce a perfect format in the printed version. After struggling for a month I took the best option available, asked for opinions, and deemed correct totals, the best alternative available. I didn’t have a lot of options and it was a project that was sideways due to lack of interest almost from the beginning.

Still, it’s just a little bit ironic how some things always come back around. I predict that in 5-years they’ll be another meeting and the message will be the same: our statements suck!

I asked this question awhile back when I was tasked with designing a report that tracked pricing accuracy.  It turns out that I had it wrong.  I almost had it right but, well, I didn’t which is why this shows up in the dumb things section.  But first, how did we handle pricing?

Old School Pricing

Well, “old school” pricing was handled in any of the following ways:

1. At what price did we sell the product to the customer the last time we sold it to the customer?
2. Mark it up X% which varied by salesperson, location, manager and possibly the orientation of the stars.
3. Using one of the price-breaks in the system.  Depending on the business they could be counted to reliably stay on those breaks, or, not.
4. Pulled it out of…

So, it made sense to management, especially in the current economic climate to get pricing under control.  It wasn’t just that we had to have control of our margins we had to be able to present a consistent and fair price to our customers.  A customer shouldn’t be able to call 3 different salespeople at 3 different locations and get 3 different prices.

New School Pricing

So, how was “new school” pricing going to be handled?

1. By price-break determined by quantity and customer type.  Essentially we had a pricing matrix.   There were multiple breaks; units of measure and many customer types in our system although for pricing the applicable types were reduced.
2. By location as determined by still to be determined factors.  For instance, we had a North region, a South region, a really, really North region and possibly even by business location.  It is what it is today but it won’t be that tomorrow.
3. By customer ranking, sort of.  This hasn’t been implemented directly but likely will someday.

The final variable to this is the order entry process itself, which can consist of the any of the following: a bid; a quote; an order and finally an invoice.  This is where I got into trouble but more on that in a second.

Oops!

When I was first approached with this project I pulled some old code out of the system, used the bailing wire and said something to the effect of “I’m invincible”.  This was really nifty until the “new school” system was fully set up and the old code got really old really fast.  It wasn’t going to do the job, which is right about when I heard a siren say, “Use the system price” and as sure as Luke heard Obi-Wan I listened.

After 6 years with the company I had to admit that I had no idea what a system price was.  Basically the system price takes our business system’s pricing matrix, chews on the variables, but not all of them it turns out, and says this is price you should have sold the SKU at.  So, I did some testing, it looked good, I fired, and it turns out I wasn’t invincible.  Unfortunately, for me, while the system price does bring the price out of the matrix it doesn’t bring it out when a bid is involved.

A bid in our system becomes the default price to a customer for a given SKU for a given date range.  Basically, a bid is used to lock in a price over time.  For instance, you want to buy X for 6 months at Y price.  When I looked at the data, the pricing source in the system correctly reflects when a price came from a bid.  The only problem is that the system price did not speak bids and stuck with the price matrix.

Why Didn’t I Catch The Bug?

First, we probably didn’t have a lot of bids in the data prior to the “new school” pricing.  Bids became much more common when our salespeople weren’t writing orders at the correct price and started looking for ways to circumvent the scoring system.  Possibly, the sample size was simply too small at implementation.

Second, I wasn’t looking for the problem.  I was convinced that the system price reflected all of the pricing options.  I should have known better, especially after 6 years of dealing with our ERP vendor but well, I didn’t.  Sometimes you can, but most of the time you can’t find what you aren’t looking for and I certainly didn’t.

I didn’t QA the way I should have.  I believe, especially in my working environment (I’m the designer, coder and head the one man QA dept), that you have to include source data in the reporting so an end user can challenge it.  I guarantee that our end users, especially when they started not believing our data, would have caught the bid problem sooner than I caught it. We may not have caught it at implementation but we would have caught it earlier.

Finally, I have to own that I screwed up.  There’s no way around it as I’m the one who put the report out.

How did I catch it?

Well, it turns out that our end-users didn’t believe the results being tossed back at them and they started making up theories as to why they were scoring lots of misses.  It never occurred to them that “they sucked at hitting the price breaks”, instead they decided it must be due to rounding error.  Yes, our pricing gets still more complex.  We price in a way that can run out to 4-digits so with the right quantity, the right unit of measure and the right price it was technically possible, except for one small detail, I’d thought of that and included a 1% variance in the calculation and I was certain that worked.  So, I dug into the data, hard, and found the bid problem.  Basically, I reviewed the raw data for a few hours until I found it.

Things don’t always mean what they mean.

Believe it or not, there were actually other things that went wrong on this report but I’ll spare you those in this post.  This single report became a collection of fluke events that honestly are kind of amazing.  It’s amazing how things can change in the span of a few months on things you are certain work a certain way.  For instance, the vendor changes some code, a user changes a value, a manager changes policy, a sneaky salesperson does something sneaky, or who knows what else.

The real lesson here is that in the real world, at least in my real world, things don’t always mean what they mean and they may not mean the same thing tomorrow that they do today.