Waiting time calculation

Hi All,

I'm struggling with the following calculation. It seems straight forward, but there is something that I am missing

Daily we have 122 sales vehicles that return to base for reloading of goods

The sales vehicles return to base with a different frequency;

From 5 pm - 6 pm : 35-40 will arrive to base (average of 37.5)
From 6 pm - 7 pm : 35-40 will arrive to base (average of 37.5)
From 7 pm - 8 pm : 30-35 will arrive to base (average of 32.5)
From 8 pm - 9 pm : 12-17 will arrive to base (average of 14.5)
- The average calculatation adds up to 122 in total

The loading of each vehicle takes on average 1 minute and 54 seconds. (Spot checks has shown, that no. 20 in line, spends around 38 minutes before his turn is up for loading.)
(explanation follows)

To complicate things further, the sales vehicle goes through 3 steps before they can return to marked.
1. Stock taking of returned load
2. Unloading of empty packaging
3. Loading of new fresh products

The first returned vehicle will spend around 1 minute having its stock counted, and afterwards the unloading and loading will be done by forklifts, hence a total service time of around 2 1/2 - 3 minutes.
While the first vehicle is being unloaded and loaded the 2nd vehicle can have its stock counted, meaning that that time spend on stock taking on the second vehicle will not affect its total "service time" as process no. 1 can be done simultaneously with process no. 2 & 3.
- But keep in mind that a total of 38 minute will pass before vehicle no 20 in line will be in front of the line, ready to get unloaded and loaded (the stock counting has happend already at this point)


1. Please explain to me how I can calculate the total waiting time for all vehicles and what an average waiting time would be.
2. Further, instead of vehicles arrive in a steady pace, how will the calculation be if all 35-40 vehicles had returned at exactly 5 pm?

I would appriciate if your answer include explanation of the "data" used also, and not only the result. In this way I would be able to use the same sheet (with formulars) when changes will happend to this operation and therefore also these times.

Thank you very much in advance

Regards
mojupe

Hi MarvinP

Thank you for the welcome!

Your hit the nail on the head in your response, as this is a project, looking into improving the operation, optimizing the processes and finding the bottlenecks and shortages at the present situation.

I just can't get my head around finding a way to calculate this "queue-issue", so I might have to go the long and hard way and have a team look into this and manually time all vehicles in line.

Further, right you are that this was not a specific excel problem, but when we have the data, I'll get back to this if still in problems with the calculation

Thank you for your reply

/Mojupe