Using Spreadsheet for Backlog - Dependencies and Priority

Question

I'm currently using a Google Spreadsheet to manage my product backlog. It has a list of stories, each with an identifier, feature it belongs to, story points, and the sprint that it's assigned to. It works well for most of my small projects; I simply add a collection of stories, estimate the work, carve off groups for sprints, and the project gets done.

However, there are two major failings that I can't seem to surmount; these are:

1. How can I effectively manage priorities within and across features?
2. How do I indicate dependencies between stories?

As a preamble, I cannot move to any other project management tool, nor build my own. I tried both of these solutions; nothing works the way that I need it. (For example, I use the historical average velocity to plan new sprints.)

Issue #1: Managing Priorities. At the beginning of the project, I add dozens of stories, prioritize them amongst each other, and rank them with a priority number. However, since I need completed stories for historical purposes, I can't just sort by priority to find the next most important stories; I see a lot of clutter. I need to go through, checking the status of each story, and picking out the unfinished ones. Very tedious.

To make it worse, I add new stories in the middle, and some of them are high priority; but then, it becomes that every story is relative to every other story -- otherwise, I get a chunk of done, super-high-priority stories at the top that I always have to look at.

I've tried removing the priority number entirely and just shuffling stories; this seems to work a bit better, but doesn't let me easily view dependencies between stories.

Issue #2: Managing Dependencies I mentioned that I use a priority number; this allows me to implicitly code dependencies. if story A (with priority N) must be done before story B, I put story B's priority as N-1 or N-2.

This has several problems; first, if I split off work, I need to re-prioritize everything. Even if I use N-10 or N-100 instead, it still means a lot of manual renumbering.

Plus, I prefer not to use a priority number; but this makes it impossible to see which stories can be done in parallel, and which depend on each other.

I'm hoping there are good solutions to these. These are significant problems, and I'm keeping my scope very limited right now to work around them.

Answer 1

For what I get from your first point, managing priorities, your actual problem is that done sprints clutter your spreadsheet. So why you don't just add a "done" column and sort with 2 columns, "priority" and "done". Google Spreadsheets lets you do just that! Your columns just have to be adjacent:

• Select your 2 columns with the mouse
• Go in Tools->Sort...
• Click on "add another sort option"

Choose the right columns and the right sorting direction, and you're done!

Concerning your second issue, I can imagine a solution, but it's a bit more complicated. You need to have a column with an ID for each sprint and a dependency column. If each sprint depends only on one single other sprint, it's not too complicated: The priority field becomes a formula, calculated with the priority from the task on which depends the current sprint, minus an offset, always the same or stored in another colulmn. If your sprints can depend on more than one other sprint, it's also doable, but the formula becomes really more complicated: Choose the sprint from which the current sprint depends, with the biggest priority or something like that.

Answer 2

I personally try not to spend too much time prioritizing everything. My roadmap is a living, breathing document that is in a constant state of flux. If an opportunity comes up, we quickly assess the situation and put resources on the problem if the likelihood is high that we can take advantage of the opportunity and also build a product that can be marketed.

If we miss the opportunity with client X, then we can fallback and market it to other clients.

If you only prioritize the items that will be tackled in the next few sprints, then you increase your flexibility and free up your time to focus on other aspects of the project.

The market can sometimes change so much that priority item #100 could slip into obscurity, and the time you spent prioritizing that is time you can't get back.

Answer 3

If I understand correctly you use the spreadsheet for two reasons: you manage current backlog content (prioritizing, adding stories, etc) and analyze historical data. You may consider splitting these goals into two different tools:

1. Spreadsheet which basically looks the same. You add every feature there, fill the data (sprint number, story points, all that stuff you need for your analysis). You use the spreadsheet basically to store and analyze historical data. Which leaves us with little support for backlog management, but that's where another tool kick in...

2. Sticky notes on the wall. When you add a story you write it on a sticky note and put on the wall/board/whatever. Record in the spreadsheet is a copy on a sticky note (you can even add records to the sheet when you have a story finished). You do whole backlog management on the wall with sticky notes. You don't have any problems with reprioritizing them - it is as easy as shuffling cards. You don't have problems with showing dependencies - you can easily group some cards in one place. You can add some mark to make top priority stories visible, etc.

I worked in this kind of setup with Kanban and it proved to work extremely well. And it doesn't really require anything more but a stack of stickies and some empty room on the wall.

Answer 4

Group dependent stories into buckets ("projects").

Prioritize the buckets/projects as a first filter, then prioritize stories within as a second filter.

Another idea is to create a network diagram /flow chart of the stories and the dependencies and use different colors of the nodes for priority.