Tracking Project Performance with Spreadsheets

There are dozens of project management software applications available in the market.  But many project teams have found them either too difficult to use, or limited in their capability; so teams revert back to using basic spreadsheets or action item lists.  That’s OK!  When I am managing small projects, I normally do all my project performance tracking using a spreadsheet.

There is a project planning table or spreadsheet that is ideally suited for this and it is called a WBS Dictionary.  WBS stands for Work Breakdown Structure and is an organized list of all the tasks, activities and deliverables in the project.  It represents all of the “work” of the project.  I have no clue why the table is called a dictionary – you definitely don’t want to organize the project tasks alphabetically!

Let’s talk about how to create the WBS Dictionary and some of the uses.

1.     Develop your project plan using your normal scope planning, schedule planning and resource planning tools.

2.     Document the plan in the spreadsheet. First, create the column headings you will use for each of the project planning elements. There should be at least one column for the task description, normally at least two columns for the schedule – those being the start and finish date for the task, and at least one column for resources – either personnel or budget.  I will often have a second scope column which describes the quality criteria for task completion – the “definition of done.”  And I will sometimes include multiple resource columns such as estimates or the requirement for a tightly constrained resource.   

3.  Then add additional columns that will be used for managing the project.  There should be at least one column for current status.  Often I include columns for items such as risk factors, variance, or relationships with other tasks.

4.     Now organize the project work based upon how you intend to manage the project.  If the project will be managed by deliverables, organize the tasks and activities into groups that support each project deliverables.  If the project is managed in phases, list each phase and the deliverables for that phase.  Then organize the tasks into the phase categories.  If the project is to be managed by departments or functions, organize the tasks by the department responsible for leading or completing the task.

5.     Take your organized list and insert it into the spreadsheet.  Fill in the information for each column and each task.  (I often will create the spreadsheet columns and structure first, and then as I create the plan I document it immediately in the spreadsheet and don’t bother with any other planning tools.)

6.     As the project progresses, insert the status information into the appropriate cell in the spreadsheet.  A technique that I often use is to “gray out” the lines that represent tasks that are completed and change the background colour to yellow for the tasks that are late.

7.     If there is a replan or update to the project, copy the spreadsheet into a new tab and give it a revision name or number.  Then update the project plan in the new tab and use the new tab for tracking status.  The old tab is then a historical record that you can use during Lessons Learned sessions at the end of the project.

8.     I share the spreadsheet with the entire team so that everyone can see our status and I run the team status meetings from the spreadsheet.     

When to use WBS Dictionary

Small/Focused Projects: The WBS Dictionary is an ideal tool for consolidating and communicating the project plan on small or focused projects.  The entire project plan can be presented in one table.  This minimizes the paperwork and documentation effort required.  In addition it is easy for everyone to follow and understand.

Large/Complex Projects: The WBS Dictionary is useful on large or complex projects for managing sub-projects within the larger project.  Examples of how I have used this are for summarizing a portion of the project such as a phase or tracking all the activities required to support a major deliverable.  The technique becomes unwieldy when the table grows to include hundreds of tasks.