Scrum Product and Sprint Backlogs using Excel

• Download documents and help file - 85.7 KB

Introduction

This article describes two Excel documents that can be used as a Product Backlog and Sprint Backlog for the Scrum Framework.

Although there is software out there that does a better job in many areas, these are intended in any of the following circumstances:

• People who want to quickly get into using those artifacts, without needing to buy hardware or software. The only software needed is Excel, which is prevalent in most companies.
• Since it is free, it also avoids the issue of having a complex purchasing process hinder forward progress.
• It may also be one of the few choices for organizations that are Windows only as I have yet to see a good, free Scrum tool that runs on Windows\SQL Server.

Because each backlog is in one document, it also has a few other advantages:

• The entire document can be emailed out. This is useful for status reports to people who are not directly involved.
• If stored within a document management or source control system, you can get versions of the entire project. This can be useful if you want to see what items were in the backlog at a certain point in time.
• You can easily add or remove columns, rows or other information. Quite often, I've added columns to the backlogs that are only relevant to a specific project. For example: whether we have to discuss a requirement with an external development team, whether we need clarification on the requirement, etc.

Some disadvantages:

• It is not intuitive. There is a learning curve to using the documents, but once you gain some experience, they are easy to maintain. This is the area that software holds a huge advantage, because it can guide a user through entering the data. I have tried to compensate for this by writing a detailed help guide.
• Because it is a single document, you cannot have multiple people working concurrently on it.
• Not ideal for storing artifacts. Some software allows you to link and store documents with the tasks. In the case of Excel, you can embed links to the file system or embed the artifacts directly into the document, but it's not ideal.
• You can't reliably roll up data from multiple backlogs. Although it is still possible to reference data across Excel documents, it is not always dependable. This makes it hard to have "Scrums of Scrums"

I would like to point out that this article is not focused on educating readers on Agile methodologies or Scrum. It only covers how the artifacts I created should be used. There are a number of fantastic books and websites that should be used for research.

Background

While working at T4G, an eBook by Martin Fowler got me interested in Agile methodologies. The more I read, the more I admired them. Eventually, Scrum became my Framework of choice, because even though our development processes were top notch, I always felt there was a better way to manage projects than traditional predictive methodologies.

I just got off a massive project where our Gantt chart changed so frequently, it was almost a full time job just re-prioritizing, revising, re-estimating and reordering tasks. On the next project, people were hesitant to adopt Agile methodologies, so I needed a way to quickly and with minimal cost, start using Scrum and prove that it was a better way to manage a project. I wrote up the first version of a Product Backlog and Sprint Backlog one evening, thinking it would only take me a few hours. Although that was true, the version that is downloadable here has been improved during every project I use it on, to the point where I think all the kinks have been worked out. There were actually quite a few unexpected problems I did not foresee, especially with the charts.

Using the code

There are a few commonalities with both documents:

• They are both written in Excel 2003 and have been tested on Excel 2007.
• There are no macros. Too many organizations restrict macros and I don't want to have any concern about my documents spreading VBA viruses.
• They both have a single sheet that has all the tasks for the team.
• There are one or more charts in different sheets.
• There is a "Lookup" sheet that contains data that some cells will use to provide a list of choices. This is done using the "Defined Name" and "Data Validation" functionality in Excel.

I highly recommend you store these documents in a centralized store, such as a document management system (e.g. LiveLink, SharePoint, Documentum, etc.) or in a source control system (e.g. CVS, Team System, SourceSafe, SourceGear Vault, etc.). That way, you can have multiple people (e.g. Product Owner, ScrumMaster, etc.) editing the documents, you get version tracking and you can see who made the change.

There is a set of instructions (called Help.doc) that describe common tasks that you would perform with both the Product Backlog and Sprint Backlog. Please see that for details on using the artifacts.

Reports

There are a set of reports based on the data found in the Backlogs:

• Product Backlog

  • Product Burn-down: This shows the velocity of the team at completing tasks per iteration. It shows how much effort was completed (burn-down), new scope that was completed, new scope that was not completed and how much work is remaining.
  • Effort By Iteration: A pie chart that shows the effort that has been allocated to each iteration. This can be used to try to balance out the requirements across the iterations.
  • Effort By Priority: A pie chart that shows the effort for each priority level. This can be used to make sure effort is balanced across the priorities.
  • Effort By Risk: A pie chart that shows the effort for each risk. This can give a visual idea of the risk of the project.

• Sprint Backlog

  • Sprint Burn-down: For a Sprint that is currently in progress, it gives an idea of the velocity of the team at completing tasks at a daily level. It tracks the current work remaining on a day to day basis. It also predicts the completion date of the Sprint based on a trend line.

Points of Interest

The concept of velocity caused quite a few issues. A spreadsheet is really only good at tracking the current state. Building a full history into it would take up a lot of space. So I had to come up with ways to track initial sets of effort, re-estimations, etc. in a way that still tracked the velocity, without requiring a day to day tracking of changes. I feel I found the right balance, but sometimes the lack of an accurate history has caused some confusion.

Conclusion

Whenever possible, I will always use software to track an Agile project. Although when we first started to explore Agile methodologies, these documents were a quick way to get up and running without any hardware or software. It takes some time to learn the documents, but once you get used to them, they are easy to maintain, sometimes even being faster than logging into a system and editing the tasks.

History

• August 3rd, 2007: First revision
• August 8th, 2007: Fixed some defects in the backlogs. Category was hidden in the Sprint Backlog and unassigned tasks were not being correctly added to the scope for the Product Backlog
• September 7th, 2007: Changed the way Burn-down was being calculated to better take into account dramatic changes in scope. Fixed an issue with weekdays. Removed min effort estimate for Product Backlog.