Use complete sentences to fully answer the questions.
An agribusiness manager is evaluating plans for the use of the acreage in Farm #39. There are three crops that could be planted, canola, alfalfa and wheat on the 1000 acre farm.
The market prices that are acceptable for planning purposes indicate that each acre in canola will yield revenue of $600, alfalfa will yield $500 per acre, and wheat will yield $450 per acre. Seed and fertilizer costs are $40 per acre for canola; $80 for alfalfa and $80 for wheat. Labor costs $10 per hour, regardless of crop. However, each acre of canola requires 5 hours of labor, alfalfa requires 3.5 hours and wheat requires 4.5 hours.
Water is a scarce resource at Farm #39. Each acre of canola requires 0.6 acre-feet of water; alfalfa requires 0.3 acre-feet per acre; and wheat requires 0.2 acre-feet per acre. For planning purposes, water costs $150 per acre-foot and the best estimate of total water availability is 450 acre-feet.
Recent changes in immigration law suggest that there will not be more than 3800 hours of labor available.
The management of the agribusiness is interested in getting as much cash flow as possible; for their purposes, this is considered to be revenue from the crop minus the costs of seed, water and labor.
Set up and solve the problem in Excel using Excel Solver.
Use Solver Sensitivity Report to answer the questions below.
Each question is independent.
Use only the sensitivity report given to answer the question, do not re-solve the problem.
C = NUMBER OF ACRES OF CANOLA TO PLANT
A = NUMBER OF ACRES OF ALFALFA TO PLANT
W = NUMBER OF ACRES OF WHEAT TO PLANT
MAXIMIZE CASH FLOW = 420C + 340A + 295W
ST
C + A + W <= 1000
0.6C + 0.3A +0.2W <= 450
5C + 3.5A + 4.5W <= 3800
Use the Solver Sensitivity Report to answer the questions below.
1. What is the optimal solution?
2. What is the optimal cash flow result?
3. What change in cash flow for wheat is necessary in order to change the recommended plantings to include wheat?
4. If 100 additional acre-feet of water could be obtained, what would be the value to the firm?
5. If 50 additional acres could be obtained at a cost of $135 per acre, should the firm acquire the additional acreage? Why or why not?
6. Within what limits could the cash flow per acre for canola range before the optimal solution changes?