Question

The American Metal Fabrication Company (AMFC) produces various products from steel bars. One of the initial steps is a shaping operation, which is performed by rolling machines. There are three machines available for this purpose, the B3, B4, and B5. There features are given by the following table:

Machine	Speed in Feet/Min.	Allowable Raw Material Thickness in Inches	Available Hours Per Week	Labor Cost Per Hour Operating
B3	150	3/16 to 3/8	35	$10
B4	100	5/16 to 1/2	35	$15
B5	75	3/8 to 3/4	35	$17
Machine	Speed in Feet/Min.	Allowable Raw Material Thickness in Inches	Available Hours Per Week	Labor Cost Per Hour Operating
B3	150	3/16 to 3/8	35	$10
B4	100	5/16 to 1/2	35	$15
B5	75	3/8 to 3/4	35	$17

   This week there are three products, which must be processed. AMFC must produce at least 218,000 feet of ¼ in. material, 114,000 feet of 3/8 in. material, and 111,000 feet of ½ in. material. The profit contribution per foot excluding labor for these products are .017, .019 and .02. These prices apply to all production, e.g., any in excess of the required production. The shipping department has a capacity limit of 600,000 feet per week, regardless of the thickness. Formulate a linear programming model to maximize profit.
   a)   Please define your variables clearly and the units used.
   b)   What is the value of an additional hour of capacity on the B4 machine?
   c)   What is the value of an additional 2 hours of capacity on the B3 machine?
   d)   By how much would one have to raise the profit contribution/1,000 ft. of ¼” material before it would be worth producing more of it?

EXCEL Answer only plz!

Answer 1

The above problem can be solved through Solver in Excel.

We have three product, say a (1/4 Inch), b (3/8 Inch) and c (1/2 Inch).

Product a and b can be producted in B3,

Product b and c can be produced in B4 and B5.

Now, Lets Assume

Product a produced in B3 = Xa

Product b produced in B3 = Xb

Product b produced in B4 = Yb

Product c produced in B4 = Yc

Product b produced in B5 = Zb and

Product c produced in B5 = Zc

Considering the above, we can generate an excel page as below,

Applying Solver on the excel sheet with constrainst, will provide the quantities to be produced by each machine B3, B4 and B5 as below:

The excel also provides the slacks available for each of the contraints as below:

As evident in the above, Machine B5 has not been used fully with a slack of 82500 Feet whereas B3 and B4 doesnt have any slack.

b) Now to know the value of additional hour of capacity for Machine B4, we can change the available hour for B4 from 35 to 36, which increases the profitability to 10081 USD i.e 7.67 USD more.

c) Similarly the value of 2 additional hour for B3 can be calculated by increasing the value and running the solver again.

The profit increases to USD 10121/- with and increase of USD 47/-.

d) To produce 1/4 Inch prodcut more, The profit margin of Xa should be more or equal to Xb.

Xa Profit Margin / 1000 Feet = 158.9 USD

Xb Profit Margin /1000 Feet = 178.9 USD

The the price of product a i.e 1/4 inch should be increased by 20 USD / 1000 Feet length.