Question

Hartman Company is trying to determine how much of each of two products should be produced over the coming planning period. The only serious constraints involve labor availability in three departments. Shown below is information concerning labor availability, labor utilization, overtime, and product profitability.

	Product 1	Product 2	Regular Hours Available	Overtime Hours Available	Cost of Overtime per Hour
Profit per Unit	27	19
Dept A hours/Unit	1	0.35	94	17	$15
Dept B hours/Unit	0.3	0.2	46	11	$17
Dept C hours/Unit	0.2	0.5	51	11	$11

If all production is done in a standard workweek, then Profit per Unit includes the cost to pay for the workforce. But, if overtime is needed in each department, then the Profit Function needs to be reduced by the Cost per Hour of Overtime in Each Department multiplied by the Number of Overtime Hours Used in Each Department. For example, if we used 5 hours of Overtime in Department A, we would need to Subtract $15*5 from our Profit equation.


Setup and Solve the Linear Programming Problem and determine the number of units of Product 1 and Product 2 to produce to Maximize Profit. Add an Additional Constraint to your LP to make sure that ALL of the Variables are INTEGERS


Hint: You will need 5 Decision Variables, 2 of them to determine the production quantities, and 3 of them to determine how much overtime to use in each of the departments.


Max Profit = $

(Do Not Use Commas) Hint: Max Profit is Between $3328 and $3578
Number of Units of Product 1 to Produce =

Number of Units of Product 2 to Produce =

Overtime in Department A =

hours
Overtime in Department B =

hours
Overtime in Department C =

hours

Answer 1

Data:

	Product 1	Product 2	Regular Hours Available	Overtime Hours Available	Cost of Overtime per Hour
Profit per Unit	27	19
Dept A hours/Unit	1	0.35	94	17	15
Dept B hours/Unit	0.3	0.2	46	11	17
Dept C hours/Unit	0.2	0.5	51	11	11

Decision variables:

Let P1 and P2 be the two decision variables which represent the units of Product 1 and Product 2 to produce respectively

Let A, B and C be the no. of overtime hours utilized in department A, B and C respectively.

Objective Function: To maximize the total profit

Maximize 27P1+19P2-15A-17B-11C

Constraints:

1P1+0.35P2-A <= 94 (Overtime hours = Regular time required in dept A for both products - Regular time available in department A)

0.3P1+0.2P2-B <= 46 (Overtime hours = Regular time required in dept B for both products - Regular time available in department B)

0.2P1+0.5P2-C <= 51 (Overtime hours = Regular time required in dept C for both products - Regular time available in department C)

A <= 17 (maximum overtime available in A)

B <= 11 (maximum overtime available in B)

C <= 11 (maximum overtime available in C)

P1, P2, A, B, C are integers

P1, P2, A, B, C >= 0

Solving the LP in solver:

The solver is an excel plug in which can be installed form excel options. After installation, it is available in the data segment of the excel sheet. Once installed and launched, the parameters can be added

Spreadsheet Model along with formula:

Adding Parameters to Solver:

1st: Enter Green highlighted cell (objective function) in the set objective field

2nd: Select Max

3rd: Enter the yellow cells (decision variables) in the by changing variable cells field

4th: In constraints, click on add, enter the blue cells in the dialogue box which appears.

On the left area (cell reference), enter the left side values, select relationships in the middle, and in the right enter the right side values of the inequality signs. Similarly, repeat for the next constraints by clicking on add button. Then click ok to go back to the parameters part.

Additional constraint: Add yellow cells reference in the cell reference part and select integer in the middle.

5th; Select Simplex Lp in solving method

6th: Click solve

Solution:

Max Profit = 3485

Number of Units of Product 1 to Produce = 77

Number of Units of Product 2 to Produce = 93

Overtime in Department A = 16 hours

Overtime in Department B = 0 hours

Overtime in Department C = 11 hours