This is mint - would love to execute this in my role as Scrum Master - but need to really tackle this subject and take a deep dive to understand the statistics
Thanks 🙏 I appreciate that:-) I’m releasing another Monte Carlo vid this week that shows you how to forecast how many stories you can complete within a sprint with the same 85% probability of it happening - click the bell icon so you get notified when it lands. I’d also recommend checking out my latest video on what makes a great scrum master ruclips.net/video/o5V8-s7_M-Q/видео.html as you’ll need to do all of these things to ensure your forecasts are accurate, thanks for watching.
@@TheAgileLeanGardener Legend mate will check it out thank you for providing such an immense service for free! I'll put you up there with Vicanti as a thought leader in the space.
Thanks for the reply Steve. We do start counting cycle time when the tickets gets moved to In Progress. Our cycle times are high, but because we are completing a fairly high percentage of the committed tickets during the 2 week sprints the dev managers are not willing to take the extra time to break the tickets into smaller pieces.
Hmm I see. But the figures don’t seem to tally to me, something isn’t quite right. Are you sure the devs are moving their tickets into done on the right day? If they don’t, even a day or 2 late will skew the forecast and cycle time.
@@TheAgileLeanGardener we will look to hone that some more, but regardless, MCS is still showing only 6-7 tickets per sprint and I know we are hitting 12-17 per sprint . I know having a large number of tickets completing the last day of the sprint hurts the predictability; however, when there is a seven month pattern happening you would think it would take that pattern into account.
Yep I get it but these figures don’t tally to me, I still think something is wrong. Monte Carlo is simply a calculation based on the data input, there is no intelligence at work. Have you tried sampling a shorter more recent data sample, for example the last 8 sprints (and check the throughput for each of those 8 sprints)? Have they been any changes of significance to the team in the last 6 months?
@@TheAgileLeanGardener now I am really worried. I ran the same data through your Excel template and it shows 15 tickets at the 85th percentile. I am using ActionableAgile and it shows 6 days with the exact same data set.
Hmm the plot thickens….. Well 15 sounds more like it based on your figures.. But I’m not sure why AA is saying 6…. When I’ve compared my Excel to AA it’s always the same +-1 sometimes 2
Hey Steve, another great video! What drew me to monte carlo simulation + your other cycle time videos was that I have recently been in that same situation with leadership you touched on. It's refreshing to know it's not just me that has gone through it!
Thanks macca I appreciate that, yes you’re not alone, Monte Carlo Simulation is by far the best way to turn it around in my experience, spread the word :-)
@@TheAgileLeanGardener I definitely will. Was hoping to clarify one thing, how do you forecast time to complete for 100 or 1000 stories? I noticed in your example you just used 10, would it just be expanding the time period to get 100 or 1000 stories and running the same thing through?
@@macca2272 hey, yes sure. Go to 5m21s in the vid, and change the formula in column D to say 100-C3 or 1000-C3 the only thing to bear in mind is that you will need a larger sample set than just the 2 months I have in the example or completing stories every day or every other day otherwise you’ll get a lot of errors - this is why it’s important to get your 85th percentile cycle time well under 10 days, let me know how it goes..
Hi Steve, Real liking your clear and succinct posts! Since the techniques you’ve shared here rely on building a model from historical data… I want to understand what you’d do if you’re starting with a brand new team, and also what if you have to put together a new team for every new project (not exactly the long lived team idea for Agile!) … but if that’s the reality, how can I adapt these methods?
Hi Jarrod, I really appreciate that thank you 🙏 Ok so if you don’t have any historical data then at the outset of a project you obviously won’t be able to use Cycle Time or Monte Carlo. But as long as you collect that data (cycle time) as you start you will be able to use it after a few weeks. It won’t be accurate since you have limited data. But what you will do is constantly run Monte Carlo each week (as you build your data set) it will get more accurate as you build your data. Even with limited data you will still be able to forecast how long it will take to deliver x number of stories but just be mindful that at the start it won’t be very accurate since it’s a new team working with each other. You can’t compare one team to another so you’ll have to use this approach every time you start a new team. As you mention the approach you’ve described is not agile, you’re bringing people to the work instead of bringing the work to the people, this is a waterfall approach. But Cycle Time and Monte Carlo are agnostic of frameworks and methodologies so it will still work but with caveats e.g. no historical data so the forecast is built over time, new teams each time so you can’t use the forecast for the previous team for the new team, the forecast will change a lot at the start because the team is new and just forming so it will need to be rerun regularly. Hope that helps.
Hey, you mentioned at 6:51 that we can save ourselves from the hassle of assigning story points and going through endless estimation sessions. You suggested that all we need to do is make sure our stories are right-sized so that they can be completed within eight days. So far, so good. But it's also an estimation, but this time it's based on (cycle)time rather than story points. This could also take up hours of valuable time to right-size stories. Have I misunderstood something?
Hi and thanks for the question. So firstly using this technique does not mean we don’t discuss the work break it down, identify dependencies and constraints. We still of course need to do this. Cycle time is not an estimate it’s something that ‘has’ happened compared to a story point which is an educated guess. If your 85th percentile to complete a story (based on your historical data set) tells you it takes 5 days then the idea is to right size all future stories not to exceed 5 days based on your experience with all of the stories you’ve done (not to ‘be’ 5 days just not to exceed it). This part, the right sizing, you could argue is similar to assigning a story point except that you’re basing it on data, things that have actually happened. Most teams I’ve worked with when using story pointing assign a story, for example, 5 points, but it never then gets re-examined. Was it really 5 points? Using cycle time is a quick and easy way to truly identify how long a story takes to get done. There are caveats to this of course which I’ve tried to point out in the video. Hope that helps.
Steve, I love your videos and I am working to move all of my Scrum Teams over to the way of Flow Metrics, but I am having an issue with explaining why the MonteCarlo simulation only shows one of my team with an 85% chance of completing 6 tickets or more during a sprint, when they typically complete between 12-17 tickets per sprint. As you can possibly imagine, their Throughput is fairly consistent; however, they are delivering the large majority of their tickets on the final day of the sprint. Even after 7 months of data, the MonteCarlo simulation has not seen this pattern and adjusted accordingly. Any thoughts on how I can overcome this hurdle in making the move? btw, the team's cycle time is 14 days (85th percentile).
Hi and thanks 🙏👍 are you doing 2 week sprints? If so your 85th percentile cycle time of 14 days is too high - but this ‘could’ be due to other factors than it actually taking that long. For example, are the team really updating tickets when the item is done on the right day? If not this will make a difference both to your cycle time and the Monte Carlo forecast. Also when does your cycle time start? It needs to start only when you move a ticket in sprint from ‘to do’ to ‘in progress’ (or equivalent). If your cycle time starts as soon as you put something into sprint regardless of whether you’ve actually started work on it then this will also skew both cycle time and Monte Carlo. I would look into this because if Monte Carlo is saying you can only get 4 or 5 items done to the 85th percentile and your 85th percentile cycle time is saying 14 days but you always seem to get a throughput in sprint of 7 or 8 then something is definitely not right.
it's not a new idea, in kanban this is pretty much already done like this. Estimation is a waste of time, just break the work down while discussing it, eventually team speed can be calculated and you have fairly accurate estimates from those
@@TheAgileLeanGardener Thanks for the quick response. I find the challenge with this method to be "how many stories" is the feature. Do you have any suggestions on best practice for that? I have my own techniques i won't mention because i'm looking to expand my toolbox.
Yes true. You’re never going to know exactly how many stories a piece of work is going to take and of course you would never try and get all the stories ready before you start - that would be waterfall! But I always pick from previous work a small, medium and large piece of work that people are familiar with and work out how many stories each took. Then you can say a small piece of work is 50 stories and that takes 1 month, a medium piece of work is 150 stories and that takes 2.5 months and a large piece of work is 300 stories and that takes 5.5 months with an 85% chance of completing on time. To set expectations before you start a new piece of work the team decide which category of size it is compared to the 3 baselines (small, medium or large). Then once you’ve started each week I rerun Monte Carlo against the remaining backlog items to update leadership on the completion date bearing in mind that we said at the start this would be a medium piece of work taking 2.5 months. I usually communicate this at the sprint review. Hope that helps.
@@TheAgileLeanGardener Looking at comps works for sure - however this becomes really hard when you're building a new product from scratch (which I have done for most of my career). I found in this case it DOES pay to try to discover the stories using user story maps, or at least, making bullet points of each potential story/feature. Or at least sets of major sub-features which can then be t-shirt sized and roll up into an overall estimate. Breaking down work into stories, assuming the stories are user facing features and not technical tasks, can be helpful for alignment purposes anyway.
Hi, yes absolutely agree with you on story mapping, it’s a great way to understand and discuss the work. We definitely need to do this type of discovery at the start but we just need to realise that the point is to give us a great start and not to capture all requirements up front as that would kill innovation as we work 👍
Hi Steve - in the excel I'm not getting a date in F1 as you do. Here are the formula's for the columns. Column A(End Date) =+A3+1 Column B(Total No of Completed) No Formula Column C(Random No From Done) =INDEX($B$3:$B$63,RANDBETWEEN(1,61)) Column D(Items left to complete) =10-C3 F1(Date) =INDEX($A$3:$A$63,MATCH(TRUE,$D$3:$D$63
There is no hard or fast rule - 85% because it provides a high degree of certainty that it will actually happen. You could use 95% but you’ll find your forecasts are quite a lot larger. But you don’t have to use a single % you could provide 3 65% 75% 85% it all depends what your stakeholders are happy with, hope that helps.
Hello again Steve. Are there any negatives of using the sprint throughput (# of tickets completed in a given sprint) instead of the daily throughput for your MCS? I have 16 sprints worth of data and when I run the daily throughput MCS I get 6 tickets at the 85%. When I use the sprint level throughput I get 14 tickets at the 85%. Thoughts on this approach?
Hi, I’m not quite sure what you mean. To use MCS all you need for your 16 sprints is the throughput for your completed stories. So, on what date was each story completed. You then plot this on your date range in the Excel. Let’s say the first date that you had a completed story was 1st March 2024 and the last date you had a completed story was 31st July 2024. You then plot that date range in the Excel (every date in this range must be plotted whether you completed a story on that date or not) you then simply put how many stories where completed on a date, it could be 2, 5, 10 or it could be zero. Once you’ve done this you can change the date range to start in the future if you wish so long as every date in this new range is plotted. Just don’t change the column that shows how many stories were done. Are you doing this? Or have I misunderstood your question?
@@TheAgileLeanGardener I modified your template and instead of have each day listed in column A of Sheet1, I have the start date of each of my next 16 sprints listed in column A. I then put the total count of tickets from each of my 16 sprints in column B. I then changed the value in F6 (Date to Complete By) to coincide with the date value in the first Date Range column (A3).
…further thought. Usually the more data you have the better the forecast will be. The less data you have the worse the forecast will be. But the data you use must model what is currently happening. Significant changes in the team or major events (everyone is on holiday or has covid) will affect the forecast. MCS models past performance and so will forecast accordingly. So you need to use data that reflects current reality.
The 2 templates I have work but if you change any logic they will not work, you’ve changed 2 very important things in order for MCS to work correctly. I would recommend using them as they are and see what result you get and if that closer aligns to what you’re seeing on the ground.
Hi and thank you, I really appreciate that. I’d give it a couple of sprints (4 weeks or so) and then run it. But the trick is to keep running it every week thereafter so you can see the deltas and take action, please let me know how you get on and if I can help I will.
Hi Steve, I have been watching your videos re cycle time and monte carlo forecasting and I am really intrigued however I cannot find a way to get cycle time in Jira that means anything useful to me. I am looking at the control chart and am not 100% sure how to set the parameters to give me the information and then of course use that to get the 85th percentile. Have I missed a video showing this or is there a nugget of wisdom you could share with me as I feel a little stuck at the first hurdle 🙈
Hi and thanks for watching the channel 😊 Ok, you can’t natively get the underlying data out of Jira, you’ll need to use an Atlassian Marketplace plugin such as Time in Status or EazyBi, I have a video on this here, hope that helps ruclips.net/video/5Szv-w6n5NE/видео.html
Hi Steve -- quick question: I think you're bang-on about how ineffective SP's are for forecasting, but when you remove SPs there seems to be a bit of a gap left over when it comes to a few things that have been important to me so far: 1. Calculating "Bang for you Buck" (or ROI) based from effort/value 2. Agreeing on what to add to the Sprint Backlog during Sprint Planning befitting the team's capacity. Any suggestions, on those points? Am I overvaluing those? To me, the Monty Carlo simulation works good for mature teams in the long term when there is consistent output and sufficient historical data. but if a given 2-week sprint only contains 5-12 stories, that seems to allow for a lot of inter-sprint variance, correct? Any insight would help. "Minimal Viable Estimation" is definitely a major plank of mine for an agile transformation I'm currently steering. Any advice for how this method does (or does not) applies to the early days of an agile transformation in the face of poorly maintained historical data, would be majorly appreciated.
Hi and thanks for your interesting and great comments. Firstly I’d say that using Cycle Time and Monte Carlo Simulation is, in my opinion, the answer to understanding how much you can do and by when for a given team or teams, whether that’s in a sprint or over 20 sprints. But, and it is a big but, NOT if it’s used in isolation of excellent ways of working. I did a video on what a Scrum Master should be doing and at a high level I broke it into 2 sections, quantitative and qualitative. Using Cycle Time, Monte Carlo Simulation, understanding your Flow Efficiency and using Ageing Charts all come under the quantitative section. Using these techniques can show you the areas that need focus and improving. This leads us to the qualitative section, which is arguably more important. So true Servant Leadership, understanding what a good requirement is whether we call that a story or a task or whatever (remember user stories have never been mentioned in the Scrum Guide or the Agile Manifesto!) close collaboration with the PO and working together to create and prioritise the backlog, reducing context switching, limiting work in progress, removing handoffs (queues), keeping the size of work small, refining and refactoring - and the list goes on :-) But to your point of ‘bang for buck’ and what should you take into sprint then I would say the 2 most important things are your product goal and your sprint goal which need to be informed by feedback from your client or customer. Without these then we could easily end up having a very high performing team delivering consistent but essentially useless products or services. The measure of success for each sprint is the sprint goal (and for that you must have a product goal). So, don’t focus on completing every ‘story’ taken into sprint. Focus on achieving the sprint goal feeding the product goal. We need to be measured on outcomes not outputs. I hope that’s of some help, a bit wordy I know :-)
@@TheAgileLeanGardener Incredibly helpful -- thanks. I still think there's a piece missing that I suspect others like me will feel is left dangling from this approach (for context, I've watched all your videos and have developed a sense--I hope--of your agile 'design imperatives', if that makes sense). That dangling piece is: how to have a scrum team feel reasonably confident that their sprint backlog is 'achievable'. Even we do steer the team to focus on the goal more than the output, those two things are mutually informative: a sense of a team's capacity will inform the sprint goal, and visa-versa. I agree that SP is a time suck, easily manipulated (Goodhart's Law), given to group power dynamics, and so on. I have been playing around with returning to the old XP notion of "Ideal Days". There's something beautifully simple about it -- and the thing that I LOVE about it is that it's a constant reminder that a shared goal of an agile Way of Working is making structural changes together as a team, to keep striding for as "Ideal" of a day as possible (by reducing unplanned work, needless meetings, peer learning). And, as you make those improvements together, that "Ideal" days starts to become realized...and they feel it. I think it's a very powerful emotional "in" for workers to see the connection between core agile values (like the real mushy ones :) ) and agile process improvement... Ha....I think I one-up'ed you for "being a bit wordy".
@@shaye5095 Hehe wow yes I think you out did me on the reply ;-) Many thanks for your thoughts and comments it’s great that these discussions are happening. And thank you for watching the channel. I try to release a video each week but work has been so hectic recently it’s slowing me down so I’ve been doing a few shorts until I can get back to long form content. With regard to the teams confidence about the sprint backlog - are they in control of how much they take into sprint? If not give them that control and allow them to decide. Then use the retro to inspect what happened and why and repeat. The retro is extremely important as it may well not be how much work they take that’s causing the lack of confidence but other reasons such as lack of clarity so deeper refinement may be needed. I’d explore what is causing the lack of confidence and fix that first. Let me know how it goes.
You need to refresh the pivot table in sheet 3 also don’t forget to delete all the dates in sheet 2 (apart from the header) before you run the macro, all the instructions are in the video so you may have to re watch it cheers Steve
I might have missed it, but where does the use of cycle time actually come into here? Seems your randomly selecting based on count and then repeating and getting the distribution, but nothing actually using that cycle time from first part of the video? Did I miss something ?
Hi, cycle time gives you your throughput. For example the number of stories completed on each day. All you need for Monte Carlo Simulation is your throughput. The number of stories completed per day, you then enumerate this in a list of dates and you put a zero in for each date you didn’t complete a story. Hope that makes sense. This was in the video but easy to miss :-)
@@TheAgileLeanGardenerthank you for replying, am I understanding correctly, that the actual cycle time avg is what you want to get low enough to have a good forecast, but the actual data from cycle time / throughput is historically the NUMBER of items completed daily to run the sim against, the actual, say, 9 days cycle time avg isn’t used but it’s an indicator of process is refined enough to run the projections ? Or am I off :)
Hi, no you are bang on! You want your 85th percentile cycle time to be in good shape before you start using Monte Carlo Simulation (otherwise your forecasts will likely be very misleading) then you use your throughput (number of completed items per unit of time, for example, number of stories completed per day) to calculate Monte Carlo Simulation. Hope that helps.
@@TheAgileLeanGardener amazing thank you. We run Monte Carlo for our agile squad, but the excel was built ages ago, looking to build something in power BI, but no one ever took the time to learn the foundations of how these simulations run to be able to properly build . Great learning thank you!
Hello, Steve! Thank you for your videos, will try it out with one of my software projects and share how that actually worked. I have a couple of questions: 1. We are going iteratively, 3 week sprints. So far, we've done Sprint 0 (which lasted a bit less than 2 weeks) and Sprint 1 (3 full weeks). Can the data dereived out of very first sprints be considered reliable? 2. In terms of cycle time: do we really need to include both: work days and weekend? What I mean is that, some story, can be completed within 4 days (Say Mon to Thu), while other story of the same size and complexity will be compled in 7 days, because it will start on Thu and will last until Wed. What's your opinion here? Thanks!
Hi and thank you and yes please let me know how you get on, if I can help I will. Ahh right great question. Well, the data will be accurate but whether or not it is reliable to use for forecasting is another matter. It would usually take a number of sprints to get the team into a good rhythm (5+). Remember that cycle time and Monte Carlo model based on historical data (so how the team was working in previous sprints) so if you forecast using data when the team were not working efficiently then the forecast will reflect that. But that does not mean don’t do it - definitely start capturing your data, first step is to get your 85th percentile cycle time under control, try and get that to no more than 10 days (since you’re doing 3 week sprints). You’ll have 15 days in your sprint (do not include weekends if you don’t work weekends) but we want to get the cycle time well under the total working days in your sprint. Remember the caveats in the video - plus check this video out which will also help you with the caveats “Speed up your Scrum & Kanban delivery with Flow” ruclips.net/video/PnRVKBhDisA/видео.html
Hello! Can I just leave a question here? When calculating cycle time, I take the first time the task left the "To Do" state. My doubt is how to handle the cases where the task is pulled to "in Progress" but then returned to "ToDo" when the developer has to do something else.
Hi! Hi, yes this happens a lot! Stories/tasks often go up and down the workflow. If your workflow looked like this ‘To Do’ ‘In Progress’ ‘In Test’ ‘Done’ and you wanted to track cycle time defined as ‘In Progress’ to ‘Done’ (I don’t track ‘To Do’ as for me that’s almost always a queue state) then you need the transition to state first date for ‘In Progress’ and transition to state last date for ‘Done’ and this accounts for stories/tasks going up and down the workflow. Time in Status by OBSS and eazyBI both let you do this.
@@TheAgileLeanGardener Thanks for answering! Yeah, I am doing it like that, but the question is what to do when the task goes from 'in progress' BACK to 'to do' and stays there, not being worked for some days until devs can go back to it. Then it goes back to 'in progress'. Do you subtract that 'Idle time'? I have some tasks that were in progress for 2 days, then went back to todo for a week, and then 2 more days in progress, cycle time should be 4? Thanks!
Hi, no, don’t subtract anything. These are the kind of things you need to know about and stop from happening. Do you track the age of your work? I’ve got a video on that here ruclips.net/video/EhVgeL_t98E/видео.html which can help you identify ‘inactive’ time. But your cycle time should include everything from when it was first started to when it was finally finished.
Hi Steve, what a great video! But i am lost on the last part where you execute the macro to generate the random date, get the frequency, and find the 85% percentile.. I mean what is the correlation between the random date with the completion of certain number of stories (which on your case 10 stories)?since it seems you just do a random sampling of the date.. I mean in the previous sheet, there is number of items done and not completed, which are make sense. Please help me to elaborate further. Thanks!
Hi and thank you so much I really appreciate that. The macro just runs the formula in cell F1 10,000 times and puts each of the returned dates into sheet2 which you will then use to create the pivot table in sheet3. The macro does not do the percentiles, that’s done as part of the pivot table. There is no correlation between the number of stories and the random date, I could have decided to find out how long it would take to do 100 stories or 1000. The random date is an essential part of the Monte Carlo calculation. I did say in the video that I would post the Excel template if I got to 1000 subscribers and unbelievably I am very nearly there so thank you to everyone who has subscribed I really appreciate it and I will post a link to the template shortly.
Thanks for the reply Steve! So you basically just generate the random date based on the F1 cell date value? Will the result change a lot if you run the macro multiple times? And i have a situation where i need to forecast more on how many stories to be taken in a sprint, instead of defining what is the expectation date to complete certain number of stories, can we still use Monte Carlo simulation to derive that? The goal is to remove story estimation on the sprint planning / PB refinement, and give ideas to the team how many stories they may committed in a sprint Thanks again Steve!
@@adriantotejokusumo3124 that’s why you run the macro 10,000 times to be sure that the forecast is good. Ahhhh right, ok, so what you first need to do is understand what your 85th percentile is for a single story - the first part of the video covers this - if you’re doing 2 week sprints then the 85th percentile will clearly need to be less than 10 working days, ideally no more than 5. I would concentrate on this first. Once you get your 85th percentile to a low number then you’re in better shape to use Monte Carlo - and yes Monte Carlo can help you to understand how many stories to take into a sprint. I’m about to do a number of videos on what you can do to help you get your 85th percentile to be a low number so watch this space - if I can help I always will so please feel free to ask anything :-)
Thanks a lot for further explanation, after i watch again on the macro related video, i can understand completely! Really hope you can share the excel file if possible too which will save my time a lot.. I got the idea of getting 85 percentile of the cycle time of the story, which we expect reasonable days. And to achieve my goal on getting best projection of total stories taken in a sprint, so i need to gather data in one sprint, and take number of items completed per date, and take the sum of items completed within the sprint cadence as the random value taken. Then, i will run the MCS to get the histogram and get the 85 percentile.. thanks a lot Steve!
@@adriantotejokusumo3124 Hi no problem, I’m happy to help. I’ve shared the Excel template now, you’ll find the link in the description of the video. Best of luck with it and please let me know how it goes.
@@TheAgileLeanGardener Thanks for your prompt response. Usually, running percentage works in monte carlo simulation. Not sure if it was captured in macro. Currently, i am calculating running percentage of total with excel formula.Let me check your video. Thanks
@@dhananjaymani6575 Hi, are you talking about getting the percentile in the last step? From the pivot table? If so, this is what to do. Firstly with Monte Carlo our result histogram will be a normal distribution unlike when we calculate our percentile against a single item which will be skewed to the left (this is to do with the central limit theorem). All of our results in the pivot table will add up to the number of times we ran the simulation. So if we ran the sim 10000 times, add up the results and you’ll see it adds up to 10000. So, we can say that if we find out the date at which 8500 results are totalled this is our 85th percentile and so on. Hope that helps.
Hey Steve -please help me understand better. In this video it says 10 items takes 32 days ? So what’s the cycle time here for 2 week sprint as per your example ? Is it more or less ? In days …
Hi and thanks for watching. In this example using this data 10 items will complete in 32 days with an 85% probability of it actually happening. Your cycle time is used to get this forecast. I’ll be posting a video next week which shows you how to use Monte Carlo to understand how many items will complete between 2 dates, for example sprint start date and sprint end date with the same 85% probability of it happening. This removes any need to estimate, use story points or burndown charts - which I think is what you’re after. I’ll also post the Excel template with that video so you can use it with your own data. Please click the bell icon so you get notified when it’s released next week, thanks Steve.
Hi, thanks a lot for videos! I'm wondering, what formula is used in sheet with pivot table - column D in order to calculate percentile? Does anybody know that?
Hi, this version tells you by what date it will take to deliver x number of stories (where x is a number you specify). I don’t have a version where you specify a date and it will tell you how many stories will be done by that date no. If I got enough interest in that I’d look at it though.
Hi again, you got me thinking :-) Ok I’m going to do a video on Monte Carlo with the ‘how many’ variant and provide the Excel template in the description of that video. It might be a couple of weeks before I post it though so please subscribe (if you haven’t already) and click the bell icon to get notified when it’s done. Cheers Steve
Hi, I am using your template to forecast the work for just 4 stories. Example - 1 person in the team delivering one user story every 5 days (This is just for playing around not the real data). I have data for 3 months which I am adding and when I want to find out how long it would take to complete remaining 4 user stories, the template is not supporting. I have data till 92nd row in Sheet1. Hence I updated the Random number formula to {=INDEX($B$3:$B$92,RANDBETWEEN(1,92))}. Modified F1 formula as well to accommodate 92 rows data but it shows error which I am unable to figure out. Could you please help?
@@ramyag9165 Hi, ok, there were a number of issues, I’ve replied to your email with some tips and fixed your Excel - you need to ensure the formulas are updated ;-)
This is mint - would love to execute this in my role as Scrum Master - but need to really tackle this subject and take a deep dive to understand the statistics
Thanks 🙏 I appreciate that:-) I’m releasing another Monte Carlo vid this week that shows you how to forecast how many stories you can complete within a sprint with the same 85% probability of it happening - click the bell icon so you get notified when it lands. I’d also recommend checking out my latest video on what makes a great scrum master ruclips.net/video/o5V8-s7_M-Q/видео.html as you’ll need to do all of these things to ensure your forecasts are accurate, thanks for watching.
@@TheAgileLeanGardener Legend mate will check it out thank you for providing such an immense service for free! I'll put you up there with Vicanti as a thought leader in the space.
I’m humbled man! Yes no doubt Daniel is awesome but I do it for free, as it should be.
Thanks for the reply Steve. We do start counting cycle time when the tickets gets moved to In Progress. Our cycle times are high, but because we are completing a fairly high percentage of the committed tickets during the 2 week sprints the dev managers are not willing to take the extra time to break the tickets into smaller pieces.
Hmm I see. But the figures don’t seem to tally to me, something isn’t quite right. Are you sure the devs are moving their tickets into done on the right day? If they don’t, even a day or 2 late will skew the forecast and cycle time.
@@TheAgileLeanGardener we will look to hone that some more, but regardless, MCS is still showing only 6-7 tickets per sprint and I know we are hitting 12-17 per sprint . I know having a large number of tickets completing the last day of the sprint hurts the predictability; however, when there is a seven month pattern happening you would think it would take that pattern into account.
Yep I get it but these figures don’t tally to me, I still think something is wrong. Monte Carlo is simply a calculation based on the data input, there is no intelligence at work. Have you tried sampling a shorter more recent data sample, for example the last 8 sprints (and check the throughput for each of those 8 sprints)? Have they been any changes of significance to the team in the last 6 months?
@@TheAgileLeanGardener now I am really worried. I ran the same data through your Excel template and it shows 15 tickets at the 85th percentile. I am using ActionableAgile and it shows 6 days with the exact same data set.
Hmm the plot thickens….. Well 15 sounds more like it based on your figures.. But I’m not sure why AA is saying 6…. When I’ve compared my Excel to AA it’s always the same +-1 sometimes 2
Hey Steve, another great video! What drew me to monte carlo simulation + your other cycle time videos was that I have recently been in that same situation with leadership you touched on. It's refreshing to know it's not just me that has gone through it!
Thanks macca I appreciate that, yes you’re not alone, Monte Carlo Simulation is by far the best way to turn it around in my experience, spread the word :-)
@@TheAgileLeanGardener I definitely will.
Was hoping to clarify one thing, how do you forecast time to complete for 100 or 1000 stories? I noticed in your example you just used 10, would it just be expanding the time period to get 100 or 1000 stories and running the same thing through?
@@macca2272 hey, yes sure. Go to 5m21s in the vid, and change the formula in column D to say 100-C3 or 1000-C3 the only thing to bear in mind is that you will need a larger sample set than just the 2 months I have in the example or completing stories every day or every other day otherwise you’ll get a lot of errors - this is why it’s important to get your 85th percentile cycle time well under 10 days, let me know how it goes..
@macca I hear you .. you are not alone on this one!
Great video, this is so helpful for our forecasting!!
You’re welcome, let me know how it goes!
I've been waiting for a video like this for ages! Thank you!!!!
Cool 😎 thank you 🙏👍
This is just AMAZING! Thank you! 👍
You’re welcome, thank you 🙏👍
Hi Steve,
Real liking your clear and succinct posts! Since the techniques you’ve shared here rely on building a model from historical data… I want to understand what you’d do if you’re starting with a brand new team, and also what if you have to put together a new team for every new project (not exactly the long lived team idea for Agile!) … but if that’s the reality, how can I adapt these methods?
Hi Jarrod, I really appreciate that thank you 🙏 Ok so if you don’t have any historical data then at the outset of a project you obviously won’t be able to use Cycle Time or Monte Carlo. But as long as you collect that data (cycle time) as you start you will be able to use it after a few weeks. It won’t be accurate since you have limited data. But what you will do is constantly run Monte Carlo each week (as you build your data set) it will get more accurate as you build your data. Even with limited data you will still be able to forecast how long it will take to deliver x number of stories but just be mindful that at the start it won’t be very accurate since it’s a new team working with each other. You can’t compare one team to another so you’ll have to use this approach every time you start a new team. As you mention the approach you’ve described is not agile, you’re bringing people to the work instead of bringing the work to the people, this is a waterfall approach. But Cycle Time and Monte Carlo are agnostic of frameworks and methodologies so it will still work but with caveats e.g. no historical data so the forecast is built over time, new teams each time so you can’t use the forecast for the previous team for the new team, the forecast will change a lot at the start because the team is new and just forming so it will need to be rerun regularly. Hope that helps.
@@TheAgileLeanGardener thanks! that's what I thought too, was just hoping there was a way to make this a silver bullet for all these cases 😅
This is just amazing, thank you!!!
Thank you, very much appreciated 👍🙏
Hey, you mentioned at 6:51 that we can save ourselves from the hassle of assigning story points and going through endless estimation sessions. You suggested that all we need to do is make sure our stories are right-sized so that they can be completed within eight days. So far, so good. But it's also an estimation, but this time it's based on (cycle)time rather than story points. This could also take up hours of valuable time to right-size stories. Have I misunderstood something?
Hi and thanks for the question. So firstly using this technique does not mean we don’t discuss the work break it down, identify dependencies and constraints. We still of course need to do this. Cycle time is not an estimate it’s something that ‘has’ happened compared to a story point which is an educated guess. If your 85th percentile to complete a story (based on your historical data set) tells you it takes 5 days then the idea is to right size all future stories not to exceed 5 days based on your experience with all of the stories you’ve done (not to ‘be’ 5 days just not to exceed it). This part, the right sizing, you could argue is similar to assigning a story point except that you’re basing it on data, things that have actually happened. Most teams I’ve worked with when using story pointing assign a story, for example, 5 points, but it never then gets re-examined. Was it really 5 points? Using cycle time is a quick and easy way to truly identify how long a story takes to get done. There are caveats to this of course which I’ve tried to point out in the video. Hope that helps.
Steve, I love your videos and I am working to move all of my Scrum Teams over to the way of Flow Metrics, but I am having an issue with explaining why the MonteCarlo simulation only shows one of my team with an 85% chance of completing 6 tickets or more during a sprint, when they typically complete between 12-17 tickets per sprint. As you can possibly imagine, their Throughput is fairly consistent; however, they are delivering the large majority of their tickets on the final day of the sprint. Even after 7 months of data, the MonteCarlo simulation has not seen this pattern and adjusted accordingly. Any thoughts on how I can overcome this hurdle in making the move?
btw, the team's cycle time is 14 days (85th percentile).
Hi and thanks 🙏👍 are you doing 2 week sprints? If so your 85th percentile cycle time of 14 days is too high - but this ‘could’ be due to other factors than it actually taking that long. For example, are the team really updating tickets when the item is done on the right day? If not this will make a difference both to your cycle time and the Monte Carlo forecast. Also when does your cycle time start? It needs to start only when you move a ticket in sprint from ‘to do’ to ‘in progress’ (or equivalent). If your cycle time starts as soon as you put something into sprint regardless of whether you’ve actually started work on it then this will also skew both cycle time and Monte Carlo. I would look into this because if Monte Carlo is saying you can only get 4 or 5 items done to the 85th percentile and your 85th percentile cycle time is saying 14 days but you always seem to get a throughput in sprint of 7 or 8 then something is definitely not right.
it's not a new idea, in kanban this is pretty much already done like this. Estimation is a waste of time, just break the work down while discussing it, eventually team speed can be calculated and you have fairly accurate estimates from those
👍
Excellent video. Thanks for sharing.
Thank you, really appreciate that :-)
Great lightweight equivalent to Troy's arguably more robust but heavy process
Thank you, I appreciate that 🙏👍
@@TheAgileLeanGardener Thanks for the quick response. I find the challenge with this method to be "how many stories" is the feature. Do you have any suggestions on best practice for that? I have my own techniques i won't mention because i'm looking to expand my toolbox.
Yes true. You’re never going to know exactly how many stories a piece of work is going to take and of course you would never try and get all the stories ready before you start - that would be waterfall!
But I always pick from previous work a small, medium and large piece of work that people are familiar with and work out how many stories each took. Then you can say a small piece of work is 50 stories and that takes 1 month, a medium piece of work is 150 stories and that takes 2.5 months and a large piece of work is 300 stories and that takes 5.5 months with an 85% chance of completing on time.
To set expectations before you start a new piece of work the team decide which category of size it is compared to the 3 baselines (small, medium or large).
Then once you’ve started each week I rerun Monte Carlo against the remaining backlog items to update leadership on the completion date bearing in mind that we said at the start this would be a medium piece of work taking 2.5 months. I usually communicate this at the sprint review.
Hope that helps.
@@TheAgileLeanGardener Looking at comps works for sure - however this becomes really hard when you're building a new product from scratch (which I have done for most of my career). I found in this case it DOES pay to try to discover the stories using user story maps, or at least, making bullet points of each potential story/feature. Or at least sets of major sub-features which can then be t-shirt sized and roll up into an overall estimate. Breaking down work into stories, assuming the stories are user facing features and not technical tasks, can be helpful for alignment purposes anyway.
Hi, yes absolutely agree with you on story mapping, it’s a great way to understand and discuss the work. We definitely need to do this type of discovery at the start but we just need to realise that the point is to give us a great start and not to capture all requirements up front as that would kill innovation as we work 👍
Hi Steve - in the excel I'm not getting a date in F1 as you do. Here are the formula's for the columns. Column A(End Date) =+A3+1 Column B(Total No of Completed) No Formula Column C(Random No From Done) =INDEX($B$3:$B$63,RANDBETWEEN(1,61)) Column D(Items left to complete) =10-C3 F1(Date) =INDEX($A$3:$A$63,MATCH(TRUE,$D$3:$D$63
Send me your excel and I’ll take a look for you stephen.angood@spa-agile-consulting.co.uk
I haven't seen this question asked so I am going to ask it. Why 85% and not 95%?
There is no hard or fast rule - 85% because it provides a high degree of certainty that it will actually happen. You could use 95% but you’ll find your forecasts are quite a lot larger. But you don’t have to use a single % you could provide 3 65% 75% 85% it all depends what your stakeholders are happy with, hope that helps.
Hello again Steve. Are there any negatives of using the sprint throughput (# of tickets completed in a given sprint) instead of the daily throughput for your MCS? I have 16 sprints worth of data and when I run the daily throughput MCS I get 6 tickets at the 85%. When I use the sprint level throughput I get 14 tickets at the 85%. Thoughts on this approach?
Hi, I’m not quite sure what you mean. To use MCS all you need for your 16 sprints is the throughput for your completed stories. So, on what date was each story completed. You then plot this on your date range in the Excel. Let’s say the first date that you had a completed story was 1st March 2024 and the last date you had a completed story was 31st July 2024. You then plot that date range in the Excel (every date in this range must be plotted whether you completed a story on that date or not) you then simply put how many stories where completed on a date, it could be 2, 5, 10 or it could be zero. Once you’ve done this you can change the date range to start in the future if you wish so long as every date in this new range is plotted. Just don’t change the column that shows how many stories were done. Are you doing this? Or have I misunderstood your question?
@@TheAgileLeanGardener I modified your template and instead of have each day listed in column A of Sheet1, I have the start date of each of my next 16 sprints listed in column A. I then put the total count of tickets from each of my 16 sprints in column B. I then changed the value in F6 (Date to Complete By) to coincide with the date value in the first Date Range column (A3).
…further thought. Usually the more data you have the better the forecast will be. The less data you have the worse the forecast will be. But the data you use must model what is currently happening. Significant changes in the team or major events (everyone is on holiday or has covid) will affect the forecast. MCS models past performance and so will forecast accordingly. So you need to use data that reflects current reality.
This won’t work. Each day in the date range must be listed, it’s very important!
The 2 templates I have work but if you change any logic they will not work, you’ve changed 2 very important things in order for MCS to work correctly. I would recommend using them as they are and see what result you get and if that closer aligns to what you’re seeing on the ground.
Great video! How long should a team collect data (for that 85%) in order to be able to run monte carlo estimation?
Hi and thank you, I really appreciate that. I’d give it a couple of sprints (4 weeks or so) and then run it. But the trick is to keep running it every week thereafter so you can see the deltas and take action, please let me know how you get on and if I can help I will.
Hi Steve, I have been watching your videos re cycle time and monte carlo forecasting and I am really intrigued however I cannot find a way to get cycle time in Jira that means anything useful to me. I am looking at the control chart and am not 100% sure how to set the parameters to give me the information and then of course use that to get the 85th percentile. Have I missed a video showing this or is there a nugget of wisdom you could share with me as I feel a little stuck at the first hurdle 🙈
Hi and thanks for watching the channel 😊 Ok, you can’t natively get the underlying data out of Jira, you’ll need to use an Atlassian Marketplace plugin such as Time in Status or EazyBi, I have a video on this here, hope that helps ruclips.net/video/5Szv-w6n5NE/видео.html
@@TheAgileLeanGardener Thanks Steve, I will have a watch and have a look at the plugins too
Hi Steve -- quick question: I think you're bang-on about how ineffective SP's are for forecasting, but when you remove SPs there seems to be a bit of a gap left over when it comes to a few things that have been important to me so far:
1. Calculating "Bang for you Buck" (or ROI) based from effort/value
2. Agreeing on what to add to the Sprint Backlog during Sprint Planning befitting the team's capacity.
Any suggestions, on those points? Am I overvaluing those? To me, the Monty Carlo simulation works good for mature teams in the long term when there is consistent output and sufficient historical data. but if a given 2-week sprint only contains 5-12 stories, that seems to allow for a lot of inter-sprint variance, correct? Any insight would help. "Minimal Viable Estimation" is definitely a major plank of mine for an agile transformation I'm currently steering. Any advice for how this method does (or does not) applies to the early days of an agile transformation in the face of poorly maintained historical data, would be majorly appreciated.
Hi and thanks for your interesting and great comments.
Firstly I’d say that using Cycle Time and Monte Carlo Simulation is, in my opinion, the answer to understanding how much you can do and by when for a given team or teams, whether that’s in a sprint or over 20 sprints.
But, and it is a big but, NOT if it’s used in isolation of excellent ways of working. I did a video on what a Scrum Master should be doing and at a high level I broke it into 2 sections, quantitative and qualitative.
Using Cycle Time, Monte Carlo Simulation, understanding your Flow Efficiency and using Ageing Charts all come under the quantitative section. Using these techniques can show you the areas that need focus and improving.
This leads us to the qualitative section, which is arguably more important. So true Servant Leadership, understanding what a good requirement is whether we call that a story or a task or whatever (remember user stories have never been mentioned in the Scrum Guide or the Agile Manifesto!) close collaboration with the PO and working together to create and prioritise the backlog, reducing context switching, limiting work in progress, removing handoffs (queues), keeping the size of work small, refining and refactoring - and the list goes on :-)
But to your point of ‘bang for buck’ and what should you take into sprint then I would say the 2 most important things are your product goal and your sprint goal which need to be informed by feedback from your client or customer.
Without these then we could easily end up having a very high performing team delivering consistent but essentially useless products or services. The measure of success for each sprint is the sprint goal (and for that you must have a product goal).
So, don’t focus on completing every ‘story’ taken into sprint. Focus on achieving the sprint goal feeding the product goal. We need to be measured on outcomes not outputs. I hope that’s of some help, a bit wordy I know :-)
@@TheAgileLeanGardener Incredibly helpful -- thanks. I still think there's a piece missing that I suspect others like me will feel is left dangling from this approach (for context, I've watched all your videos and have developed a sense--I hope--of your agile 'design imperatives', if that makes sense). That dangling piece is: how to have a scrum team feel reasonably confident that their sprint backlog is 'achievable'.
Even we do steer the team to focus on the goal more than the output, those two things are mutually informative: a sense of a team's capacity will inform the sprint goal, and visa-versa.
I agree that SP is a time suck, easily manipulated (Goodhart's Law), given to group power dynamics, and so on. I have been playing around with returning to the old XP notion of "Ideal Days". There's something beautifully simple about it -- and the thing that I LOVE about it is that it's a constant reminder that a shared goal of an agile Way of Working is making structural changes together as a team, to keep striding for as "Ideal" of a day as possible (by reducing unplanned work, needless meetings, peer learning). And, as you make those improvements together, that "Ideal" days starts to become realized...and they feel it. I think it's a very powerful emotional "in" for workers to see the connection between core agile values (like the real mushy ones :) ) and agile process improvement...
Ha....I think I one-up'ed you for "being a bit wordy".
@@shaye5095 Hehe wow yes I think you out did me on the reply ;-) Many thanks for your thoughts and comments it’s great that these discussions are happening. And thank you for watching the channel. I try to release a video each week but work has been so hectic recently it’s slowing me down so I’ve been doing a few shorts until I can get back to long form content.
With regard to the teams confidence about the sprint backlog - are they in control of how much they take into sprint? If not give them that control and allow them to decide. Then use the retro to inspect what happened and why and repeat. The retro is extremely important as it may well not be how much work they take that’s causing the lack of confidence but other reasons such as lack of clarity so deeper refinement may be needed. I’d explore what is causing the lack of confidence and fix that first. Let me know how it goes.
I put my dates on Sheet 1, but they still remain in 2022 in Sheet 3. Any ideas?
You need to refresh the pivot table in sheet 3 also don’t forget to delete all the dates in sheet 2 (apart from the header) before you run the macro, all the instructions are in the video so you may have to re watch it cheers Steve
I might have missed it, but where does the use of cycle time actually come into here? Seems your randomly selecting based on count and then repeating and getting the distribution, but nothing actually using that cycle time from first part of the video? Did I miss something ?
Hi, cycle time gives you your throughput. For example the number of stories completed on each day. All you need for Monte Carlo Simulation is your throughput. The number of stories completed per day, you then enumerate this in a list of dates and you put a zero in for each date you didn’t complete a story. Hope that makes sense. This was in the video but easy to miss :-)
@@TheAgileLeanGardenerthank you for replying, am I understanding correctly, that the actual cycle time avg is what you want to get low enough to have a good forecast, but the actual data from cycle time / throughput is historically the NUMBER of items completed daily to run the sim against, the actual, say, 9 days cycle time avg isn’t used but it’s an indicator of process is refined enough to run the projections ? Or am I off :)
Hi, no you are bang on! You want your 85th percentile cycle time to be in good shape before you start using Monte Carlo Simulation (otherwise your forecasts will likely be very misleading) then you use your throughput (number of completed items per unit of time, for example, number of stories completed per day) to calculate Monte Carlo Simulation. Hope that helps.
@@TheAgileLeanGardener amazing thank you. We run Monte Carlo for our agile squad, but the excel was built ages ago, looking to build something in power BI, but no one ever took the time to learn the foundations of how these simulations run to be able to properly build . Great learning thank you!
You’re very welcome 🙏👍 best of luck with it. Let me know how you get on, always happy to help.
Hello, Steve!
Thank you for your videos, will try it out with one of my software projects and share how that actually worked.
I have a couple of questions:
1. We are going iteratively, 3 week sprints. So far, we've done Sprint 0 (which lasted a bit less than 2 weeks) and Sprint 1 (3 full weeks).
Can the data dereived out of very first sprints be considered reliable?
2. In terms of cycle time: do we really need to include both: work days and weekend? What I mean is that, some story, can be completed within 4 days (Say Mon to Thu), while other story of the same size and complexity will be compled in 7 days, because it will start on Thu and will last until Wed.
What's your opinion here?
Thanks!
Hi and thank you and yes please let me know how you get on, if I can help I will. Ahh right great question. Well, the data will be accurate but whether or not it is reliable to use for forecasting is another matter. It would usually take a number of sprints to get the team into a good rhythm (5+). Remember that cycle time and Monte Carlo model based on historical data (so how the team was working in previous sprints) so if you forecast using data when the team were not working efficiently then the forecast will reflect that. But that does not mean don’t do it - definitely start capturing your data, first step is to get your 85th percentile cycle time under control, try and get that to no more than 10 days (since you’re doing 3 week sprints). You’ll have 15 days in your sprint (do not include weekends if you don’t work weekends) but we want to get the cycle time well under the total working days in your sprint. Remember the caveats in the video - plus check this video out which will also help you with the caveats “Speed up your Scrum & Kanban delivery with Flow” ruclips.net/video/PnRVKBhDisA/видео.html
@@TheAgileLeanGardener
Thank you for your answer, Steve!
I will be posting my updates here ;)
@@DanboBAKprod I look forward to hearing how you get on
Hello! Can I just leave a question here? When calculating cycle time, I take the first time the task left the "To Do" state. My doubt is how to handle the cases where the task is pulled to "in Progress" but then returned to "ToDo" when the developer has to do something else.
Hi! Hi, yes this happens a lot! Stories/tasks often go up and down the workflow. If your workflow looked like this ‘To Do’ ‘In Progress’ ‘In Test’ ‘Done’ and you wanted to track cycle time defined as ‘In Progress’ to ‘Done’ (I don’t track ‘To Do’ as for me that’s almost always a queue state) then you need the transition to state first date for ‘In Progress’ and transition to state last date for ‘Done’ and this accounts for stories/tasks going up and down the workflow. Time in Status by OBSS and eazyBI both let you do this.
@@TheAgileLeanGardener Thanks for answering! Yeah, I am doing it like that, but the question is what to do when the task goes from 'in progress' BACK to 'to do' and stays there, not being worked for some days until devs can go back to it. Then it goes back to 'in progress'. Do you subtract that 'Idle time'? I have some tasks that were in progress for 2 days, then went back to todo for a week, and then 2 more days in progress, cycle time should be 4?
Thanks!
Hi, no, don’t subtract anything. These are the kind of things you need to know about and stop from happening. Do you track the age of your work? I’ve got a video on that here ruclips.net/video/EhVgeL_t98E/видео.html which can help you identify ‘inactive’ time. But your cycle time should include everything from when it was first started to when it was finally finished.
@@TheAgileLeanGardener Great! yes, I track WIP age, actually based on your videos! I'll leave it there then and I'll also add a blocked state. Thanks!
Cool 😎👍
Hi! The link to the template download is not working. Any idea what happened?
Hi, I’ve just tested it and it works fine, so give it another go 👍
@@TheAgileLeanGardener my bad :(. Company vpn
Ahh yeah, hope the template works for you 👍
Hi Steve, what a great video! But i am lost on the last part where you execute the macro to generate the random date, get the frequency, and find the 85% percentile..
I mean what is the correlation between the random date with the completion of certain number of stories (which on your case 10 stories)?since it seems you just do a random sampling of the date..
I mean in the previous sheet, there is number of items done and not completed, which are make sense. Please help me to elaborate further. Thanks!
Hi and thank you so much I really appreciate that. The macro just runs the formula in cell F1 10,000 times and puts each of the returned dates into sheet2 which you will then use to create the pivot table in sheet3. The macro does not do the percentiles, that’s done as part of the pivot table. There is no correlation between the number of stories and the random date, I could have decided to find out how long it would take to do 100 stories or 1000. The random date is an essential part of the Monte Carlo calculation. I did say in the video that I would post the Excel template if I got to 1000 subscribers and unbelievably I am very nearly there so thank you to everyone who has subscribed I really appreciate it and I will post a link to the template shortly.
Thanks for the reply Steve! So you basically just generate the random date based on the F1 cell date value? Will the result change a lot if you run the macro multiple times?
And i have a situation where i need to forecast more on how many stories to be taken in a sprint, instead of defining what is the expectation date to complete certain number of stories, can we still use Monte Carlo simulation to derive that? The goal is to remove story estimation on the sprint planning / PB refinement, and give ideas to the team how many stories they may committed in a sprint
Thanks again Steve!
@@adriantotejokusumo3124 that’s why you run the macro 10,000 times to be sure that the forecast is good. Ahhhh right, ok, so what you first need to do is understand what your 85th percentile is for a single story - the first part of the video covers this - if you’re doing 2 week sprints then the 85th percentile will clearly need to be less than 10 working days, ideally no more than 5. I would concentrate on this first. Once you get your 85th percentile to a low number then you’re in better shape to use Monte Carlo - and yes Monte Carlo can help you to understand how many stories to take into a sprint. I’m about to do a number of videos on what you can do to help you get your 85th percentile to be a low number so watch this space - if I can help I always will so please feel free to ask anything :-)
Thanks a lot for further explanation, after i watch again on the macro related video, i can understand completely! Really hope you can share the excel file if possible too which will save my time a lot..
I got the idea of getting 85 percentile of the cycle time of the story, which we expect reasonable days.
And to achieve my goal on getting best projection of total stories taken in a sprint, so i need to gather data in one sprint, and take number of items completed per date, and take the sum of items completed within the sprint cadence as the random value taken. Then, i will run the MCS to get the histogram and get the 85 percentile..
thanks a lot Steve!
@@adriantotejokusumo3124 Hi no problem, I’m happy to help. I’ve shared the Excel template now, you’ll find the link in the description of the video. Best of luck with it and please let me know how it goes.
Hi Steve, Great video. Quick question. How did you calculate percentile? I don't think it was captured in macro?
Hi and thanks :-) go to 2m 36s of the video to see how the percentile calc works
@@TheAgileLeanGardener Thanks for your prompt response. Usually, running percentage works in monte carlo simulation. Not sure if it was captured in macro. Currently, i am calculating running percentage of total with excel formula.Let me check your video. Thanks
@@dhananjaymani6575 Hi, let me know how it goes and I'll help if I can
@@dhananjaymani6575 Hi, are you talking about getting the percentile in the last step? From the pivot table? If so, this is what to do. Firstly with Monte Carlo our result histogram will be a normal distribution unlike when we calculate our percentile against a single item which will be skewed to the left (this is to do with the central limit theorem). All of our results in the pivot table will add up to the number of times we ran the simulation. So if we ran the sim 10000 times, add up the results and you’ll see it adds up to 10000. So, we can say that if we find out the date at which 8500 results are totalled this is our 85th percentile and so on. Hope that helps.
Hey Steve -please help me understand better. In this video it says 10 items takes 32 days ? So what’s the cycle time here for 2 week sprint as per your example ? Is it more or less ? In days …
Hi and thanks for watching. In this example using this data 10 items will complete in 32 days with an 85% probability of it actually happening. Your cycle time is used to get this forecast. I’ll be posting a video next week which shows you how to use Monte Carlo to understand how many items will complete between 2 dates, for example sprint start date and sprint end date with the same 85% probability of it happening. This removes any need to estimate, use story points or burndown charts - which I think is what you’re after. I’ll also post the Excel template with that video so you can use it with your own data. Please click the bell icon so you get notified when it’s released next week, thanks Steve.
@Sumitha Acharya the video is now live, link here ruclips.net/video/k22ZwODjgkE/видео.html
At 7:55 you mentioned that we can define the max cycle time based on 85% percentile. How can I find that out in MCS template?
Hi, go to 6m 20s of this video to see. Or you can check this video at 6m 28s ruclips.net/video/k22ZwODjgkE/видео.html
@@TheAgileLeanGardener Thank you so much
Hi, thanks a lot for videos! I'm wondering, what formula is used in sheet with pivot table - column D in order to calculate percentile? Does anybody know that?
oops, I think I get it, percentage of count of dates above to total number of dates generated, right?
Yep you got it :-) let me know how you get on with it, very interested to hear.
I think you just answered your own question:-)
Do you have a version of this that answers the how many question also?
Hi, this version tells you by what date it will take to deliver x number of stories (where x is a number you specify). I don’t have a version where you specify a date and it will tell you how many stories will be done by that date no. If I got enough interest in that I’d look at it though.
Hi again, you got me thinking :-) Ok I’m going to do a video on Monte Carlo with the ‘how many’ variant and provide the Excel template in the description of that video. It might be a couple of weeks before I post it though so please subscribe (if you haven’t already) and click the bell icon to get notified when it’s done. Cheers Steve
@@TheAgileLeanGardener fantastic!
No problem 😊 the video is live now, link here ruclips.net/video/k22ZwODjgkE/видео.html
@DubLdubya the video is live now, link here ruclips.net/video/k22ZwODjgkE/видео.html
Hi, I am using your template to forecast the work for just 4 stories. Example - 1 person in the team delivering one user story every 5 days (This is just for playing around not the real data). I have data for 3 months which I am adding and when I want to find out how long it would take to complete remaining 4 user stories, the template is not supporting. I have data till 92nd row in Sheet1. Hence I updated the Random number formula to {=INDEX($B$3:$B$92,RANDBETWEEN(1,92))}. Modified F1 formula as well to accommodate 92 rows data but it shows error which I am unable to figure out. Could you please help?
Hi, send me your Excel and I’ll have a look for you contact@spa-agile-consulting.co.uk
@@TheAgileLeanGardener Thanks a ton. sent an email to you now.
@@ramyag9165 Hi, ok, there were a number of issues, I’ve replied to your email with some tips and fixed your Excel - you need to ensure the formulas are updated ;-)
I'm having this same problem. I've made no changes to the template. If I click into cell F1 and hit enter, I get #value error.
Hi Stacy, apologies, I’ve only just seen this. If you send me your Excel I’ll take a look at it for you stephen.angood@spa-agile-consulting.co.uk