Question

The Sharps Electrical Electronics Corporation manufactures four highly technical products that it supplies to aerospace firms that hold BGP contracts. Each of the products must pass through the following departments before they are shipped: wiring, drilling, assembly, and inspection. The time requirement in hours for each unit produced and its corresponding profit value are summarized in the following table:   

The production available in each department each month, and the minimum monthly production requirement to fulfil contracts, are as follows: 

5   

The production manager has the responsibility of specifying production levels for each product for the coming month. Required: Using Excel for Windows, generate all the necessary reports to present your solution. (a) Help him by formulating (that is, setting up the constraints and objective function) Sharps Electrical’s problem using LP.                                                                                     (15 Marks) (b) Using Excel QM, find the optimal product mix. (10 Marks) (

Answer 1

Let denote the number of product XJ, denote the number of product XM, denote the number of product TR, and denote the number of product BR.

Using the values in the problem, the goal is to maximize the total profit, then the LP problem is given by

Using QM for Windows, based on the output,

We find the optimal solution is

It indicates that the company should produce 20,700 units of product XJ, 100 units of product XM, 2,800 units of product TR, and 300 units of product BR. And then the maximum profit is given as $232,800.