A key product of a local company, is assembled from three major Components L, M, and N. Each unit of Product X requires1 unit of L, 2 units of M and 3 unit of N. For the coming quarter, the company plans to make 5,000 units of the product. All three components may either be made in house or purchased from a supplier. In-house production costs for each of components L, M, N are $76, $23, and $13, respectively. Alternatively, they can be purchased from the supplier at $102, $30, and $15per unit.

For in-house production, each component must be processed in three departments: Department1, Department2, and Department3. The time (in minutes per unit) required to process each component in each department is given in the following table.

L M N

Department 1 4.4 3.4 0

Department 2 7 2.6 1.6

Department 3 6.2 5.2 3.4

Departments 1, 2, and 3 have 800, 700, and 900 hours available to produce these components, respectively, in the coming quarter.

1) Formulate the problem as a spreadsheet LP model.

2) Solve the problem using the Solver. Also generate the sensitivity report.

3) How many of each component should be made in house and how many should be purchased in the optimal solution?