• Author / Uploaded
• Wendy Rico

Citation preview

Microsoft Excel 12.0 Answer Report Worksheet: [8289359.xls]Sheet1 Report Created: 30-06-2012 22:27:18

Target Cell (Max) Cell Name $L$34 Cost= Max

Original Value 1790

Final Value 1790

Adjustable Cells Cell Name $H$35 Initial Solution $I$35 m4 $J$35 l4 $H$36 Initial Solution $I$36 m4 $J$36 l4 $H$37 Initial Solution $I$37 m4 $J$37 l4 $H$38 Initial Solution $I$38 m4 $J$38 l4

Original Value 1 1 0 2 1 0 2 0 0 2 0 0

Final Value

Constraints Cell Name $N$28 4000*s1+6000m1+80000*l1 $N$29 4000*s2+6000m2+80000*l2 $N$30 4000*s3+6000m3+80000*l3 $N$31 4000*s4+6000m4+80000*l4 $N$28 4000*s1+6000m1+80000*l1 $N$29 4000*s2+6000m2+80000*l2 $N$30 4000*s3+6000m3+80000*l3 $N$31 4000*s4+6000m4+80000*l4 $H$35 Initial Solution $I$35 m4 $J$35 l4 $H$36 Initial Solution $I$36 m4 $J$36 l4 $H$37 Initial Solution $I$37 m4 $J$37 l4

Cell Value 100000 140000 80000 80000 100000 140000 80000 80000 1 1 0 2 1 0 2 0 0

1 1 0 2 1 0 2 0 0 2 0 0

Formula $N$28=$K$31 $H$35=integer $I$35=integer $J$35=integer $H$36=integer $I$36=integer $J$36=integer $H$37=integer $I$37=integer $J$37=integer

Status Binding Binding Binding Binding Not Binding Not Binding Not Binding Not Binding Binding Binding Binding Binding Binding Binding Binding Binding Binding

Slack 0 0 0 0 75000 105000 60000 60000 0 0 0 0 0 0 0 0 0

$H$38 Initial Solution $I$38 m4 $J$38 l4

2 $H$38=integer Binding 0 $I$38=integer Binding 0 $J$38=integer Binding

0 0 0

A European manufacturer of industrial furniture has a factory located in Munich and four warehouses in Western Europe. The warehouses collect customer orders, which are then shipped from factory. Upon receipt, the warehouse distributes custo Daily demand at each of the four warehouses along with distance from Munich is as shown: Warehouse Daily Demand (kg) Distance (km)

Daily Warehou Demand Distance se (kg) (km) Milan 25,000 800 Paris 35,000 1,000 Copenhagen 20,000 600 Madrid 20,000 1,300 All shipments are by truck. Three truck sizes are available, with capacities of 40,000 (small), 60,000 (medium), and 80,000 kg (

Small: 100 + 0.1x euros Medium: 125 + .1x euros Large: 150 + 0.1x euros X is the distance to be traveled in kilometers. For replenishment frequency varying between one and four days for each wareh What other factors should be considered before deciding on the replenishment frequency? Solution Let

s1 s2 s3 s4 m1 m2 m3 m4 l1 l2 l3 l4

no. of small trucks to Milan no. of small trucks to Paris no. of small trucks to Copenhagen no. of small trucks tomadrid no. of medium trucks to Milan no. of medium trucks to Paris no. of medium trucks to Copenhagen no. of medium trucks tomadrid no. of large trucks to Milan no. of large trucks to Paris no. of large trucks to Copenhagen no. of large trucks tomadrid

Obj: MinZ: 800s1+1000s2+600s3+1300s4)*(0.1) 40000 s1 s2 s3 s4

60000 m1 m2 m3 m4

80000 l1 l2 l3 l4

constraints 4000*s1+6000m1+80000*l1 4000*s2+6000m2+80000*l2 4000*s3+6000m3+80000*l3 4000*s4+6000m4+80000*l4 Initial Solution 40000 60000 1 1 2 1 2 0 2 0

80000 0 0 0 0

es in Western Europe. arehouse distributes customer orders using small trucks.

(medium), and 80,000 kg (large). Transportation costs for the three types of trucks are:

d four days for each warehouse, identify the optimal transportation option and the associated cost.

00s2+600s3+1300s4)*(0.1)+(800m1+1000m2+600m3+1300m4)*(0.1)+(800l1+1000l2+600l3+1300l4)*(0.1)+(s1+s2+s3+s4)*100+(m1+m2+m

Min 25000 35000 20000 20000

Cost=

Max Constraints 100000 140000 80000 80000

100000 140000 80000 80000

1790 Optimum Cost= 1790 I small truck amd 1 medium truck to Milan 2 small truck and 1 medium truck to Paris 2 small truck to Copenhagen 2 small truck to Madrid

Other factor which can be considered are customer loss cost, backorder cost and inventory cost.

s2+s3+s4)*100+(m1+m2+m3+m4)*(125)+(l1+l2+l3+l4)*(150)