Sharon owns an indoor/outdoor decorating firm in North Dakota and needs white sand and raw cotton for a project for one of her biggest customers. She needs 20,000 pounds of white sand and 6,000 pounds of raw cotton. One of her suppliers can sell her up to 15,000 pounds of white sand for $0.20 per pound and as much raw cotton as she wants for $0.50 per pound. One of the trucks that Sharon’s company owns has just made a delivery in Key West, Florida, and is scheduled to return empty to North Dakota. Sharon has just found out that white sand can be purchased in Florida for $0.09 per pound and that raw cotton can be purchased in Alabama for $0.36 per pound. The amount the truck can carry is limited by weight restrictions to 10,000 pounds. Also, load balancing must be taken into consideration. To ensure proper weight distribution to maintain stability for the truck, the weight of the sand on the truck must be at least twice the weight of the raw cotton on the truck. Assume that the additional cost for picking up the sand and raw cotton and for the increased consumption of diesel fuel for the truck to carry the added weight is negligible.
a) (8 points) Formulate Sharon’s problem using linear programming.
– All decision variables
– Objective function
– All relevant constraints.
b) (6 points) Using Excel solve the LP model of part a). Using the Excel Answer Report identify the optimal procurement plan and all binding constraints. Please, include the Excel Answer Report table in your solution.
c) (11 points) Using the Excel Sensitivity Report (when possible) to answer the following questions:
– (3 points) What is the shadow price for the constraint that forces Sharon to purchase 6,000 pounds of raw cotton? How do you interpret this value?
– (4 points) How much would Sharon be willing to pay to decrease the project’s white sand requirement to 10,000 pounds? What about 5,000 pounds?
– (4 points) Sharon can rent an additional truck to ship white sand from Florida and raw cotton from Alabama. The following options are available:
§ Small Truck: cost = $400 and capacity = 3,000 pounds.
§ Medium Truck: cost = $600 and capacity = 6,000 pounds.
§ Large Truck: cost = $800 and capacity = 9,000 pounds. Would you recommend Sharon to rent an extra truck? If so, which one?
Please make sure to include the Excel Sensitivity Report table in your answer.
I checked, the solution I provided is correct. The Excel file gives the solver solution, it is not the summary. If you click... View the full answer