With Chris Bexon – Agile Coach and Trainer at B-Agile. Chris coaches people, agile teams, and organisations to achieve true agility. Chris is passionate about providing free neurodiversity coaching to professionals on the spectrum.

In this blog, we will look at how to use Throughput data to answer those “When will it be Done?” questions – DIY style.

Using probability for forecasting can help us raise transparency over the Product Goal and Product Strategy. More transparency can move us away from deadlines and commitments towards collaborative product management with agile teams and stakeholders. This further enhances our ability for strategic planning and adapting to risk

These flow metrics can be used in any iterative and incremental or flow processes

By the end of the blog, you will be able to measure Throughput and forecast When and How Much using Monte Carlo simulations in Excel

## Table of Contents

## Let’s start by reviewing some basic concepts

The image below shows a simple workflow We have work items in a Ready queue state waiting on the left and a Done queue state on the right. To get the work items from left to right requires some activities to be completed. In this example some build and test activities.

You’ve probably seen many boards either physically or digitally that resemble this. You’ll see that there’s lots of work in the Build and Test activities and nothing much being completed. I.e. the Done queue state is empty. The total amount of work in Build and Test is referred to as Work In Progress or WIP and is defined as the number of work items started but not finished.

There’s nothing in the workflow to limit new work being started and encourage work to be completed. This type of workflow is referred to as a push system.

In order to use Flow Metrics for forecasting we need to encourage valuable work items to be completed before starting new work to create a flow of value. Otherwise, our customers might be waiting a long time and they cannot give us feedback!

## Work In Progress Limits Applied

We have now limited parallel Work In Progress or the total amount of work items in the workflow to 5. As new work cannot enter the workflow, the team now has to complete the highest-value work items on the right-hand side first to create a flow of value. There may be other ways of controlling WIP but in this example we are going to use limits to keep it simple. Other considerations around the batch size and uncertainty may also be considered.

Scrum itself is a pull system and is Work In Progress limited by default in various ways. We focus on a single Product Goal to guide the valuable future state of the product and focus on a single Sprint Goal, Each Sprint results in a useful valuable increment. We can enhance Scrum further by optimising the flow of value in a Sprint by understanding the Workflow and controlling WIP. Kanban systems are designed to control Work In Progress from the ground up through the ongoing process of creating the definition of workflow.

By controlling WIP we can avoid work items piling up in any part of the workflow, ensure work items are moving and not aging unnecessarily and unblocking work items to keep them moving

For more information check here as a starting point https://kanbanguides.org/wp-content/uploads/2021/01/Kanban-Guide-2020-12.pdf or the https://www.scrum.org/resources/kanban-guide-scrum-teams

There are four primary flow metrics Throughput, Cycle Time, WIP, and Age We will focus on Throughput in this blog.

## Flow Metric: Throughput

The number of work items finished per unit of time. So Throughput is the exact count of work items per period of time

We are going to need to record some basic data for each work item. For this example, we are going to record the number of work items moving into the Done queue state each day. All we need to record is the date this happened.

Done Count of Work Items

22/06/2023 1

23/06/2023 0

24/06/2023 1

25/06/2023 2

## Let’s Walk Through How We Use The Data

Now that we have established the foundational concepts, let’s explore how we can effectively leverage the data. Join us for a short video explanation.

The data below shows throughput (in our case, count of work items per day) for 3 agile teams. The spreadsheet contains around three months of data. You can see from the data that there is a spread of values. Some days have zero work items and some days have more. Some work item counts will be more common than others.

The same data is represented on a scatterplot when we can see the variance in the range of values

The distribution of probability can be shown on a histogram. The histogram places a range of occurrences in a bucket. You can see here that the highest probability in in the range 0 – 5.4 work items per day

The Normal distribution is used **to analyse data when there is an equally likely chance of being above or below the mean for continuous data whose histogram fits a bell curve**.

From the data, we calculate:

The **Mean **is **2.838384**. The mean of all values in the set of data

The** Standard Deviation** is **3.492292**. How dispersed the data is in relation to the mean. You can use the STDDEV.P function to calculate this value. Alternatively, we can calculate – see spreadsheet

If we use the Excel function for Normal Distribution. Norm.Dist we calculate the probability of occurrence. The plot below shows the curve

Now the data has lots of gaps and missing values. The data is obviously skewed as we are not going to have negative numbers. So it may be difficult to use this data for forecasting. **An advantage of Monte Carlo forecasting is that you obtain a complete distribution for future events, not just a point estimate and standard error like above**

To run a Monte Carlo simulation use the NORM.INV in Excel. Provide it with a random probability between 0 and 1, the mean, and standard variation. In the spreadsheet, we ran the simulation 10000 times

NORM.INV(RAND(), Mean, Standard Variation)

This resulted in the following PDF. A normal distribution will produce equal results on either side of the Mean. We are not interested in values less than zero so will ignore these.

Different distribution curves may be a better fit. For the blog, I’m simulating the functionality of existing commercial products.

## Forecast: Estimated Completion Dates

### When will it be done?

**All you need up to this point is the Mean and SD**

*Let’s* say a stakeholder is interested when we will likely solve the Assess Image Curvature idea. There are* roughly 100 work items of value in the queue before it.*

So when will we get the next 100 work items?

Mean work items per day **2.838384 **

Standard Deviation **3.492292 **

Calculate 100 / Mean gives **35.23132 **days as the 50th centile

Scaling the Standard Deviation we get **43.34792**

Now plugging these numbers back into a Monte Carlo simulation 10000 times

If we query the data using the Excel Percentile function we can then start to answer forecasting questions.

Let’s say the current date is the 11-Apr-2023

50th centile **35.37035** Days** 50%** chance 100 work items completed by **16-May-23 **

85th centile **79.86692** Days **85%** chance 100 work items completed by **29-Jun-23 **

95th centile **106.7747** Days **95%** chance 100 work items completed by **26-Jul-23**

**Warning** – common commercial tools on the market use a standard deviation of 1. This means they may be more ambitious than the PDF. Using a different distribution model like a Gamma or Log may be more appropriate as there’s limited downside to zero work items but unlimited upside

### How Many Items By A Given Date?

Mean **2.838384 **

Standard Deviation **3.492292 **

**45 **Days Between 01/Jul/2023 and 15/Aug/2023

Mean * 45 = **127.7273 **

Now plugging these numbers back into a Monte Carlo simulation 10000 times

Querying the data with the Percentile function we can forecast:

**95th** centile **52** work items completed by 15-Aug-23

**75th** centile **97** work items completed by 15-Aug-23

**50th** centile **127** work items completed by 15-Aug-23

**Using probability to forecast when can change the narrative away from commitments and milestones to a collaborative conversation about how to maximise the value of our work.**

To learn more about Agility, Scrum, Kanban, Agile Coaching, and Facilitation please check out courses with me, Chris Bexon. Check out our courses at www.bagile.co.uk