Question

pictou pallets manufatures shipping pallets at two plants and distributes them to three strategically located warehouses.

Plant 1 is capable of producing 8000 pallets per year and plant 2 is capable of producing 5000 pallets per year.

Warehouse A requires 4500 pallets, warehouse b requires 3000 pallets, and warehouse c requires 4000 pallets per year.

the cost of shipping one pallet from each plant to each warehouse are listed below:

(eg. the cost of shipping from plant 1 to warehouse A is $1.10 per pallet)

plant	warehouse a	warehouse b	warehouse c
1	$1.10	$1.40	$1.25
2	$1.20	$0.95	$1.00

a) find the optimal shipping schedule to minimize shipping costs.

B) would the shipping schedule change if the cost of shipping from plant 1 to warehouse A increased by $0.30 to $1.40? explain

C) how much would the shipping charges change if plant 2's capacity were decreased by 100 pallets?

d) would the same hold for another decrease of 100 pallets?

E) the optimal shipping schedule has no pallets shipped from plant 1 to warehouse b. What change in shipping cost along this route would make such shipments worthwhile.

Please solve in Excel

Answer 1

Given Information:
Plant	Capacity of pallets per year
1	8000
2	5000
Warehouse	Demand of pallets per year
A	4500
B	3000
C	4000
Plant	Shipping Cost for Warehouse
A	B	C
1	1.10	1.40	1.25
2	1.20	0.95	1.00
Answer : Part (a)
The optimal Schedule to minimze cost will be:
		Plant	Total Capacity of Units of Plant wise	Warehouse
	Allocation of Units order wise	A	B	C
	4500/2000/0	1	8000	4500 1.10	1.40	2000 1.25
	3000/2000/0	2	5000	1.20	3000 0.95	2000 1.00
			Total Demand Warehouse wise	4500	3000	4000
			Balance Demand after allocations in order	4500/0	3000/0	2000/2000/0
	Steps	Allocation
	1	Allocate 3000 units of Plant 2 to Warehouse B since it has the lowest shipping cost amounting to ($0.95 * 3000) $2850.
	2	Allocate 4500 units of Plant 1 to Warehouse A since it has the lowest shipping cost amounting to ($1.10*4500) $ 4950.
	3	Allocate 2000 units of Plant 2 to Warehouse C at the rate of $1 p.u and 2000 units of Plant 1 to Warehouse C at the rate of $1.25. Total Cost ($1*2000 u +$1.25*2000u) $4500.
	Hence the optimal shipping schedule to minimize shipping cost will be :
	Warehouse	Plant	Shipping Cost ($)
	A	1	4950
	B	2	2850
	C	1	2500
	C	2	2000
		Total	12300
		Answer: Part (b) When Shipping cost from Plant 1 to Warehouse A is changed to $1.40. Revised total shipping cost will be: Plant Total Capacity of Units of Plant wise Warehouse Allocation of Units order wise A B C 2500/4000/0 1 8000 2500 1.40 1.40 4000 1.25 3000/2000/0 2 5000 2000 1.20 3000 0.95 1.00 Total Demand Warehouse wise 4500 3000 4000 Balance Demand after allocations in order 2000/2500/0 3000/0 4000/0 Steps Allocation 1 Allocate 3000 units of Plant 2 to Warehouse B since it has the lowest shipping cost amounting to ($0.95 * 3000) $2850. 2 Allocate 2000 units of Plant 2 to Warehouse A and 2500 units of Plant 1 to Warehouse A to minimize cost. (1.20*2000) + (1.40*2500). Total $5900 3 Allocate 4000 units of Plant 1 to Warehouse C at the rate of $1.25 p.u = (1.25*4000) $5000. Hence revised Shipping cost will be : Warehouse Plant Shipping Cost ($) A 1 3500 A 2 2400 B 2 2850 C 1 5000 Total 13750 It is pertinent to note an increase in shipping cost by $ 1450 in comparison to option a. Answer : Part C The shipping cost will change as below when Plant 2's capacity is decreased by 100 pallets. Plant Total Capacity of Units of Plant wise Warehouse Allocation of Units order wise A B C 2100/4500/0 1 8000 4500 1.40 1.40 2100 1.25 3000/1900/0 2 4900 1.20 3000 0.95 1900 1.00 Total Demand Warehouse wise 4500 3000 4000 Balance Demand after allocations in order 4500/0 3000/0 1900/2100 Hence revised Shipping cost will be : Warehouse Plant Shipping Cost ($) A 1 6300 B 2 2850 C 1 2625 C 2 1900 Total 13675 Answer : Part (d) In case Capacity of plant 2 is decresed it will lead to increase in shipping cost. Hence this position doesn’t hold good. Answer : Part (e) Restriction on shipping from Plant 1 to Warehouse B No change in comparison to part b as there has been no shipping from plant 1 to warehouse b in any of the above cases.