With an annual demand equivalent to 10,000 packages with each package containing 500 units of devices, TBG Home electronics assembles and packages small electronic devices in two contracted manufacturing plants: Olympia, Washington and Houston, Texas. Due to capacity constraints, each contracting plant can only produce up to 7,000 packages (3.5 million units) for TBG. Since TBG has been using standardized packaging for shipping all its products, the shipping package has the same transportation characteristics (dimensions and weight).Smith noted that while transportation rates from TBG's own regional distribution centers (DCs) to each market were $0.20 per package mile, transportation rates from TBG's manufacturing plants to regional DCs differed significantly due to the competitive nature and other route-specific characteristics (see Table 3). Smith collected distance information between various points for TBG's distribution network (Table 4).

Table 3Transportation Rates for Shipments from Plants to DCs

Plant to DC (per package per mile)	Manufacturing Plant
Olympia, WA	Houston, TX
Regional DC	West DC(Sacramento, CA)	$0.25	$0.19
East DC(Richmond, VA)	$0.18	$0.15
Central DC(Topeka, KS)	$0.29	$0.22

Table 4Average Distance for TBG's Distribution Points

Distance (miles)	West DC (Sacramento, CA)	East DC (Richmond, VA)	Central DC (Topeka, KS)
Manufacturing Plant	Olympia, WA	693	2,902	1,891
Houston, TX	1,929	1,323	735
Market	NW Market	553	2,442	1,359
SW Market	755	2,296	1,147
Central Market	2,044	799	588
NE Market	3,011	548	1,480
SE Market	2,469	531	865

To minimize total transportation costs under current logistics network design, what are your recommendations on the distribution strategy (e.g., allocation of production in terms of number of packages between two contract manufacturing plants, and allocation of inventory in terms of number of packages from each contract manufacturing plant to each regional DC and from regional DC to individual markets) in order to minimize total transportation costs while satisfying all demand by each distribution channel in each market?

Use an Excel file to build Solver models.

Q6. Based on your Excel Solver solution,determinethe production volume allocation (number of packages to be manufactured in each contract manufacturing plant). Type numerical values only, use thousands separator. For example, 4,000,599. Type 0 if no package is assigned.

Olympia: packages

Houston: packages

Q7 Based on your Excel Solver solution,determinethe number of packages shipped from each contract manufacturing plant to each regional DC (Type numerical values only, use thousands separator. For example, 4,000,599. Type 0 if no package is assigned.)

From Olympia to West DC:

From Olympia to East DC:

From Olympia to Central DC:

From Houston to West DC:

From Houston to East DC:

From Houston to Central DC:

Q8 Based on your Excel Solver solution,determinethe number of packages shipped from each regional DC to each individual market.Typenumerical values only, use thousands separator. For example, 4,000,599. Type 0 if no package is assigned.

From West DC to NW Market:

From West DC to SW Market:

From West DC to Central Market:

From West DC to NE Market:

From West DC to SE Market:

From East DC to NW Market:

From East DC to SW Market:

From East DC to Central Market:

From East DC to NE Market:

From East DC to SE Market:

From Central DC to NW Market:

From Central DC to SW Market:

From Central DC to Central Market:

From Central DC to NE Market:

From Central DC to SE Market:

Q9 Basedon your Excel Solver solutions, what will be the total transportation costs under the new distribution strategy? For simplicity and as a starting point, you don't need to consider other costs, such as local stocking and delivery charges at this step(type numerical values only without the $ sign, use thousands separator, rounded to whole dollars. For example, 4,000,599)