Question

Pat’s Problem

A manufacturing company makes two products. The profit estimates are $1000 for each unit of

product 1 sold and $1200 for each unit of product 2 sold. The labor-hour requirements for the

products in each of the three production departments are summarized below:

   Department A Department B    Department C

Product 1 8 hrs 3 hrs 10 hrs

Product 2    12 hrs    3 hrs 5 hrs

The production supervisors in the departments have estimated that the following number of

labor-hours will be available during the next month: 48 hours in department A, 18 hours in

department B, and 40 hours in department C. Assuming that the company is interested in

maximizing profits, formulate a linear program for this problem. Solve in Excel.

PAR, Inc.

Par, Inc. is a small manufacturer of golf equipment whose management has decided to move into

the market for medium and high-price golf bags. Par's distributor is enthusiastic about the new

product line and has agreed to buy all the golf bags Par produces over the next the next three

months. Two types of golf bags will be produced standard and deluxe. The manufacturing of

each bag will require the following operations and the corresponding times:

Production Operations and Production Requirements (per bag) (in hours)

Bag Cutting and Dyeing    Sewing Finishing Inspection and Packaging

Standard 7/10    1/2 1 1/10

Deluxe 1 5/6 2/3    1/4

The accounting department assigned all the relevant variable costs and arrived at prices for both

bags that will result in a profit (from an accounting viewpoint this is contribution margin per bag,

e.g. overhead has not been allocated) contribution of $10 for every standard bag and $9 for every

deluxe bag produced. The workload projections for the next three months estimates that 630

hours of cutting and dyeing time, 600 hours of sewing time, 708 hours of finishing time and 135

hours of inspection and packaging time will be available.

Formulate a linear program to maximize profit contribution for the next three months. Solve in Excel.

Answer 1

Question 1:

I am using following notations

Product 1: X

Product 2: Y

Production unit of Product 1 :U1

Production unit of Product 2 :U2

Profit : P

Dept A : A

Dept B: B

Dept C :C

Now objective function is to max(P) = (U1*X) + (U2*Y)

Subjected to following constraints-

Constraint 1: (U1*A) + (U2*A) <= 48
Constraint 2: (U1*B) + (U2*B) <= 18
Constraint 2: (U1*C) + (U2*C) <= 40

Putting all these values and constraints in excel and using problem solver to optimize our profit. Below are the snapshot of the solution and optimized profit value-

Question 2:

Question 2:

I am using following notations

Standard	S
Deluxe	D
Production unit of Standard	Us
Production unit of Deluxe	Ud
Profit	P
Bag Cutting & Dyeing	A
Sweing & Finishing	B
Inspection	C
Packaging	D

Now objective function is to max(P) = (Us*S) + (Ud*D)

Subjected to following constraints-

• Constraint 1: (Us*A) + (Ud*A) <= 630
• Constraint 2: (Us*B) + (Ud*B) <= 600
• Constraint 3: (Us*C) + (Ud*C) <= 708
• Constraint 4: (Us*D) + (Ud*D) <= 135

Putting all these values and constraints in excel and using problem solver to optimize our profit. Below are the snap shot of solution and optimized profit value-

In case you need help in excel please let me know.