Product Mix: Acme Industries produces four types of men’s ties using three types

Question

Product Mix: Acme Industries produces four types of men’s ties using three types of material. Your job is to determine how many of each type of tie to make each month. The goal is to maximize profit, where profit per tie = selling price - labor cost – material cost. Labor cost is $0.75 per tie for all four types of ties. The material requirements and costs are given below.

a) Formulate the problem as a linear program with an objective function and all constraints.
b) Determine the optimal solution for the linear program using any software you want. Include a copy of the code and output.
c) What are the optimal numbers of ties of each type to maximize profit?

Explanation / Answer

We will have a single cell to represent the ob jective. We will enter a formula that represents
the ob jective. This formula must be a linear formula, so it must be of the form:
cell1*cell1'+cell2*cell2'+::: , where cell1, cell2 and so on contain constant values and cell1',
cell2' and so are the variable cells.
Helpful Hint: Excel has a function sumproduct() that is designed for linear programs.
sumproduct(a1..a10,b1..b10) is identical to a1*b1+a2*b2+a3*b3+:::+a10*b10. This function
will save much time and aggravation. All that is needed is that the length of the rst
range is the same as the length of the second range (so one can be horizontal and the other
vertical).
Helpful Hint: It is possible to assign names to cells and ranges (under the Insert-Name menu).
Rather than use a1..a10 as the variables, you can name that range var (for example) and
then use var wherever a1..a10 would have been used.
We then have a cell to represent the left hand side of each constraint (again a linear function)
and another cell to represent the right hand side (a constant).
We then select Solver under the Tools menu. This gives a form to ll out to define the linear
program.
In the Set Cell" box, select the ob jective cell. Choose Maximize or Minimize.
In the By Changing Cells", put in the range containing the variable cells

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.