Question

Jack Williams operates a small mechanics shop in his hometown, Lima, Ohio. He works six days a week, Monday thru Friday, 10:00 AM to 6:00 PM and Saturdays from 9:00 AM to 12:00 PM. On a regular weekday, customers’ arrival time is exponentially distributed with a mean of 45 minutes and service time is also exponentially distributed with a mean of 35 minutes. Simulate Jack Williams’ shop for 100 customer arrivals to estimate Average Time in Line and Average Time in System.

I need the solution by using EXCEL SOlver with snapshot as well

Answer 1

As we know that we don' t have an EXPONENTIAL inverse function in Excel to generate a random number, we will use the GAMMA distribution in Excel to simulate Exponential random number.

Note: EXPON.INV(RAND(), λ) function in Excel is the same as GAMMA.INV(RAND(), 1, 1/λ)

Repeat the following formulation in Excel:

Then compute:

Average Time in Line = Average of the numbers in column E (will vary based on the random numbers)

Average Time in System = Average of the numbers in column I (will vary based on the random numbers)

Average waiting time	113.0
Average time in system	152.5