DIFFERENT QUESTIONS PLEASE READ

b) Solve the resulting problem in Excel Solver. c) If you could get more material, how much would you like? How would you use it? What would you be willing to pay? Answer using excel sensitivity report. Hi-precision company produces lenses for special purpose devices such as long-range cameras and telescopes. This company produces four type of lenses in three plants. Each type of lenses can be produced at each plant, but each plant has its own restric- tions and costs. Product Resource constraints are given in Tables 1-3 for each plant. The company has 3500 pound of raw material in the inventory. The company has three major costumers (A, B, C). For each customer, the maximum sales of each product is given in Table 4. the product sale prices and shipping costs are given in Table 5 and Table 6 respectively. The production cost for each plant-product pair is given in Table 7. Customer A and B require special inspection on products those are shipped from plant 1 and plant 2. These product are inspected at a center, then sent to their destinations. The capacity of this center is 1500 units. Products Product 1 Product 2 Product 3 Product 4 Total available Table 1: Product-Resource Constraints: Plant 1 Resources Labor (Hours/Unit) Machine (Hours/Unit) Material(Lb./Unit) 3 8 1.0 3 8.5 4 9 1.2 4 9 1.3 6,000 10,000 1.1 3.5 1.1 Products Product 1 Product 2 Product 3 Product 4 Total available Table 2: Product-Resource Constraints: Plant 2 Resources Labor (Hours/Unit) Machine (Hours/Unit) Material(Lb./Unit) 7 3.5 7 1.0 4.5 8 1.1 4.5 9 1.4 5,000 12,500 Products Product 1 Product 2 Product 3 Product 4 Total available Table 3: Product-Resource Constraints: Plant 3 Resources Labor (Hours/Unit) Machine (Hours/Unit) Material(Lb./Unit) 3 7.5 1.1 3.5 7.5 1.1 4 8.5 1.3 8.5 3,000 6,000 4.5 1.3 Table 4: Maximum Product Sales (Unit) Customers Products Customer A Customer B Customer C Product 1 200 400 200 Product 2 300 300 400 Product 3 500 200 300 Product 4 200 400 300 Table 5: Product Sales Price (S) per Unit Customers Products Customer A Customer B Customer C Product 1 17 16 16 Product 2 18 18 17 Product 3 22 22 23 Product 4 29 26 27 Table 6: Shipping Cost ($) per Unit Customers Plants Customer A Customer B Customer C Plant 1 1.0 1.6 1.1 Plant 2 1.2 1.5 1.0 Plant 3 1.4 1.5 1.3 Table 7: Product Cost ($) per Unit Plants Products Plant 1 Plant 2 Plant 3 Product 1 14 13 Product 2 16 17 15 Product 3 20 19 Product 4 24 23 14 18 26