Estimation in Scrum | STOP IT | Cycle Time & Monte Carlo | Excel Template
ฝัง
- เผยแพร่เมื่อ 8 มิ.ย. 2024
- How do you estimate a user story and should you even try? In my experience spending time 'estimating' user stories with story points with the Fibonacci sequence and using planning poker almost always results in wasting everyones time. They generally don't provide any real value. Neither does figuring out your 'velocity' and plotting it onto a burn down chart. The best way is to use cycle time and then Monte Carlo Simulation to forecast - with accuracy - when you will be able to deliver your work, whether that's 50 user stories or 1,000. This works equally well in either the Scrum or Kanban frameworks. So, no need for story pointing, burndown charts or velocity - and no need to estimate! In this video I show you how to forecast delivery of any number of stories with an 85% probability using Monte Carlo Simulation in Excel.
Note: This relates to column D in sheet 1.
The formula in cell D2 is 10-C2 but the formula in D3 needs to be D2-C3 and the formula in D4 needs to be D3-C4 and so on to allow column D to eventually get to 0 or a minus number.
If you want to calculate how long it takes to get more than 10 items done change the formula in cell D2 to be 100-C2 or 1000-C2 etc
Monte Carlo Simulation Template Download:
drive.google.com/file/d/1JRau...
Related videos:
How many stories can you get done in a sprint using Monte Carlo Simulation
• How to use Monte Carlo...
How to create a macro in Excel to allow you to run Monte Carlo Simulation thousands of times
• How to create a macro ...
How to extract data from Jira for cycle time and Monte Carlo Simulation using EazyBI & TIS
• How to extract data fr...
How to be a great Scrum Master
• What do Scrum Masters ...
Supercharge your Scrum & Kanban delivery with Flow
• Limit work in progress...
Ageing work in progress charts
• Scrum Metrics Ageing W...
Flow Efficiency
• What is flow efficienc...
Are story points, velocity & burndown charts effective for Scrum Teams? There is a better way!
• Should a Scrum Team us...
The secret behind the definition of done in Scrum
• The Definition of Done...
How to create a product backlog quickly
• User story mapping - c...
#TheAgileGardener
#NoEstimates
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 th-cam.com/video/o5V8-s7_M-Q/w-d-xo.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.
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 🙏👍
This is just amazing, thank you!!!
Thank you, very much appreciated 👍🙏
Great video, this is so helpful for our forecasting!!
You’re welcome, let me know how it goes!
Excellent video. Thanks for sharing.
Thank you, really appreciate that :-)
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!
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 😅
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
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.
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.
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 th-cam.com/video/5Szv-w6n5NE/w-d-xo.html
@@TheAgileLeanGardener Thanks Steve, I will have a watch and have a look at the plugins too
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 -- 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.
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” th-cam.com/video/PnRVKBhDisA/w-d-xo.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
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.
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.
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, 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:-)
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
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
👍
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 th-cam.com/video/k22ZwODjgkE/w-d-xo.html
@@TheAgileLeanGardener Thank you so much
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 th-cam.com/video/EhVgeL_t98E/w-d-xo.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 😎👍
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 th-cam.com/video/k22ZwODjgkE/w-d-xo.html
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 👍
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 th-cam.com/video/k22ZwODjgkE/w-d-xo.html
@DubLdubya the video is live now, link here th-cam.com/video/k22ZwODjgkE/w-d-xo.html
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.
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