Forecast Agile Project or Feature Spreadsheet

Posted by in Featured, Reference, Tools |

This spreadsheet helps forecast how long a single feature or single project will take to deliver using agile (scrum or kanban) using Monte Carlo simulation. We have to balance keeping the spreadsheet light to use and easy to understand whilst capturing the most likely factors that cause feature or project blowout.

We have made our Single Feature Forecaster spreadsheet available for free for over six months, and get a lot of feedback and questions about how it works and how to use it. This post tries to answer how to use it. Future post will cover how it works in detail.

Get the spreadsheet here: Single Feature Forecaster Spreadsheet (latest version) 

(uses no macros – all formulas, requires Microsoft Excel or Google sheets (but it is slow))

Single Feature Agile Forecasting Inputs

Single feature forecaster inputs

Forecast a single feature spreadsheet inputs

1. Start date – seems relatively straightforward, but don’t be fooled. Poor choice of start date is a major factor in poor forecast accuracy. Often the planned start date is missed, staff aren’t moved from prior projects, or a final decision to begin doesn’t arrive in time. Be pragmatic. Too optimistic or pessimistic is will cause errors. Consider other projects that may run long, these might impact on the start date for this feature or project.

2. How Many Stories – this is the base amount of work to be performed for this feature or project. Its is a range estimate, and our advice is to keep it wide. Monte Carlo works best when it is more certain that the actual final range is within the estimate, so making this range narrow increases the risk of a miss. Include any item count that is included in your throughput samples of estimates. If your throughput includes defects, then estimate defects. Our advice is to NOT include defects, to just include work contributing to the features. Also, don’t include risks, these are best managed separately (in the Risk tab).

3. Story Split Rate – this range estimate wasn’t in our first release. What we encountered is the mistake (that all of our competitors make) when historical throughput data is used to forecast. Items in the backlog are often split before being delivered. If the throughput count is post-split (items are split into multiple parts or grow in scope), the forecast will be out by factors of up the three times (if stories split on average into three parts, which is common in our experience). This is a significant miss, so we added it to the spreadsheet. Our advice is to split low estimate 1, and high estimate 3 until you have more accurate data.

4. Throughput – The rate that work will be delivered. The only requirement is that it is in the same units as the story estimate, whether that be story points or count. There are two ways to give this estimate. Use a low and high range guess if no historical data is available, or historical data if it is available. If a range estimate is used, keep it wide. You are estimating the rate that the story estimate given in input 2 and 3. If that includes defects, then this estimate should as well. We find it easier to NOT include defects in either the throughput or story estimate. Start wide pay particular attention to the low estimate and be realistic and use the lowest you have seen (don’t be afraid of 0 or 1 until you get more information). Don’t be to pessimistic on the high bound. Go one higher than you expect or have seen. Remember to set whether your estimate is per week or two weeks. We like to keep data per-week. If you choose to use historical data, enter your samples in the Throughput Samples tab and choose “Data” from the drop-down list.

The Results Table

Single Feature Forecaster Results

Forecast Single Feature Results Table

Forecast results are calculated continuously. We have tried to suggest that 85th percentile and above is safe territory. What we have found traditional estimation methods relay on averages. Although not exact, the 50th percentile often matches what is expected and although (much) more realistic, the 85th percentile is a shock. Be ready to defend the process used. To help, we have added some charts that pictorially help explain how it works.

Forecast Result Charts

Simulation Run Lightning Chart

Forecast Single Feature Burndown Lightning Chart (for 50 of 1000 simulations)

The lightning burndown chart shows the first fifty simulated project burn-downs. When explaining the process used to others, explain that this spreadsheet hypothetically complete the project 1,000 times given the estimates you provide. 85th percentile will be the date that 850 of the simulations completed on or before. Go onto explain that traditional methods would give a date closer to where only 500 of these simulated runs would have completed.

Single Feature Forecaster Histogram

Forecast Single Feature Result Histogram

The other chart displays the histogram of results, up to 20 segments maximum. This is similar to the results table, but we find people just want a date and find this histogram too technical to interpret. When multiple risks are entered into the risk tab, this histogram can get very complex. We will go into much deeper detail in a future post on how to capture project risk factors.


We use this same spreadsheet when doing complex analysis. For us its a great first step to getting to a reliable and defensible forecast outcome and allows us to play with the scope and risk factors to find a close solution. We have more complex tools, but sometimes this answers the main question asked – is it possible.

Feel free to send us ideas on improving it.

Again, Get the spreadsheet here: Single Feature Forecaster Spreadsheet (latest version)

Read More

Single Feature Forecast Spreadsheet

Posted by in Featured, Reference, Tools |

We often build tools that help forecast or teach the concepts behind our statistical methods. Turning our internal tools into public property takes some time and tuning. The spreadsheet performs a Monte Carlo simulation to generate a delivery date forecast for a single feature. There are no macros, everything in this spreadsheet is based on formulas.

Features –

  • Given a start date, story count range estimate and throughput/velocity it gives date and probability forecasts
  • Throughput can be story count based or velocity based
  • If historical throughput/velocity data is available, it can be used instead of a simple range estimate
  • If risks are known, they can be used to forecast their impact – totally optional
  • Charts so you can see the analysis visually – just for teaching
  • NO MACROS – No security issues to have to worry about. We only do what Excel does in formulas and don’t share or send any data externally.

Get it here –

Throughput Forecaster.xlsx

(See all of our free tools here:

How it works –

Given the feature count estimate range, 500 hypothetical completions of this feature are simulated. These simulated trials are used to compute how likely a given delivery date is against others. This analysis correctly computes how the uncertainty in feature size performs against the uncertainty of delivery rate (historically or estimated).

We will be posting hands-on labs about its application in the future. For now, we just ant to make our internal tools available for use by the industry so that we can improve on the simplistic and flawed current methods of forecasting that plague the software development industry.


Read More

Free Tools and Resources

Posted by in Featured, Reference, Tools |

We often build custom tools and spreadsheets during our consulting work. We offer these to the community for free under a Creative Commons Attribution Non-Commercial License. Please help us keep these resources free and updated by abiding by the conditions of this license.

\Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

These tools are kept under version control on GitHub here –


Read More

Liquidity and Pull Transactions

Posted by in Announcements, Featured, Reference, Tools |

David Anderson has been presenting his thoughts on using the number of card pulls (Pull Transactions) as a metric for measuring the health and liquidity of a Kanban process. He starts the conversation here –

Our software has always charted the number of empty positions and queued positions over time which is a close proxy to this measurement and found the sum of Empty and Queued each time period a reliable indicator for forecasting where the stress points are in the Kanban board process. We decided that this liquidity chart might add an easier dimension for people to understand the impact of changes when they are experimenting with our simulation software. So we added the chart.









We are still refining the way it looks and going to add trend lines to make it easier to discern the running average, and we are also building a set of examples that show how it adds value. Remember -this is the ONLY chart where higher is BETTER. All of our other measure are lower is better – causing us some stress internally!

This is in the latest version (v1.3.1)  you can download now.


Read More

KanbanSim and ScrumSim v1.1

Posted by in Announcements, Tools |

We have just made our latest simulation tool available on our download page.

This edition is a major upgrade from version 1.0, some of the new features and enhancements are –
  • Performance is improved by over 10 times for forecasting
  • Scrum and Kanban models co-exist in the one application (our intention going forward)
  • 35+ example files built-in to kickstart learning
  • New data reverse engineering tools (fitting data from samples)
A full list of enhancements are documented in our knowledge base.
Read More

New Version (1.0.44) Download

Posted by in Announcements, Tools |

The next beta version of the simulation engine has been uploaded to the Downloads page.
** If you are running the previous beta version downloaded December 2011, please un-install and download and install this version. **

This version contains many new features and bug fixes that were reported by our early adopters. A full description of the new features can be found in the release notes, but to summarize:

Multiple project phase support – you can specify any number of project phases and apply fixed multiplier adjustments to estimates and occurrence estimates (e.g. make estimates during the ramp-up phase take a little bit longer), and have events target specific phases (e.g. have more defects reported later in the project).

  • New command line application – allows you to automate and integrate the simulation engine with other tools and combine the results with other analysis tools.
  • Customizable HTML Report Templates – you can now edit and customize all of the HTML reports using a templating language. There was a lot of feedback that you wanted these reports to be completely customizable, so we have opened up complete control.
  • Scrum HTML Reports now included – these missed the previous beta, all reports are now available for all simulation commands for both Agile/Scrum and Lean/Kanban.
  • Scrum Simulation examples – these missed the previous beta. There are now Scrum examples for every simulation command installed into the Examples folder.
  • Custom and Built-in Distributions – there are now over 20 built-in random number distributions, and two powerful custom distribution types that allow complete control over how random numbers are generated. This is an advanced topic that will be documented and explained in future posts.
  • ALPHA: Batch transfer rates for starting and completing work: To model work being started as a batch every so often, and work only being moved forward in a batch every so often, each Kanban column can specify a replenishInterval and a completeInterval. Cards will only be started after the number of simulation steps specified in the replenishInterval for a column, and will only be allowed to move to the next column or backlog after the number of simulation steps specified in the completeInterval for each column has passed. The counter resets after each trigger.

Examples and more information can be found in this versions release notes.

Read More