How Manager Make Decision : Explained by Real Time Data Tables

The Delux Nut Company produces a deluxe mix composed of almonds, cashews, peanuts and walnuts.The deluxe mix must contain at least 10% of each kind of nut. At least half of the mix must be composed of almonds and cashews and it can contain at most 20% peanuts. The company also produces a companion mix composed of cashews, walnuts and raisins. This mix must contain at least 10% cashews, at least 30% walnuts, and between 20% and 40% raisins. Raisins are available in unlimited supply at a purchasing cost of R3,60 per kilogram. The quantities of nuts available and the purchasing costs in rand per kilogram are as follows:

Both mixes are sold in 100 g packages. The company must produce at least 4 000 packages of the deluxe mix and at least 5 000 packages of the companion mix.
(a) Formulate an LP model for this problem. 
(b) Solved the LP using LINGO or SOLVER 
(c) Write down the optimal purchasing plan and the associated purchasing costs.
(d) The suppliers inform the company that the availability of almonds has been reduced to 200 kg and that of peanuts to 100 kg. How will this influence the optimal plan and purchasing costs Give reasons for your answer. 
(e) An extra 50 kg of walnuts can be bought at R11,00 per kg. Would you make use of this offer Give reasons for your answer.

(f) Assume that the optimal plan is obtained from the original available quantities of nuts. The company now decides that it must produce at least 4 500 packages of the deluxe mix. Will the total purchasing costs be influenced? Give reasons for your answer.

(e) The supplier of peanuts announces a 40% increase in price. What will the effect of this be? Give reasons for your answer.

The Bloom & Blossom Company wants to expand its activities. In the next few years a number of large capital amounts will become untied, and they wish to plough these back into the company. The amounts that will become available at the beginning of each year are given in the following table:
 Year Amount
1 R150 000
2 R135 000
3 R210 000
4 R120 000
5 R 90 000

Four projects are being considered. The capital needed per year for each project (in R1 000) and the present value of the net return (in R1 000) on each project are given in the table below.
Capital needed Net
Project 1 2 3 4 5 return
1. Expand plant in Alberton 30 60 81 60 30 240
2. Build new plant in Port Elizabeth 60 30 120 60 60 300
3. Enlarge small machine capacity in Rosslyn 30 15 60 30 0 120
4. Enlarge large machine capacity in Rosslyn 90 60 30 30 30 210.

If a project is selected, it has to be completed as a whole. Money not used can be invested at 12% per annum for a year. Bloom & Blossom wants to select one or more of the projects in such a way that the net return on the chosen projects will be a maximum. Formulate the problem as linear integer programming model. Do not solve the model!

Nuts	Cost of purchasing (Rand/kg)	Material available (Kg)
Almonds	1200	400
Cashew	1060	200
Peanuts	280	600
Walnuts	840	300

As given in question,

In a pack of Delux mix the amount of almond cashew is Xa and Xc, … Xr respectively

Similarly in companion mix, the amount of almond, cashew and raisins will be as a, c, p,w,r

One again suppose the package of Delux mix is D and companion mix be C

Our objective function will

Minimise (Total Cost) = Cost of almond + Cost of Cashew + Cost of Peanut + Cost of Walnut + Cost of raisins

Now I have to formulate LP. This is as follows.

Cost(Z) = Xa*D +Xc(Dc +Cc)+Xp*Dp+Xw(Dw+Cw)+Ir*Cr

The constraints are stated in excel file, which is as follows

General constraint

a<=400

c+x<=200

p<=600

w+y<=300

For delux Mix

a>=0.1(a+b+c+d)

c>=0.1(a+b+c+d)

p>=0.1(a+b+c+d)

w>=0.1(a+b+c+d)

(a+b)>=0.5*(a+b+c+d)

p<=0.2(a+b+c+d)

(a+b+c+d)>=(4000*0.1)

For companion Mix

x>=0.1(x+y+z)

x>=0.3(x+y+z)

x>=0.4(x+y+z)

z>=0.2*(x+y+z)

(x+y+z) >=(5000*0.1)

After setting and running the solver we get the following result

Cell	Name	Original Value	Final Value
$B$8	Total Cost(Z) Cost of purchasing (Rand)	482000	482000
Cell	Name	Original Value	Final Value	Integer
$E$3	Cashew Companion mix	120	120	Contin
$E$5	Walnuts Companion mix	180	180	Contin
$E$6	Unlimited Companion mix	200	200	Contin

The optimum purchasing plan for deluxe given below

Nuts	Cost of purchasing (Rand)	Material avaialable (Kg)	Delux mix	Companion mix
Almonds	1200	400	120
Cashew	1060	200	80	120
Peanuts	280	600	80
Walnuts	840	300	120	180
Raisins	360	Unlimited	0	200

Since requirement of almond and peanut is quite less that availability, therefore, no effect on purchase plan will be seen. We require only 120 kg of almonds and 80 kg of peanuts. Supply is still more than demand.

After purchasing the 50 kg of walnut at the price of R 1100/kg, the average price of walnut becomes around R 877 /kg. After feeding the update price of walnut, we have to again run the solver. It was seen that no effect on total cost of the plan is seen. The reason is that we are already using almond in minimum way, we cannot use more at higher price.

Yes, the price is changed from 482000 to 525500, this because after increasing the constraint from 4000 to 4500, the amount of material required also changed accordingly, almond increased by 40 kg, Cashew is reduce by 15 kg, Peanut increased by 10 kg and walnut by 15 kg.

The new plan is as follows

Nuts	Cost of purchasing (Rand)	Material avaialable (Kg)	Delux mix	Companion mix
Almonds	1200	400	160
Cashew	1060	200	65	135
Peanuts	280	600	90
Walnuts	840	300	135	165
Raisins	360	Unlimited	0	200

If the price of peanut will increased by 40%, then its new price will be 280+280*0.4= 392. We have to place the new price in solver and solve accordingly. I think this effect is only due to unlimited stock of raisins, But raisins already have at optimum use. Therefore, it’s not affected the solution.