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

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 &#8804; amount available at supplier
All &#8805; 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 &#8804; available 1 184 ...

Solution Summary

Solution guide provided for you in Excel.

\$2.19