Question

Material 1 costs $7 a pound, material 2 costs $5 a pound, and labor costs $15 per hour. Product A sells for $101 a unit, product B sells for $67 a unit, and product C sells for $97.50 a unit. Each week there are 300 pounds of material 1; 400 pounds of material 2; and 200 hours of labor. Also, there is a weekly demand of at least 10 units of product C each week. Formulate the given problem scenario as a linear program, and solve the problem using Microsoft Excel Solver. For the formulation, provide a complete description of the decision variables used along with their units and also label the constraints mentioned in the problem as completely as possible. Also, the Excel set-up should provide clearly labeled values used for the decision variables, constraints, and objective function.

Answer 1

Answer:

Some answers are attached below

Explanation:

We need to maximize profit -

Selling prices for A,B,C are given; Costs of materials 1,2 and labor are given.

Also given is that 1 unit of each product A,B,C requires certain units of material 1, material 2 and labor based on the table provided.

Profit for 1 unit of A = Selling Price - Cost = 101 - [(7*3)+(5*2)+(15*4)] = 10 $

Profit for 1 unit of B = Selling Price - Cost = 67 - [(7*1)+(5*4)+(15*2)] = 10 $

Profit for 1 unit of C = Selling Price - Cost = 97.5 - [(7*5)+(5*0)+(15*3.5)] = 10 $

LP Formulation

Maximize profit Z = 10A+10B+10C

subject to Constraints

3A+B+5C<=300 ----> Material 1 constraint

2A+4B<=400 ---->Material 2 constraint

4A+2B+3.5C <=200 ----->Labor

C>=10 -----> Product C Demand constraint

A,B,C>=0

Below is the solver solution and formulation table from Excel -

Maximum profit = 925 $