A manufacturer of prefabricated homes has decided to subcontract four components of the homes. Several companies are interested in receiving this business, but none can handle more than one subcontract. The bids made by the companies for the various subcontracts are summarized in the following table.

Bids by Companies (in $1,000s) for Various Subcontracts

	Company
Component	A	B	C	D
1	375	389	425	445
2	200	190	175	225
3	330	320	315	300
4	185	225	193	207

Assuming all the companies can perform each subcontract equally well, to which company should each subcontract be assigned if the home manufacturer wants to minimize payments to the subcontractors? (Let

A_i = 1

if contract i is awarded to company A and 0 otherwise. Let

B_i = 1

if contract i is awarded to company B and 0 otherwise. Let

C_i = 1

if contract i is awarded to company C and 0 otherwise. Let

D_i = 1

if contract i is awarded to company D and 0 otherwise.)

(a)

Formulate an LP model for this problem to minimize cost (in $1,000s).

MIN:

Subject to: assigning subcontract 1

assigning subcontract 2

assigning subcontract 3

assigning subcontract 4

total number of subcontracts assigned to Company A

total number of subcontracts assigned to company B

total number of subcontracts assigned to Company C

total number of subcontracts assigned to Company D

A₁, B₁, C₁, D₁, A₂, B₂, C₂, D₂, A₃, B₃, C₃, D₃, A₄, B₄, C₄, D₄, 0

(b)

Create a spreadsheet model for this problem and solve it using Solver. What is the optimal solution?

(A₁, B₁, C₁, D₁, A₂, B₂, C₂, D₂, A₃, B₃, C₃, D₃, A₄, B₄, C₄, D₄) = ( )