• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula - Cycle Time

Hello Excel Ninjas,


I have been trying for so long to try to come up with a formula to calculate the "cycle time" specifically now for 4 months, unfurtunately with 0 succes....


The manual formula is: FR(Revision Frequency) + DT (Delivery Time) = CT (Cycle Time)


FR: If a store generates purchase orders in Tuesday and Friday, that would be a FR of 4 because you need to compare how many days are in between from one generation day to another and get bigger number. (Sometimes there will be stores that generate in 3 days per week). In this cases from Tue to Fri there are 3 days and from Fri to Tue there are 4 days which is longer (or bigger number).


At this point we have: FR(4) + DT (Delivery Time) = CT (Cycle Time)


In order to get the DT the procedure that needs to be done is:


The store Delivery days are: Monday and Thursday and generates purchase orders in Tuesday and Friday as previously said, so now we compare the days from purchase order generation days and delivery days; from TUE(generation) to THU (Delivery) there are 2 days and from FRI (generation) to MON (delivery) there are 3 days which is the bigger number (This is our Delivery time number).


Now we have FR(4) + DT(3)= CT(7 days!!!!!!)



Unfortunately I cannot do this with excel -_-


Can anybody help me????


Please look at the file with the results done manually.
 

Attachments

  • Excell Challenge.xlsx
    13.8 KB · Views: 1
Hi Max ,

But in the data range , there is no separation of the generation and delivery days ? Can you explain how, given the numbers in A2 through K4 , this data can be derived ?

Narayan
 
Narayank,
Sorry i was missing a column, please just take into account only the green columns. The info below is my input from a database i just need the other columns to have a formula.

store area code Store Name Generation Delivery
10 Store A TUFR MOTH
10 Store B TUFR MOTH
10 Store C TUFR MOTH
 

Attachments

  • Excell Challenge.xlsx
    14.4 KB · Views: 5
Hi Max ,

Things are still not very clear.

I can understand that if the generation days are Tuesday and Friday , then the interval from one Tuesday to the Friday of the same week is 3 days while the interval from one Friday to the Tuesday of the next week is 4 days.

However , when we come to delivery , there are 4 combinations possible ; if we assume that the delivery days are Monday and Thursday , they are :

Tuesday of one week to Monday of the next week - 6 days

Tuesday of one week to Thursday of the same week - 2 days

Friday of one week to Monday of the next week - 3 days

Friday of one week to Thursday of the next week - 6 days

How do we choose one combination out of these 4 ?

Narayan
 
Sorry Narayank, I keep ommiting information. Well if a store generates a purchase order on Tuesday, the order will deliver at the closest delivery day in this case thursday and if it generates a purchase order on Friday the order will deliver at the closest day which is Monday. Now we can see the intervals are Tuesday to thursday (2 days interval) and Friday to Monday (3 days interval). Which at this point we always choose the biggest (3 days interval).In other words if we have 2 different generation days we will be having just 2 combinations; TU-TH & FR-MO.
 
Narayank,

Your approach is good, but in the cell N2, did not provide the right number (4) instead it provided 3. I came up with a solution derived from you altough it is too big.

Please have a look a the "Final!" Sheet and where I list all the posible kinds of stores, in this case 1,2,3, and more generations.

Here I have a few lines to consider:

*Bottom Line: The number of generatios is always equal to the number of deliveries

Rule 1: *Store type "D" will always have a FR=2 and DT=2, and therefore Cycle Time= 4. In addition to this rule; no complex calculations need to be done since it is always the same output when it meets the characteristics mentioned in this rule.

Rule 2: Cycle time is always equal to FR + DT.


I need help with a formula that resolves type C stores, and also another formula for all DT but type D stores(because for these stores it is always the same one:2). Also keep in mind that all the data from E2:F11 is entirely right since I did them manually at first)
 
Hi Max ,

I think there is some misunderstanding ; the workbook I uploaded does not use columns N , O and P.

The calculations have been spread over the columns Q through Z , with the final output in column AA.

Narayan
 
Hi Max ,

See the following file , with the new data for Type B stores. Obviously , the same formulae cannot be used for Type A stores , since there are only single days for generation and delivery.

Narayan
 

Attachments

  • Excel Challenge_test.xlsx
    13.8 KB · Views: 3
Hi Max ,

Can you please wait till tomorrow ? I was out of town , and have returned a few hours back. I will upload your file tomorrow.

Narayan
 
Back
Top