Share
Explore BrainMass

TILLERMAN'S TEA TASK - Determine target and constraint cells

Attached is the file see tabs with solver answer and tab with tables and formulas

TILLERMAN'S TEA TASK

PROBLEM: Production costs, including shipping are to high
OBJECTIVE: Lower the cost

Brewing Facilities Bottle Suppliers
1- Des Moines 1- Biggs Bottles, St. Louis
2- St. Louis 2- Mighty Miss Glass Co., Davenport
3- St. Paul 3- Downstate Glass Corp., Springfield, Ill
4- Chicago 4- Kansas Bottles, Kansas City Kansas
i=brewing facilities 5- Haffle Glass Co., Cedar Rapids
j=bottle suppliers

OBJECTIVE FUNCTION OBJECTIVE FUNCTION FORMULA

Bottle and shipping costs per case lots of 100's cases to brewer from supplier MIN: c11x11 + c12x12 + c13x13 + c14x14 + c15x15+ ...
(in $)
x = # bottles received from the supplier to the brewer plant
cij 1 2 3 4 TARGET CELL
1 $960 $444 $1,272 $1,104 311969.88 Formula
2 $672 $1,272 $1,632 $1,032
3 $1,380 $816 $1,464 $1,176
4 $960 $960 $1,200 $1,320
5 $828 $1,668 $1,548 $1,428
total $23,136

CONSTRAINTS # bottles received = demand
# bottles shipped ≤ amount available at supplier
All ≥ 0

Weekly demand from plants in case lots CONSTRAINTS FORMULA
Supplier 1 2 3 4 total demand total availability
1 6.95 62.82 33.31 80.92 184 184
2 0 0 0 37 37 37 DEMAND
3 15.71 0 0 0 15.7 46 X11 + X12+X13+X14+X15=DEMAND 1= 59.66 ...
4 0 0 46 0 46 46
5 37 0 0 0 37 37 AVAILABILITY
total 59.66 62.82 79.31 117.92 319.7 350 x11+x21+x31+x41 ≤ available 1 184 ...

Attachments

Solution Summary

Solution guide provided for you in Excel.

$2.19