Question

I need to know how to answer this question only in Excel. Please include instructions, screenshots, etc. in Excel which explain the process (formulas included).

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

1. Define the decision variables.

Decision variables:

Let x₁₁, x₁₂, x₁₃, x₂₁, x₂₂, x₂₃, x₃₁, x₃₁, x₃₃, x₄₁, x₄₂, x₄₃, be the shipments between the origins and the destinations.

(4 is a dummy origin)

2. Define the Constraints

Objective function:

Minimize S = 18x₁₁ + 51x₁₂ + 39x₁₃ + 34x₂₁ + 33x₂₂ + 20x₂₃ + 52x₃₁ + 22x₃₂ + 37x₃₃

Constraints:

x₁₁ + x₁₂ + x₁₃ ≤ 275000

x₂₁ + x₂₂ + x₂₃ ≤ 400000

x₃₁ + x₃₂ + x₃₃ ≤ 300000

x₄₁ + x₄₂ + x₄₃ ≤ 50000

x₁₁ + x₂₁ + x₃₁ + x₄₁ = 200000

x₁₂ + x₂₂ + x₃₂ + x₄₂ = 600000

x₁₃ + x₂₃ + x₃₃ + x₄₃ = 225000

All x_ij are ≥ 0

3. Implement and Solve the Problem in Excel.

Load the data into Excel.

Add a dummy variable to balance the supply and demand for the transportation problem.

Load the data one more time so that Excel can calculate using solver.

Use the commands so that Solver should expect what to calculate exactly.

Extend the formula cell to dummy row.

Similarly, do for the column addition.

Extend it to the Leesburg column.

.Calculate Total transportation costs now.

Let us use the Solver now.

Go to Data>Solver.

Use the commands as below.

Adding the constraints example:

After all, is done as mentioned in the solver parameter screenshot, click on Solve.

The dialogue box will appear on the screen as follows:

The final solution is as below:

Therefore, the transportation cost is $24300000.

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

According to the output, a dummy source with a capacity of 50000 should be set up at Orlando.