Monte Carlo simulation could help to “estimate” the probable range of a sprint.

In order to demonstrate this, I have prepared a small Excel sheet which could be downloaded and I will explain how it works.

The idea is to use a set of story points and simulate several scenarios in order to have a kind of tendency.

The column Sprint [A] provides a history of 20 sprints.

The columns available story point comes from the initial estimations.

Column [C] contains the actual Story points completed over the sprints.

I will not describe now the columns [D,E,F,G], I will discuss this in another subject (maybe).

What is important here is to define a mean to resample a set of these sprints, I have done that over the column [J] & [K].

The formula I have used is

[J2]<=====>[K2]

=J2+1<==>=INDEX(B$2:B$21,RANDBETWEEN(1,ROWS(B$2:B$21)),1)

The K column provide a means to provide a random value from the column [B] which contains “effective” story point.

Each time you press F9 you will have another set of values.

So it’s a bit like having twenty balls in a basket and you pick them off and you are able to repeat this several times.

Columns [N] and [O] are a representation of these Sprints repeated several times, in this case 201.

So [N] is the Sprint number and the column [O] formula is SUM($K$2:$K$21) which is the number of Story points after 20 Sprints.

A quick way to set this data over is to use the condition What If (not the TV series 😉 but the Excel function).

Now, you can press F9 to obtain another set of data, and let’s say that you are willing to check the probability that the team could achieve 530 story points over 20 Sprints.

This what you might be looking for.