Tips for Project Managers: Doing Scrum in Microsoft Project 2010

Two years ago, I was involved in a project for a luxury company. The previous project manager had used Scrum as a tool to manage the project and the deliverables. After a quick introduction to this methodology, I used his tools (mainly Excel) to implement Scrum.

Two years ago, I was involved in a project for a luxury company. The previous project manager had used Scrum as a tool to manage the project and the deliverables. After a quick introduction to this methodology, I used his tools (mainly Excel) to implement Scrum.

Today I would like to reuse Scrum in another project, but I wanted to see if it was possible to use Microsoft Project instead of Excel to manage Scrum artifacts such as Product backlog, Sprint Backlog and the Burn down chart.

Note that this article will not explain the methodology; you can check this article in Wikipedia or watch this interesting video by Hamid Shojaee for a quick course on Scrum.

Step 1: Create Product backlog

The Wikipedia article says: “The product backlog is a high-level list (of features).” This is basically what Microsoft Project does with tasks list. The article goes on to say: “The product backlog contains rough estimates of both business value and development effort.” This can be achieved by adding some custom fields and by displaying existing ones.

  • Task Name
  • Description (this will be done in the task note)
  • Sprint (Custom text or number, must be a lookup), see below on how to create a lookup.
  • Points (using for example the Fibonacci sequence) (Custom number or list), note that “Priority” as a field name is already used
  • Baseline work
  • Actual Work
  • % Complete
  • Work
  • Duration
  • Start and finish date
  • Resource Names

Here is how to create a lookup field: 
 Scrum Sprint lookup table

Right click on one column header and select custom fields, then on the Custom attributes section, click on Lookup… to define values.

This is an example of a project file I made:

Scrum project file example

As you can see, on note mouse over, I see the task description (note). We can also add this field as a column to display it in the table.

Step 2: Sprint backlog

To display stories based on sprint, I just create a custom Group by (Group by Sprint). This will be my sprint(s) backlog.

Sprint backlog screenshot

Step 3: Burn down chart

Now it’s time to create the burn down chart. First of all I will input some actuals in my planning and update it. Note that the status date has been set to 16/01/2012 (I am a little bit ahead of schedule). Then click on Visual Reports.

Visual reports burndown screenshot

Click on new template, select Excel application and Assignment usage as Data Type. You also need to add  the custom fields that you have created to your report. Click on Field Picker and select the Sprint (Text 2) field; this can be used to filter data. Let’s use days as Level of  usage data to include in the report (Note you can use weeks or any other time frame).

 Visual reports new template screenshot

Important note: To be exported as a dimension, the custom field should be a lookup. This is why we have defined the Sprint custom field as a lookup.

Visual reports field picker

Create your pivot table in Excel as follows:

 Excel pivot table

Now you need to create additional columns to make calculations of:

  • Cumulative Baseline: Cumulative of Baseline Work
  • Cumulative Actual: Cumulative of Actual work
  • Remaining Planned = Baseline Work Grand total – Cumulative baseline
  • Remaining Actual = Baseline Work Grand total – Cumulative actual
  • Also copy the day number on the first column

Excel pivot table additional columns 

This will be used to draw your burn down chart. Select the first three columns and insert a 2D line chart.

Remaining actual v planned line graph

You now have your burn down chart. The yellow tag highlights when remaining work is not being done; though in this case it  simply shows the weekends. As you can see at the end of the graph, on January 16th we are ahead of schedule.

NOTE that of course this report can be saved as a template for future use. When saving your file, you will be prompted for that.

Save prompt 

Alternative solution

Some people may say, “Wow it’s too complex for me, do you have an easier solution?”

There is always a simpler solution for lazy project managers :). You can download the Microsoft Project 2010 Scrum Solution Starter a free package (under the MICROSOFT PUBLIC LICENSE) on the MSDN website.

The pitch given on this page is:

Microsoft Project 2010 Scrum Solution Starter is designed to provide guidance on using the Microsoft Project 2010 to manage Scrum projects, aiming to help individual Scrum teams to start using Project to:

  • Manage Product backlog
  • Manage Scrum backlog
  • Track progress and generate Burn down charts

You can check the overview document under download section for a quick “how to guide.”

I did not use it in a real project yet, but I will try and I will not miss out on the opportunity to write again about my experiences.

Digital news!

Are you as digitally addicted as we are? We can supply you with a regular dose of digital news. Simply sign-up, or click to Follow us online.

About the author

Jeremy Cottino
Jeremy Cottino

Experienced in project management of IS/IT projects for multinational companies. Jeremy is the reference profile within the company with regards project management methodology and tools. Confirmed client-server and web development engineer, Microsoft Certified Professional (.net platform, SQL Server and SharePoint)