Transporting Fruits at Tampa Logistics. Tampa Logistics (TL) ships oranges and strawberries once a week from Tampa, Florida to Philadelphia, Pennsylvania. This morning they have 39 tons of oranges and 36 tons of strawberries on hand. The fruits all go to the Philadelphia Fruit Terminal Market and therefore can be mixed on the companys three trucks.

The capacities of trucks 1, 2, and 3 are 27, 31, and 28 tons, respectively. Fruits are packed in 20 pound cartons. Assume that there are 2000 pounds in a ton, so that 39 tons of oranges are packed into 3900 cartons, and 36 tons of strawberries are packed into 3600 cartons.

The travelling costs of the trucks are negligible and independent of what fruits they ship; all three trucks will be used in this shipment.

Spoilage occurs during the transportation of fruit from Tampa to Philadelphia. Because of differences in their refrigeration systems, the fruit losses differ by truck as follows

: % Losses in Transit Truck no. Orange Strawberry 1 5 10 2 4 12 3 3 11

For example, 5% of oranges in each carton placed in truck 1 and 11% of the strawberries placed in truck 3 will be spoiled upon arrival in Philadelphia.

TL sells the unspoiled oranges for $20 per carton and unspoiled strawberries for $32 per carton. Because of spoilage during transport to Philadelphia, however, TL must discount the amounts for which it sells cartons of fruit. For example, because 5% of the oranges shipped in truck 1 will be spoiled by the time they reach Philadelphia, TL will be able to sell cartons shipped on truck 1 for only $20*(1-0.05) = $19. Similarly, because 11% of the strawberries shipped on truck 3 will have spoiled, TL can sell cartons of strawberries shipped on truck 3 for only $32*(1-0.11) = $28.48. TL needs to determine the number of cartons of each fruit to load on each truck to maximize its revenue from selling fruits in Philadelphia.

TL wishes to determine the number of cartons of each fruit to load on each truck to maximize its revenue from selling fruits in Philadelphia

. Constraints: 1. Total Oranges and Strawberries shipped in all three trucks must equal 3900 for oranges and 3600 for strawberries.

2. Each trucks capacity cannot be exceeded. As an example, the total number of cartons of oranges and strawberries in Truck 1 must be equal to or less than 2700 cartons.

3. The optimized number in Carton Assignments must be an integer. When you are adding the constraint to solver select the range B18:C20 and in the drop down menu select INT.

Tips

1. You are building the spreadsheet so that solver has the data needed to complete the optimization.

2. Solver is going to want to change values and if you type in the SUM number of cartons by fruit and truck instead of using a formula/function you will get an error.

3. You need the SUMPRODUCT function for total revenue, it cant be blank. Deliverable: Use the Excel Challenge file to set up and solve the TLs optimization problem using Excel and Solver. What is the optimal solution? What is the optimal objective function value (Total Revenue)?

HOW can i apply it in excel ?

Losses in Transit Truck no. Orange Strawbe 2 4 12 Losses in Transit Truck no. Orange Strawbe 2 4 12