codybearlover
New member
- Joined
- Dec 26, 2010
- Messages
- 1
Hello! I would REALLY appreciate your help!!!! I've tried working this problem for over three weeks, and I cant seem to get an answer that is plausible. My instructor wants us to use Excel to construct this min/cost problem. Below you will see the problem given. I cant attach my excel spread sheet to this link, but I can copy what it looks like on the sheet. it will follow below the problem.
I just cant seem to do this correctly and without this one question being done correctly, I will not be able to do some of the others!!! I will FAIL. Please help me. I am at the end of my rope!!!!
Thank you!!!!!
Aerial Products has received a contract to construct airframes produced at a client’s factory in Tennessee. There are strict quality control measures in place for the airframes. The metal they are made of must meet the following content specifications:
Material Minimum Maximum
Manganese 5% 7%
Silicon 2% 3.4%
Carbon .73% 1.29%
The metal consists of these materials as well as five other added products to make one airframe. The following table outlines the requirements for producing the metal for the airframe. Construct a linear program that optimizes cost while meeting the customers’ requirements.
Material Manganese Silicon Carbon Pounds Available Cost/lb.
AL1 20% 10% 1% 200 3$
AL 2 1% .05% 3% 500 4$
Powder 1 15% 20% .03% 1000 1500$
Powder 2 10% 12% .05% 100 10000$
Ide 1 1% 2% 1% 50 .5$
Ide 2 5% 10% 5% 5 50000$
Metal 3 6% 4% 10% 15 12000$
Spreadsheet #1Problem 1 Assumption of airframe weight= 1,250 pounds
Material Manganese Silicon Carbon Pounds Available Cost/lb.
AL1 20% 10% 1% 200 $3
AL 2 1% 0.05% 3.0% 500 $4
Powder 1 15% 20% 0.03% 1000 $1,500
Powder 2 10% 12% 0.05% 100 $10,000
Ide 1 1% 2% 1% 50 $0.50
Ide 2 5% 10% 5% 5 $50,000
Metal 3 6% 4% 10% 15 $12,000
QUALITY CONTROLS
MATERIAL MINIMUM MAXIMUM
MANGANESE 5% 7%
SILICON 2% 3.4%
CARBON 0.73% 1.29%
ASSUMING AIRFRAME WEIGHS (LBS) 1250
DECISION VARIABLES
X1=AL1 280.9210526
X2=AL 2 0
X3=Powder 1 0
X4=Powder 2 0
X5=Ide 1 631.5789474
X6=Ide 2 0
X7=Metal 3 0
MIN COST $1,158.55
Min/max constraints
MANGANESE lhs rhs
MIN 62.5 62.5
MAX 62.5 87.5
SILICON
MIN 40.72368421 25
MAX 40.72368421 42.5
CARBON
MIN 9.125 9.125
MAX 9.125 16.125
COST CONSTRAINT
AL1 0 200
AL 2 0 500
Powder 1 0 1000
Powder 2 0 100
Ide 1 0 50
Ide 2 0 5
Metal 3 0 15
TOTAL WEIGHT CONSTRAINT 912.5 1,250
I just cant seem to do this correctly and without this one question being done correctly, I will not be able to do some of the others!!! I will FAIL. Please help me. I am at the end of my rope!!!!
Thank you!!!!! Aerial Products has received a contract to construct airframes produced at a client’s factory in Tennessee. There are strict quality control measures in place for the airframes. The metal they are made of must meet the following content specifications:
Material Minimum Maximum
Manganese 5% 7%
Silicon 2% 3.4%
Carbon .73% 1.29%
The metal consists of these materials as well as five other added products to make one airframe. The following table outlines the requirements for producing the metal for the airframe. Construct a linear program that optimizes cost while meeting the customers’ requirements.
Material Manganese Silicon Carbon Pounds Available Cost/lb.
AL1 20% 10% 1% 200 3$
AL 2 1% .05% 3% 500 4$
Powder 1 15% 20% .03% 1000 1500$
Powder 2 10% 12% .05% 100 10000$
Ide 1 1% 2% 1% 50 .5$
Ide 2 5% 10% 5% 5 50000$
Metal 3 6% 4% 10% 15 12000$
Spreadsheet #1Problem 1 Assumption of airframe weight= 1,250 pounds
Material Manganese Silicon Carbon Pounds Available Cost/lb.
AL1 20% 10% 1% 200 $3
AL 2 1% 0.05% 3.0% 500 $4
Powder 1 15% 20% 0.03% 1000 $1,500
Powder 2 10% 12% 0.05% 100 $10,000
Ide 1 1% 2% 1% 50 $0.50
Ide 2 5% 10% 5% 5 $50,000
Metal 3 6% 4% 10% 15 $12,000
QUALITY CONTROLS
MATERIAL MINIMUM MAXIMUM
MANGANESE 5% 7%
SILICON 2% 3.4%
CARBON 0.73% 1.29%
ASSUMING AIRFRAME WEIGHS (LBS) 1250
DECISION VARIABLES
X1=AL1 280.9210526
X2=AL 2 0
X3=Powder 1 0
X4=Powder 2 0
X5=Ide 1 631.5789474
X6=Ide 2 0
X7=Metal 3 0
MIN COST $1,158.55
Min/max constraints
MANGANESE lhs rhs
MIN 62.5 62.5
MAX 62.5 87.5
SILICON
MIN 40.72368421 25
MAX 40.72368421 42.5
CARBON
MIN 9.125 9.125
MAX 9.125 16.125
COST CONSTRAINT
AL1 0 200
AL 2 0 500
Powder 1 0 1000
Powder 2 0 100
Ide 1 0 50
Ide 2 0 5
Metal 3 0 15
TOTAL WEIGHT CONSTRAINT 912.5 1,250