Photo Credit: 'pit-yacker' at FlickrFresh out of school, Maxim was able to score himself a pretty sweet entry level position in a small private bank. Rather than being responsible for maintaining the company's static phone book on the corporate intranet or some other stereotypical entry level thing, he was placed in the understaffed Publications department to do some surprisingly heavy impacting IT work.

The department's job was to produce and distribute various financial reports including the most important one of all - the Recommendation List. This list was considered mission critical for the bank as it contained the official recommendations of what stocks and bonds to buy or sell and was sent out to managers and customers alike. A bad recommendation could make or break the financial managers, not to mention the customers whom invested their money with the bank, who made decisions based on this information.

At the core of the Recommendation List was the Great Excel Spreadsheet (or "G.E.S." as it was known internally). Its existence was so crucial that it even had its own dedicated employee, Helen, the Report Editor.

Each week, the Helen's dance would go like this:

  1. First she would send the spreadsheet to a team of Analysts who would make adjustments based on their "insight" of the market.
  2. Once returned to the Editor, she would then run a macro that would update stock and bond values from a data provider.
  3. This spreadsheet would then be returned to the Analysts for re-verification.
  4. If everything was in order, it would be sent to the print shop and then snail mailed to managers every week and to their private customers monthly.

With all of this back and forth, Management felt that this process was ripe for improvement. So, armed with a copy of Access for the front end and a SQL Server account to handle everything on the back end, Maxim went to work.

Never Underestimate the Power of Yield

The original developer of the G.E.S. was long gone by the time Maxim showed up, but this wasn't much of a problem. Amid the ocean of financial mumbo-jumbo, he found good comments and descriptively named data fields. From a technical perspective, everything was clear except for one field - Yield.

The information surrounding Yield was more cautionary than helpful. The formula's description, for example, simply read "DO NOT EVER ATTEMPT TO CHANGE THIS CALCULATION! EVER!" Looking for some useful information, Maxim turned to the analysts for some help.

"Oh, God!" the lead analyst cringed, "you're not touching the Yield!?! Oh no no no, don't touch that! That's the most important part of the whole spreadsheet!!"

After a bit more prodding, he learned that the Yield was the magic number that influenced the Analysts' decision to recommend a stock or bond. Thankfully, Maxim was able to port over the function that calculated the yield value alone and very soon, he was on his way to making his new application live.

Heisenberg Uncertainty Principle...of Copy-and-Pasting into Spreadsheets?

With a month's worth of experience under his belt, Maxim's project was coming along quite well.  Everybody loved using the pretty Access front end with its drop-downs and he had created instead of the ominous facade of the Great Excel Spreadsheet. Even Helen was satisfied since she now had more of purpose than pushing paper out week after week! However, the joy was short-lived, as was revealed during an emergency department meeting.

The lead analyst started, "Maxim, we're finding some discrepancies in the report.  Several values in what we're finding to be random stocks and bonds are being grossly misrepresented."

"How do you mean?" asked Maxim.

"Point blank - we believe that YOU broke the Yield calculation and we're two days away from sending out bad figures that could ruin the bank and its investors."

After having to awkwardly sit through the rest of the meeting where "Plan B" (resurrect Helen) was discussed, Maxim got to work looking for a root cause.  Skeptical that the database would calculate some fields incorrectly, Maxim manually worked out the values by hand and found the problem and found the biggest shocker of all - some of the formulas in the original spreadsheet were wrong to begin with.

In going back previous editions of the spreadsheet, somehow, they managed to send completely idiotic numbers to their customers for three full years (at least). Not a single customer, not a single manager ever noticed the inconsistency for what were supposed to be trivial multiplications; not a single one of them noticed that "The-most-important-figure-on-this-chart-we-base-all-our-decisions-on" was random garbage.

In the end, he found out that whenever Helen needed to create a new row, she would simply copy and paste some random row and then adjust the values. At some point, however, she must have messed up and the spreadsheet ended up in a weird state, with formulas referring to cells in other rows, or sometimes even referring to nothing, creating a whole bunch of inconsistent values.

Excited at the chance to clear his name, Maxim revealed his findings to the lead analyst.  However, instead of relief, he only shrugged and responded “Hmph... well, we usually just use our gut for recommendations, anyway."

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!