Calendar Days vs Work Days (Storing and using cycle time data)

Posted by in Featured, Forecasting |

Problem in a nutshell: Should work time in process (cycle time) and lead time be workdays or calendar days? Does it matter which we use for forecasting?

Just want the spreadsheet: Get it here: Cycle%20Time%20Adjustments.xlsx

We get this question a lot. Should weekend and holidays be captured in cycle time data. Our answer is along the lines, “whatever you have.” It doesn’t matter from a forecasting perspective, as long as you are consistent. Here are this issues that may sway one way or the other –

  1. If your work item estimate are time based, and they are expressed in work days, it may be easier to use work days as time in process (cycle time) numbers.
  2. If you are capturing item data as date started and date ended, then it will naturally fall out to include weekends, and we can remove those days to get work days.

We often have to convert from one to the other. Its easy if we have dates to work from, because Excel has some helpful functions for computing workdays between dates (removing non-work days and a list of public holidays) Lookup the NETWORKDAYS documentation. This is an lossless conversion.

Trickier is when we just have a number of days. We spend some time checking into how these were calculated. If it is calendar days and we cannot get the raw date data, we use a statistical approach for removing an approximate number of days from each sample. Here is how our algorithm works (it’s a little complicated, but it is the best we have found).

For every multiple of 7 days in the original cycle time we can remove 2 days. If we have a cycle time of 7 days, and we know that a company works 5 days (Monday to Friday), then we can remove two days. When a cycle time is less than 7 days, we have to guess what day of the week the work started. For example, if a cycle time is 3 days, valid starting days where all 3 days fit into a working week are, Monday, Tuesday, Wednesday. If the work started Thursday, 1 day would be weekend, Friday, 2 days weekend. If every day of the working week has equal chance, there is a 3/5 chance the right value is a 3, 1/5 it’s a 2, and 1/5 it’s a 1. We use these probabilities and adjust based on a uniform random chance.

Don’t worry. We of course have encoded all of this logic into a spreadsheet. Our Cycle Time Adjustments.xlsx spreadsheet can convert in both directions for dates and numerical cycle time inputs. It can never be exact for numerical cycle times, but it is pretty close from our round trip testing (dates -> numerical -> dates).

Get it here: Cycle%20Time%20Adjustments.xlsx

You can see our logic for the time based probability logic in the time based setup worksheet.

Setup for the probabilities of cycle time adjustment.

Setup for the probabilities of cycle time adjustment.


For recommendations about data capture of cycle time and lead times, we suggest –

  1. Capture date started (committed to start delivery), date completed and date captured as an option to be considered (often, date created).
  2. Store cycle time data as dates, don’t convert to days until the last moment you need to.
  3. Be consistent with date format. We like yyyy-mmm-dd (eg. 2016-Apr-20) as a format that is unlikely to be confusing whatever the native date format is in your country or region.


Read More

Decision Making

Posted by in Featured, Forecasting |

I recently have the opportunity to do training with Michael Tardiff, a gifted facilitator and trainer for Solutions IQ. One of Michael’s specialty subject is group decision making. We take different approaches to teaching this topic, i’m more about getting to any answer, Michael is more about knowing the method used to get to the answer so that it has the greatest chance of surviving use over time. Michael is right of course, the goal of decision making is to get to the right answer (for now) and to avoid future “I never agreed to that” problems. Whilst consensus isn’t necessarily the key, finding agreement that persists over stress and time is the purpose and goal.

Michael says there are four basic types of decision making process, and others are a combination of these –

  1. King Rules (gets to live why we like their decisions, then beheaded)
    speed: fastest, risk: high if technical, long-lasting: until change of king
  2. Majority Rules (works while the minority of the last decision believes they will be the majority one day)
  3. Consent (staying silent means you agree)
  4. Consensus (hardest to achieve, but once agreed it was so hard, it tends to stick)
    speed: slowest, risk: low if the right people agree consensus, long-lasting: good

Its important to call out (when its unsaid) how a decision is being made or has been made. Consensus is the longest and hardest to achieve, but tends to stick because people are invested in the decision. Consent offers middle ground if there is time and capability to handle objections. If your system demands King Rules, just acknowledge it. Majority rules is a muddy area. You haven’t managed to sway the minority opinion who might believe their day will come. But, if a decision is needed by a certain time, or total agreement may never be achieved it is a (often) fair way to resolve decisions. But it may not stick for long.

Hofstede’s Cultural Dimension Theory (see here)

Decision making styles can be culturally impacted. Even within one country, there are very different styles in lively discussion one coast to the other (in the USA, West coast are more consensus introverts, and East coast more Extrovert). Pay attention when working with experts from cross geographies that the ability for challenging authority varies, and you may just think you have consensus. The classic measure of this is Hofstede’s Cultural Dimension Theory which ranks countries based on set of interesting dimension relevant to decision making attributes. I’ve found an awareness of Power distance index (PDI): The Power Distance Index is defined as “the extent to which the less powerful members of organizations and institutions (like the family) accept and expect that power is distributed unequally.” important. And Long-term orientation vs. short-term orientation (LTO): This dimension associates the connection of the past with the current and future actions/challenges. A lower degree of this index (short-term) indicates that traditions are honored and kept, while steadfastness is valued, key to understanding some group dynamics. More ideas can be found in these articles and books: Wikipedia: Cross Cultural Decision Making, and the book Advances Cross Cultural Decision Factors Ergonomics.

Its key that even the introverts who know why a decision is a poor or impossible choice gets heard by the group, independent of salary or positional power. If the decision is more technical than opinion, weight the technical voices in the room higher than the opinion voices.

Reducing Thrashing

To reduce elongated analysis time, I often nudge teams in the following directions –

  1. “Good for now” Agree for how long you are going to test the decision and revisit it for further analysis. Often by helping people remember a decision isn’t in stone, but for now, they overcome hesitancy to commit based on uncertainty.
  2. “Close the gap” Narrow in on a few actionable things. Even if you cant decide on the whole solution, can you agree on first steps. Often, the team realizes that most of the value in the decision is achieved.
  3. “Guard Rails” Identify what factors occurring invalidate any key assumptions and need the decision revisited. Helps people agree for now and feel that dooms-day scenarios are protected against.
  4. “Agree on Research” If agreement on the decision can’t be reached, identify what research inputs are needed to proceed and get a decision. Document what is in the way of reaching a decision and what data would clarify and get clarity or reduce uncertainty.
  5. And Sebastian Eichner (@stdout) mentioned another important tool. “Roll a dice and pick at random.” Often people find reasons why the one picked at random isn’t a viable choice, or if the decision is really that similar in risk and reward, its as good a choice as any! Use it to draw out opinions.

Its good to have teams make smaller, less risky decisions to practice putting contrary views in a productive way. Decision making is a skill to be built in a team, and a great indicator of team maturity.

The one final point often mentioned. “Who is responsible for a decision if one can’t be reached?” There is an eventual moment where King Rules needs to and should apply. If the cost of no decision outweighs the risk of moving forward, someone has to make the best decision they can. If thats you, and you are in a position of power you have a couple of acceptable choices –

  1. Delegate to the most informed expert, and say “which one, we need a choice and i think you have the most information” and then cover them if it goes badly.
  2. Break the deadlock. If two options are equally liked by different people, make it clear that no decision is worse and that you are going decision A for two or three months (as long as you need to see if it was likely right). By making it clear you are only stepping in because of the cost of no decision as a tie-breaker, you still give the team a good chance of making their own choices. If this is re-occuring, you need to make staff changes!


Read More

Does setting arbitrary goals (times or dates) work?

Posted by in Featured, Forecasting |

Problem in a nutshell: Work should be released when it reaches the quality needed with the features required. Of course small releases give the fastest feedback, and this post isn’t saying you should do larger releases. This post looks at whether setting a date or time goal does impact delivery.

Runners in the New York marathon finish in higher concentration just prior to hour and half hour and fifteen-minute elapsed time boundaries. Why? It is speculated that their is a mental race going on in each runner head and they try and achieve the next personal goal-post. Don’t they just run as fast as they can? Sure, but they also need something to pace themselves against in order to judge their ongoing pace and balance it against exhaustion (it should be noted i’ve never run a marathon!).

Clustering of finish times.

Clustering of finish times.

Its 1.4 times more likely to finish 3:59 than 4:01.

Its 1.4 times more likely to finish 3:59 than 4:01.

Having a goal in mind means that constant adjustments throughout the marathon help achieve finishing on goal. Whilst the runners can’t pickup half hour faster than personal best, they get early feedback they are off pace and adjust early to maybe reach a few minute(s) before a boundary.

I think the same needs to happen when we set goals for software delivery teams. They need constant feedback that they are on-pace for adjustment early – NOT cramming at the end. Having a date in mind is the only way to compare delivery pace of work versus a pace required to achieve that delivery without heroics. Heroics are failure. It puts teams in burnout mode and they fail to continue consistent pace after crunch making it impossible to reliably forecast. If I see a team moving into a feature or project have crunched in a prior delivery, I halve my throughput estimates for one to two times the crunch period. Its just NOT cost effective to have teams crunch.

My advice –

  1. If teams have crunched, reduce throughput estimates by 1/2 for 2 times the crunch period they endured
  2. DON’T use throughput samples during crunch mode. They are artificially high and cause crunch mode in the next plan!
  3. Set a delivery date and work out what team size and scope will fit into that period (using our spreadsheets of course :))
  4. Track delivery pace against this plan. The moment delivery falls behind, revisit the scope expected and communicate it is at risk. Get small actions taken earlier
  5. Track when teams are crunching versus sustainable. I put a C and a S in the notes of any throughput weeks I capture in our spreadsheets. Any team spending more than 10% year crunching is costing the company delivery pace and money. Compute this by estimating the salary of the team and computing what running half pace for 2x the crunch period costs.




Read More

Excel Spreadsheet Tips and Design Goals

Posted by in Featured, Reference, Tools |

We put a lot of effort into our free spreadsheet tools. We want our spreadsheets to be usable by anyone who needs an answer to their questions. Some good practices have become common in our tools and we want to share them with you as well.

No Macros or Add-Ins

A lot of what we do would be easier if we used Excel macros or add-ins. We resist the urge to do this. By including macros or add-ins we are shipping code and Excel gives all manner of warnings to the user that the spreadsheet may be a security risk. It also inhibits the spreadsheets being compatible on Google Sheets. We haven’t needed to use macros, and compete feature for feature against many paid add-ins. We are extremely proud to be performing Monte Carlo simulation and distribution fitting algorithms using plain formulas that YOU can see. Nothing is hidden. But gee, a lot of sleepless nights in this goal…

Title and Read me worksheet

Always have a title page worksheet that describes the spreadsheets intention, and who and how to contact us with ideas for improving them. Document the main steps in getting a result. Some of our spreadsheets are four or five worksheets deep and we want to avoid people becoming immediately lost in the appropriate workflow.

Data entry is by offset reference

Initial versions of our spreadsheets had a column for user data to be populated. What we found we needed was the ability to copy, paste, drag, delete, manipulate this data in the spreadsheet directly. However, whenever a column or cell was moved, our formulas referencing those cells broke. We solved this problem by using an indirect reference to “duplicate” the user data on another worksheet using Excel’s INDEX function, for example:


This formula references another cell by row number, and the row number doesn’t change with any user action you can do with the clipboard, dragging or manipulation. We write all formulas against the indirect copy of the original data in a worksheet commonly called “Calculations”. We copy down the formula as shown, where the A2 will be a series, A3, A4, A5 …

We standardized on a worksheet called “Your Data” which no formula every directly references. We find ourselves dragging columns of different data into this sheet and everything keeps just working.

Column References and Capacity Growth

No matter how big we make the spreadsheets support input data, someone always emails wanting more. We structure our formulas now to limit the places where an absolute row count is needed. Here are the ways we tackle this –

  1. Use column references A:A rather than A2:A5000 in formulas wherever possible
  2. Use names ranges, Formulas-Name Manager to define names for ranges. We use the names in formulas where possible
  3. When we need to handle a date range, we do 250 weeks or 5,000 day dates individually
  4. We always set the background color of a formula cell to the calculation cell type, so we can visually see where we stopped copying a formula row down. We also try and put a comment at that point saying how the user can expand it.

Top Left to Bottom Right Flow

We design our worksheets to be read and populated from the top left to bottom right. We also try and number each input sell title, and add a tip nearby. When we get a question we try and add more documentation to these tips. We would love feedback on where people get stuck. This is pretty standard user design guidelines.

Auto Chart Axis Growth & Shrink

A very hidden feature we utilize is how the charts automatically grow and shrink the axis values to match your specific data. By default, you specify a pre-determined range for chart data, but we don’t know in advance how many days or weeks your data contains. To auto-grow/shrink, we use a named cell range that starts at the first row of data for the column being chosen, and stops at the last row with valid data (not blank or zero is the normal rule). We bind this named range to the chart axis, and Excel takes care of the rest. For example, for cycle time a range formula CycleTimeRange is defined as –


This decodes to a range Calculations!L2:Lx where x is the last row before any zero date (excel counts empty dates as a zero integer value, we just know this by experimentation). In any chart data series, you reference this range like this (it has to be fully qualified) –

='Throughput and Cycle Time Calculator (5000 samples).xlsx'!CycleTimeRange

This technique allows us to handle any amount of user data and have the charts auto-resize. Its a little cumbersome to get working, but works great one you get it right.

Documenting Complex Formulas

On our more complex calculation sheets we try and add a not about each formula and how it works. Nothing is hidden in the formula bar, but some aren’t even clear to us after we have written and debugged them the first time.

Notes document formulas in calculation sheets.

Notes document formulas in calculation sheets.

We continually learn new things, and will post more tips over time. Please give us feedback on what we can do to make these spreadsheets easier to use.


Read More

Should I capture defect and un-planned work item data?

Posted by in Featured, Forecasting |

Problem in a nutshell: Historical data about delivery pace (throughput or velocity) contains work that isn’t part of the original backlog causing it to be a poor predictor of future delivery pace of planned work. Should we just capture planned items? Or Defects and un-planned work as well? Yes, capture it ALL, but be selective what data to use when forecasting.

Historical data is important for forecasting. We need to capture how the entire system has operated historically to avoid being misled by our own cognitive and wishful biases about how the future might unfold. The issue arises how we choose to use that data when not all of it is applicable for some reason. Defect work and un-planned work are common reasons some data should be omitted when forecasting some measures.

A common issue and most common question I get asked when using historical data is “should we count defects or interrupt (non-feature) work?” My answer is always Yes, you should capture every bit of data you can if its passively attainable (meaning, little or no extra effort). This does not mean you should use it all when forecasting.

When capturing item data, here are the ones I think are a must –

  1. The date that item was committed to being work on. This is a more important date than the date it was created in any tool, it might be the date it was pulled into a committed queue, or the date it was put into a sprint backlog.
  2. The date the item was delivered (ideal) or completed ready for delivery (ok, and most common).
  3. Whether the item was planned or un-planned work. This varies. My most common categories are planned, un-planned, defect.

These inputs are the three our free Throughput and Cycle Time Calculator use which you can download it here (Excel spreadsheet, no macros)

The data inputs used in our throughput and cycle-time calculator.

The data inputs used in our throughput and cycle-time calculator.

This spreadsheet allows you to customize what value in the original system maps to planned and un-planned work in the spreadsheet. It doesn’t handle both un-planned and defect, so not perfect and it will be updated to do both.

The setting tab allows customizing what data categories are planned an unplanned.

The setting tab allows customizing what data categories are planned an unplanned.

Different type of items help estimating and forecasting different things. Here is how we suggest using the data from our spreadsheet:

To forecast how long to deliver the rest of the feature or project backlog: Use the Planned item throughput adjusted by the observed split rate.

To estimate the planned item split rate: Use the amount of original planned backlog count divided by the planned completed backlog count (see our article on this here).

To estimate defect rates: Use the Defect items throughput rate. To calculate how many defects per planned item, divide the defect throughput by the planned throughput.

To estimate the interrupt or un-planned work rate or cycle times: Use the un-planned items throughput and cycle time.

A different throughput (items per week/sprint) shown for planned and un-planned work. Forecasting planned work? Use the planned throughput!

A different throughput (items per week/sprint) shown for planned and un-planned work. Forecasting planned work? Use the planned throughput!

Wanting to give a percentile SLA for unplanned work? Use the un-planned cycle-time, it may be longer or shorter than planned items, never the same!

Wanting to give a percentile SLA for unplanned work? Use the un-planned cycle-time, it may be longer or shorter than planned items, never the same!

Knowing what percentage of your work is planned or un-planned helps see where work is coming from over time. Confirming the mix is what you expected helps manage the delivery system.

Knowing what percentage of your work is planned or un-planned helps see where work is coming from over time. Confirming the mix is what you expected helps manage the delivery system.

Its important to understand how different items are ordered and prioritized by the teams through intentional policy. Making sure everyone knows why (and when) some types of work should be accelerated keeps the right work finishing earlier. Keep collection as light as possible, and make the team invested in proper classification and sign-off of the key dates by making the data accessible for process improvement and discussion. A good outcome of these discussions is a proposed change in policy to keep the most important items completing when needed.

Get the Throughput and Cycle Time Calculator spreadsheet we used in this article which you can download it here (Excel spreadsheet, no macros)

(its free, and uses no macros so its generally safe. Please consider answering the survey and sending us anonymous data so that we have a wider set of data to share with researchers.)

Read More

Dealing with the Close-out phase – When delivery is held hostage by a few un-finished items

Posted by in Featured, Forecasting |

Problem in a nutshell: During development of a feature or project work can often (although not always) be delivered faster with more people or teams. At some point in a project, most often near the very end, a few defects or missing features remain unfinished to a releasable quality. These items block the release, but CANNOT be accelerated by adding more resources or cutting scope (they are part of the minimum viable set of features). To forecast delivery date of this phase of a project or feature, you must consider the longest individual item forecast, not the backlog driven probabilistic forecast.

Near the end of a delivery cycle work is blocked by a few remaining items, either defects or agreed necessary completion of features. Much of the delivery system will appear idle at this time but these items don’t reduce time with added people. They are staffed to the necessary level and all viable ideas for accelerating them have been implemented. This phase of a delivery I call “Close-out.” Its where how much work remaining matters less than how long the longest single remaining item will take.

I find probabilistic forecasts (eight out of ten of mine) reliably predict a completion date EXCLUDING this phase. When i’m wrong, i’m wrong because i’ve not adequately managed or modeled this part of a project or feature delivery. My only mitigation is to minimize or avoid this phase by ensuring done means done, and by putting effort into excluding risky outcomes earlier in the project. Its a significant issue especially for large installed applications and user bases where regular releases still aren’t as common as they should be.

How to tell you are entering the Close-out Phase

First step in understanding the close-out phase is recognizing when you are entering it. I look for a point where work-in-progress for the entire team or organization decreases below its 75th percentile. A key point of close-out is that the number of people who can work on the remaining items are at saturation levels, so the remaining people help, but sit idle. In reality, its not that simple to see. Idle people aren’t tolerated (although they should, but that’s another blog post), they move onto the next release or do technical debt removal or needed refactoring. This is healthy, but not pertinent to the current deliverable. I exclude these and look for the actual work in progress at an item or person level for the deliverable held hostage. When this is down 25% from its highest, I assume if 1 in 4 people are doing other stuff, then the system is saying we are time-based not scale-based in throughput. I use this 1 in 4 people to manage and mitigate future close-out risk.

How to manage the Close-out phase

I capture a list of all of the remaining items blocking release. Using the ¼ of the people NOT directly involved in the close-out items but still allocated to the project, i give them the mission of brainstorming items that still may crop up (to avoid worsening the problem), and to be available on request for immediate code reviews and testing the close-out items. I also collect ideas as to how these items may have been detected earlier to minimize this impact earlier. Ideally, this brainstorming should be constantly performed be all team members.

Close-out item causes and mitigation

Forecasting in advance is difficult, the best process minimizes or avoids these close-out items be looking and dealing with them earlier. When there is no data for the proposed project I look at other projects this team or organization has done.

I particularly look for external players who can block a release. My most common reasons are –

  1. External parties have a final go or no-go decision, and they give late feedback
    • Know who has the final go and no go decision – if its external incentivize them to help earlier
    • Get sign off as early as possible; give these people previews and hand-hold them
    • Learn how they will test and decide the system is “good,” for example, get them to supply you with THEIR test data and cases during development
  2. Performance testing is performed at the very end and the feature fails required performance.
    • Agree what impact is acceptable for the new feature or project from the prior
    • Build enough of the system to do performance testing early and retrofit changes earlier if it fails
  3. Edge Cases. Browser versions (Internet explorer in the old days), operating system updates (android and iOS particularly)
    • Learn what browsers and operating systems are essential. Ask sales and marketing what the top 10 customers use and make sure you do these!
  4. Localization and Internationalization
    • Late changes to text means late response to language translations. Get FINAL text signed off early.
    • Don’t wait to the end of the project for someone to check German number and date formats!
  5. Defects
    • Keep defect count low. Keep things at known point of stability, and encourage that point be releasable (thanks Michael Tardiff for this terminology)
    • Rigorously get defects discovered. Schedule bug bash days to shake out the defects early. Celebrate finding defects, and then get them fixed. Biggest risk is that you just think you are in a known stable and releasable point.

Sitting down with a team early and looking for items such as these and avoiding them is the only way to minimize the final impact and allow forecasting to be reliable. Do brainstorming session specifically on these items often by asking the simple questions “What could stand between us and release of this product or feature tomorrow?” – its my standard stand-up question, or the first question I have for anybody I meet in the hallway.


Read More