Margaret Blacks family owns five parcels of farmland broken

Margaret Black\'s family owns five parcels of farmland broken into a southeast sector, north sector, northwest sector, west sector, and southwest sector. Margaret is involved primarily in growing wheat, alfalfa, and barley crops and is currently preparing her production plan for next year. The Pennsylvania Water Authority has just announced its yearly water allotment, with the Black farm receiving 7,400 acre-feet. Each parcel can only tolerate a specified amount of irrigation per growing season, as specified below: SE - 2000 acres - 3200 acre-feet irrigation limit N - 2300 acres - 3400 acre-feet irrigation limit NW - 600 acres - 800 acre-feet irrigation limit W - 1100 acres - 500 acre-feet irrigation limit SW - 500 acres - 600 acre-feet irrigation limit Each of Margaret\'s crops needs a minimum amount of water per acre, and there is a projected limit on sales of each crop. Crop data follows: Wheat - 110,000 bushels (Maximum sales) - 1.6 acre-feet water needed per acre Alfalfa - 1800 tons (Maximum sales) - 2.9 acre-feet water needed per acre Barley - 2200 tons (Maximum sales) - 3.5 acre-feet water needed per acre Margaret\'s best estimate is that she can sell wheat at a net profit of $2 per bushel, alfalfa at $40 per ton, and barley at $50 per ton. One acre of land yields an average of 1.5 tons of alfalfa and 2.2 tons of barley. The wheat yield is approximately 50 bushels per acre. (a) Formulate Margaret\'s production plan. (b) What should the crop plan be, and what profit will it yield? (c) The Water Authority informs Margaret that for a special fee of $6000 this year, her farm will qualify for an additional allotment of 600 acre-feet of water. How should she respond? I NEED THIS PROBLEM DONE IN EXCEL CAN YOU PLEASE SHOW ME HOW

Solution

PARCEL                                      AREA(ACRES)                                                WATER IRRIGATION LIMIT

                                                                                                                             (ACER-FEET)

SOUTHWEST                              2000                                                                   3200

NORTH                                       2300                                                                    3400

NORTHWEST                               600                                                                      800

WEST                                         1100                                                                      500

SOUTHWEST                               500                                                                       600

Each of Mararet\'s crops needs a maximum amount of water per acre, and there is a projected limit on sales

of each crop. Crop data follow

CROP                 MAXIMUM SALES               WATER NEEDED PER ACRE   NET PROFIT YIELD PER ACRE

WHEAT              110,000 BUSHELS                 1.6                                            $2.00   50 BUSHELS

ALFALFA            1,800 TONS                           2.9                                            $40.00 1.5 TON

BARLEY             2,200 TONS                           3.5                                            $50.00   2.2 TONS

ANSWER

1250 WHEAT IN NORTH PARCEL

500   WHEAT IN NORTHWEST PARCEL

312.5 WHEAT IN WEST PARCEL

137.5 WHEAT IN SOUTHWEST PARCEL

131   ALFALFA IN SOUTHWEST

600 BARLEY IN SOUTH EAST

400 BARLY IN NORTH

PROFIT =PRESENT VALUE OF FUTURE CASH FLOWS/INITIAL INVESTMENT

WHERE PRESENT VALUE=cash flow/(1+r)^n

here r is the discount rate and n is the period in with the cash flow occurs

PROFIT=$337.862.10