Optimal Shipping Costs and Ice Cream Profit

This assignment discusses the optimization of shipping costs between nodes and the maximization of profit from ice cream flavors. It includes the objective function, constraints, and the optimal solutions obtained using the Simplex method in Excel Solver.

Assignment
Assignment
Name of the Student
Name of the University
Author Note

Assignment
Problem 1:
1) The shipping cost per unit between the nodes is given in the following table. The black
cells represent the shipping cost is zero.
To Node
From
Node FA
F
B
F
C W1 W2 Client 1 Client 2
FA 10 6 10 10 40 20
FB 18 18 5 2 16 30
FC 0.8 16 2 1 20 24
W1 2.4 4 24
W2 0.8 4 24
Client 1 5
Client 2 14
Let, the optimum number of transaction of units between the nodes are given by aij (i≠j)
where i = 1 to 7 and j = 1 to 7. The variable names are assigned to the nodes by its
corresponding row and column number.
The capacities of the factories and the demands of clients are given in the following table. It
is also given that the maximum exchange of the units possible between any two nodes is 150
units.
Capacity (# of units)
Factory A 600
Factory B 500
Factory C 200
Demand (# of units)
Client 1 550

Assignment
Client 2 400
Hence, objective function
Minimize, total shipping costs between nodes C =
10*a12 + 6*a13 + 10*a14 + 10*a15 + 40*a16 + 20*a17 +
18*a21 + 18*a23 + 5*a24 + 2*a25 + 16*a26 + 30*a27 +
0.8*a31 + 16*a32 + 2*a34 + a35 + 20*a36 + 24*a37 +
2.4*a45 + 4*a46 + 24*a47 +
0.8*a54 + 4*a56 + 24*a57 + 5*a67 + 14*a76
Subjected to constraints:
a12 + a13 + a14 + a15 + a16 + a17 <= 600 (Factory A capacity)
a21 + a23 + a24 + a25 + a26 + a27 <= 500 (Factory B capacity)
a31 + a32 + a34 + a35 + a36 + a37 <= 200 (Factory C capacity)
a16 + a26 + a36 + a46 + a56 + a76 >= 550 (client 1 demand)
a17 + a27 + a37 + a47 + a57 + a67 >= 400 (client 2 demand)
all aij (i≠j) <= 150
all aij are integers.
2) The optimal solution is found using Simplex method in excel solver and the solution is
given below.

Assignment
a12 a13 a14 a15 a16 a17 a21 a23 a24 a25 a26 a27 a31 a32 a34 a35 a36 a37 a45 a46 a47 a54 a56 a57 a67 a76
0 0 0 0 0 150 0 0 0 0 100 0 0 0 0 0 0 100 0 150 0 0 150 0 150 150
Objective function( in $)
10*a12 + 6*a13 + 10*a14 + 10*a15 + 40*a16 + 20*a17 + 11050
18*a21 + 18*a23 + 5*a24 + 2*a25 + 16*a26 + 30*a27 +
0.8*a31 + 16*a32 + 2*a34 + a35 + 20*a36 + 24*a37 +
2.4*a45 + 4*a46 + 24*a47 +
0.8*a54 + 4*a56 + 24*a57 + 5*a67 + 14*a76
Constraints
a12 + a13 + a14 + a15 + a16 + a17 <= 600 150
a21 + a23 + a24 + a25 + a26 + a27 <= 500 100
a31 + a32 + a34 + a35 + a36 + a37 <= 200 100
a16 + a26 + a36 + a46 + a56 + a76 >= 550 550
a17 + a27 + a37 + a47 + a57 + a67 >= 400 400
Hence, the total minimum shipping costs between the nodes is $11050 with the number of
units between the nodes is zero except a17 = 150, a26 = 100, a37 = 100, a46 = 150, a56 =
150, a67 = 150 and a76 = 150.
3) Sensitivity analysis:

Assignment
Cell Name Original Value Final Value
$B$7 10*a12 + 6*a13 + 10*a14 + 10*a15 + 40*a16 + 20*a17 + a13 0 11050
Variable Cells
Cell Name Original Value Final Value Integer
$A$2 a12 0 0 Integer
$B$2 a13 0 0 Integer
$C$2 a14 0 0 Integer
$D$2 a15 0 0 Integer
$E$2 a16 0 0 Integer
$F$2 a17 0 150 Integer
$G$2 a21 0 0 Integer
$H$2 a23 0 0 Integer
$I$2 a24 0 0 Integer
$J$2 a25 0 0 Integer
$K$2 a26 0 100 Integer
$L$2 a27 0 0 Integer
$M$2 a31 0 0 Integer
$N$2 a32 0 0 Integer
$O$2 a34 0 0 Integer
$P$2 a35 0 0 Integer
$Q$2 a36 0 0 Integer
$R$2 a37 0 100 Integer
$S$2 a45 0 0 Integer
$T$2 a46 0 150 Integer
$U$2 a47 0 0 Integer
$V$2 a54 0 0 Integer
$W$2 a56 0 150 Integer
$X$2 a57 0 0 Integer
$Y$2 a67 0 150 Integer
$Z$2 a76 0 150 Integer
Constraints
Cell Name Cell Value Formula Status Slack
$B$16 a12 + a13 + a14 + a15 + a16 + a17 <= 600 a13 150 $B$16<=600 Not Binding 450
$B$17 a21 + a23 + a24 + a25 + a26 + a27 <= 500 a13 100 $B$17<=500 Not Binding 400
$B$18 a31 + a32 + a34 + a35 + a36 + a37 <= 200 a13 100 $B$18<=200 Not Binding 100
$B$19 a16 + a26 + a36 + a46 + a56 + a76 >= 550 a13 550 $B$19>=550 Binding 0
$B$20 a17 + a27 + a37 + a47 + a57 + a67 >= 400 a13 400 $B$20>=400 Binding 0
$A$2 a12 0 $A$2<=150 Not Binding 150
$B$2 a13 0 $B$2<=150 Not Binding 150
$C$2 a14 0 $C$2<=150 Not Binding 150
$D$2 a15 0 $D$2<=150 Not Binding 150
$E$2 a16 0 $E$2<=150 Not Binding 150
$F$2 a17 150 $F$2<=150 Binding 0
$G$2 a21 0 $G$2<=150 Not Binding 150
$H$2 a23 0 $H$2<=150 Not Binding 150
$I$2 a24 0 $I$2<=150 Not Binding 150
$J$2 a25 0 $J$2<=150 Not Binding 150
$K$2 a26 100 $K$2<=150 Not Binding 50
$L$2 a27 0 $L$2<=150 Not Binding 150
$M$2 a31 0 $M$2<=150 Not Binding 150
$N$2 a32 0 $N$2<=150 Not Binding 150
$O$2 a34 0 $O$2<=150 Not Binding 150
$P$2 a35 0 $P$2<=150 Not Binding 150
$Q$2 a36 0 $Q$2<=150 Not Binding 150
$R$2 a37 100 $R$2<=150 Not Binding 50
$S$2 a45 0 $S$2<=150 Not Binding 150
$T$2 a46 150 $T$2<=150 Binding 0
$U$2 a47 0 $U$2<=150 Not Binding 150
$V$2 a54 0 $V$2<=150 Not Binding 150
$W$2 a56 150 $W$2<=150 Binding 0
$X$2 a57 0 $X$2<=150 Not Binding 150
$Y$2 a67 150 $Y$2<=150 Binding 0
$Z$2 a76 150 $Z$2<=150 Binding 0
$A$2:$Z$2=Integer

Assignment
Hence, from the above sensitivity analysis it is evident that the maximum shipping load
between the nodes is under 150 and most of the constraints variable is not binding for integer
constraints.
Question 2:
a) The supply of ingredients (in kg) Milk, Sugar and Cream constraints are given in the
following table.
Milk Sugar Cream
200 150 60
The profit matrix is given below.
Chocolate
(in $)
Vanilla (in $) Banana (in
$)
1 0.9 0.95
The basic ingredients that are needed for making flavours (in kg) are given in the following
table.
Chocolate Vanilla Banana
Milk 0.45 0.5 0.4
Sugar 0.5 0.4 0.4
Cream 0.1 0.9 0.95
Let the optimal number of chocolate, vanilla and banana ice creams that are needed for
maximum profit are C, V and B respectively.
Hence, the objective function is

Assignment
Maximize,
Profit = 1*C + 0.9*V + 0.95*B
Subjected to constraints:
0.45*C + 0.5*V + 0.4*B <= 200
0.5*C + 0.4*V + 0.4*B <= 150
0.1*C + 0.9*V + 0.95*B <= 60
C, V and B are positive integers
C V B
273 0 33
Objective function
Profit(P) 304.35
Constraints
0.45*C + 0.5*V + 0.4*B <=
200
136.05
0.5*C + 0.4*V + 0.4*B <=
150
149.7
0.1*C + 0.9*V + 0.95*B <=
60
58.65

Assignment
Now, the maximum profit of is obtained when the optimum quantities of Chocolate, Vanilla
and Banana are 273, 0 and 33.
b) Now, when the profit from banana changes to $ 0.92 then the objective function will be
changed i.e. the amount of the profit but the optimal solution or the number of C, V and B
will remain same.
c) Now, when the company buys 15 kg of sugar from at a cost of $15 then the objective
function will be
Profit(in $) = 1*C + 0.9*V + 0.95*B – 15
Constraints:
0.45*C + 0.5*V + 0.4*B <= 200
0.5*C + 0.4*V + 0.4*B <= 165
0.1*C + 0.9*V + 0.95*B <= 60
The modified problem is solved via solver in excel as given below.
C V B
306 0 30
Objective function
Profit(P) 319.5
Constraints

Assignment
0.45*C + 0.5*V + 0.4*B <=
200
149.7
0.5*C + 0.4*V + 0.4*B <=
165
165
0.1*C + 0.9*V + 0.95*B <=
60
59.1
Now, the optimum quantities of C, V and B are 306,0 and 30 respectively for a maximum
profit of $319.5.
This profit is more than the previous profit of $304.35 and hence company should buy 15 kg
sugar in just $15.
d) Now, when the 3 kg of cream have gone sour then the cream capacity is reduced by 3 kg
or cream capacity = 60 – 3 = 57 kg.
Hence, modified problem is
Profit (in $) = 1*C + 0.9*V + 0.95*B
Constraints:
0.45*C + 0.5*V + 0.4*B <= 200
0.5*C + 0.4*V + 0.4*B <= 150
0.1*C + 0.9*V + 0.95*B <= 57
Now, the optimal solution is given below.

Assignment
C V B
276 0 30
Objective function
Profit(P) 304.5
Constraints
0.45*C + 0.5*V + 0.4*B <=
200
136.2
0.5*C + 0.4*V + 0.4*B <=
150
150
0.1*C + 0.9*V + 0.95*B <=
57
56.1
Hence, the profit in this condition is $ 304.5 and the numbers of C, V and B are 276, 0 and 30
respectively.
e) The shadow price for milk constraint is the difference between the optimized value of the
function and the value of the objective function evaluated when the milk constraint value is
increased by 1.
Now, it can be seen in solver by n increasing the milk constraint by unity doesn’t change the
optimal solution. Hence, the shadow price for milk constraint is zero.
Question 3:
a) The northwest Corner method is given below.

Assignment
b) The stepping stone method is given below.