UBike is a bicycle manufacturer based in Japan. Haruki, the supply chain manager at Ubike is designing the manufacturing network and has selected four potential sites - Toyko, Sapporo, Fukuoka, and Sendai. The below table shows the annual fixed costs at the four locations as well as the cost of producing and shipping a bicycle to each of the four markets. Note that plants could have a capacity of either 250,000 or 500,000 units and that each open plant should have at least 60% capacity utilisation.

Production and Transportation Costs ($/unit)

Tokyo Sapporo Fukuoka Sendai Annual Demand

Southwest 50 80 35 52 210,000

Northeast 65 35 70 40 150,000

Central 30 80 55 53 160,000

East 48 30 42 35 170,000

Annual fixed cost of 250,000 $8 million $6 million $7 million $7,4 million

Annual fixed cost of 500,000 $12 million $10 million $11 million $11.4 million

The following is my ILP model:

Let xij be the number of bicycles produced at plant i and shipped to market j (i = 1, 2, 3, 4; j = 1, 2, 3, 4)

Let x11 = the number of units produced at Tokyo plant and shipped to Southwest

Let x12 = the number of units produced at Tokyo plant and shipped to Northwest

Let x13 = the number of units produced at Tokyo plant and shipped to Central

Let x14 = the number of units produced at Tokyo plant and shipped to East

Let x21 = the number of units produced at Sapporo plant and shipped to Southwest

Let x22 = the number of units produced at Sapporo plant and shipped to Northwest

Let x23 = the number of units produced at Sapporo plant and shipped to Central

Let x24 = the number of units produced at Sapporo plant and shipped to East

Let x31 = the number of units produced at Fukuoka plant and shipped to Southwest

Let x32 = the number of units produced at Fukuoka plant and shipped to Northwest

Let x33 = the number of units produced at Fukuoka plant and shipped to Central

Let x34 = the number of units produced at Fukuoka plant and shipped to East

Let x41 = the number of units produced at Sendai plant and shipped to Southwest

Let x42 = the number of units produced at Sendai plant and shipped to Northwest

Let x43 = the number of units produced at Sendai plant and shipped to Central

Let x44 = the number of units produced at Sendai plant and shipped to East

yij is a binary variable indicating whether plant i is open to operate (1 if open, 0 otherwise)

Let y1 = Tokyo plant

Let y2 = Sapproro plant

Let y3 = Fukuoka plant

Let y4 = Sendai plant

Min. 8,000,000y1 + 12,000,000y1 + 6,000,000y2 + 10,000,000y2 + 7,000,000y3 + 11,000,000y3 + 7,400,000y4 + 11,400,000y4 + 50x11 + 65x12 + 30x13 + 48x14 + 80x21 + 35x22 + 80x23 + 30x24 + 35x31 + 70x32 + 55x33 + 42x34 + 52x41 + 40x42 + 53x43 + 35x44

Subject to

Demand constraint for each market

x11 + x21+ x31+ x41 = 210,000 (Demand for the Southwest market)

x12 + x22+ x32+ x42 = 150,000 (Demand for the Northwest market)

x13 + x23+ x33+ x43 = 160,000 (Demand for the Central market)

x14 + x24+ x34+ x44 = 170,000 (Demand for the East market)

Capacity constraint for each plant

x11 + x12+ x13+ x14

x21 + x22+ x23+ x24

x31 + x32+ x33+ x34

x41 + x42+ x43+ x44

60% plant utilisation constraint

x11 + x12+ x13+ x14 >= y1 x 60% (Utilisation constraint for the Toyko plant)

x21 + x22+ x23+ x24 >= y2 x 60% (Utilisation constraint for the Sapporo plant)

x31 + x32+ x33+ x34 >= y3 x 60% (Utilisation constraint for the Fukuoka plant)

x41 + x42+ x43+ x44 >= y4 x 60% (Utilisation constraint for the Sendai plant)

A plant can be either opened or closed

y11

y21

y31

y41

Non-negativity, where the number of units produced and shipped cannot be a negative amount

xij > 0 for all i and j

Binary constraint

yi = {0, 1} for all i

Need some guidance for both the model I set up and a rough visual explanation of how I should set it up on Excel cause I can't seem to get the Solver to solve it. Attached my Solver attempt for reference, please let me know how I should set it up in order for me to get an optimal solution!

Subject to the Constraints: $B$16:$1$16= binary $B$21:$1$21=$B$23:$1$23 $1$17:$1$20=$K$17:$K$20 Make Unconstrained Variables Non-Negative Select a Solving Method: Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are nonsmooth. Subject to the Constraints: $B$16:$1$16= binary $B$21:$1$21=$B$23:$1$23 $1$17:$1$20=$K$17:$K$20 Make Unconstrained Variables Non-Negative Select a Solving Method: Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are nonsmooth