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 –
- 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.
- 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.
For recommendations about data capture of cycle time and lead times, we suggest –
- Capture date started (committed to start delivery), date completed and date captured as an option to be considered (often, date created).
- Store cycle time data as dates, don’t convert to days until the last moment you need to.
- 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.