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 ﬁnd 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 ﬁrst 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 ﬁve. In the ﬁrst 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