Question

 

TropSun is a leading grower and distributer of fresh citrus products with three large citrus groves scattered around central Florida in the cities of Orlando, Eustis, and Winter Haven. TropSun currently has 275,000 bushels of citrus at the grove in Mt. Dora, 400,000 bushels at the groves in Eustis, and 300,000 bushels at the grove in Clermont. TropSun has citrus processing plants in Ocala, Orlando, and Leesburg with processing capabilities to handle 200,000, 600,000, and 225,000 bushels respectively. TropSun contracts with a local trucking company to transport its fruit from the groves to the processing plant. The trucking company charges a flat rate for each mile that each bushel of fruit must be transported. Each mile a bushel of fruit travels is known as a bushel-mile. The following table summarizes the distances (in miles) between the groves and processing plant.

	Distance (in miles) Between Groves and Plants
Grove	Ocala	Orlando	Leesburg
Mt. Dora	18	51	39
Eustis	34	33	20
Clermont	52	22	37

TropSun wants to determine how many bushels to ship from each grove to each processing plant to minimize the total number of bushel-miles the fruit must be ship. [ Another way to put it, MINIMIZE the TRANSPORTATION costs of the bushel-miles from the groves to the Plants] (30 Points) HINT: What decision variables can change.

1. Define the decision variables.

2. Define the Constraints

3. Implement and Solve the Problem in Excel

4. Analyze the Solution, what is it telling the decision maker?

Answer 1

Add solver plugin to Excel:

Enter the data and constraint equations as shown:

Drag down to other cells of column K

Cell K3 is the one we want to minimize. Choose cell K3 and click on Solver in the data tab

Following window will appear

Check that you have entered correct cell in cell objective, which is $K$3

select Min

select cells $B$2:$J$2 in "By Changing variable cells:" tab

Now add constraints by clicking on Add next to "Subject to Constraints"

You will see following window:

Choose Simplex LP in "Select a Solving Method:" and click on Solve

You will see following window:

Make sure that Keep Solver Solution radio button is selected.

Click on Ok and you will see following screen:

Cell below each variable (B2, C2, D2 upto J2) give the optimum values of the respective variables. K3 gives the optimized value of the minimization equation.