You’ve read about user stories, story points and “planning poker,” but how do you put it all together? How do you quickly create a software development estimate for your next project and provide a projected time frame to your higher-ups?
You may be overwhelmed, concerned about getting buy-in from your team, and feeling pinned down by the boss saying, “This seems pretty straightforward to me. Don’t you think you can get this done in six weeks?”
You’ve come to the right place. We’ve created a free Software Development Estimate Template, an Excel document that allows you to create user stories, record story point estimates, estimate your team’s velocity, and calculate time frames, all in one place. Combined, these estimation techniques ensure an accurate projection of costs.
In this post, we will walk you step by step through the process, explaining how to use this template to create an accurate estimation.
Download the free software development estimate template in Excel here to help you come up with your project’s estimate.
The 4-Step Process to Create Your Software Development Estimate
Throughout the rest of this post I’ll use a fictional example of creating a basic e-commerce web application. This will be an overly simple example to illustrate the process without getting tied up in a lot of detail.
This process, originally designed for estimating software development, is not limited to software projects. Although the example in this post is a fictional software product, you can use this process for any type of project.
Not familiar with user stories, story points, planning poker and velocity? See our blog post Why We Love Agile Estimation (And You Should, Too!) for a big-picture overview.
Step 1: Write User Stories
The first step is to capture the overall scope of the project at a high level. Focus first on the “minimum viable product” for this project. That is, the smallest number of features that can be built in order to make the software usable.
Open up the Excel template and go to the User Stories sheet.
Click on any screen shot to enlarge.
This sheet has four columns:
- User Story – The text of the user story, describing a type of user, the feature they want, and the benefit they get from the feature.
- Conditions of Acceptance – A checklist of things the feature needs to do in order for it to be acceptable to the user. Some people call these “Acceptance Criteria.”
- Story Points – A numerical estimate of relative size of the story. This column will be filled in later when the team does their estimating.
- Estimating Notes – Any notes about why the team came up with their estimate. This will also be filled in later.
Write each user story in the format: As a < type of user > I want < feature > so I < benefit >.
Here are the stories for our fictional e-commerce web application:
Next, in the Conditions of Acceptance column, write a brief checklist of what it means for the story to meet its requirements.
We like to use asterisks in this column to help separate each checklist item. As a nice side-benefit, when we import the user stories into JIRA Agile this text is put into the Description field and the asterisks are automatically converted into bullets.
Here is our complete user story list including conditions of acceptance:
These are written as if each started with, “I can…” This keeps the conditions consistently written, which makes them more compact and easier to read. It also keeps you thinking from the user’s perspective, which will help you capture all the things the user might need.
Your team will get involved in the next step to help fill in the conditions of acceptance with anything you might have missed or may be confusing.
Step 2: Assign Story Points Using Planning Poker
The next step is to get the team together to talk through the stories and play some planning poker. See my previous blog post Agile Software Estimation with Scrum Planning Poker for details about this process.
During the estimating process, the team will ask questions and look for clarification about the stories and conditions of acceptance. Be sure to add any details that come up so when they start to create each story they’ll remember everything.
As you and the team work through the process, there is one important rule: you–or whoever is on the hook for telling stakeholders how long this project is estimated to take–are not allowed to vote. Only the team–those who are responsible for doing the work to deliver the project–can have a say.
This avoids any risk of you skewing the results because the team wants to bend to your wishes when you think a story is a 5 and the rest of the team thinks it’s a 13.
Once the planning poker process is complete, your spreadsheet will look similar to this:
Notice the estimating notes, which helps the team remember some of the reasons they came up with their estimate.
Step 3: Create a Velocity Estimate
As I discussed in my blog post about why we love agile estimation, there are three ways to estimate the team’s velocity for this project:
- Run a sprint or two.
- Plan out a sprint to see how many stories will “fit.”
- Use the team’s velocity from a similar project.
For the purposes of this post, we’ll assume this team has not worked on a project that has the same combination of team, type of product, and technology. We’ll also assume running a sprint or two is not an option. For example, maybe we’ve been tasked with sizing a project to help the sales team write a proposal for a new client.
In this case we’ll do two things:
- Determine team capacity. We’ll figure out how many hours per week we expect each team member can dedicate to the project.
- Determine velocity. We’ll break a few stories down into discrete tasks to figure out how many story points “fit” into a two-week sprint.
Determine Team Capacity
Click the Estimated Team Capacity tab, and fill in the team members and how many hours per week you expect them to be available for this specific project.
You don’t want to fill in 40 hours per week for each team member here. Everyone has other things they need to do during the day that’s not related to the project, whether it’s e-mail, unrelated meetings, etc. Some team members also may be needed on multiple projects. Being aware of constraints that pull team members away is critical for an accurate estimation.
In our example, everyone is shared between this project and another one. Ideally, having team members–particularly the software engineers–focused on only one project is the best approach for your software development estimate.
Notice that we also include a “Primary Contribution” column. Although we expect a team to be cross-functional, with every team member pitching in to work on whatever is necessary, each team member will still have a primary specialty. I.e. a quality assurance engineer is probably not going to be writing production code.
You should balance the team appropriately for the type of project. For example if this project is coding-heavy, you’ll want to make sure you have enough software engineering bandwidth.
Subtract Ceremony Time
The team will spend some of its time in meetings–“ceremonies” in Scrum terminology–and this time needs to be subtracted from the hours they can spend working on project tasks.
This time is extremely important; it’s what allows the team to move quickly and keep their throughput high, but you don’t want to assume they have that time available to be working on project tasks.
Adjust the number of weeks in your sprints if they are not 2 weeks long, and the duration of your various Scrum ceremonies. The Excel template will perform some calculations based on how many team members you have, to account for everyone’s time in the Hours/Sprint column.
Here’s an example:
The Net Capacity Per Sprint is the total hours the team can spend on tasks over the course of our 2 week sprint.
Now you’ll pick a few user stories and break them down into tasks. Each task will have an hour estimate. Once you fill up the total time available, you’ll add up the story points for the stories you were able to fit in, and use that as your velocity.
Click the User Story Breakdown sheet in the Excel workbook and work with your team to break down each story into a series of tasks, each with an hour estimate. We recommend that no individual task has an hour estimate larger than 4 hours. This helps the team really think through everything that’s required.
Be sure to use a mix of small and large stories in order to get a representative cross-section for this exercise.
Keep breaking down stories until you can’t do all the work necessary for another story with the number of hours you have left.
Here is our example:
We estimated three stories and have 9.25 hours left over. We don’t feel comfortable that we can complete another story so we came up with an estimated velocity of 21 story points per sprint.
Step 4: Calculate Projected Duration
The final step is to combine the total story points and estimated velocity to come up with a projected number of sprints. From that we can get a projected time frame.
Go to the Projected Duration sheet to see the results of your work. We’ve built all of the calculations, so you’ll see that all of the information is filled in for you.
The low and high multiplier values come from the table below. We use 0.6 and 1.6 because we haven’t executed any sprints yet. If we had run a couple of sprints, we could have used a more narrow range.
We adopted these from Mike Cohn’s book, “Agile Estimating and Planning:”
Adjust the start date on the sheet as desired to see projected completion dates.
Don’t forget to think about any other time you might need, for example to ramp up the team, to set up the framework of the new application, and to conduct one or more release sprints. Add the time required for these into your overall software development estimate.
Updating Your Software Development Estimate
This template can be used as you’re working on the project to update your projections as you learn more.
There are two primary adjustments you’ll make to your software development estimate as you start working on the project:
- Using the actual team velocity. Use the average story points completed by the team in the last three sprints to get a more accurate idea of the actual pace of progress on the project. Make sure you only count points from user stories that are completely “done” and shippable.
- Narrowing the cone of uncertainty. As you complete sprints, adjust the high and low multipliers based on the number of sprints you’ve completed, using the table above. This will narrow the range of your forecast.
After the conclusion of each sprint, just do the following:
- Zero out the story points of completed stories to adjust the total to what’s left to complete.
- Adjust the velocity to the average from the last three completed sprints.
- Adjust the high and low multipliers based on the number of sprints completed.
- Adjust the start date to the start of the next sprint.
This gives you the information you need when the boss comes along and says, “So…when will it be done?”
Using our free template you can quickly complete a scoping and estimating process to come up with a range of completion time frames. This provides high level decision-makers with the information they need.
Sometimes you’re going to get push-back, particularly if your projected time frame doesn’t meet the hopes and dreams of your stakeholders. The good news is this technique provides them the information they need to make strategic decisions. In the end, your team will make more accurate software development estimates, which is the goal of the exercise.
For example, they can think about the following questions:
- Is every story required for version 1.0?
- Can stories be simplified?
- Can the size of the team be increased?
- Can the team start sooner?
- Can plans be adjusted around the estimated time frame?
- Can Bill be pulled off that other project so he can focus 100% of his time on this one?
What did I miss? Contact us if you have questions or want to learn more about our Scrum team services.
Editor’s Note: This post was originally published in September, 2015 and has been updated for accuracy and comprehensiveness.