A company manufactures mechanical heart valves from the heart valves of pigs. Different heart operations require valves of different sizes. The company purchases pig valves from three different suppliers. The cost and size mix of the valves purchased from each supplier are given in the file P03_36.xlsx. This file also contains the maximum number of valves available from each supplier per month. Each month, the company places an order with each supplier. At least 1300 large, 900 medium, and 400 small valves must be purchased each.supplier for1InputsCost/Valve%small%medium%largeSupplier1$7540%40%20%Supplier 2$6530%35%35%Supplier 3$5020%20%60%PurchaseAvailaleSupplier 1<=2400Supplier 2<=2000Supplier 3<=1000SmallMediumLargeValves obtaines>=>=>=Required4009001300Total Costlooking for-Changing CellsThree Formulas for Valves ObtainedFormula for Total CostTwo ConstraintsObjective, Min, Non-negativity

Respuesta :

Total Cost of Looking-Changed Cells Obtained: Three Valves Formulas Cost Calculation Formula Objective, Min. Two Restrictions is clrarly show below with excel file worksheet.

Set up the excel file as above, use fraction instead of percentage for ease in calculation.

Calculating the amount of each type valve

Formula for Values obtained in B16 is '=SUMPRODUCT(C3:C5,B9:B11)'

Formula for Values obtained in C16 is '=SUMPRODUCT(D3:D5,B9:B11)'

Formula for Values obtained in D16 is '=SUMPRODUCT(E3:E5,B9:B11)'

Calculating total Cost

in B22 formula used is =B9*B3+B10*B4+B11*B5

In B9, B10 and B11, initialize values to zero..

Now, go to solver and add these constraints and objective function:

in Set Objective select the Total cost cell.

Select Min instead of Max. since you want to minimize cost

Solving method: Select Simplex LP

Learn more about excel file here:

https://brainly.com/question/5450162

#SPJ4