BUSINESS PROCESS REENGINEERING efforts, computer system implementations, plant capital expansions, training and documentation (T&D) development initiatives, and plant launches—projects that create or develop information often involve coordinating the activities of numerous knowledge workers. often these workers must analyze data and develop outputs, the primary value of which is the clear presentation of this analysis. project outputs can include policies, reports and recommendations, proposals, business process flow diagrams, operating procedures, work instructions, job aids, and training materials.
In some cases, numerous people may be involved with the construction of a single deliverable. In other cases, one person may be responsible for 50 deliverables that need to be indistinguishable (as to authorship) from another person's 50 deliverables. As more and more deliverables are ultimately going to be warehoused electronically, well-thought-out and consistent electronic file structures are imperative. This leads to a situation in which creativity in the presentation of content may be detrimental.
How can you minimize the risks of not meeting consistency, quality, budget, and schedule requirements by a project team? How can percent complete be unobtrusively and impartially measured? How can project status be accurately and efficiently captured without taking up the valuable time of the development team?
Simplified Tracker
Exhibit 1. The spreadsheet tracking tool used in the training development project described in this article was a matrix that helped to calculate percent complete by deliverable. This simple tracker could be linked with others to give a projectwide picture of progress.
Linked Spreadsheets
Exhibit 2. By establishing a “parent-child” relationship among the linked spreadsheets, the higher-level spreadsheet can be used to summarize information, while the lower-level ones can address the high level of detail needed to actually do the work.
In my organization we've used the tools described in this article to manage training and documentation development projects. These tools can also be applied to other types of projects that involve numerous people in complex work processes.
For example, a training development effort for a recent systems Applications and Products software (SAP, release 3) implementation resulted in the development of more than 350 software work instructions. Each work instruction contained screen shots and, in some cases, complex branching tables to accurately depict the choices a user would need to make when performing a system transaction. The materials were designed to be used in paper form for the short term and in an Electronic Document Management System (EDMS) for the long term. The project's engineers and instructional system designers needed to apply creativity and logical thinking to analyze the computer system and determine the most efficient way to perform each end-user task. clear writing and conformance to standards were required in the presentation of the information.
Additionally, because the constraints of the EDMs were not known at the beginning of the project, the documents were designed for consistency and robustness from an electronic perspective. This was critical because most application software allows for numerous ways of doing the same thing. For example, indenting a word using a word processor can be done using a style sheet, the tab key, the space bar, or even a table. Inconsistency in this seemingly trivial detail could cause an EDMs to read each document differently and require unnecessary and non-value-added rework. on the other hand, strict conformance to electronic standards could greatly simplify the programming to convert the documents.
Finally, the materials needed to be developed on time and on budget. on this project 10 engineers and instructional designers composed the work instruction development team. Work processes to ensure quality and tools to measure production and identify bottlenecks were needed. Three tools were used to ensure that each member of the team produced high-quality outputs on budget and on time that also conformed to a single standard: documentation standard, electronic template, spreadsheet tracking tools.
The Documentation Standard was developed during the analysis and design stages of the project. It articulated the analysis approach used to identify specific software documentation requirements, document design principles for constructing each deliverable, and define work processes. The Documentation standard addressed the following subjects:
Development process, including technical and internal reviews (managing the technical review process proved to be very important during project execution)
Document organization and formats
Conventions, mechanics of style, and punctuation standards
Standard approaches for documenting user decisions, branching, and loops within system transactions
Graphical standards for system screen shots and business processes and system flows.
The initial analysis team developed human-factored formats and built these into the electronic template. Entry of critical document management information was required as a first step in document construction to help ensure every deliverable could be properly cataloged and that paper copies could be matched to the electronic versions located on servers. The template provided a consistent layout for the documents and automated many repetitive tasks. For example, four different types of branching tables were used. Each table had a different number of columns, each column contained different column heading information. Rather than have each developer take the time to construct these (and hundreds were eventually used), macros were developed to define the various branching tables consistently and automatically. Dialog boxes were developed to prompt users where intervention was required.
Parent Tracker
Exhibit 3. Affectionately dubbed “the Mother of All Trackers,” by project personnel, this spreadsheet was used to summarize status for all tasks involved in a 10-course training development effort. (Only one-third of the actual document is pictured.)
Spreadsheet tracking tools were developed to manage and facilitate oversight of the development of each deliverable. As shown in Exhibit 1, the spreadsheet tracking tool is a matrix in which the left column lists the deliverables to be produced and the top row lists each development step. Each development step was assigned a weighting, and these weightings were used to help calculate percent complete.
From Exhibit 1, you can easily see the deliverable and development step perspectives. For example, by reading across the row, you can see that Deliverable 2 is 80 percent complete. By reading down the column, Development step 3 is 33 percent complete.
So far, this is simply another way of calculating technical percent complete by deliverable using a weighted work breakdown structure. In fact, on small development projects, you could stop at this point with very satisfactory results. on larger or more complex projects, you may want to link various trackers together and use the system as a central tool for project management and communication.
At first, some team members may resist recording their efforts. “I am so experienced that project management tools are not needed” or “I am so busy producing work that tracking my efforts will distract me from the task at hand.” While many project managers might disagree with these perceptions, if in the end the spreadsheet does not add value to development efforts, the perceptions might be reinforced.
The rest of this article focuses on some of the details of linking spreadsheet tracking tools, using the tool to understand and “debottleneck” the work process, spreadsheet development guidelines, and reviewing uses other than training and documentation development projects.
Linking Spreadsheets. Much of the power of spreadsheet tracking tools is realized by linking the spreadsheets of team members on a LAN, WAN, intranet, or Internet so the results of a total development team are summarized in one document. As shown in Exhibit 2, a parent/child relationship is established among the trackers.
Exhibit 1 was an example of a “child” spreadsheet; Exhibit 3 is an example of a “parent.” As shown in Exhibit 2, the child spreadsheets can be linked to either a parent spreadsheet and/or most any other application. one management approach is to have individual developers manage the spreadsheet files for their portion of the project (the child), while the project manager manages a higher-level document (the parent) that is automatically updated from the individual files.
With this setup, the child spreadsheets can address the high level of detail needed to actually do the work, while the parent document can summarize the information in a manner that allows bottlenecks to be identified and resolved. In the project described earlier, more than 6,000 discrete tasks were performed to develop 366 work instructions and associated training. About one-third of these tasks involved a handoff of work in process (WIP) deliverables from one person to another. Developing child spreadsheets to document these 6,000 tasks and 2,000 handoffs and then providing a one-page summary sheet to the project manager was an important step in ensuring quality, schedule, and budget.
Observations Based on Data in Parent Tracker
Exhibit 4. By examining the percentages complete of various taks in the parent tracker, it is possible to identify bottlenecks and develop a plan to get the process moving.
Using the Tool to De-bottleneck the Work Process. The software documentation development project described earlier was under extremely tight schedule and resource constraints and the spreadsheet tracking system was critical in managing the work effort within the constraints. Ten courses were under development, and there was a step in the development process that required the documentation developer to hand off an interim deliverable to the client for a technical review. This review was on the critical path for the documentation development. The documentation development was on the critical path for the system implementation because an overall goal of the project was to have the workers trained before the system went live.
Exhibit 3 shows the spreadsheet (affectionately known on the project as “The Mother of All Trackers”) that was used to summarize these tasks for the 10-course development efforts described. For the sake of clarity and brevity in this presentation, only one-third of the tracker is shown.
This spreadsheet identifies milestones/major handoffs among organizations in a typical documentation development process. The first milestone (system ready) was achieved when a programmer was able to run a system transaction in the presence of the documentation developer. The next handoff/milestone was achieved by the developer providing a draft document for technical review. The third handoff occurred when the user returned a reviewed document to the developer. The last handoff was when the documentation developer finished incorporating the reviewer's comments and the document was handed off for validation during system testing. In the above example, undesirable variances from the averages (“Total” row) for each course are in bold italics.
By inspecting the Exhibit 3 summary document, it is possible to quickly make the observations in Exhibit 4. Obviously, more investigation is required to surface the root cause of these observations. However, if some of this information corroborates other observations (for example, the Order Management developer has been pulling out all the stops, the Order Management subject matter expert (SME) appears to be overwhelmed, and the Distribution SME hasn't been able to focus on your deliverables), this tool could be used as an impartial way of measuring and communicating this information. For example, you might point out that your Order Management developer is working a lot of overtime to keep his or her deliverables on track, but you are concerned that configuration and review tasks outside your control might be falling behind. Likewise, you might point out that the Distribution developer has not returned any deliverables, and you want to see if there is anything that can be done to get that process working.
If desired, more detail can be gleaned by “drilling down” to the detailed child spreadsheet (Exhibit 5) that feeds the parent summary document. By examining the Master Records spreadsheet, you can try to develop an understanding of the status of this course.
The basic steps in the process are to draft a work instruction and have it pass through two reviews (initial technical and editorial) before it is sent out for an SME review. After the SME reviews it, the comments are incorporated and reviewed. The work instruction is then ready for testing. Overall, the Master Records developer appears to be developing her materials in an organized manner. Some things to watch for based on the data in this spreadsheet include:
Child Tracker
Exhibit 5. Drilling down from the parent spreadsheet to the child spreadsheet gives a more detailed picture of the various parts of the project and how they are contributing to the overall status.
The third and fourth work instructions have not been drafted.
Apparently, several work instructions have been developed without the benefit of an editorial review and sent out for review. Half of these have not been returned by the SME.
While the system was not ready on time, the developer has managed to overcome the initial schedule slip, and this work effort appears to be back on schedule.
Establishing Speadsheet Development Guidelines. Following two key principles when designing spreadsheets will help ensure they add value to the team and management. The first is that the spreadsheets should be relevant to their development process. The second is that the spreadsheets should provide the proper management data.
Keep the Spreadsheets Relevant to the Developers. At first, some team members may not see the value in tracking their development efforts. For a variety of reasons, they may resist recording their efforts. The reasons expressed might include, “I am so experienced that project manager tools are not needed” or “I am so busy producing work that tracking my efforts will distract me from the task at hand.” While many project managers might disagree with these perceptions, if in the end the spreadsheet does not add value to development efforts, the perceptions might be reinforced.
On the other hand, if the tool is structured so that it helps developers manage themselves and it is easy to complete, both experienced and novice developers will quickly see the value in it. On a project that recently ended, the team initially expressed reservations about the tools but then grew to use them regularly. When these developers shifted to other projects, their new projects quickly adopted project-specific variants of the tool. Below are some guidelines that will help make the tool and its implementation useful:
Structure the development steps in the top row so they describe the way your developers will really do the work. If the work process is new to the team, the tool can teach and then reinforce the work process.
Develop granular enough tasks so that they can be checked off as they are completed. Don't make the tasks so large that they require a percentage to be input. Having developers check off at least a block a day is good.
Design for the normal development case. Don't overcomplicate the tracker by designing it to handle every possible development permutation. Handle exceptions as exceptions.
Don't ask for status updates too often. You want people to develop deliverables, not status reports.
Ensure the Spreadsheets Capture the Right Data. You want to ensure that the tool your developers complete gives you the big picture so you can assess trends, yet provides enough detail so you can dig deeper. Some guidelines to help in this area:
Handoffs between people and/or organizations are valuable points to track. Major internal development steps that are key to quality are important also.
Identify key milestone handoffs and steps and insert target dates. For these milestones, have your developers insert the dates they actually meet the milestones.
Don't hammer people if they report they missed a milestone. The reporting goal at the end of the project is to assess where you beat, hit, and missed milestones. If you press the point, records often will inaccurately show that you hit every milestone. This does not obviate the need to meet schedule, budget, and quality expectations.
If you have to err when assigning weightings, slightly underweight the early ones. This will tend to make any surprises at the end pleasant ones.
If deliverables with work expended are deleted during the project, keep them on the tracker. At the end of the project you will be able to see tasks performed on outputs that were not finished. This might help you identify a source of cost underestimations.
Avoid the temptation to overcomplicate and micromanage. It is possible to use logical operators in spreadsheet formulas that will flag individual deliverables when a work process step is completed out of order or behind schedule. With such programming, detailed exception lists can be produced (for example, deliverable 123 is behind 1 day on work process step 3). For most teams and projects, this kind of microreporting can quickly get unwieldy, as projects rarely go exactly according to plan.
Uses Other Than Training and Documentation Development Projects. These tools have applicability beyond training and documentation efforts. They could be used for any project on which the same basic work process is applied to a large number of deliverables. Such projects include business process reengineering, software development, and engineering design efforts.
In business process reengineering, the activities in the work process might flowchart the as-is process, flowchart the to-be process, describe the delta, develop documentation describing the to-be process, or develop processes to change to the to-be process. Each of these activities probably has tasks and sub-tasks. A typical corporate reengineering project may impact dozens of processes. A workable set of spreadsheet matrices listing all the processes and the critical tasks could be developed and then used to track and manage the effort. As tasks/processes are added and deleted, the impact of the change can be efficiently determined.
This is especially important when deliverables are deleted. For example, if a business process is deleted from the effort halfway through the reengineering process it can remain on the tracker, clearly showing which tasks were performed on that process. The formulas could be modified to remove that process from the technical percent complete calculations. At the end of the project, this can help people who are not close to the project understand resources expended that were not tied directly to an end product.
In software development, projects typically have analysis, design, and prototype phases, followed by development. Development may require the coding and integration of numerous modules by a number of developers. Spreadsheet tools to track and manage the development effort could be designed to codify all the steps in the development process. If all the developers on the team used the spreadsheets, the project manager could unobtrusively gain insight about progress, and the developers might find they increase their conformance to the development process.
In engineering design efforts, the design of a complex assembly may involve the detailed design and testing of numerous small parts. A project manager faced with managing such an effort might find a linked spreadsheet tracking system helpful in managing the progress of thousands of parts.
THESE TOOLS WERE DESIGNED to support providing professional services to clients on a limited time and materials basis. The tools allow you to have a clear understanding of the work effort and the assumptions, and can help to impartially present project status to project stakeholders. This impartiality is very valuable when stakeholders/budgetholders are responsible for a bottleneck. ■