Question

A company buys liquid material for its three manufacturing facilities from several suppliers. Facility X requires 57,000 gallons of liquid material per month, facility Y requires 64,000 gallons of liquid material per month, and facility Z requires 73,000 gallons of liquid material per month. Supplier B charges $11.80 per gallon and can supply up to 56,000 gallons per month, Supplier C charges $12.40 per gallon and can supply up to 50,000 gallons per month, Supplier D charges $13.70 per gallon and can supply up to 70,000 gallons per month, and Supplier E charges $12.45 per gallon and can supply up to 58,000 gallons per month, The company must also pay shipping costs to suppliers. The shipping costs per gallon ($/gal) from suppliers to manufacturing facilities are shown below From Facility Facility Facility Supplier X 2.80 2.45 2.83 2.30 2.05 3.15 2.85 2.45 1.99 2.18 2.55 3.40 Determine the best supplier selection for the company Answer the following questions using the optimal solution. Do not use any comma in your answer Answer the following questions using the optimal solution. Do not use any comma in your answer Supplier B should send Supplier C should send Supplier D should send Supplier E should send Supplier B should send Supplier C should send Supplier D should send Supplier E should send Supplier B should send Supplier C should send Supplier D should send Supplier E should send The total monthly cost for the company is thousand gallons every month to facilityX thousand gallons every month to facilityX thousand gallons every month to facilityX thousand gallons every month to facilityX thousand gallons every month to facilityY thousand gallons every month to facilityY thousand gallons every month to facilityY thousand gallons every month to facilityY thousand gallons every month to facility Z thousand gallons every month to facility2Z thousand gallons every month to facility Z thousand gallons every month to facility 2Z

Answer 1

Here we have to considered fixed amount of liquid material per gallon and transport cost from source to destination.

We solve the given question in excel using excel solver as shown below:

As seen from above, we get answer as below:

Supplier B should send 56,000 gallons to Facility X

Supplier C should send 1,000 gallons to Facility X

Supplier D should send 0 gallons to Facility X

Supplier E should send 0 gallons to Facility X

Supplier B should send 0 gallons to Facility Y

Supplier C should send 0 gallons to Facility Y

Supplier D should send 30,000 gallons to Facility Y

Supplier E should send 34,000 gallons to Facility Y

Supplier B should send 0 gallons to Facility Z

Supplier C should send 49,000 gallons to Facility Z

Supplier D should send 0 gallons to Facility Z

Supplier E should send 24,000 gallons to Facility Z

The total monthly cost for the company is $2,890,260

The above excel tables in form of formula and solver extract is as shown below for better understanding:

Solver Parameters 1 From 2 Supplier Facility Facili Set Objective 2.45 С 12 45 D 2.3 Mip Value Of: 6 E .15 7 Demand 57000 2.05 2.85 2.18 2.55 3000 Ry Changing Variable Cells: 194000/234000 BS12:SD515 ubject to the Constraints SES12:SES15SGS12SGS15 10 From 11 Supplier Fac Supply B S6000 s$UMI B12:D12) |仁156000 SUM(B13:D1350000 SUMB14:01470000 SUM(B15:015 13 C 1000 14D10 15 E O 16 Total SUMIB12:B15) SUMIC12:C15) SUMID12:015) 17 18 Demand 5700D 34000 24000 58000 Reset All 73000 Total 118 E12+124 E13 13.7 E14 12 45 E15)+SUMPROOUCTB3 D6,812:D15) 20 21 Select a Sohing Method Simplex LP 23 24 25 Solming Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP implex engine for linear Sohver Problems, and select the Evolutionary engine for Sover problems that are non-smooth. 27 29 30