Question

Solve it only by Excel

Amalgamated Products has just received a contract to construct steel body frames for automobiles that are to be produced at the new Japanese factory in Tennessee. The Japanese auto manufacturer has strict quality control standards for all of its component subcontractors and has informed Amalgamated that each frame must have the following steel content:

Material	Min percentage (%)
Manganese	2.3
Silicon	4.6
Carbon	5.35

Amalgamated mixes batches of three different available materials to produce a steel used in the body frames. The following table give details these materials. Only Formulate the LP model that will indicate how much each of the three materials should be blended into a steel so that Amalgamated meets its requirements while minimizing costs.

Material Available	Manganese (%)	Silicon (%)	Carbon (%)	Cost per Pounds ($)
Alloy	70	15	3	12
Iron	1	10	3	9
Carbide	0	24	18	10

Answer 1

Let,

x1 = Pound of Alloy in the blend of 1 pound of steel
x2 = Pound of Iron in the blend of 1 pound of steel
x3 = Pound of carbide in the blend of 1 pound of steel

Objective is to minimize cost = Min 12x1+9x2+10x3

Subject to,

0.7x1 + 0.01*x2 + 0*x3 >= 0.023 (Min percentage of Manganese requirement constraint)

0.15x1 + 0.1x2 + 0.24x3 >= 0.046 (Min percentage of Silicon requirement constraint)

0.03x1 + 0.03x2 + 0.18x3 >= 0.0535 (Min percentage of Carbon requirement constraint)

x1+x2+x3 = 1 (Weight of 1 pound of steel)

x1,x2,x3 >= 0(non-negativity constraint)

Solving in excel solver we get,

x1 = Pound of Manganese in the blend of 1 pound of steel = 0.02111
x2 = Pound of Silicon in the blend of 1 pound of steel= 0.82222
x3 = Pound of carbon in the blend of 1 pound of steel= 0.15667

and minimized cost = $9.22

Solver screenshot

x1 X2 X3 Objective function 0.021111 0.822222 0.156667 9.22 3 Constraints 0.7 0.15 0.03 0.023 0.046 0.0535 0.01 0.1 0.03 0.023- 5 Manganese requirement 6 Silicon requirement 7 Carbon requirement 0.24 0.122989- 0.18 0.0535

Sensitivity report

A B 1 Microsoft Excel 16.0 Sensitivity Report 2 Worksheet: [Travel Sheet.xlsx]Sheet36 3 Report Created: 4/17/2019 11:39:29 PMM 6 Variable Cells Final Value Reduced Objective Allowable Allowable Coefficient Increase Name Cost Decrease Cell 9 B$2 x1 12 1E+30 9 1.028571429 10 $C$2 x2 11 SD$2 x3 12 13 Constraints 0.822222222 1E+30 1E+30 1.043478261 10 0.156666667 Final Value Shadow Constraint Allowable Allowable 15 Cell 16 SE$5 Manganese requirement 17 $E$6 Silicon requirement 18 SE$7 Carbon requirement 19 SE$8 Price R.H. Side Increase Name Decrease 0.023 4.347826087 0.023 0.567333333 0.014566667 1E+30 0.0235 1 0.783333333 0.675396825 0.122988889 0.046 0.076988889 0.0535 0.121571429 0.05356.956521739 1 8.747826087