Excel

Repeat after me — Excel is not a project management solution

Aside from dealing with organizations that oftentimes must use Excel as workarounds due to limitations of legacy software systems, I was reminded of the ubiquity of Excel in a recent article by my colleague Dave Gordon at AITS on the use and misuse of RAID (Risk Assumptions, Issues, and Decisions).  His overall assessment of the weakness of how RAID can be applied is quite valid.  But the literature on risk is quite extensive.  The article “Risk Management Is How Adults Manage Projects” at Glen Alleman’s Herding Cats blog is just one quick overview of a very mature process that has a large amount of academic, statistical, mathematical, and methodological grounding.

The dangers that Dave is identifying are not implicit in RAID so much as they are implicit in anyone who uses Excel.  It is not that Excel is a bad tool.  It is very useful for one-off spreadsheet problems.  It is not a software solution and is not meant to be one.  Going to Microsoft’s site on the progression of Excel to Access to SQL Server clarifies the differences.

Note that in my title I didn’t use the word tool.  This word has been the source of great confusion.  To the layman it seems to be a common-sense descriptive.  When you physically work on something–a home repair, an automobile, a small bit of machinery–you have a toolbox and you have a set of tools to address the problem.  So the analogy is that projects (and other processes) should be approached the same way.  Oftentimes this is done in the shorthand of marketing.  It’s easier to explain something complex through a simple analogy.

But no descriptive has been more harmful or destructive in preventing people from fully conceiving of an understanding of the needed solutions in the project management community than the word tool when applied to software applications.  A project is a complex system–a complex adaptive system.  What is called for are software applications that fit into the manner that project systems operate.

Back in the 1980s and 1990s there was a great deal of justification to take the spreadsheet software that came with the operating environment or the Office Suite and adapt it administrative needs.  There were a great number of individual tasks and processes that needed to be automated, and the market had not yet responded to that demand.  In some cases the demand was only vaguely understood, and the solutions were not fully conceptualized.

Then, as software applications became more sophisticated to begin to replace manual processes, they oftentimes could not address all of the corollary operations that need to be performed in those systems.  Oftentimes, the solutions addressed the 80% need of these requirements, and so one-off workarounds until the software was developed to be more comprehensive, were applied.  The very process of automating previously manual tasks had an effect on organizational processes, driving them toward greater sophistication.  The phenomena of Knowledge Discovery in Databases (KDD) is but one of these effects.

But note that when relying on Excel for important processes, such as risk handling, that KDD is impossible.  An accountant or head of finance in a corporation would not use Excel to keep the company’s books, even though that was the original target audience back in the 1980s for spreadsheet software.  No doubt that financial personnel use Excel to supplement their work, but using it as the primary solution to constitute the system of record would be foolhardy.  Even very small businesses today use more sophisticated financial management solutions.

The reason why one must be extra careful with any process when using Excel as the solution is that the person is still to a large extent, a part of the computer.  The person must perform operations that a spreadsheet application cannot perform.  This is why in risk management that assumptions, issues, decisions, and handling are tied to the work.  The origin of all work decomposed from the requirements are the plan and then the detailed schedule.  The detailed schedule, consisting of activities, is then further decomposed into the work organization: tasks, resources, etc.  There may or may not be a WBS or OBS that ties performance reflected in terms of earned value.

Using Excel as an external tool in addressing this important and essential process separates it from the other elements of project management systems.  It thus creates a single point of failure in the process–the mind of the individual keeping the Excel spreadsheet.  It also containerizes the information, preventing it from being mainstreamed into the larger organization, and thus being a source of KDD.

More on Excel…the contributing factor of poor Project Management apps

Some early comments via e-mails on my post on why Excel is not a PM tool raised the issue that I was being way too hard on IT shops and letting application providers off the hook.  The asymmetry was certainly not the intention (at least not consciously).

When approaching an organization seeking process and technology improvement, oftentimes the condition of using Excel is what we in the technology/PM industry conveniently call “workarounds.”  Ostensibly these workarounds are temporary measures to address a strategic or intrinsic organizational need that will eventually be addressed by a more cohesive software solution.  In all too many cases, however, the workaround turns out to be semi-permanent.

A case in point in basic project management concerns Work Authorizations Documents (WADs) and Baseline Change Requests (BCRs).  Throughout entire industries who use the most advanced scheduling applications, resource management applications, and–where necessary–earned value “engines,” the modus operandi to address WADs and BCRs is to either use Excel or to write a custom app in FoxPro or using Access.  This is fine as a “workaround” as long as you remember to set up the systems and procedures necessary to keep the logs updated, and then have in place a procedure to update the systems of record appropriately.  Needless to say, errors do creep in and in very dynamic environments it is difficult to ensure that these systems are in alignment, and so a labor-intensive feedback system must also be introduced.

This is the type of issue that software technology was designed to solve.  Instead, software has fenced off the “hard’ operations so that digitized manual solutions, oftentimes hidden from plain view from the team by the physical technological constraint of the computer (PC, laptop, etc.), are used.  This is barely a step above what we did before the introduction of digitization:  post the project plan, milestone achievements, and performance on a VIDS/MAF board that surrounded the PM control office, which ensured that every member of the team could see the role and progress of the project.  Under that system no one hoarded information, it militated against single points of failure, and ensured that disconnects were immediately addressed since visibility ensured accountability.

In many ways we have lost the ability to recreate the PM control office in digitized form.  Part of the reason resides in the 20th century organization of development and production into divisions of labor.  In project management, the specialization of disciplines organized themselves around particular functions: estimating and planning, schedule management, cost management, risk management, resource management, logistics, systems engineering, operational requirements, and financial management, among others.  Software was developed to address each of these areas with clear lines of demarcation drawn that approximated the points of separation among the disciplines.  What the software manufacturers forgot (or never knew) was that the PMO is the organizing entity and it is an interdisciplinary team.

To return to our example: WADs and BCRs; a survey of the leading planning and scheduling applications shows that while their marketing literature addresses baselines and baseline changes (and not all of them address even this basic function), they still do not understand complex project management.  There is a difference between resources assigned to a time-phased network schedule and the resources planned against technical achievement related to the work breakdown structure (WBS).  Given proper integration they should align.  In most cases they do not.  This is why most scheduling application manufacturers who claim to measure earned value, do not.  Their models assume that the expended resources align with the plan to date, in lieu of volume-based measurement.  Further, eventually understanding this concept does not produce a digitized solution, since an understanding of the other specific elements of program control is necessary.

For example, projects are initiated either through internal work authorizations in response to a market need, or based on the requirements of a contract.  Depending on the mix of competencies required to perform the work financial elements such as labor rates, overhead, G&A, allowable margin (depending on contract type), etc. will apply–what is euphemistically called “complex rates.”  An organization may need to manage multiple rate sets based on the types of efforts undertaken, with a many-to-many relationship between rate sets and projects/subprojects.

Once again, the task of establishing the proper relationships at the appropriate level is necessary.  This will then affect the timing of WAD initiation, and will have a direct bearing on the BCR approval process, given that it is heavily influenced by “what-if?” analysis against resource, labor, and financial availability and accountability (a complicated process in itself).  Thus the schedule network is not the only element affected, nor the overarching one, given the assessed impact on cost, technical achievement, and qualitative external risk.

These are but two examples of sub-optimization due to deficiencies in project management applications.  The response–and in my opinion a lazy one (or one based on the fact that oftentimes software companies know nothing of their customers’ operations)–has been to develop the alternative euphemism for “workaround”: best of breed.  Oftentimes this is simply a means of collecting revenue for a function that is missing from the core application.  It is the software equivalent of division of labor: each piece of software performs functions relating to specific disciplines and where there are gaps these are filled by niche solutions or Excel.  What this approach does not do is meet the requirements of the PMO control office, since it perpetuates application “swim lanes,” with the multidisciplinary requirements of project management relegated to manual interfaces and application data reconciliation.  It also pushes–and therefore magnifies–risk at the senior level of the project management team, effectively defeating organizational fail safes designed to reduce risk through, among other methods, delegation of responsibility to technical teams, and project planning and execution constructed around short duration/work-focused activities.  It also reduces productivity, information credibility, and unnecessarily increases cost–the exact opposite of the rationale used for investing in software technology.

It is time for this practice to end.  Technologies exist today to remove application “swim lanes” and address the multidisciplinary needs of successful project management.  Excel isn’t the answer; cross-application data access, proper data integration, and data processing into user-directed intelligence, properly aggregated and distributed based on role and optimum need to know, is.

Doctor My Eyes — Excel is Not a Project Management Tool (and neither is PowerPoint)

This is not to disparage the utility of a good spreadsheet to take care of those transient requirements to take a bit of data from the reporting systems and to run some custom algorithms or trends to perform what-if or other one-off analysis.  Probably most of us do this occasionally.

What I am referring to is the condition in many organizations in which data that consists of information essential to business operations is kept and analyzed using spreadsheets or other flat delimited storage or text methods.  The issue here is the optimum use of information, which the use of Excel and PowerPoint does not achieve.  Before anyone thinks that this is a contrarian’s post that is critical of Microsoft products, one need only read the technical advantages of true relational database management systems that are managed by specialized language like MS SQL.  Each of these applications and products has their proper place.

I would prefer that this post would be unnecessary since the title should be considered self-evident at this point–it is very similar to a presentation I gave almost 20 years ago when still advising senior managers in the U.S. Department of Defense and the aerospace industry–but the facts tell us that it’s assertion not so self-evident.  For example, a survey of 262 financial executives by the Financial Executives Research Foundation and the staffing firm Robert Half in 2012 indicated that 64% of public and private companies in the U.S. still used spreadsheets and manual methods for their financial solutions.  In 2011 a study by the ERP company IFS found that “of more than 281 manufacturing executives, 75 percent of study respondents aged 35 and under report using desktop spreadsheet software like Microsoft Excel instead of their company’s ERP, customer relationship management (CRM), supply chain management (SCM) or other enterprise software” while “respondents aged 36 to 45, 58 percent said they would use desktop spreadsheet software instead of their company’s designated enterprise applications.”

There are certainly good reasons in each study that people sub-optimize in using their data.  First and foremost is the hassle and expense of the centralized IT office.  The same methods and organizations that secure data and are tasked with maintaining configuration control for the IT resources of an organization are the same ones that oftentimes tend to impede flexibility in the acquisition of needed digitized business process solutions.  Back in the 1980s as the PC began to displace the old mainframes run by the stodgy folks wearing white shirts, pen holders, and black glasses who had to be bribed, cajoled, and stroked in order to obtain processing time, access to archived data, or–heaven forbid–devise and run a program, a new world order was declared in which the centralized and non-responsive central IT office was declared dead forever.  Then digital crime raised its ugly head along with a cacophony of new products, some of which delivered on their promises, but most which did not.  Thus the need, once again, to bring some semblance of order to the chaos that incompatible and ineffective products (among other problems) wrought.  So IT has come full circle and we are back to highly bureaucratized IT organizations (or aggressive IT services companies) that in many cases will defend their turf ruthlessly, many times to the detriment of the interests of the organization.  Thus, people do what they have always done when inflexible rules get in the way–they find ways around them.  The most convenient way is to use the “legal” workaround, which is the spreadsheet, the Word document, or the PowerPoint presentation.  Along with being able to achieve what is needed without having to ask permission or miss a deadline, managers and employees oftentimes learn these basic applications first.  They are intuitive, readily available, and familiar.

The problem, of course, takes many forms.  The first is that manual methods are rife with errors.  In fact, study after study shows that almost 90% of spreadsheets contain errors.  In 2007 CIO Magazine published an article listing the eight worst spreadsheet errors of all time, highlighting the very real damage that relying on these methods have created to organizations and businesses.  And this was before the financial crisis revealed similarly large errors in the financial markets during the most recent housing bubble deflation and resulting Lesser Depression.

In providing project management solutions to my target verticals, the area most in need of remediation–and which presents the most immediate opportunity for return on investment, improving data and process credibility, and preventing fatal business errors–is in displacing those processes built around managing data using Excel, Word, and PowerPoint.  In identifying data that is most appropriate for moving to a relational database management solution, I have found that this data shares certain characteristics:

a.  The data is an element essential to decision-making and must therefore be credible.

b.  The data is an element essential to trend analysis, organizational history, or corporate knowledge.

c.  The methods and algorithms used in the data’s processing must be provide results that are repeatable and consistent.

d.  The various elements of data are interrelated and may originate from systems of record.

g.  The output from the processing of the data is essential to job of more than one person or one process.

h.  The time it takes to construct, maintain, update, adjust, and use the manual processing environment for the data greatly exceeds the marginal value of the effort that could be saved using more automated methods to achieve the same or greater results.  That is, replacing the manual and spreadsheet method of using the data results in greater productivity and either cost savings or work shifting to more productive tasks tied to project success.

As a localized tool or as an intermediate point of review for data residing in tables augmented by more robust automation, Excel has proven itself to be a workhorse.  For in-depth papers and extended communication Word is the appropriate medium and for presentations PowerPoint provides a good basis for simplified communication of an idea or set of ideas.  But for the processing and integration of enterprise data these applications are inappropriate–and can be quite damaging–to business operations.