Thursday, March 12, 2015


A financial miracle: The Magical Spreadsheet
 (Tell me when this starts to bore you, you know, numbers and spreadsheets and all!)

I was asked to prepare end-of-year expenditure projections for a public health program that had two funding sources, therefore two separate budgets, a dozen budget categories with forty different line items to manipulate.  Hey, it’s what I do (when I’m not, I’m writing).
Unfortunately, the projections I had been receiving from the directors kept changing over and over again, some because of human foresight, some because of human error (mostly these!) – what’s the opposite of foresight? Buttsight?  AKA: Get your act together, people!
Time and time again I needed to adjust these spreadsheets and since they were inter-connected programmatically, every change to one meant, at best, one change to the other, but usually more like one change equaled five changes.  Finally, after twenty to thirty modifications and numerous updates from program managers over the course of two weeks, I had a final product late one Friday, which I printed out and summarized with appropriate staff.  Ahh,  TGIF and I felt good!
Great week-end, back to work on Monday and energized to move on to new tasks!  Of course, here was the first email I received: “Phil, I think I forgot to tell you that I need to move $75,000 to cover expenses for XYZ….”  WHAT?!?!?!?!  I didn’t believe it, you have got to be kidding me – ANOTHER CHANGE?!?!
I couldn’t deal with it, I left my office and walked the hallways for a few minutes, grumbling and muttering grouchy things to myself, although I’m certain by the way other folks were stepping widely around me that some of my comments to myself actually escaped into the atmosphere.  Okay, deep breaths, feeling better, whatever you can do this, Phil, don’t let work get the best of you, just make the changes and finish it up, no stress, no worries.  Okay.
I got back to my desk, settled in, figured out the changes I would need to make to adjust the year-end projections and balance these budgets.  Simple, undo an appropriate number of modifications and related cost adjustments from the last changes of the previous week.  A little bit of work, might take some time for sure, but straightforward so okay, let me just put my thinking cap on and dive in.  I pulled up the saved spreadsheets in my EXCEL file and prepared to compute the changes I thought I would need to make.
But, what?! What gives here?  The spreadsheet file numbers weren’t matching my hardcopy print-outs from what I thought were the final analyses – oh no, no way, major issue!
In my haste to complete the job or maybe in my exhilaration for having THOUGHT I had completed the job - a state I like to call ‘The Drunken Work Stupor’, which isn’t really like a stupor at all, more like a “Can I Go Home Now?’ Euphoria – I MAY HAVE FAILED TO SAVE MY FINAL ADJUSTMENTS BEFORE SHUTTING DOWN THE OEPRATING PROGRAM ON FRIDAY!  And due to my haste, my stupor, my euphoria, WHATEVER, now I would have to go back through my notes to make sure the earlier revision requests that needed to be kept in the reports were still incorporated or needed to be added, even before I moved ahead with the new request!  RATS!
So now, even more frustrated, I began my initial review of the details, although actually that was only my initial review THAT Monday morning, it was probably my 57th review of this project overall - but I grouse and whine.  Because then: A MIRACLE HAPPENED!
As I began to review and compare the “final-not-really-final” spreadsheet print-outs to the computer file spreadsheets, I realized Two Things:

1.     The revisions that were not included, because maybe I did fail to save them on Friday but let’s not deal with that right now, were a number of salary projection adjustments.  And while that fact, in and of itself was not so thrilling, what was good is that the number of these adjustments, although they were many, they were at least contained in one budget category, so much easier to identify and correct, as needed.  So I would be able to track down those non-saved adjustments fairly easily.

2.     And here’s the gist of the MIRACLE.  The total of the omitted items equaled $75, 007.23.  That’s right, almost exactly equal to the amount of the new modification I had to incorporate!!!

In other words:  The Magical Spreadsheet had MAGICALLY FIXED ITSELF!

(Addendum:  Since my work on those spreadsheets was effectively complete, I decided to spend the time starting this Blog.  Unfortunately, this Blog did not write itself, so any complaints as to its content and enjoyment factor are to be blamed entirely on me.  Whassup WORD, can’t be as bad-ass as EXCEL and write yourself?!)