Please answer with Excel 7. Shipping Fruit. Wollmer Distribution Company collects fruit from several small farms in

Please answer with Excel

Transcribed Image Text:

7. Shipping Fruit. Wollmer Distribution Company collects fruit from several small farms in the region, consolidates its collections, and then ships the fruit to a regional wholesale fruit market by truck. Having made their collections, the company has in stock 57 tons of grapes, 62 tons of peaches, and 81 tons of bananas. Wollmer owns four trucks that can transport fruit to market. Each truck has its own capacity, and Wollmer has discovered that yield losses occur at different rates depending on which fruit is carried in which truck. The truck suspension system and the efficiency of the refrigeration system seem to account for most of the losses. The following table shows the loss as a percentage: Truck 1 Truck 2 Truck 3 Truck 4 Grapes 12% 12% 16% 18% Peaches 10% 14% 13% 17% Bananas 4% 5% 6% 8% Fruit Grapes $500 Peaches 1,000 Bananas 1,750 The current market prices for the three fruits are as follows: Capacity 40 T 50 T 55 T 75 T Price/Ton a. As a logical way of loading the trucks, a Wollmer's dispatcher follows a standard rule: take the largest yield from the table of yields and assign as many tons as possible; then go on to the next largest yield available, and so on. For this rule, what is the resulting revenue and how many tons are brought to market? b. What shipping plan will bring in the most revenue for Wollmer? What is the optimal total revenue? c. In the optimal solution, how many tons of fruit are delivered to market? What is the maximum possible number of tons brought to market? 7. Shipping Fruit. Wollmer Distribution Company collects fruit from several small farms in the region, consolidates its collections, and then ships the fruit to a regional wholesale fruit market by truck. Having made their collections, the company has in stock 57 tons of grapes, 62 tons of peaches, and 81 tons of bananas. Wollmer owns four trucks that can transport fruit to market. Each truck has its own capacity, and Wollmer has discovered that yield losses occur at different rates depending on which fruit is carried in which truck. The truck suspension system and the efficiency of the refrigeration system seem to account for most of the losses. The following table shows the loss as a percentage: Truck 1 Truck 2 Truck 3 Truck 4 Grapes 12% 12% 16% 18% Peaches 10% 14% 13% 17% Bananas 4% 5% 6% 8% Fruit Grapes $500 Peaches 1,000 Bananas 1,750 The current market prices for the three fruits are as follows: Capacity 40 T 50 T 55 T 75 T Price/Ton a. As a logical way of loading the trucks, a Wollmer's dispatcher follows a standard rule: take the largest yield from the table of yields and assign as many tons as possible; then go on to the next largest yield available, and so on. For this rule, what is the resulting revenue and how many tons are brought to market? b. What shipping plan will bring in the most revenue for Wollmer? What is the optimal total revenue? c. In the optimal solution, how many tons of fruit are delivered to market? What is the maximum possible number of tons brought to market?