I have a practical problem at my workplace for which I have been
racking my brains out for arriving at a feasible solution through
excel .

In our factory we receive coal through Coal Rakes. Each rake
comprises of 60 Wagons. The monthly intake of Coal Rakes is 18, i.e.
(18 X 60) Wagons per month. The coal is unloaded by operators who work
in in Shifts mainly Morning, Evening & Night. Number of Operators who
are involved in the above work = 49

Each Operator works on a 7 day shift- cycle pattern i.e. 2 Mornings,
2 Evenings, 2 Nights, 1 Off. The shift rota is accordingly offset such
that on any day, we have a book strength of 14 Operators for Morning,
14 Operators for Evening, 14 Operators for Night and remaining 7 are
enjoying their weekly Offday. Additionally each operator does 2 shifts
overtime in a month.

Practically, considering 30 days in a month, an operator is available
for 24 shifts in a month. ( - 4 days Off, - 4 days Sick and C/L, + 2
shifts Overtime )

Coal rakes are received throughout the month but with no definite
pattern. On some occasions rakes may be received one after the other
and sometimes there may be a gap of 1-2-3 days. But on an average the
monthly intake is 18.

Presently 18 Rakes or (18 X 60) wagons are taking about 48 shifts in a
month for unloading. or 2.66 shifts per Coal Rake. or 22.5 Wagons per
shift that too more or less equally distributed in ( 16 Morning, 16
Evening 16 Night shifts)


Question 1: Considering the above situation and data, is it possible
to theoritically calculate on an average, how many wagons or wagon-
shifts are available for an operator in a month ?


Question 2: Suppose there is a reduction of the no. of shifts for
unloading the same no. of wagons from the present 48 to 36, i.e. a
coal rake shall be unloaded in 2 shifts. ( or 30 wagons per shift)

then
In such a case what how shall I calculate the average
no. of wagon-shifts @ 30 wagons, available to an operator in a
month ? or.

What would be the probability or percentage chance
that an operator would atleast get 12 wagon-shifts @ 30 wagons each
shift for unloading in a month.


Hope that I have been able to explain the problem clearly. Eagerly
waiting for an expert help.

Thanks