Starting points for the quantitative CIO: downloadable basic tools

Much as in any field, IT executives constantly have to seek a balance between idealism and pragmatism. Given a particular problem and the range of possible solutions, do we insist on “doing it right”, or do we buckle down and “just get it done”, even with gaps?

There’s obviously no single right answer, which is what makes IT consistently so fun and frustrating at the same time. Over time, though, my own approach has typically been to focus on the continuous improvement aspect of “doing it right”: whenever possible, get something going as a start, then hone it over time as you learn more about the problem and your situation.

Using spreadsheets as a management tool definitely falls on the “just get it done” side of this spectrum of approaches. Spreadsheets are seductively easy, omnipresent, and usable by people with a variety of skill sets and technical savvy.

But there’s a host of downsides: spreadsheets are frail creatures. Errors can creep in fairly easily, even for experienced users, as data and circumstances change, and spreadsheets are especially prone to the incursion of silent errors and omissions when undergoing revision.  And once implemented, in all their imperfection, spreadsheet-based solutions can broaden and become large-scale, long-term systems (I’ve seen this happen again and again).

Yet, I feel that every technology executive should be maximally fluent in spreadsheeting: simple tracking, analyzing, modeling alternatives, understanding costs and risks. The technology provides a readily available, easy way to knock out quick and dirty models that can clarify one’s thinking and approach enormously. They work well, as long as you keep in mind that the spreadsheet is usually a stop-gap, for those times when you are faced with a glaring need and you don’t have time, budget, or staff to implement anything deeper right away.

In an early blog post, I listed the seven areas where a quantitative approach is especially necessary for the technology executive:

1. Budget planning
2. Return-on-Investment (ROI) analyses
3. Project estimation and portfolio management
4. Resource allocation
5. Capacity forecasting
6. Performance metrics, operations
7. Performance metrics, development

In my consulting life and in my various executive stints, I’ve frequently gone into situations (even in large companies) where there were few or no tools in place to handle the above critical areas; in nearly all of them, “seat of the pants” “HiPPO” (Highest Paid Person’s Opinion) judgment has tended to substitute for solid quantitative analysis or even depth of understanding of the problem. In those situations, action and improvements were needed immediately so that we could start making facts-based decisions, yet there has rarely been the time or the necessary cultural foundation to select, obtain, and implement “real” tools, such as IT Asset Management systems, Project Portfolio Management software, and so on.

In the six years of this blog’s life, I’ve written posts describing (in keeping with this site’s subtitle) my intensely practical approach to most of these areas. As part of those posts, I’ve occasionally included downloadable spreadsheets illustrating the points I’m making or as examples of “quick and dirty” implementations. I’ve done this with some trepidation, knowing that people often glom onto a tool as “the answer”, especially when that tool is free and available with just a click or two. Yet, in the spirit of collaboration and “giving back”, it seems right to share carefully done spreadsheet examples and templates that I feel provide useful “starting point” approaches to the seven areas of quantitative focus listed above.

So here I’m quite consciously talking out of both sides of my mouth:

1. Rather than get caught up in analysis paralysis (“we don’t have a tool at all, and it’ll be a huge project to select and implement one!”), do something. Given a particular gap, a spreadsheet may indeed be better than doing nothing at all: i.e., much better than just continuing to rely on gut instinct for your decisions.

2. Remember that a spreadsheet is usually not the long-term answer. At best, it should serve to get your team rolling on appropriate process, decisions, domain expertise.

In that vein, I’m centralizing a repository/page on this site (available through the “Downloads” menu choice) that will collect the tools that I’ve published so far, along with links to the articles here that go into depth (and that usually name any number of cautions and caveats) about each of them. More will follow. All of them are carefully documented, reasonably robust to modification, and useful as a starting point for your own efforts in a similar situation. (They are all freely usable and modifiable, at no charge, under the broadest of the Creative Commons licenses).

Downloadable tools

To start, you’ll find the following tools and templates, all Excel-based, some very general, some specific to a particular problem:

• Project Portfolio Management. A workbook providing various alternative mechanisms (ranging from the simple to the more complex) to quantitatively “right-size” your portfolio of projects to your department’s overall capacity, so that you don’t bite off more than you are able to chew.

  • Downloadable spreadsheet

  • Blog post describing the approach and tool

• Return-on-Investment (ROI) analyses. A template for Investment analysis for any project, allowing for input of core assumptions on costs and benefits and producing quantitative measures such as ROI, IRR, and payback period.

  • Downloadable spreadsheet

  • Blog post describing the approach and tool

• Resource allocation. An elaborate spreadsheet to help in detailed day-to-day resource allocation across multiple projects for a small team.

  • Downloadable spreadsheet

  • Blog post describing the approach and tool

• Budget planning. A budget planning tool to model a corporate desktop/laptop refresh approach, allowing for corporate growth and planned retirement of obsolete machines.

  • Downloadable spreadsheet

  • Blog post describing the approach and tool

Additional tools

Additional tools that I may write up in the future and add to the catalog include the following:

• Performance metrics, development. Defect trend tracking and launch readiness evaluation.

• Performance metrics, development. Test case tracking and trending.

• Performance metrics, operations. Web site outage cost calculator and tracker. See my blog post here on such a tool.

• Performance metrics, operations. Bug trend category and throughput analysis

• Financial analysis. Total Cost of Ownership analyses for IT assets

So by all means, examine the tools I’ve provided, and feel free to appropriate and modify them for your use where they’re applicable. I’d certainly appreciate hearing of your results when doing so. While I can’t promise to provide deep support for issues or modifications pertaining to these tools, I’m happy to answer quick questions about them.

But keep in mind as you do this: no tool, whether simple or complex, is ever a cure-all. Achieving successful solutions takes more than plugging in a tool; it takes reflection and constant examination of the people, processes, and policies that surround the use of tools.

Good luck with the balance!