Spreadsheet Morality

After six years in my previous job, my department ran like a well-oiled machine. Not just well-oiled, super-cooled frictionless nano diamonds. I had top-shelf, A-1 people working with me, so I don’t want to discount that. But I also used technology, including a large dose of Office automation, to make the processes efficient, effective, and error-proof. After seven years, I left. Almost immediately, my awesome solutions started collecting dust and my successor went a different way. I would call that way “backward”, but that’s only from my perspective. From his perspective, it was the only way he could go to get the job done.

He didn’t understand my solutions, couldn’t maintain them, and couldn’t change them when the inputs changed. What responsibility did I have to leave behind processes that someone without my skill set could maintain?

Jump ahead five years and I’m walking down that same path. There’s less Office automation in the processes I’m creating (still a fair bit), but I’m still using technology to make things better. When I leave here, who will pick up where I left off? Will they have the skill set to do it?

The obvious answer is that it’s none of my business. And that’s correct. But let’s say for the sake of argument that I cared. I’ve taken a different approach to developing Excel solutions that only I, or a small number of people within shouting distance, use. I build more robust solutions that will last beyond my death. Mostly. If the inputs or the outputs change, maintenance is necessary. If the file format that is uploaded to the unemployment insurance website changes, my solutions fail. If the sales people implement a radically different commission structure, my solutions fail.

On one hand, I’m providing better service to my employer. If I get hit by a bus, my efficient, effective solutions continue to work. On the other hand, the added robustness reduces the number of people that have the skills to maintain those solutions. How many CPAs in Omaha can fix an Excel project that uses custom class modules? I know of one.

The alternative is to build solutions with my successor in mind. They would be less efficient, less effective, and less error proof, but they would be maintainable by a far larger group of people. Honestly, I’m not going to dumb down my spreadsheets any more than Stephen Hawking is going to start writing physics books for toddlers. Maybe I should, but I probably won’t. Do you ever think about this stuff?

33 thoughts on “Spreadsheet Morality

  1. About 6 years back I left one job having developed quite a complex and sophisticated contracts tracking system in MS-Access. I was pretty pleased with it, as were my peers/superiors but within about two weeks of my leaving somebody made the decision that it needed additional functionality and, you guessed it, there was nobody within the department with the necessary skills to make that change.

    They just stopped using the application as a result and moved back to paper-based contracts checking.

    It took me the best part of 18 months to develop and implement and they gave up using it in an eyeblink. I’ll admit I was disappointed. I guess that’s part of developing solutions and moving on – if there’s nobody there to support the ongoing development of the solution it will eventually lose its usefulness.

  2. I not only think about this but I have lived it over the course of my career, as have many others with a military background.

    The nature of the military – all branches – is that you get into a job, take maybe a year to figure out how to do it properly, take the next year to figure out how to do it better, and then it is time to move on to the next job. You want to set it up for the next guy or gal so that it is easier for them to figure out that first year, but it rarely works that way. There is no guarantee that your relief (the military term for your successor) will have the same skills as you or even any background that is appropriate for the job. And it doesn’t matter how good you are, either. Within 6 months the new guy will be finding a way to disparage the old guy…

    Now that I’m retired from the military I have the opportunity to be long term memory in the project I support. 3 years ago I developed a database in Access for use by my customer, and the incumbent loved it. He frequently asked me for new bells and whistles, and I happily obliged. He’s moved on, but the new guy isn’t really fond of the database (primarily because, IMHO, he doesn’t understand it although I’m right across the hall and am always available to help discuss the database), so I watch the nice tool gather dust. I’m sure that the next “next guy” will probably want something new, he’ll ask me to design it, and I’ll polish up the old db.

    Such is life, especially business life. All you can do is hope to add value, and the more value you add, the better.

  3. Your work is your business. When banks saw how much it was costing keeping tellers around…wala we have ATMs. Companies all around the world provide their employees with tools (software, networks, computers). The fact that you chose to utilize the “tools” and your successor did not is the company’s fault.

    As they say in the Godfather…”its business not personal”.

    Nor is it a morality tale.

    Peace

  4. Of course. I work extensively with Excel and I avoid using VBA unless there is absolutely no other way to get the job done. As soon as I touch VBA, then I know that maintenance/understanding by my customers/colleagues in my absence will be more problematic.

  5. Slight twist on the situation is when you move jobs within the same company. You have exciting new things to be working on, but the department that relied on your skill set to maintain key solutions still has some claim over that skill set.

    In my case, my new boss has to concede part of my time to supporting the solutions from my old job, simply because the hole I left wasn’t filled with somebody who had those skills. You can’t say, “it’s their problem”, since we’re all part of the same company. Sure, I get paid whatever I do, but it’s not a motivating situation to be in when you are maintaining solutions for a department to which you no longer have a clear vested interest. The company has no drive to replace my skill set, since the workaround (i.e. holding me back) is cheaper, easier and more convenient.

    Do I “downgrade” my solutions now so that more people can maintain them? No, I can’t bring myself to do that. I’d rather put more effort into raising the skill sets of other people and spread the Excel love.

    Excellent post, Dick.

  6. Hi Dick,

    I think most of us think about this.

    I nearly always end up designing stuff that is simpler/dumber than it could be, 1, so that people might have a chance to maintain it, 2, so that they can manually work around the broken bit when something does change. For example, I’ll often let the user copy/paste data, rather than, click import, I don’t like it, but I’ve learned it’s better. Now, I don’t work in a department, so I’m not around, so that’s a bit different. If i was building stuff that I was going to use, and my team was going to use, and I could support, I would be more comfortable with automating more of it.
    Should you worry about what happen when you leave. I would say no.
    Its not up to you if the skills are not there when you go, it up to your boss to take that into account, 1, when your building the systems and 2, when their recruiting – even if that means employing a consultant after your gone…
    Think like this, if I run x Corps, and I get a Mike, the 86 year old Clipper programmer with the dodgy ticker to builder our new system, then I’m a fool. It’s not Mikes fault, its the managers fault. Not understanding whats going on is not an excuse, its bad management.
    In the example form Kevin above, thats an example of terrible management, assuming the tool made everything better/fast/ etc, i.e. it was a decent solution (I’m sure it was Kevin!), then the management should have extended the functional using consultancy if they did not have the skills in house, to scrap it and go back to paper is BS!

    >>I’m not going to dumb down my spreadsheets any more than Stephen Hawking is going to start writing physics books for toddlers. Maybe I should, but I probably won’t. Do you ever think about this stuff?
    Funny, because that’s exactly what Hawkings did, when he wrote a brief history, but in this case his motivations was to make money. His objective was money, yours is quality, do what best meets your objective I’d say.

    Thanks
    Ross

  7. I don’t understand, if the project is making the job easier and making the work faster why wouldn’t the company just hire a contractor to update it? I would think it would be much cheaper than going back to the old, inefficient ways. Is it nothing more than bureaucracy, laziness, or incompetence?

  8. The government department that I work for has, over the last 4-5 years, cracked down heavily on the creation of new Business Developed Applications (BDApps) – the definition of which essentially encompasses anything that uses an access database, or any VBA. The only exception is for a one off solution to a problem, which does not store business critical information, and will not have a wider rollout within the business.

    Nowadays you have to be registered internally as a developer of BDApps, which requires you to have signoff from somebody within your business unit who is so senior that in practical terms they have no idea what it is their business unit does. You then have to obtain approval to begin development, again from someone ridiculously high up within the business unit, before development can begin. You have to complete copious documentation as you go along, and follow very strict guidelines in writing the code and developing the user interface, so that in theory someone else can easily maintain the code once you have moved on.

    The two main reasons given for this are:

    1. These BDApps have frequently been used to store business critical information, and there is a concern that this will be lost/corrupted/unrecoverable as a result of shoddy coding.

    2. They don’t want people crying to the IT solutions provider when a locally developed spreadsheet stops working.

    The main result of this is that people like myself who are not registered developers and likely never will be, but who are able to see better ways of working using these technologies on a day to day basis, spend an awful lot of time trying to convince middle managers that a particular solution is a one-off, and holds no business critical information.

  9. I didn’t even get a chance to plan anything for my successor, after being unceremoniously dumped by my previous employer after seven years’ service without any warning. I didn’t care anyway; mentally I checked out months earlier. For the most part, the code I wrote is maintainable, although I don’t expect anything I wrote to still be used today because it does require some VBA skill to update.

    Now that I’ve had more experience, I try to write future-proof code that’s easier to maintain. For example, when I create a dashboard, instead of putting everything in the VBA code I list constants on the worksheet t so that anyone can make changes without me having to do it.

    And now all of my code is late bound so that anyone can just cut and paste it without worrying about versioning or manually setting references.

    “If the file format that is uploaded to the unemployment insurance website changes, my solutions fail. If the sales people implement a radically different commission structure, my solutions fail.”

    That’s something you should think about for yourself, right now, not just for your successor.

  10. I taught an Excel Macro class so that when I left there would be somebody who had at least taken a step towards continuing my work. In the end we are not responsible for how a company manages their resources after we leave.

  11. I think about it alot. I will be changing jobs with my employer and leaving behind a highly efficient set of processes that will blow my successor’s mind. I can hear the comments now “John’s processes are way too complicated!”

    The fact of the matter is that it was the only super efficient way to make it happen. But I will let go and they can forge ahead backwards eh?

  12. I think about it a lot.

    Excel gave me dynamic range names, so why limit my options just cause the next guy cant figure them out?
    For the more complex solutions, an engineering document and/or operating document accompanies the solution, which allows an unfamiliar tech/user to get an overview in English.

    Fortunately, I don’t really suffer the problems you describe. A few things different, maybe?

    I work from an IT department.
    I practically never run the solutions I build. Always handed over, and I move onto the next task.
    Our IT department has at least 2 people, at a given time, capable of working on any given solution – overlapping skill set. I think our situation is unusual, in that so many of our IT staff are talented.
    There is low turnover of staff at our organisation. I’ve been working at the same place for =DATEDIF(DATE(1998, 11, 23), TODAY(), "y") years now, and that's somewhere in the middle for my department.

    Work toward making yourself redundant, then you can work on higher value tasks.
    I build applications for lowest possible ongoing maintenance, and I rarely compromise on this. If I'm maintaining an application, I cant move onto the next big thing!

  13. I think about this all the time, but in the end, I think it makes me a better… application writer? Solution creator? Guru? … whatever it is I do with technology.

    I make my applications robust not only because someone else might have to maintain them someday, but because *I* have to maintain them today. If someone shifts a column over, I don’t want everything I ever wrote to break, because fixing it would be a hassle for me (never mind some poor schmoe who ends up taking over my stuff). A few extra seconds spent to make sure a file folder exists — even if I know it does, and always should, barring catastrophe — means that I never have to worry about that problem ever again, not even a little.

    Morality schmorality: this is about making MY life easier. :)

  14. After having walked into several jobs with no documentation (and no one to explain the position/processes) – I think about it alot.

    Although my skill sets are well below the other posters I do believe some form of documentation is necessary. If it is aimed at slightly less than your own abilites, then it’s the companies own fault if they do not hire a person with sufficient skills or adaptability to understand what has been done and how to maintain/adjust or use it. Personally I love moving to a position where the previous incumbent was exremely clever as it provides a great learning experience and the chance to improve my own skills.

    I’ve often seen people with the ability to provide good quality solutions undervalued by management who don’t understand or value their work.

    As I get older and have a harder time remembering what I did 6 months ago, the documentation I write is as much for my own benefit as the next person who comes along.

  15. I say keep the bar high. It’s not like you’re creating compiled solutions and walking off with the source code. All of your source code can be adapted by another VBA expert, or a savvy Excel user can get a couple of books or just use http://www.google.com/search?q=excel+vba+your_keywords_of_choice. I’m regularly surprised by the number of folks that are still using Excel/VBA-based tools that I wrote years ago and have long forgotten about. You can’t future-proof everything, but my thoughts are that Office automation leaves an application readily available for future changes or upgrades, as opposed to compiled, standalone solutions that often get separated from the original source code.

  16. Most companies have no clue as to how to manage efficiency. Further, it is not part of anyone’s KPIs. It is the reason why computers have not made the impact they were envisioned to make on workplace productivity. Finally, since the corporate bean counters do not care to quantify cost savings from ALL computer-based efficiency solutions, nobody else cares either.

  17. Dick,

    I think about these issues all the time. Ultimately I think that you shouldn’t worry too much about it, but your boss should. Would you have let one of those top-shelf, A-1 people that you had working for you develop a solution that you didn’t have the background to understand? Maybe data manipulation in PERL or a complicated statistics routine in R? Would you have been willing to take the risk that he’d get hit by a bus or walk out on you? Things get even worse if an application failure could result in legal penalties or fines. Paradoxically the more valuable the solution the higher the risk.

    This is a problem shared by all knowledge workers, not just programmers and developers. What happens when the ace trader leaves the firm, or the guy who knows all the relevant tax issues retires, or the person who is singlehandedly responsible for all aspects of a major customer’s account skates? All we can do as individual employees is contribute as much value to our company and customers as we can. It’s the manager’s or customer’s responsibility have a backup plan in place.

    So don’t sweat it when it comes to your own work, but worry, a lot, about your employee’s work.

    David

  18. It depends. :)

    If it is a mission critical business spreadsheet model – say the output of the budget and planing process – then it should be laid out transparently, fully documented as to information sources and version control, and importantly be designed as simply as possible so it can be readily understood and widely used as a business tool(ie little, preferabbly no VBA, no array formulas etc). It is the corporation’s model not an inviduals plaything. Typically though these models will be well designed and run, perhaps externally audited. The spreadsheet scope and it’s visibility in the company is clear

    At the other end is the full blown, whizbang automated application. It’s basis of design and ongoing onwnership skill dependencies are also clear

    The grey area is the routine tasks run by particular departments, the perspective that you indentified well in your opening paragraph.
    – what is effective and efficient to a high-end programmer may mean a highly exposed weakness to that coder’s company. Did the class module (used as part of your example not intended as personal commentary on you) really deserve inclusion in the project ,or was it there for the programmer’s – but not the employer’s – satisfaction. Is it reasonable to justify robustness from a coder’s perspective when the coder knows that the work will be exposed, or worse, be completely useless if the designer leavees
    – alternatively has the employer hit the jackpot in employing someone who brings skills beyond that needed for the core role, and is that the employee appropriately remunerated for improving processes with coding skills. Furthermore irrespective of skill fit with job description, a good company will understand the resourcing and skills risks it faces, and it should identify where potential skill gaps are and address them. In the actual case you raise above that first employer was slack.

    “The obvious answer is that it’s none of my business.”
    I disagree with that. You are/were part of a team that presumably you want to succeed both with and without you. If it was a contract position I’d understand that viewpoint better, but to me after 7 years that was both striking and a little sad.

  19. Dermot Balson gave his view in a paper to Eusprig 2010 as “design spreadsheets for others, not for yourself”. But he was taking for granted a certain skill level in the peers. One can say your employer needs to know what kind of skills you have so that they can replace you with an equivalent; and if they replace you with a less qualified person, thats their choice. How much transparency is possible? With a planned leave, you could interview / test your successor. If unplanned, all sides are at the mercy of the market. There does not exist certification in EUC (above simple ECDL anyway – maybe MOSS?) as in more traditional professions; but even in those, standards can differ widely from country to country.

    http://arxiv.org/abs/1009.5701

    Changing User Attitudes to Reduce Spreadsheet Risk
    Authors: Dermot Balson
    (Submitted on 28 Sep 2010)
    Abstract: A business case study on how three simple guidelines:
    1. Make it easy to check (and maintain) 2. Make it safe to use 3. Keep business logic out of code changed user attitudes and improved spreadsheet quality in a financial services organisation.

  20. My first macro took a two hour highly error prone manual procedure and turned it into a 20 second automated procedure but now I think I should not have done it.

  21. yes, I often think about this with my super-macrotized football pool spreadsheet. been maintaining it over 16 years! with incremental improvements each year I think about how someone else could possibly maintain it. Code comments only go so far. Though I do think the use of named-ranges is very key. It also has mad, mad statistics (think well beyond the normal curve) I’ve made some things pretty simple with user-forms, but preparing the spreadsheet for a new season takes a little bit of work and inner-workings knowledge. I’ve tried to “retire” a few times, but can never find a replacement commissioner. That being said I’ve narrowed the qualifications down to four attributes.

    1. A LOVE of not just football, but the HISTORY of the game.
    2. An understanding of college-level stats (Z-scores, T-tests, degrees of freedom, etc)
    3. Advanced Excel skills (VBA, etc)
    4. Finally, and MOST importantly a genuine HATRED of the Cowboys

    believe it or not I found a replacement that had 3 of the 4 skills. He used to unlock my spreadsheets and post Silver and Blue starts on them. For that I couldn’t let him be my successor….so I continue on.

  22. Are you sure the title of this thread shouldn’t be “Spreadsheet Mortality?” ;-)

    Just kidding, Dick. I think about this a lot, too. I think it’s only responsible, and it’s one of the ways I believe I show my associates and customers that I truly care about and am proud of my work. K’udos.

  23. This is an interesting topic. I too have seen highly effective Excel solutions abandoned simply because the VBA developer has left the building. This is really a shame because some of the Excel apps that I have been involved with have not only automated analysis tasks, but have allowed clients to do things that were nearly impossible to do before or ridiculously time consuming complete. But this does not have to be the case. I mean, come on, what the heck are VBA guns for hire for anyhow? In my own experience, I have found that trying to keep an Excel model simple because the original developer may not always be around is the wrong approach. I choose to embrace VBA for all its glory and write the meanest, cleanest, fastest code possible. But, I always operate on the assumption that someone else WILL have to pick up the ball when I’m gone, so document your code accordingly.

  24. Dick Kusleika wrote “…When I leave here, who will pick up where I left off? Will they have the skill set to do it?
    The obvious answer is that it’s none of my business. And that’s correct…”

    Actually, it is your business — but it is more the business of the person(s) you report to.

    Creating (but more importantly, allowing) an environment that is unsustainable is simply wrong. One of the things I am proud of is that when I left my last employment, I had nothing to do for the last month! I went into to work around 9, twiddled my thumbs until about 4 and left.

    Over the years that I worked there, I pioneered solutions that most people did not even imagine, let alone know how to implement. My boss, his boss, and his boss, were all comfortable with / aware of every bizarro system I concocted. [And, the business units loved the productivity gains / cost savings / functional improvements that these systems delivered.] Consequently, when I left, every one of my systems hummed along.

    Several years later I happened to talk with a friend who still worked there. To my surprise, I discovered that all but one of my systems were still in use. [The one that was no longer in use was a custom solution for a business unit that no longer existed.] He mentioned that a few of my systems had seen some enhancements and all had needed minor tweaks with upgrades to the OS / DB software. But, aside from a few easily implemented bug fixes, that was it.

  25. That’s one of the reasons I really enjoy pair programming, i.e. writing code as a pair. It’s slightly slower than writing on your own, but on the other hand, the quality of the result is usually much better (if only because with a second set of eyes, lots of small mistakes get caught) and it’s a fantastic way to teach a team and share knowledge. There is always someone else who knows about the application, and why it was designed a certain way.

  26. I think that both management and spreadsheet modellers have responsibilities here. Management should be clear on what they expect from the model; i.e. they should be aware of whether it can be maintained / enhanced by a non-expert user. The quality of documentation is important; I suggest that the builder of the model should spend as much time / thought on the documentation to support it as building the model itself. And if the model is specialised / complex, management should be prepared to pay for ongoing support of it from an appropriate professional / organisation.

  27. I read this from Steegness:


    I make my applications robust not only because someone else might have to maintain them someday, but because *I* have to maintain them today

    Yup. What he said.

    Also, I work on a trading floor and pretty much everyone is a contractor. We never, ever rehire them if we can’t maintain their code.

    The ‘revolving doors’ nature of the job isn’t great, but at least – for me – the doors do indeed revolve inward as well as out: for some, it’s a series of one-way trapdoors leading down to to the Flying Spaghetti Monster’s seventh hell – I won’t identify the bank by name but conceptual representations of their spreadsheets flow-of-logic are reminiscent of HR Giger’s most lurid excesses – and working there will lead the unwary into insanity, burnout, unemployment and resurrection amongst the desperate and the damned as a Business Analyst.

  28. Very true, and I can relate to it. Currently I am also in a similar situation – I have built several VBA based solutions for my current company in past 5 months – (I had to join this small company after working in 3 MNCs for 18 years! ). Before I joined, everything in this company was paper based. Now I have built several Excel based systems for Sales, Admin and Production departments. Some of them are very complex and I am very proud of them. However, The Sales manager (who has returned from some foreign country) refuses to use anything that is not written on a paper. His sales team (3 persons) maintains a stack of 1200 papers (customer addresses) that he calls as his database. He has also created similar paper based systems fro HR, Purchase, Production etc.

    Currently, everyone in the company uses my Excel based solutions for day to day work, but again write everything on paper to report to this manager, and he is proud of it.

  29. Very familiar territory, this.

    I’m in the same boat as Yard in that I’ve moved out of the operation into the central business, but many of the little and not-so-little helpers that I’ve put in here over the past five years still need my attention from time to time. Sometimes it’s daft things like someone deleting a named range, other times it’s major changes to business logic.

    There’s a lot of (too much?) VBA in there as well, and not all of it very good I’m afraid. My early VBA days lacked the rigour that I now try to apply!

    I’m moving to ‘wish I’d never created this’ in many cases, but this ignores the massive value they’ve been to the site. Tomorrow I have a meeting to document each and draw up a support matrix. I’m assuming some will be allowed to wither and die should an upgrade be required, but some are so fundamental to the business and save so much time that they really need long term support.

    Another way of looking at it is, if they went the ‘dark ages’ way and reverted to a paper and sweat process to avoid having to find someone with the skills to maintain/upgrade a system, I’ve still saved the company £££ over the years the solution was in place. So I shouldn’t feel too bad about leaving an unsupportable application behind me, right?

    Personally though, these applications allowed me to demonstrate my ability and this led directly to my new (and vastly better paid) position. Happy days!

  30. Problem is what to assume as the skill level of one’s successor. Wallowing in the mud of details, would the successor understand when/how to use the 4th arg to [VH]LOOKUP? Would s/he need to maintain VBA or just stuff in worksheets? Managers who oversee people who build spreadsheet models are almost always outside IT, and almost never have any experience specifying features which could be used in models (implying all features not permitted would be prohibited). Even though no one considers building spreadsheet models to be a form of programming, it is, and it needs standard software engineering practices to prevent spreadsheet models from becoming write-only code.

  31. This is one of the most important issues that faces those who create software. Systems which incorporate tools that have been created using hidden source code create the most vulernerable legacy systems. Part of the solution lies in the use of OPEN source tools. The other missing component for improved treatment of this issue is a greater recognition of the importance of the position of in-house software creators and maintenance. I recently read of a comparison between those who refine a nation’s legislation that has been ‘passed’ by politicians (in the UK these are members of the house of lords) and those creators of software code which directs the day to day operations (and in many cases higher level functions as well). Perhaps one day software creators will hold similarly ‘lofty’ positions, fulfilling a similarly respected role and form the new ‘upper house’ !


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.