Machine Type

Milling machine

Lathe

Grinder

X$,$

$8$

A manufacturing firm has discontinued production of a certain unprofitable product line.

Considerable excess production capacity was created as a result. Management is consid$-$

ering devoting this excess capacity to one or more of three products: X$1,$ X$2,$ and X$3.$

Machine hours required per unit are:

Product

x$2$

$2$

$3$

$4$

$2$

O

X

$3$

$0$

$1$

Milling machines

Lathes

Grinders

The available time in machine hours per week is:

Machine Hours per Week

$800$

$480$

$320$

The salespeople estimate they can sell all the units of X$,$ and X$2$ that can be made. But

the sales potential of X$2$ is $80$ units per week maximum.

Unit profits for the three products are:

Unit Profits

$$20$

$6$

$8$

X

X$.$

X

a$.$ Set up the equations that can be solved to maximize the profit per week.

b$.$ Solve these equations using the Excel Solver.

c$.$ What is the optimal solution? How many of each product should be made, and what

should the resultant profit be$?$

d$.$ What is this situation with respect to the machine groups? Would they work at capacity,

or would there be unused available time? Will X$3$ be at maximum sales capacity?

e$.$ Suppose an additional $200$ hours per week can be obtained from the milling machines

by using staff overtime. The incremental cost would be $$1\mathrm{.}50$ per hour. Would