Problem Description:
The data given is a representative sample of data from a typical job shop. The job shop has 10 primary resources – A through J, which are utilized to process customer orders. The firm specializes in customized orders, each of which may require a unique way of processing through the shop floor.
Once the orders are accepted, the raw material needed for processing the order is obtained. A customer order may require up to 3 different kinds of raw materials, from a set of eight different categories of raw materials (S1 to S8). These are listed in columns D, E and F.
Customer orders also vary in volume. The order size for each order is mentioned in column G.
The orders then move through the different resources as per their requirement. This data can be found in the columns H to AE. A setup time and run time are associated with each job-resource combination, and can be found in these columns after every resource ID.
For example, for job J000001, the first resource used is resource G, with a setup time of 35 minutes and a runtime of 1 minute per unit. As the order size is 200, the time that this job spends at G is 35 + 200 x 1 = 235 minutes
Columns AF and AG contain the output metrics, i.e, delivery delay and customer quality ratings.
The second excel sheet contains the weekly capacities for each resource in the system.
Using Python, analyze the following
A) Weekly resource utilization
B) Any patterns between stock usage and delivery delay? Top decile or top quartile?
C) Any patterns between stock usage and customer rating? Bottom decile or bottom quartile?
D) Any patterns between resource utilization and the delivery delays? Top quartile
E) Any patterns between resource utilization and customer rating? Bottom quartile
F) Any patterns between stock usage and resource usage?
G) ABC analysis on stock usage? Weekly stock usage different than overall stock usage?
