Max Alejandro Molina
Member
Hello Excel Gurus, I have been trying to develop an approach to try to get the cicle time of stores which implicates the days a store generates product orders and the days the products are delivered.I have calculated it manually in yellow columns. This can be achieved manually by doing the following 3 simple steps; first we have to get something called revision date:
1. Revision Time: a store may have several generation days and we have to manually test for the longest time between all generations for instance if a store generates in MONDAYS and THURSDAYS then we can say that from MO to TU there are 3 days, but from TU to MO there are 4 days so we get the 4 (the max) and that will be the revision date, sometimes the stores only have 1 generation date which means to always be 7 days of revision.
2.Lead Time: taking the previous example now lets say this store delivers the product on WEDNESDAYS and SUNDAYS, now we will compare the time between MONDAYS-WEDNESDAYS (Generation vs Delivery) which is 2 days and then the time between THURSDAYS and SUNDAYS (Generation vs Delivery) which is 3 days, and then we will get the biggest which is 3 days.
3. At this point we have Revision Date: 4 days and Lead Time: 3 days, now we simply sum them up and we get a cicle time of 7.
Stuff to consider:
*the number of generation days will always match the number of deliveries.
*store may generate even 5 days
*Macros can be used
Thank you for the help, and hopefully this can be achieved with formulas, if you have any questions let me know!
1. Revision Time: a store may have several generation days and we have to manually test for the longest time between all generations for instance if a store generates in MONDAYS and THURSDAYS then we can say that from MO to TU there are 3 days, but from TU to MO there are 4 days so we get the 4 (the max) and that will be the revision date, sometimes the stores only have 1 generation date which means to always be 7 days of revision.
2.Lead Time: taking the previous example now lets say this store delivers the product on WEDNESDAYS and SUNDAYS, now we will compare the time between MONDAYS-WEDNESDAYS (Generation vs Delivery) which is 2 days and then the time between THURSDAYS and SUNDAYS (Generation vs Delivery) which is 3 days, and then we will get the biggest which is 3 days.
3. At this point we have Revision Date: 4 days and Lead Time: 3 days, now we simply sum them up and we get a cicle time of 7.
Stuff to consider:
*the number of generation days will always match the number of deliveries.
*store may generate even 5 days
*Macros can be used
Thank you for the help, and hopefully this can be achieved with formulas, if you have any questions let me know!
Attachments
Last edited: