qt assignment
Post on 27-Jan-2016
213 Views
Preview:
DESCRIPTION
TRANSCRIPT
RED BRAND CANNERSTotal number of tomatoes, T = 3,000,000
Grade ‘A’ tomatoes, A = 20% of 3,000,000 = 600,000
Grade ‘B’ tomatoes, B = 80% of 3,000,000 =2,400,000
Cost Price, cp= $0.06
Calculating the demand of Tomatoes in the form of various products
1. Whole tomatoes = X = 800,000 casesEach case of whole tomatoes = 18 poundsTotal pounds of whole tomatoes demanded = 18 x 800,000 = 1,440,000 pounds
2. Tomato juice = Y = 50,000 casesEach case of tomato juice = 20 poundsTotal pounds of tomato juice demanded = 20 x 50,000 = 1,000,000 pounds
3. Tomato paste = Z = 80,000 casesEach case of tomato paste = 25 poundsTotal pounds of tomato paste demanded = 25 x 80,000 = 2,000,000 pounds
Composition of Various Tomato products in terms of type of tomatoes
1. Whole tomatoes = X1 and X2 where A and B signify the respective grades
The quality of whole tomatoes can be given by equation9(X1) + 5(X2) = 8(X1+X2)where 9,5 and 8 show the rating of the tomatoes=> X1 – 3X2 = 0 & X1 + X2 ≤ 1440000
2. Tomato Juice = Y1 and Y2
Tomato Juice production as per demand = 1,000,000 pounds
The quality of Tomato juice can be given by the equation9(Y1) + 5(Y2) = 6(Y1 + Y2)=> 3Y1 – Y2 = 0 & Y1 + Y2 ≤ 1,000,000
3. Tomato paste = Z1 and Z2
Tomato paste production as per demand = 2,000,000 pounds
=> Z1 + Z2 ≤ 2,000,000There are no quality constraints
Contribution without taking tomatoes into account
1. Whole tomatoes = $4 - $2.52 = $1.48/18 = P1
2. Tomato Juice = $4.5 - $3.18 = $1.32/20 = P2
3. Tomato Z1ste = $4.8 - $2.95 = $1.85/25 = P3
CASE 1:
Objective Function:
P1(X1 + X2) + P2(Y1 +Y2) + P3(Z1+Z2) – 180,000
Constraints:
X1 X2 Y1 Y2 Z1 Z2 Constraints
1 1 <=1440000
0 1 1 <= 1000000 1 1 <= 2000000 1 1 1 <= 600000 1 1 1 <= 2400000 1 -3 >= 0 3 -1 >= 0 1 >= 0
Answer Report:
Target Cell (Max)
Cell NameOriginal Value Final Value
$E$23 Profit X1 0 225200
Adjustable Cells
Cell NameOriginal Value Final Value
$E$5Quantity X1 0 525000
$F$5Quantity X2 0 175000
$G$5Quantity Y1 0 75000
$H$5Quantity Y2 0 225000
$I$5Quantity Z1 0 0
$J$5Quantity Z2 0 2000000
ConstraintsCell Name Cell Value Formula Status Slack
$K$16 Used 0
$K$16>=$M$16 Binding 0
$K$17 Used 2000000
$K$17>=$M$17
Not Binding 2000000
$K$10 Used 700000
$K$10<=$M$10
Not Binding
13700000
$K$11 Used 300000
$K$11<=$M$11
Not Binding 700000
$K$12 Used 2000000
$K$12<=$M$12 Binding 0
$K$13 Used 600000
$K$13<=$M$13 Binding 0
$K$14 Used 2400000
$K$14<=$M$14 Binding 0
$K$15 Used 0
$K$15>=$M$15 Binding 0
Sensitivity Report:
Adjustable Cells
FinalReduce
d Objective AlloX1ble AlloX1ble
Cell Name Value CostCoefficie
nt Increase Decrease
$E$5Quantity X1 525000 0 0.082
0.154666667
0.021333333
$F$5Quantity X2 175000 0 0.082 0.464
0.021333333
$G$5Quantity Y1 75000 0 0.066
0.021333333
0.154666667
$H$5Quantity Y2 225000 0 0.066
0.014222222
0.051555556
$I$5Quantity Z1 0 -0.032 0.074 0.032 1E+30
$J$5Quantity Z2
2000000 0 0.074 1E+30 0.016
Constraints
FinalShado
wConstrai
nt AlloX1ble AlloX1bleCell Name Value Price R.H. Side Increase Decrease
$K$16 Used 0 -0.008 0 1400000 200000$K$17 Used
2000000 0 0 2000000 1E+30
$K$10 Used 700000 0
14400000 1E+30 13700000
$K$11 Used 300000 0 1000000 1E+30 700000$K$12 Used
2000000 0.016 2000000 200000
466666.6667
$K$13 Used 600000 0.09 600000 600000
466666.6667
$K$14 Used
2400000 0.058 2400000
466666.6667 200000
$K$15 Used 0 -0.008 0
466666.6667 600000
CASE 2:
Objective Function:
P1(X1 + X2) + P2(Y1 +Y2) + P3(Z1+Z2) – 180,000 – 6,800
Constraints:
X1 X2 Y1 Y2 Z1 Z2
1 1 <=1448000
0
1 1 <= 1000000 1 1 <= 2000000
1 1 1 <= 680000 1 1 1 <= 2400000
1 -3 >= 0 3 -1 >= 0 1 >= 0
Answer Report:
Target Cell (Max)
Cell NameOriginal Value Final Value
$D$24 Profit X1 0 232400
Adjustable Cells
Cell NameOriginal Value Final Value
$D$6Quantity X1 0 615000
$E$6Quantity X2 0 205000
$F$6Quantity Y1 0 65000
$G$6Quantity Y2 0 195000
$H$6Quantity Z1 0 0
$I$6Quantity Z2 0 2000000
ConstraintsCell Name Cell Value Formula Status Slack
$J$11 Used 820000$J$11<=$L$11
Not Binding
13660000
$J$12 Used 260000$J$12<=$L$12
Not Binding 740000
$J$13 Used 2000000$J$13<=$L$13 Binding 0
$J$14 Used 680000$J$14<=$L$14 Binding 0
$J$15 Used 2400000$J$15<=$L$15 Binding 0
$J$16 Used1.16415E-
10$J$16>=$L$16 Binding 0
$J$17 Used 0$J$17>=$L$17 Binding 0
$J$18 Used 2000000$J$18>=$L$18
Not Binding 2000000
Sensitivity Report:
Adjustable Cells
FinalReduce
d Objective AlloX1ble AlloX1ble
Cell Name Value CostCoefficie
nt Increase Decrease$D$6
Quantity X1 615000 0 0.082
0.154666667
0.021333333
$E$6Quantity X2 205000 0 0.082 0.464
0.021333333
$F$6Quantity Y1 65000 0 0.066
0.021333333
0.154666667
$G$6
Quantity Y2 195000 0 0.066
0.014222222
0.051555556
$H$6
Quantity Z1 0 -0.032 0.074 0.032 1E+30
$I$6Quantity Z2 2000000 0 0.074 1E+30 0.016
Constraints
FinalShado
wConstrain
t AlloX1ble AlloX1bleCell Name Value Price R.H. Side Increase Decrease
$J$11 Used 820000 0 14480000 1E+30 13660000$J$12 Used 260000 0 1000000 1E+30 740000$J$13 Used 2000000 0.016 2000000
173333.3333
493333.3333
$J$14 Used 680000 0.09 680000 520000
546666.6667
$J$15 Used 2400000 0.058 2400000
493333.3333
173333.3333
$J$16 Used
1.16415E-10 -0.008 0
546666.6667 520000
$J$1 Used 0 -0.008 0 1480000 173333.333
7 3$J$18 Used 2000000 0 0 2000000 1E+30
CONCLUSION
With earlier quantity of tomatoes profits are less i.e.
Total Cont. 225200Raw Material 180000Profit 45200
Now with additional 80000 pounds @ 8.5 cents/pound scenario is:
Total Cont. 232400Raw Material 186800Profit 45600
So, its profitable to get extra 80000 pounds of tomatoes even at higher rate.
top related