Demand of each region is not specified.
Therefore, for sake of understanding, a hypothetical demand of
10,000 units is considered for each region.If actual demand is
different, same spreadsheet model can be used by changing the
actual demand of each region.
Spreadsheet model and solution using Excel solver is
following
B19 @ fx =SUMPRODUCT(B12:F16,B3:F7) А с E G H 1 K M N 0 Р Q R S Solver Parameters X Set Objective: SB519 To: O Max Min value of: 0 By Changing Variable Cells: SB$12:$F$16 $: Subject to the Constraints: SF512:$F$16 - binary SB$17:$E$17 = $B$8:SE8 SG$12:$G$16 >= 0 SI$12:$I$13 > SK$12:$K$13 SIS14 <- SK$14 Add Change Additional constraints Delete 1 Parameter Table 2 East West South North Fixed Cost Capacity 3 Barcelona 55 43 46 60 70,000 22,000 4 Budapest 40 50 45 55 50,000 18,000 5 Helsinki 45 60 50 30 80,000 15,000 6 Lyon 57 40 37 49 95,000 13,000 7 Munich 43 43 52 37 75,000 24,000 8 Demand 10,000 10,000 10,000 10,000 9 10 Optimal distribution plan Open w/h = Spare 11 East West South North 1, otherwise Capacity 12 Barcelona 0 10,000 2,000 0 1 10,000 13 Budapest 10,000 0 8,000 0 1 0 14 Helsinki 0 0 0 10,000 1 5,000 o o o 0 0 0 16 Munich 0 0 0 0 0 0 17 Column 10,000 10,000 10,000 10,000 18 19 Total Cost = 1,782,000 Reset All LHS RHS 2 Make Unconstrained variables Non-Negative 3 0 0 Select a Solving Method Simplex LP Options 0 1 15 Lyon Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear Select the LP Simplex engine for linear Solver Problems. Solve Close 20
SUM Xfx =SUMPRODUCT(B12:F16,B3:F7) А B с D E F G H JK East West South North Capacity 46 60 43 50 Fixed cost 70000 50000 22000 18000 45 50 60 1 Parameter Tal 2 3 Barcelona 55 4 Budapest 40 5 Helsinki 45 6 Lyon 57 7 Munich 143 8 Demand 10000 9 10 Optimal distril 55 30 49 15000 80000 95000 40 37 13000 124000 75000 43 10000 52 10000 37 10000 Additional const 11 =B2 -D2 -E2 =C2 10000 Open w/h = 1, otherwise = 0 1 10 12 =A3 13 A4 LHS RHS =SUM(F12:F16) >= 2 =F12-F13 >= 0 F1S+F16 <= 1 10000 0 1 Spare Capacity =G3*F12-SUM(B12:E12) =G4*F13-SUM(B13:E13) =G5*F14-SUM(B14:E14) =G6*F15-SUM(B15:E15) |=G7*F16-SUM(B16:E16) 1 2000 0 8000 0 0 10000 0 0 lo 0 =SUM(D12:016) =SUM(E12:E16) 0 10 14 =AS 10 0 15 =A6 0 0 0 0 17 Column Total=SUM(B12:B16) =SUM(C12:016) 18 19 =SUMPRODUCT( B12:F16 , B3:F7) 20 21 16 A7