Using this information in excel how do I calculate the optimal supply chain for the given budget and constraints
This is all the given information. The objective is to design the optimised supply chain given the constraints.
Something like this. My problem is I'm unsure of how to list in the constraints for minimum order size for each type of transport.
Weight (T) Product Info Product LCD 42" LCD 32" Demand (Units) 0.022 920,000 530,000 0.0165 Budget () 3,000,000,000 Rail Air Express 64,400 115,920 103,040 64,400 4,250.40 5,796 5,796 4,250.40 Product - ODM LCD 42" ODM 1 LCD 42" ODM 2 LCD 42" ODM 3 LCD 42" ODM 4 LCD 42" ODM 5 LCD 42" ODM 6 LCD 42" ODM 7 LCD 32" ODM 1 LCD 32" ODM 1 Shipping Cost Per Metric Ton () Road Road LTL Road Network 70,840 6,182.40 5,216.40 4,830.0 127,512 7,084 5,796 5,667.20 113,344 7,084 5,796 5,677.20 70,840 6,182.40 5,280.80 5,216.40 9,660 9,016 8,694 148,120 112,700 7,084 5,538.40 70,840 6,182.40 5,216.40 4,830 127,512 7,084 5,796 5,667.20 Water 3,091.20 2,704.80 3,284.40 3,091.20 Distance to DC (Kms) TV Unit Production Cost () Regular Air 2508 1,983.40 1553 2,254.00 1380 2,582.00 2150 1,976.10 30 2,711.30 690 2,704.80 686 2,125.20 2508 1,818.00 1553 1,996.40 5,796 135,240 103,040 64,400 115,920 5,860.40 4,250.40 5,796 3,413.20 2,769.20 3,091.20 2,704.80 Constraints Minimum Order Number at Any ODM Maximum Oder Number at any ODM Minimum number of units to be shipped by air LCD 42" (LCD 32") Minimum number of units to be shipped by road LCD 42" (LCD 32") Minimum number of units to be shipped by rail LCD 42" (LCD 32") 200,000 600,000 46,000 92,000 138,000 53,000 79,500 79,500 Decision Varaibles Regular Air Objective Function Cost (%) 2,999,995,614.45 Air Express 39279 O open = 1 319998 0 Water open=1 Total 47436 48146 o O 0 127 599237 0 ODM 1 ODM 2 ODM 3 ODM 4 ODM 5 ODM 6 ODM 7 Total Production Cost () 2783888433 Supply From Plants Road Road LTL Road Network Rail 43258 47198 47317 47364 0 0 O 0 o 0 o O 127 127 127 127 0 0 O O 0 O O 0 O 1 100 100 100 100 O 0 127 599999 0 0 O 0 O 0 0 Total Shipping Cost () 216107181.8 0 1 3 920000 1 100 530000 100 0 0 529400 O 0 530000 ODM 1 32" ODM 2 32" Min 42" Min 32" Constraints Shipping weights ODM 1 ODM 2 Regular Air Air Express 864.138 0 0 ODM 3 ODM 4 ODM 5 2.794 Road Road LTL Road Network Rail Water total 951.676 1038.356 1040.974 1042.008 1043.592 1059.212 0 o o ol 0 0 0 0 2.794 2.794 2.794 2.794 2.794 13183.214 o 0 0 0 0 0 0 0 0 0 0 0.022 0.022 0.022 1.65 1.65 1.65 1.65 1.65 8735.1 0 0 ODM 6 ODM 7 ODM 1 32" 0 1.65 LODILI Sheet1 Sheet3 Weight (T) Product Info Product LCD 42" LCD 32" Demand (Units) 0.022 920,000 530,000 0.0165 Budget () 3,000,000,000 Rail Air Express 64,400 115,920 103,040 64,400 4,250.40 5,796 5,796 4,250.40 Product - ODM LCD 42" ODM 1 LCD 42" ODM 2 LCD 42" ODM 3 LCD 42" ODM 4 LCD 42" ODM 5 LCD 42" ODM 6 LCD 42" ODM 7 LCD 32" ODM 1 LCD 32" ODM 1 Shipping Cost Per Metric Ton () Road Road LTL Road Network 70,840 6,182.40 5,216.40 4,830.0 127,512 7,084 5,796 5,667.20 113,344 7,084 5,796 5,677.20 70,840 6,182.40 5,280.80 5,216.40 9,660 9,016 8,694 148,120 112,700 7,084 5,538.40 70,840 6,182.40 5,216.40 4,830 127,512 7,084 5,796 5,667.20 Water 3,091.20 2,704.80 3,284.40 3,091.20 Distance to DC (Kms) TV Unit Production Cost () Regular Air 2508 1,983.40 1553 2,254.00 1380 2,582.00 2150 1,976.10 30 2,711.30 690 2,704.80 686 2,125.20 2508 1,818.00 1553 1,996.40 5,796 135,240 103,040 64,400 115,920 5,860.40 4,250.40 5,796 3,413.20 2,769.20 3,091.20 2,704.80 Constraints Minimum Order Number at Any ODM Maximum Oder Number at any ODM Minimum number of units to be shipped by air LCD 42" (LCD 32") Minimum number of units to be shipped by road LCD 42" (LCD 32") Minimum number of units to be shipped by rail LCD 42" (LCD 32") 200,000 600,000 46,000 92,000 138,000 53,000 79,500 79,500 Decision Varaibles Regular Air Objective Function Cost (%) 2,999,995,614.45 Air Express 39279 O open = 1 319998 0 Water open=1 Total 47436 48146 o O 0 127 599237 0 ODM 1 ODM 2 ODM 3 ODM 4 ODM 5 ODM 6 ODM 7 Total Production Cost () 2783888433 Supply From Plants Road Road LTL Road Network Rail 43258 47198 47317 47364 0 0 O 0 o 0 o O 127 127 127 127 0 0 O O 0 O O 0 O 1 100 100 100 100 O 0 127 599999 0 0 O 0 O 0 0 Total Shipping Cost () 216107181.8 0 1 3 920000 1 100 530000 100 0 0 529400 O 0 530000 ODM 1 32" ODM 2 32" Min 42" Min 32" Constraints Shipping weights ODM 1 ODM 2 Regular Air Air Express 864.138 0 0 ODM 3 ODM 4 ODM 5 2.794 Road Road LTL Road Network Rail Water total 951.676 1038.356 1040.974 1042.008 1043.592 1059.212 0 o o ol 0 0 0 0 2.794 2.794 2.794 2.794 2.794 13183.214 o 0 0 0 0 0 0 0 0 0 0 0.022 0.022 0.022 1.65 1.65 1.65 1.65 1.65 8735.1 0 0 ODM 6 ODM 7 ODM 1 32" 0 1.65 LODILI Sheet1 Sheet3
