Microsoft Office Tutorials and References
In Depth Information
Exhibit 8.18 Calculation worksheet for 250 days of operation
in the cell comment for B254, a pair of VLOOKUP()s with a lookup value argu-
ment of RAND() determines the hourly demand in each of the two hours. The cell
formula uses the Arrival Data table in the Brain , B7:H18, to randomly select two
one-hour arrivals. (Recall that each period is 2 hours in length and the arrival rate is
hourly, thus the need for two lookups.) It is possible to calculate one hour’s arrivals
and multiply by two, but this will exaggerate a single hour’s behavior.
There are three categories of demand (Corporate Client, Police, and US Mail)
and hourly arrivals for each: columns B, D, F, H, J, and L. The arrival order is
calculated for each client in a time period in the adjacent columns: C, E, G, I, K,
and M; thus, in the Day 1 row (A5:P5), we find that in the 5:00–7:00 time period
the number of arrivals for the Corporate Client is 6 (B5) and they are the 1 st (C5) to
arrive. For the US Mail arrivals in the 7:00–9:00 time horizon, there are 0 arrivals
(L5) and they are the 2nd (M5) arrival. Thus the number and sequence of arrivals
for the first simulated day is:
a) 5:00–7:00: Corporate Client
=
6
...
.Police
=
2
...
.USMail
=
1
...
.subtotal 9
=
...
=
...
=
...
b) 7:00–9:00: Corporate Client
2
.USMail
0
.Police
0
.subtotal 2
=
c) Thus, Total Day 1 demand
11
The logic used to determine the sequence of arrivals is shown in the cell M9
comment of Exhibit 8.18. The formula consists of an IF with four additional nested
IFs, for a total of five. In the first condition, a random number is referenced in cell
P9, as well as all of the nested IFs. This is done to make the comparisons consis-
tent. Remember that every RAND() placed in any cell is independent of all other
Search JabSto ::




Custom Search