Explore BrainMass
Share

Use excel solver to find solution for LP problems

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

A farmer's family is making plans for the year's planting. Its members are considering planting corn, tomatoes, potatoes, and strawberries. They have 50 acres to plant on. The aim is to determine what it costs to plant an acre of each crop, computing the yield in bushels, forecasting the revenue for a bushel of each crop, and choosing the combination of crops that will yield the most profit. The data they have collected, along with the availability of resources, is shown in the table.

a)a) Determine the best mix of crops to maximize their revenue.

b) The farmers also have the opportunity to buy a 78-acre farm adjacent to their land. They would like to narrow their selection of crops to corn, strawberries, or a combination of the two. If they acquire the land, they will be able to increase the time available to 1,800 hours for planting, 825 hours for tending, and 1,400 hours for harvesting. All these increases are from the available limits in part (a). Between the two farms, there is 510 acre-feet of water available for the season. The farmers can obtain up to 7,000 pounds of fertilizer. The new farm has not been cultivated in a while, so the farmers believe that each acre of the new farm will take an extra 4 hours of labor to plant, and an extra 2 hours per acre to tend. Because of the condition of the new farm, they expect the yields to be down from 50 to 45 bushels per acre for corn and from 56 to 50 bushels per acre for strawberries. They want to know the best combination of crops to plant on each farm, with the goal to maximize revenue from at least 75% of each farm's acreage.

© BrainMass Inc. brainmass.com October 17, 2018, 4:34 am ad1c9bdddf
https://brainmass.com/math/linear-programming/use-excel-solver-to-find-solution-for-lp-problems-468324

Attachments

Solution Summary

The solution provides detailed explanation how to find maximal or minimal values by using excel solver.

$2.19
Similar Posting

Managerial Business

A couple has agreed to attend a "Casino Night" as part of a fundraiser for the local hospital. They do not like to gamble because they believe that gambling is generally a losing proposition. However, for the sake of the charity, they have decided to attend and spend $300 on the games. There will be four games, each involving standard decks of cards.

The first game, Jack in 52, is won if you select the Jack of a particular suit from the deck. The probability of actually doing this is 4 in 52 (4/52 or .0769). Gamblers may place bets of $1, $2, or $4 on this game. If they win, the payouts are $12.00 for a $1 bet, $24.55 for a $2 bet, and $49 for a $4 bet.
The second game, Red Face in 52, is won if you select a red face card (including the Jack, Queen, or King) from the deck. The probability of winning is 6 in 52 (.1154). Again, bets may be placed in denominations of $1, $2, and $4. Payouts are $8.10, $16.35, and $32.50, respectively.

The third game, Face in 52, is won if you select 1 of the 12 face cards from the deck. The probability of winning is 12 in 52 (.2308). Payouts are $4, $8.15, and $16 for $1, $2, and $4 bets.

The last game, Red in 52, is won if you select a red card from the deck. The probability of winning is 26 in 52 (.5). Payouts are $1.80, $3.80, and $7.50 for $1, $2, and $4 bets.

Given that they can calculate the expected return or loss for each type of game and level of wager, they have decided to see if they can minimize their expected loss by planning their evening using LP. For example, a $1 bet in Jack in 52 has a return of $12.00, but there is only a 1 in 13 chance of winning. Therefore, the expected value of the dollar bet is ($12.00*(1/13)) or $.9231. This computes to an expected loss of $1-.9231, or $.0769.
The couple wants to appear generous. Therefore, they will place at least 20 bets (of any value) on each of the four games. Further, they will spend at least $26 on 1-dollar bets, at least $50 on 2-dollar bets, and at least $72 on 4-dollar bets. They will bet exactly the agreed-upon $300. What should be their gambling plan, and what is their expected loss for the evening?

View Full Posting Details