Integer values maximization

I need help in devising computer solutions and sensitivity analysis for linear programming.
I have worked some of them.
Please use EXCEL and show all work.

Fruit Stand. It is harvest time in the Rio Grande Valley in South Texas. Every year at this time the Garcias set up their roadside stand and sell fresh vegetables to the local people in McAllen, Texas. The stand remains open for about 3 months and then closes for the year.

Mrs. Rosa Garcia is the grand lady of the business. She has opened this stand and been very successful in marketing to the people of McAllen. The locals look forward to her opening day each season.

This is the first week of the season that the stand will be open. It is time to cross the Rio Grande and go south of Reynosa to the Mexican farm to buy the produce from the Gonzales. The Garcias usually buy one week's worth of produce and then repeat the process each week for the season.

The Gonzales know the Garcias. Over the years they have sold many fruits and vegetables to them. Being steady customers they have a standard price of selling to the Garcias. The price is $50.00 (American) for all that can be loaded in one standard size Ford pick-up truck. The volume of the body is five feet by eight feet by two feet.

Watermelons, lemons, oranges, grapefruit, squash, honeydews, and tangerines are now ripe and ready for sale. The problem is what and how much to buy of each farm product?

Rosa has checked local prices and she feels that the stand can easily sell all of the produce for the following prices this next week. She has placed these prices in a table.

Rosa states, "We must stock the stand. Bring back at least the following quantities." These minimum quantities are also added to the table.

The table is as follows.
Produce Product Sale Price Min Quantity Needed
Water melon 4.00 10
Lemon .43 50
Oranges .36 30
Grapefruit .57 20
Squash .17 200
Honey Dews 1.35 20
Tangerines .20 20

"I don't want all citrus in the stand. Make sure the lemons, oranges, grapefruit and tangerines do not total more than 200," Rosa demands.

Jesus is driving the truck down to Mexico to pick up the produce. Jesus must be sure he does not overload the old Ford pick-up. The produce takes room and has much weight. He knows that if he "breaks down" the delay will cause the loss of the fruit. Fresh fruit is very perishable. The Garcias know they must have fresh products to sell.

Jesus has his own table of the amount of space and weight from each of the produce products. These represent space and weight for one of each item.

Produce Product Cubic feet Weight in pounds
Water melon 2.0500 25.00
Lemon .0175 .30
Oranges .0255 1.00
Grapefruit .0600 1.90
Squash .1040 .45
Honey Dews .4135 6.25
Tangerines .0190 .60

Jesus wants to keep the weight of the truck under 1000 pounds. He usually spends $15.00 on gasoline for the trip. He fills up in Mexico where the gasoline is much cheaper.

There is one other constraint. Jesus found out with a phone call that only 50 watermelons are available. Even if the Garcias wanted more than 50 watermelons, they are not available.

a) Build a model for the trip. Attach the Excel solution of the model.

b) What are the quantities of each produce product purchased?

c) What is the profit for the stand this week? Do not forget that $50 must be paid for the produce and $15.00 in gasoline is consumed.

d) Jesus can put the side rails on the old truck and increase the volume by about 6 cubic feet. The price will still be $50 for the load. Will this increase the profit? What would the new solution be in this situation? Give the objective function value.

e) If Mrs. Garcia had set 15 for the minimum number of watermelons, would the objective function value have been more or less at the optimal solution? If a change occurred, how much would this change be?