Question

I need the answer to include solver and excel. Thank you.

A Company produces two products. Relevant information for each product is shown in the Table below. The company has a goal of $48 in profits and incurs $1 penalty for each dollar it falls short of this goal. A total of 32 hours of labor are available. A $2 penalty is incurred for each hour of overtime (labor over 32 hours) used, and $1 penalty is incurred for each hour of available labor that is unused. Marketing considerations require at least 5 units of product 1 and 10 units of product 2 be produced. For each unit of either product by which production falls short of demand, a penalty of $5 is assessed Labor Required Contribution to profit Product 1 4 hours $4 Product 2 2 hours $2 Formulate a weighted goal programming that can be used to minimize the penalty incurred by the company. Do NOT solve, just formulate. 1. 2. Suppose that company sets (in order of importance) the following goals: a. Goal 1: Avoid underutilization of labor b. Goal 2: Meet demand for product 1 c. Goal 3: Meet demand for product 2 d. Goal 4: Do not use overtime Formulate and solve the preemptive goal programming model for this situation using Excel solver. Describe clearly the optimal solution to this problem using a managerial statement.

Answer 1

x₁: Number of units of product 1 to produce.

            x₂: Number of units of product 2 to produce.

		Product 1, x1	Product 2, x2	Available	Penalty
	Labor:	4 Hours	2 Hours	32 Hours	$2 / overtime
					$1 / unused
	Profit:	$4	$2	$48	$1 / dollar short
	Requirement:	At least 7 units	At least 10 units
	Penalty:	$5 / shortage	$5 / shortage

Constraints:

            4x₁ + 2x₂ + S₁^- - S₁⁺ = 32

            x₁ + S₂^- - S₂⁺ = 7

            x₂ + S₃^- - S₃⁺ = 10

Answer 2

Im need the answer