Problems:
Integer Programming using Excel Solver
1. The Roadnet Transport Company expanded its shipping capacity by purchasing 90 trailer trucks from a competitor that went bankrupt. The company subsequently located 30 of the purchased trucks at each of its shipping warehouses in Charlotte, Memphis, and Louisville. The company makes shipments from each of these warehouses to terminals in St. Louis, Atlanta, and New York. Each truck is capable of making one shipment per week. The terminal managers have indicated their capacity of extra shipments. The manager at St.Louis can accommodate 40 additional trucks per week, the manager at Atlanta can accommodate 60 additional trucks, and the manager at New York can accommodate 50 additional trucks. The company makes the following profit per truckload shipment from each warehouse to each terminal. The profits differ as a result of differences in products shipped, shipping costs, and transport rates:
Determine how many trucks to assign to each route (i.e. warehouse to terminal) in order to maximize profits. (using integerprogramming in excel solver)
Terminal (profit)
Warehouse St. Louis Atlanta New York
Charlotte $1,800 $2,100 $1,600
Memphis 1,000 700 900
Louisville 1,400 800 2,200
2. The Easy Time Grocery chain operates in major metropolitan areas on the East Coast. The stores have a no-frills approach, with low overhead and high volume. They generally buy their stock in volume at low prices. However, in some cases they actually buy stock at stores in other areas and ship it in. They can do this because of high prices in the cities they operate in compared with costs in other locations. One example is baby food. Easy Time purchases baby food at stores in Albany, Binghamton, Claremont, Dover, and Edison and then trucks it to six stores in and around New York City. The stores in the outlying areas know what Easy Time is up to, so they limit the number of cases of baby food Easy Time can purchase. The following table shows the profit Easy Time makes per case of baby food, based on where the chain purchases it and at which store it is sold, plus the available baby food per week at purchase locations and the shelf space available at each Easy Time store per week:
Determine where Easy Time should purchase baby food and how the food should be distributed to maximize profit. (using integer programming with excel solver)
Easy Time Store (profit/case)
Purchase Loc. 1 2 3 4 5 6 Supply
Albany $9 $8 $11 $12 $7 $8 26
Binghmtn 10 10 8 6 9 7 40
Claremont 8 6 6 5 7 4 20
Dover 4 6 9 5 8 10 40
Edison 12 10 8 9 6 7 45
Demand 25 15 30 18 27 35
3. Suppose that in Problem 23 Easy Time can purchase all the baby food it needs from a New York City distributor at a price that will result in a profit of $9 per case at stores 1, 3, and 4; $8 per case at stores 2 and 6; and $7 per case at store 5. Should Easy Time purchase all, none, or some of its baby food from the distributor rather than purchase it at other stores and truck it in? (using integer programming with excel solver)
4. Computers Unlimited sells microcomputers to universities and colleges on the East Coast and ships them from three distribution warehouses. The firm is able to supply the following numbers of microcomputers to the universities by the beginning of the academic year:
Distribution Warehouse Supply (microcomp)
1.Richmond 420
2.Atlanta 610
3.Washington DC 340
Total 1,370
Four universities have ordered microcomputers that must be delivered and installed by the beginning of the academic year:
University Demand (microcomp)
A.Tech 520
B.A & M 250
C.State 400
D.Central 380
Total 1,550
The shipping and installation costs per microcomputer from each distributor to each university are as follows:
To Cost
From A B C D
1 $22 $17 $30 $18
2 15 35 20 25
3 28 21 16 14
Solve the problem by using the computer. (using integer programming and excel solver)
5. In Problem 15, Computers Unlimited wants to better meet demand at the four universities it supplies. It is considering two alternatives: (1) expand its warehouse at Richmond to a capacity of 600, at a cost equivalent to an additional $6 in handling and shipping per unit; or (2) purchase a new warehouse in Charlotte that can supply 300 units with shipping costs of $19 to Tech, $26 to A & M, $22 to State, and $16 to Central. Which alternative should management select, based solely on transportation costs (i.e. no capital costs)? (using integer programming and excel solver)