M3_IND2

.

The

fabrication

department for

an automobile component

plant is scheduling its work for

next month.

The

plant produces the following four components: A1, B2, C3, and D4.

Each

component

must go through three

departments

during the fabrication process. After fabrication, each valve is

inspected by a human being, who spends 1

5

minutes per valve. There are

5

0

0

inspection hours available

for the month. The time required (in hours)

for ea

ch department

to work on each

component

is sh

own

in the following table. Also shown are the minimum number of

componen

ts

that must be produced for

the month and the unit profit for each

component

.

COMPONENTS TO BE

PRODUCED

DEPARTMENT

A1

B2

C3

D4

CAPACITY OF EACH

DEPARTMENT

(hours)

DRILLING

0.

9

0

0.

5

0

0.

6

0.

7

14

00

MILLING

0.

8

0

0.

5

5

0.

7

0.

7

140

0

A

SSEMBLY

1.

1

0

0.60

0.50

0.

65

1

4

00

MINIMUM OF EACH

PRODUCT TYPE NEEDED

3

00

5

0

0

6

00

4

0

0

PROFIT ($/UNIT)

$1

4

$1

0

$1

1

$

1

5

Formulate and solve the problem in Excel to determine the number of each product to

manufacture that

meets the requirements and maximizes profits.

a)

What is the

maximum profit

based on your optimal solution (the value of the objective

function)?

b)

How many

A

1's

should be manufactured based on your optimal solution

?

c)

How many

B

2's

should be

manufactured based on your optimal solution

?

d)

How many

C3

's

should be manufactured based on your optimal solution

?

e)

How many

D4

's

should be manufactured based on your optimal solution

?

f)

What is the total number of hours used in

the drilling department

based on your optimal

solution?

g)

What is the total number of hours used in

the milling department

based on your optimal

solution?

h)

What is the total number of hours used in

the

assembly

departmen

t

based on your optimal

solution?

i)

What is the total number of hours used in

the inspection department

based on your optimal

solution?