Microsoft Office Tutorials and References
In Depth Information
Exhibit 8.22 Allocation of service and service time determination
respectively. Finally, Columns AY:BF determine the availability of service, provid-
ing the modeler with an opportunity to detect a service stock-out ; 2 that is, a day for
which some demand is not satisﬁed by available service capacity. If a stock-out con-
dition is observed, it is because the mechanic stationed in a bay is unable to service
all daily demand for that particular service type. In summary, the Data Collection
Worksheet has: (1) an initial allocation of overall demand to speciﬁc service types
( Engine/electrical Diagnosis , etc.), (2) a determination of actual times, in minutes,
for the service types, and lastly, (3) a comparison of the daily service time requested
versus the daily service capacity available from a mechanic.
Now, let us take a closer look at the Data Collection worksheet. Exhibit 8.22
focuses on areas (1) and (2) in the summary above. As mentioned, columns B
through D allocate the total daily demand over the three service categories. Cell E4
references the Calculation worksheet cell N5, the Daily Total for day 1. Cell range
B4:D4 allocates the total, 11, on the basis of the deterministic percentages located
in the Brain in the Type of Service table. Recall that Engine/electrical Diagnosis
is constant at 40% of the arrivals. Therefore, 11 arrivals (E4) multiplied by 40%
(G23 in the Brain ) produces approximately 4 Engine/electrical Diagnosis service
requests. This number is rounded to account for integer values for arrivals. Similarly,
3 Mechanical Diagnosis service requests and 4 Oil Changes are produced. Then
Oil Changes are calculated as the difference of the Total Clients and the rounded
Engine/electrical and Mechanical Diagnostics service requests, to insure that the
sum of all service types requested is equal to the total, 11.
Next, column range F4:W4 generates 4 service times. Logic is needed to select
only as many service times as there are arrivals. This is done by comparing the
2 A service stock-out is deﬁned as a daily period for which there is demand for service that is not
met. It does not suggest the size of the stock-out. It is simply a stock-out occasion, without regard
to the number of autos that do not receive service.
Search JabSto ::

Custom Search