Question

Bindley Corporation has a one-year contract to supply motors for all washing machines produced by Rinso Ltd. Rinso manufactures the washers at four locations around the country: New York, Fort Worth, San Diego, and Minneapolis. Plans call for the following numbers of washing machines to be produced at each location:

New York	70,000
Fort Worth	67,000
San Diego	98,000
Minneapolis	85,000

Bindley has three plants that can produce the motors. The plants and production capacities are

Boulder	110,000
Macon	114,000
Gary	121,000

Due to varying production and transportation costs, the profit Bindley earns on each 1,000 units depends on where they were produced and where they were shipped. The following table gives the accounting department estimates of the dollar profit per unit. (Shipment will be made in lots of 1,000.)

	SHIPPED TO
PRODUCED AT	NEW YORK	FORT WORTH	SAN DIEGO	MINNEAPOLIS
Boulder	15	11	12	13
Macon	22	21	18	14
Gary	25	10	24	19

Question: Find the optimal solution using Microsoft Excel.

	Candidate Solution Total Shipped Boulder    Macon Gary Total shipped

	Profit Boulder    Macon Gary Total profit

Answer 1

Let $X_{ij}$ number of lots (each lot is of size 1000) of motors shipped from plant i to location j, where i=1,2,3 correspond to {Boulder, Macon,Gary} and j=1,2,3,4 correspond to {NEW YORK, FORT WORTH, SAN DIEGO,MINNEAPOLIS} respectively.

These are the decision variables.

The total profit (in $1000s) is

15×X11+11 × X12 + 12 × X13 + 13 × X14+ 22 × X21 +21 × X22 + 18 × X23 + 14 × X24+ 25 × X31 + 10 × X32 + 24 × X33 + 19 × X34

The constraints are

Demand at 4 locations

• Demand at New York is 70,000 units (70 lots). The lots shipped to New York should be equal to 70
  • 
    X11 + X21 + X3170
    
• Demand at Fort Worth is 67,000 units (67 lots). The lots shipped to Fort Worth should be equal to 67 $\begin{align*} X_{12}+ X_{22}+ X_{32}=67 \end{align*}$
• Demand at San Diego is 98,000 units (98 lots). The lots shipped to San Diego should be equal to 98
  • 
    X13 X23 X33 98
    
• Demand at Minneapolis is 85,000 units (85 lots). The lots shipped to Minneapolis should be equal to 85
  
  • $\begin{align*} X_{14}+ X_{24}+ X_{34}=85 \end{align*}$

Capacity constraint

• Capacity of plant at Boulder is 110,000. The total lots shipped from this plant cannot exceed 110
  • $\begin{align*} X_{11}+ X_{12}+ X_{13}+ X_{14}\le 110 \end{align*}$
• Capacity of plant at Macon is 114,000. The total lots shipped from this plant cannot exceed 114
  • $\begin{align*} X_{21}+ X_{22}+ X_{23}+ X_{24}\le 114 \end{align*}$
• Capacity of plant at Gary is 121,000. The total lots shipped from this plant cannot exceed 121
  • 
    X31 + X32+ X33 +X34 1 21
    

Shipments are made in lots of 1000s. Since we cannot ship a fraction of lot, Xs are integers.

The LP model is

Maximize

15×X11+11 × X12 + 12 × X13 + 13 × X14+ 22 × X21 +21 × X22 + 18 × X23 + 14 × X24+ 25 × X31 + 10 × X32 + 24 × X33 + 19 × X34

s.t.

• 
  X11 + X21 + X3170
  
• $\begin{align*} X_{12}+ X_{22}+ X_{32}=67 \end{align*}$
• 
  X13 X23 X33 98
  
• $\begin{align*} X_{14}+ X_{24}+ X_{34}=85 \end{align*}$
• $\begin{align*} X_{11}+ X_{12}+ X_{13}+ X_{14}\le 110 \end{align*}$
• $\begin{align*} X_{21}+ X_{22}+ X_{23}+ X_{24}\le 114 \end{align*}$
• 
  X31 + X32+ X33 +X34 1 21
  
• 
  Xij 2 0, Xij - integers, i-1,2,3;j-1,2, 3, 4
  

Prepare the following sheet

1 Profit per unit SHIPPED TO 3 PRODUCED AT NEW YORK FORT WORTH SAN DIEGO MINNEAPOLIS Capacity 4 Boulder 15 13 12 110000 5 Macon 21 18 14000 6 Gary 25 21000 7 Demand 67000 98000 5000 9 Lots to be Shipped (each lot is 10 SHIPPED TO PRODUCED AT NEW YORK | FORT WORTH | SAN DIEGO MINNEAPOLIS İTotal Shipped (10005) Profit NEW YORK FORT WORTH SAN DIEGO MINNEAPOLIS Total Profit ($1000) 12 Boulder E12 E4-SUM 12:L12) SUM(113:L13) Boulder812 84 C12 C4 SUM B12 E12) 13 Macon MaconB13 B5 C13 C5 SUM B13 E13) 14 Gary Gary 14B6 C14 C6 D14 D6 E14 E6 SUM(114:L14) SUM B14 E14) 15 Total shipped SUM(M12:M14) Total profit SUMIF12:F14) 16 Maximize 17 Profit (51000) 18 19 s.t. constraints 20 1 Capacity -F4/1000 Boulder -SUM(B12 E12) 21 =F5/1000 |Macon SUMB 13 E 13)に F6/1000 Gary -SUMB14 E14)s 23 Demand SUM(B12614) B7/1000 NEW YORK 24 -C7/1000 FORT WORTH -SUM(C12 C14) 25 | SAN DIEGO -SUM D12 D14) =07/ 1000 26 E7/1000 MINNEAPOLIS SUM(E12 E14)

get this

Profit per unit 1 SHIPPED TO 3 PRODUCED AT | NEW YORK | FORT WORTH SAN DIEGO MINNEAPOLIS Capacity 11 S 13 4 Boulder 12 S 110,000 5 Macon 18 $ 21 S 14 114,000 6 Gary 10 $ 24 $ 25 $ 19 121,000 67,000 7 Demand 70,000 98,000 85,000 9 Lots to be Shipped (each lot is of size 1000 units) 10 SHIPPED TO Total Profit Total Shipped PRODUCED AT | NEW YORK FORT WORTH SAN DIEGO MINNEAPOLIS Profit NEW YORK | FORT WORTH SAN DIEGO MINNEAPOLIS ($1000) (1000s) Boulder S 0 12 Macons 13 Macon 0 14 Gary Gary S 0 15 Total shipped Total profit 0 16 Maximize Profit ($1000) 17 19 s.t. constraints 20 Capacity 110 Boulder 21 114 Macon 121 Gary 23 Demand 70 NEW YORK 24 67 FORT WORTH 25 98 SAN DIEGO 26 85 MINNEAPOLIS

setup the solver using data--->solver

Solver Parameters 1 Profit per unit SHIPPED TO Set Objective: SBS 1 3 PRODUCED AT | NEW YORK | FORT WORTH SAN DIEGO MINNEAPOLIS | Capacity 4 Boulder 15 S 12 S 13 11 IS 110,000 To: Max 18 S 5 Macon 22 S 21 S 14 114,000 6 Gary 10 is 25 S 24 S 19 121,000 By Changing Variable Cells: 7 Demand 70,000 67,000 98,000 85,000 SBS12:SES14 Subject to the Constraints: 9 Lots to be Shipped (each lot is of size 1000 units) SBS12 SES14 integer 10 SHIPPED TO Add SBS20:SBS22 <= $D$20:SD$22 I Shipped (1000s) SB$23 SBS26 SD$23:SD$26 PRODUCED AT |NEW YORK FORT WORTH SAN DIEGO MINNEAPOLIS Change Delete 12 13 Macon Reset All 14 Gary 15 Total shipped Load/save 16 Maximize Make Unconstrained Variables Non-Negative Profit ($1000) 17 Select a Solving Simplex LP Options 18 19 s.t. constraints Solving Method 20 Capacit 110 Boulder 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 21 114 Macon 121 Gary non-smooth. 23 Demand 70 NEW YORK 24 67 FORT WORTH Solve Help 25 98 SAN DIEGO 26 85 MINNEAPOLIS

get this

G HH 1 Profit per unit SHIPPED TO 3 PRODUCED AT | NEW YORK | FORT WORTH | SAN DIEGO | MINNEAPOLIS | Capacity 15 $ 12 $ 4 Boulder 11 S 13 110,000 5 Macon 22$ 21 IS 18 $ 114,000 14 25 S 10 S 24 S 6 Gary 19 121,000 70,000 7 Demand 98,000 85,000 67,000 9 Lots to be Shipped (each lot is of size 1000 units) 10 SHIPPED TO Total Profit Total Shipped PRODUCED AT | NEW YORK | FORT WORTH SAN DIEGO I MINNEAPOLIS NEW YORK | FORT WORTH SAN DIEGO I MINNEAPOLIS ($1000) (1000s) 12 Boulder Boulder S 1,105 $ 1.105 85 $ 2,441 2,927 13 Macon Macon 1034 1,407 S 67 114 2,352 $ Gary 575 $ 14 Gary 23 98 121 Total profit 15 Total shipped $ 6,473 320 16 Maximize Profit ($1000) S6,473 17 18 19 s.t. constraints 20 Capacity 110 Boulder 21 114 K 114 Macon 121 Gary 121 | 23 Demand 70 NEW YORK 70 24 67 FORT WORTH 25 98 SAN DIEGO 98 |= 26 85 MINNEAPOLIS 85 |

ans:

	SHIPPED TO
PRODUCED AT	NEW YORK	FORT WORTH	SAN DIEGO	MINNEAPOLIS	Total Shipped (1000s)
Boulder	0	0	0	85	85
Macon	47	67	0	0	114
Gary	23	0	98	0	121
Total shipped					320

Profit	NEW YORK	FORT WORTH	SAN DIEGO	MINNEAPOLIS	Total Profit ($1000s)
Boulder	$           -	$               -	$           -	$          1,105	$       1,105
Macon	$      1,034	$          1,407	$           -	$               -	$       2,441
Gary	$         575	$               -	$      2,352	$               -	$       2,927
Total profit					$       6,473