excel solver problem using lp

Project Description:

jim owns an indoor/outdoor-decorating firm in iowa and needs white sand and raw cotton for a project for one of her biggest customers. he needs 20,000 pounds of white sand and 6,000 pounds of raw cotton. a local supplier can sell him up to 15,000 pounds of white sand for $0.20 per pound and as much raw cotton as he wants for $0.50 per pound. one of the trucks that jim’s company owns has just made a delivery in orlando, florida, and is scheduled to return empty to iowa. jim 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 georga 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 can be ignored.

using excel solver, optimize 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.

answer the questions below and justify the answers:
1) should jim negotiate a white sand volume beyond 15,000 with the local supplier?
2) how much would jim be willing to pay to decrease the project’s white sand requirement from 20,000 to i) 10,000 pounds?
ii) 5,000 pounds?

3) can you quantify the economic cost of the constraint that forces the truck to use (at least) a 2:1 ratio to balance the load of white sand and raw cotton?
4) would you recommend jim to rent an additional truck to ship white sand from florida and raw cotton from georga?
Skills Required:
Project Stats:

Price Type: Fixed

Project Budget: $10 to $20
In Progress
Total Proposals: 6
1 Current viewersl
160 Total views
Project posted by:


Proposals Reputation Price offered
  • 4.5
    98 Jobs 44 Reviews
    $35 in 1 Day
  • 4.2
    69 Jobs 35 Reviews
    $25 in 1 Day
  • 4.6
    489 Jobs 297 Reviews
    $30 in 1 Day
  • 4.5
    107 Jobs 59 Reviews
    $35 in 1 Day
  • 4.7
    93 Jobs 44 Reviews
    $25 in 1 Day
  • 4.9
    374 Jobs 172 Reviews
    $20 in 2 Days