HW 2 Part 2- Please complete all work in excel. Use excel to make the calculations (cells can be clicked on to view any formulas used) and be sure to identify your answer, including units. Upload one excel file for your submission. (70 pts) 1) XYZ Company is designing a product layout for a new product. They plan to use this production line 12 hours a day in order to meet forecasted demand of 1500 units per day. For output to equal forecasted demand, what should be the actual cycle time for this production? (10 pts) 7) The manager for sales contractors wants to construct a control chart for determining whether the 10 contractors are under control with regard to the number of sales made. Accordingly, the manager sampled 100 days from last year for each contractor, with the following results: Contractor Number of Sales A 50 B 90 70 D 80 E 110 F 60 G 105 H 79 69 98 - - 2) A company needs to locate three departments (X, Y, and Z) in the three areas (I, II, and III) of a new facility. They want to minimize interdepartmental transportation costs, which are expected to be $.40 per load per meter moved. An analyst has prepared the following distances and flow matrices: Distance (meters) Flows (loads per week) From/To II III From/To X Y Z 20 40 X 100 80 II - 10 Y 70 120 III z 80 60 - In what areas, I, II, and III, should departments X, Y, and Z be located that minimize the total cost of moving loads over the distance? (Identify assignments and total cost) (15 pts) I - J Analyze these data using a control chart that would provide for a 98% confidence interval, identifying the control limits and contractors, if any, that are outside the control limits (20 pts) 3) A job had an observed time of three min., a performance rating of 70 percent, and an allowance factor of 15 percent of job time. Normal time for the job in min. is: (5 pts) 4) A job had an observed time of four minutes, a performance rating of.80, and an allowance factor of 15 percent of job time. Thirty cycles were timed. Standard time for the job in minutes is: (5 pts) 5) A location analysis has been narrowed down to three locations. The critical factors, their weights, and the ratings for each location are shown below: Location Factor Wt. A B C Labor Cost .3 60 70 80 Transportation Cost .3 80 90 70 Market Access .1 70 70 60 Utility Cost 2 60 80 70 Raw Materials Cost .1 90 80 60 What is the composite score for location B? (5 pts) 6) Determine the center of gravity for the following destinations and shipping quantities: (10pts) Destination(x) Quantity A (2,6) 700 B (1,5) 400 (3,3) 600 D (3,8) 700