The first step in analyzing the check-in process is to enter the available data, as shown below.
It is important to choose an appropriate time unit and use it consistently throughout the analysis. As you can see, from row 8 down, a minute is used consistently as the time unit.
It is good practice to distinguish between spreadsheet cells that contain numbers (the "data") and cells that contain formulas. We have done this by using boldface font for cells that contain numbers.
Converting the data into a format that is suitable for the QTP functions requires the following preliminary calculations.
Fraction of total arrivals: We have entered 30% in cell C2. In cell B2, we entered the formula "=1-C2". We entered a formula instead of a number in cell B2 to facilitate later analysis where the fraction of total arrivals that use the check-in terminals is varied.
Arrival rate (per hour): Cell B3 uses the formula "=$D$3*B2" to calculate the arrival rate, per hour, to the check-in agents system. This formula can be copied to cell C3 to calculate the arrival rate, per hour, to the check-in terminals system.
Avg. interarrival time: This is the average time between successive arrivals. Cell B4 uses the formula "=60/B3" to calculate this average time, in minutes. This formula can then be copied to cell C4.
Arrival rate (per minute): Enter "=1/B4" in cell B8 to calculate the arrival rate, per minute, to the check-in agents system. Copy this formula to cell C8 to calculate the arrival rate, per minute, to the check-in terminals system.
Service rate: Enter "=1/B6" in cell B9 and copy this formula to cell C9, to calculate service rates per minute.
Stdev. arrivals: The QTP functions that we will be using will require the standard deviation of the interarrival time distribution as input. This information is not available to us. A reasonable default assumption is that the standard deviation of the interarrival times equals the mean interarrival time, which we have already calculated. To allow flexibility in further analysis, we have entered the assumed coefficient of variation (= standard deviation / mean) of the interarrival time distribution in cells B5 and C5. We can then calculate the standard deviation of the interarrival time distribution for arrivals to the check-in agents in cell B11 as "=B4*B5" (= mean * coefficient of variation). This formula can then be copied to cell C11.
We have organized the arrival rate, service rate, number of servers, the standard deviation of the interarrival times, and the standard deviation of the service times in a contiguous block of cells (starting in row 5), because these are the five arguments that will be needed by the QTP functions we will be using. It is not necessary to do this, but it will make it easier to enter some of the later formulas.