Microsoft Office Tutorials and References
In Depth Information
number of service requests to the index numbers in range F3:W3 (1–18). Note
that 18 columns are provided to insure that a very large number of random arrivals
are possible. It is very unlikely to ever experience 18 arrivals for any service type.
For Mechanical Diagnostics , a maximum of 11 arrivals are permitted, and for Oil
Change , a maximum of 16 are permitted. The cell function used to determine the
service time value of Engine/electrical Diagnostics is a logical IF() with an embed-
ded VLOOKUP. An example is shown in cell I10 of Exhibit 8.22. The IF() tests if
B10 is greater than or equal to the index number in its column, 4. If this condition is
true, which it is, then the cell formula in cell I10 will randomly sample the Service
Time Distributions table in the Brain via a VLOOKUP and return a value.
If the index exceeds the total number of service requests, a 0 is returned. Note
that cell J4 appears blank, although it is 0. This is accomplished by using conditional
formatting for the cells. A logical test of the cell content—equal to 0—is made, and
if the answer is to the test is true, then the cell and font colors are set to similar
colors, thus resulting in a blank cell. This is done for clarity, and zeros could easily
be allowed to appear without any loss of accuracy. The same is repeated in all other
ranges for the various service types. By depressing the recalculate 3 key, F9, the
contents of the three service areas change as new service arrivals are calculated in
the Calculation worksheet.
Now, let us consider the analysis that is done in the Data Collection worksheet.
In column range AY:BF of Exhibit 8.23, we perform the calculations necessary to
determine if daily service demand is satisﬁed by available service capacity. The time
available for service is the difference between the total time available from 9:00
am to 7:00 pm, 600 minutes, and breaks, lunch, and set-ups. Each row determines
the sum of service time requested in a particular day for each service type (e.g.
AY4:BA4) then compares the request to available service time. For example, for day
1 the available Engine/electrical Diagnosis time is 440 minutes, which is calculated
by subtracting breaks (60), lunch (60) and set-up times for each service request
(4 ∗ 10
40) from a total of 600 minutes. These times are found in the Wor ke r
Assumptions table in the Brain . Thus, daily breaks and lunch always account for
120 minutes, while the set-ups sum will vary according to the number of service
requests, 4 in this case requiring 10 minutes each. The total is 160 minutes, and
this results in 440 minutes of available service capacity (600 – 160
440 minutes of capacity is greater that 160 minutes of Engine/electrical Diagnosis
requested, there is sufﬁcient capacity to deal with demand, thus no stock-out occurs
for that service type.
In row 14 of Exhibit 8.23, which corresponds to day 11 of the simulation, we
see that a service stock-out occurs for Engine/electrical Diagnosis . The comment
associated with the cell BC14 shows an IF() cell formula that compares available
capacity of 400 minutes to a capacity demand of 440 minutes. Obviously, there is
3 I would suggest setting the recalculation of formulas to manual in all worksheets. This gives you
control and eliminates annoying and inopportune recalculations. The control can be set by using
the Tools then Options menus. One of the available tabs is Recalculation and this is where you