Question

Question 6 options:

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	32	16
Dept A hours/Unit	1	0.35	90	19	$13
Dept B hours/Unit	0.3	0.2	41	11	$21
Dept C hours/Unit	0.2	0.5	59	17	$14

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 $13*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 $3393 and $3743
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

Max Profit = $ 3643

Number of Units of Product 1 to Produce = 78
Number of Units of Product 2 to Produce = 88
Overtime in Department A = 19 hours
Overtime in Department B = 0 hours
Overtime in Department C = 1 hours

Explanation and Solution:

	Product 1	Product 2	Regular Hours Available	Overtime Hours Available	Cost of Overtime per Hour
Profit per Unit	32	16
Dept A hours/Unit	1	0.35	90	19	13
Dept B hours/Unit	0.3	0.2	41	11	21
Dept C hours/Unit	0.2	0.5	59	17	14

Mathematical Model Formulation:

Decision variables:

Let X and Y represent the units produced that of Product 1 and Product 2 respectively and A, B and C be the no. of overtime hours required in department A, B and C respectively.

Objective Function:

Zmax = 32X+16Y-(13A+21B+14C)

total profit = sum of profits of two products - overtime costs

i.e Maximize the total profit

Constraints:

A <= 19 (maximum overtime available in A)

B <= 11 (maximum overtime available in B)

C <= 17 (maximum overtime available in C)

X+0.35Y-94 <= A (Overtime hours = Regular time required in dept A for product 1 and 2 - Maximum Regular time available in department A)

or X+0.35Y-A <= 90

Similarly,

0.3P1+0.2P2-B <= 41

0.2P1+0.5P2-C <= 59

X,Y,A,B and C = Integer

X,Y,A,B and C >= 0 (non negatvity)

Spreadsheet Model:

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

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.

In addition to the constraints, select the yellow cells on left and select int in the middle to add the integer constraint.

5th; Select Simplex Lp in solving method

6th: Click solve

Solution: