Question

Create a model and use Excel Solver to answer the following: A computer company manufactures two types of computers. Each type of computer will require assembly time, inspection time, and storage space. The amounts of each of these resources that can be devoted to the production of the computers is limited. The manager wants to determine the quantity of each computer to produce to maximize the profit generated by sales of these computers.

In order to develop a suitable model of the problem, the manager has met with design and manufacturing personnel. As a result of those meetings, the manager has obtained the following information:

	Type 1	Type 2
Profit per unit	$60	$75
Assembly time per unit	4 hours	10 hours
Inspection time per unit	30 minutes	20 minutes
Storage space per unit	3 cubic feet	3 cubic feet

The manager has also acquired information on the availability of resources. These daily amounts are:

Resource	Amount Available
Assembly time	100 hours
Inspection time	22 hours
Storage space	39 cubic feet

The manager also met with the firm's marketing manager and learned that demand for the microcomputers was such that whatever combination of these two types of computers is produced, all the output can be sold.

a. What is the mix of computers that the company should produce if they want to maximize profits?

b. What is the optimal value for profit using the mix from part a.?

c. If type 2 computer became twice as profitable (i.e. profit rose from $75 each to $150 each), would the solution change? If so, what is the new solution (please state the mix and the new profit amount)?

Answer 1

Let x = type 1 computer

Let y = type 2 computer

Maximize, Z = 60*x +75*y

Constraints to:

Assembly time : 4x+10y <= 100 hours

inspection time : 30 x + 20 y <=22*60 = 30x+20y <=1320 minutes

Storage space : 3x+3y <=39 cubic feet

also, x>=0, y>=0

Using excel:

a) Mix of computer that should be produced:

type 1 = 5

type 2 = 8

b) Optimal value for profit = 5*60 + 8*75 = $900

c) Yes the solution will change

Mix of computer that should be produced:

type 1 = 0

type 2 = 10

Optimal value for profit = 0*60 + 10*150= $1500