In previous article “The Product Owner Iteration – Limit Your WIP” I described one way for a person to manage his/her work on a 2-weekly basis and avoid generating waste in the system. This time I will give you a simple spreadsheet to support you with: – Requirements management that avoids generating waste – Team dependency management – External dependency management – Prioritization & impact analysis – Managing multiple projects at once
Why would you investigate and use this tool?
We have been helping a lot of different organizations, teams and people to become more effective in the work they do and as such supported a lot of them in setting up a system that helps them optimizing their system. In many cases we come to similar solutions, even though tackling different issues along the way. The tool offered through this article will guide you to… – Manage your time effectively – Generate valuable insights to manage your deadlines – Provide you an instant impact analysis when changing priorities – See the impact of this “burning house” thing that you should do immediately – Announce expected delivery dates for your projects – Detect changes compared to announced dates – …
How will this tool help me do all of that?
Step1: Download the spreadsheet “here”. Step2: Open the file Step3: Use it as it is
What is this tool offering to me and what should I do with it?
Stories sheet
The stories sheet is the main workplace for anybody that is managing team requirements at any level. It is what we call a “Product Backlog” in an Agile environment and a collection of all the work/to-do’s the team(s) still need to work on. This is done in such a way that priority is identified by the sorting, top record = highest priority, bottom record = lowest priority.
“Project Name” & “Project ID”
An identification to which project or work package the item belongs to.
“Story” & “Story ID”
The item the team needs to work on, preferably in a Story format.
“Succes Criteria”
The acceptance criteria for the item described, it should provide an answer to the question “how to demo?”.
“Resp Mgr”
The responsible person for the item to work on, the person that will help the team to discover how to get the item done.
“Entry Date”
The date when the item was entered into the system, file. Action: This date is used to keep the file clean! When things are constantly moved to the bottom of the file and already in the system for a long time you might consider removing them completely.
“Active”
Place where we indicate if the item is being worked on or is still not started. For an Agile environment this might be used to identify the iteration/sprint in which the item has been committed on by the team(s).
“Team estimates”
“A-Team” & “Team McGyver” are identifications of the teams that will work on this product backlog, this is the place where teams will record their relative estimates for the items in the list. Action: This will indicate where to deal with team dependencies. It is important that both teams will work on a shared item (item with dependencies) during the same iteration (similar timeframe) as it is in both their interest to get it done at the same time and as such they will be available to each other to get things moving. If things are not scheduled within a similar timeframe people will not be available to each other, which will lead to a longer time to market for that item, as well as frustration amongst the teams involved.
“Team Expected Delivery Dates”
“A-Team End” & “Team McGyver End” are automatically calculated dates according to the team relative estimates and their predicted capacity. How does it work? Day A – Teams select from the backlog, top-down, what they think they can do for the next iteration to come, in our example file this is for a 2 week iteration. Day A+14 – Teams show what has been completed in the last iteration. Day A is the starting date that will be used to start the calculation of the dates and is registered in the “capacity” sheet on the right top. In our example this is May 8th.
Teams select something they think they can manage in the next iteration to come, the total amount of relative estimates is tracked in the “capacity” sheet as “Estimate” for the teams. For the last iteration in our example file this is 107 for A-Team and 62 for Team McGyver. At the end when they present what is really, really finished you see what they could actually deliver upon within the last iteration. This is registered in the “capacity” sheet as “Actuals” for the teams. In our example, for the last iteration done this is 33,5 for A-Team and 32 for Team McGyver.
Note: You already notice that there is a big difference between the estimated and actual calculated capacity for the teams in our example and that is on purpose as it is commonly known that teams need to learn how to plan their time effectively and that it will take on average around 4-6 iterations to get to a somehow stable situation.
With the data registered as estimate and actuals for the teams involved you can identify a somehow predictable capacity for the teams. This is registered in the same “capacity” sheet as “Planning” capacities for both teams. In our example this is 35 for A-Team and 30 for Team McGyver. These predictable capacities are then re-calculated on average capacity per day. In our example this is 2,5 for A-Team and 2,143 for Team McGyver (capacity/14 days or 2 week iterations) It is this last calculated average capacity per day that will be used to calculate the expected delivery date per team according to their relative estimate given.
“Early start”
Sometimes teams might not be able to start before a certain date due to some outside dependency (example: delivery of HW) and it is good practice to keep notice of this in order to avoid frustrations and planning changes.
“Deadline”
In general I do not prefer given deadlines and would like people to respect the calucated times but we are not always living in the perfect world aren’t we? So, similar as the above “early start” it might also be interesting to keep the deadline for a certain item as it is good practice to prioritize items in such a way that they are done shortly before a given deadline.
“Depending on”
In case there are external dependencies, generally causing a “early start” or “deadline”, it is good practice to keep notice of this in order to support the teams more appropriately in solving the dependency or managing it. Also a column to avoid but then again…
“Priority”
Last but not least I would like to go into the “Priority” column. It is this column that is, next to the item itself, the most important! By changing priorities you change the expected delivery date of an item! This is the only attribute that gives you the control of dates as all other attributes are based on experience, reality, hindsight and as such are not variables in the system but rather constants you can not change as fast as you would like to.
Action: Items that are selected by the team(s) to work on in the ongoing/upcoming iteration will get a priority below 100 and the attribute “active” filled in. Take in mind that items where multiple teams are involved in will need commitment from all teams together within the same iteration. All other items will get a number higher than 100. It is good practice to update the file in such a way that all numbers below 100 keep the relevant priority that existed before they were selected. It is also good practice for you to update the priorities from all items above 100 again to start from 100 and being increased by 100 for each item below as this will give you the option to re-prioritize or add other requirements in between others pretty easily. Just give the new/re-prioritized item a number above 100 that does not end on “00”.
Benefit: using the file you will immediately see the impact of the change on the record you are manipulating, just re-order the records according your adjusted “priority” settings and the calculated days will provide you with detailed insight on your change.
Note: Keep in mind that all of this data is optional and what you will register is very much dependent on the system you have in place. From the above list of fields I would really consider to leave out “Resp Mgr”, “Early start”, “Depending on” as they somehow indicate towards a sub-optimal system that is in place. So why did I leave them in the file while presenting it to you then? Well to show, admit to you that in many cases you’ll be working in a sub-optimal place and that it is ok for you to admit this. In fact all systems are sub-optimal as best practice is past practice and the world is changing faster every day.
How to avoid waste?
With waste we mean the waste of generating too much detailed information up front as also described in the product owner iteration article. If you did not read that article I advise you to do so before you continue this one. Managing the Stories sheet: Step 1: Get a relative estimate for all items marked with “?” in the team estimates columns (yellow background)
Step 2: Go through the records top down and see if you find a team estimate marked with a red background. This will indicate that the story is too big in relation to the expected delivery date and that you should take action to split it or get more clarity on the subject at hand. What is marked? – items that are estimated > 13 and expected to be done within 45 days – items that are estimated > 21 and expected to be done within 90 days – items that are estimated > 40 and expected to be done within 180 days
Step 3: Go through the records tow down and see if you find team estimates marked with a blue background. This will indicate that the stories are kept on a very detailed level in relation to their expected delivery date and that you should take action to collect/group a couple of them into a bigger piece of work with more value; or that you should just delete the record out of the file. You do this in order to keep the file manageable and easy to manipulate! Not grouping or cleaning would result in a file with a lot of detailed requirements and easily having +500 records, when you then need to make decisions you always need to go back to all details and many records which will make your decision making go slow and most probably also less helpful for the teams/organization. When you resemble/clean you will be able to see impact, make decisions a lot faster not bing distracted by details where they should not be. What is marked? – items that are estimated < 21 and expected to be done more than 240 days from today
When nothing is marked then you should not do anything expect of adding more information, details and help your stakeholders, team members as described in the product owners iteration article.
Done sheet
This sheet is just there to keep track of all items that were really, really done by the teams. It is good information to review with the teams in order to optimize their team workings, it will provide meaningful insights on how things are done and as such can help you and the teams optimize for value better.
Capacity sheet
See “Team Expected Delivery Dates” item from the Stories sheet explanation.
Burnup report sheet
This is a quick reference you can use to analyze the product backlog! When no filtering is applied on the stories sheet you’ll get an overview of everything that needs to be done for the teams. A good line would be going in small steps up in the beginning and the further away from today the more the line will go up in bigger steps. In other words you’ll get an exponential growing line and that’s good.
Benefit: When you filter on a single project you’ll get an overview of that project only and can easily see how things should progress. A perfect line is similar as described above depending on the priority the project has amongst all others.
I see a big jump in the line, what does it mean?
A big jump in the line might be an indication that you have to deal with a to-do item that has a great uncertainty (big relative estimate) and that is not necessarily a problem. The only problem that you might have is the related deadline of the project, if that uncertainty is expected to occur at the end of the project then you have 2 options: (1) you move that item out of the release and for a next one to come or (2) you move this uncertainty to the beginning of the project so that it gets tackled soon and as such you soon discover what it meant. When you use project filtering while looking at the report, a big jump in the line might also be caused by another project that is scheduled/prioritized in between yours and then you should see if you could sequence those instead of mixing as this would provide you with a more optimal and easier way to manage.
I see a flat line, what does it mean?
A flat line means that the team is not involved in work for the items visualized. If there is no filtering attached it would mean that the team is out of work for a while and you should reconsider the priorities or your organizational structure. If you applied filtering on a single project and the flat line is somewhere in the middle of the project, then you should have a look what other project is prioritized in between and see if you could sequence them instead of mixing them.
How do I know which item to look at in the stories sheet?
Simply, you just hover the line and look at the tooltip popping up that will show you the related item. With this you can go back to the stories sheet and have a look at the items related to the one in the tooltip box.
Balance Report sheet
The balance report sheet is there to optimize your work related to managing all your different projects and requirements versus the capacity of the teams involved. For every iteration you keep track of the total amount of items and the sum of the estimates from the teams for the items in the “stories” sheet, or the to-do items. You also keep track of the total amount of items and the sum of the estimates from the teas for the items in the “done” sheet, or the items done. Comparing the trends from the items to-do and the items done you can see if you are either generating too much new items for the capacity available of not enough items.
What you should see is that the trend of the number of items and sum of the estimates is a flat line while having a steady growth on the done part. If the line is growing on the to-do part then you are generating too much new items for the capacity at hand and as such you are generating waste and decreasing your organizations ROI. If the trend is going down you might investigate how long you can maintain this until the teams are out of items to work on and plan your actions accordingly.
Current End Dates sheet
Impact analysis for all projects within 24 hours!
This is one of the most important sheets in the file as it shows you how many different projects or work packages you are dealing with in relation to the expected delivery dates. First of all you’ll see a pivot table with all ongoing and to-be-done project together with the sum of the estimates from the teams involved. Next columns are the calculated expected delivery dates for the teams for the project at hand. If 00/01/00 is mentioned it means that the team is not involved in the project or no estimates were registered for that project at all. The expected deadline is simply the maximum delivery date from the teams involved.
Result: At this moment we have a listing of all projects with estimated effort by the teams and the expected delivery date from those teams according to the priority set and dates calculated in the stories sheet. Announcing the exact date would not be very wise as teams work in iterations and as such the iteration end date is a better choice to use for outside announcements.
To get to the expected iteration end date we need the iteration schedule, which is registered (and maintained) in column K. When dates are passed you could remove them from the list and as such move dates up and complete… In other words you keep a rolling plan of iteration end dates in column K. It is this column that will be used to calculated the expected iteration end date for the projects at hand.
Result: Dates that we could announce according the situation as it is at the moment and as registered in the stories sheet. It is good practice to keep track of the dates that are announced to the outside! You do this simply by adding a empty sheet where you copy “values only” from the current end date sheet. (See: 20130508, 20130429…)
Once you have this “communicated” data at hand you can simply compare the current situation versus one of the previous communicated situations by changing the identification (sheet name) next to “compare with dates from tab named” which will immediately update the “compared iteration end” and “difference” columns accordingly. Next to that project (rows) with a postponed deadline versus the communicated one will be marked red, projects with a earlier deadline versus the communicated one will be marked green, projects that were not listed in the communicated one (new projects) will be marked blue.
Using this color scheme you can immediately check the impact of the work registered in the stories sheet versus the dates that were communicated externally before. What do you do when somebody asks you to take something extra on the plate? You add a record to the stories sheet, you get an estimate from the team and you check the impact versus the deadlines you communicated before. Best of all is that you can definitely get this done within 24 hours of the initial request and as such provide value to your impact analysis! If people need to wait for months to know about the impact of their request/decision they will not, if you can provide those insights within 24 hours you have something to work with!
Conclusion
The concepts used to build and use this file have helped so many organizations, teams and people that it is a must try for you as well. We are OK for everybody to use the file but kindly ask you to keep the reference to Co-Learning.be as originator in it. And most of all we are looking forward to your personal experience reports with this tool! Feel free to comment on this post, create your own article somewhere and reference it here or just contact us for more support or sharing ideas.