DIY – When will it be Done?
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