Question

(Ravindran et al., 1987) A company manufacturers three products A, B, and C. Each unit of product A requires 1 h of engineering service, 10 h of direct labor, and 3 lb of material. To produce one unit of product B requires 2 h of engineering, 4 h of direct labor, and 2 lb of material. Each unit of product C requires 1 h of engineering, 5 h of direct labor, and 1 lb of material. There are 100 h of engineering, 700 h of direct labor, and 400 lb of materials available. The cost of production is a nonlinear function of the quantity produced as shown in Table 5.16. Given the unit selling prices of products A, B, and C as $12, 9, and 7 respectively, formulate a linear mixed integer program to determine the optimal production schedule that will maximize the total profit.

TABLE 5.16 Data for Exercise 5.7

Product A Product B Product C

Production (Units) Unit Cost ($) Production (Units) Unit Cost ($) Production (Units) Unit Cost ($)

0–40 10 0–50 6 0–100 5

41–100 9 51–100 4 over 100 4

101–150 8 over 100 3 over 150 7

Note: If 60 units of A are made, the first 40 units cost $10/unit and the remaining 20 units cost $9/unit.

Answer 1

To create a linear mixed integer program for this case, following variables are considered:

• x₁,x₂,x₃ - Variables (quantities of products A, B & C)
• a₁,a₂,a₃ - units in the range given for product A
• b₁,b₂,b₃ - units in the range given for product B
• c₁,c₂,c₃ - units in the range given for product C
• x₁,x₂,x_{3 $\geq$}0
• x₁,x₂,x₃ ∈ $\mathbb{Z}$

The following data is provided for producing each unit of products A, B & C

Engg Service (hour) Direct Labor (hour) Material (Ib) Sales price (S) 2 4 2 Available 100 700 400 10 12 7

Based on the above table and the cost structure provided in the question, we get the following linear programming equations:

Maximize; Z_max = 12x₁-(10a₁+9a₂+8a₃)+9x₂-(6b₁+4b₂+3b₃)+7x₃-(5c₁+4c₂+7c₃)

s.t.

• x₁+2x₂+x₃$\leq$ 100
• 10x₁+4x₂+5x₃$\leq$ 700
• 3x₁+2x₂+x₃$\leq$ 400

Incorporating these equations into Excel Solver as:

Units produced Prod Cost 40 0 60 C) 300 700 Engg Service (hour) Direct Labor Material (lb) 100 700 180 100 700 Sales 900 Total Profit 200

In the beginning, the variable cells can be set to 0 or any desired values. The constraints are calculated by SUMPRODUCT of each constraint with units produced. Prod Cost is calculated using the cost table in the question. Sales is calculated by SUMPRODUCT of Sales price with units produced. Profit is Sales - Total Prod Cost

• "Set Objective" - Total profit (Yellow cell) to "Max"
• "By changing varibale cells" - Cells across units produced (Grey cells)
• Constraints:
  • Cells in orange color $\leq$ available data
  • Variable cells must be integer
• Select solving method as Simplex LP

After solving we get the total profit and optimum quantities as shown in the table above.