Linear optimization problem

profilehello1
Sinofert11.xlsx
Home>Business & Finance homework help>Operations Management homework help>Linear optimization problem

Feasibility Report 1

Microsoft Excel 16.21 Feasibility Report
Worksheet: [Sinofert.xlsx]Sheet1
Report Created: 2/20/19 5:59:14 PM
Constraints Which Make the Problem Infeasible
Cell Name Cell Value Formula Status Slack
$J$162 Jiangsu Linggu $J$162>=200000 Violated -200000

Sheet1

EXHIBIT 1: MARKET CAPACITY AND SINOFERT'S BUDGET IN CHINA OF MAJOR AGRICULTURE PROVINCES
Average Selling Prices (RMB/ton)
Province\Quantity Market size (ton/year) Sales Budget (ton/year) Min Sales Qty (ton/year)
Heilongjiang 1,300,000 220,000 150,000 1,917
Jilin 1,200,000 400,000 300,000 1,864
Liaoning 1,100,000 180,000 120,000 1,880
Hebei 3,000,000 200,000 100,000 1,798
Henan 6,000,000 210,000 200,000 1,787
Shandong 4,500,000 500,000 270,000 1,812
Jiangsu 4,500,000 450,000 200,000 1,866
Anhui 2,200,000 230,000 110,000 1,842
Hubei 2,600,000 200,000 100,000 1,837
Hunan 2,300,000 200,000 80,000 1,901
Jiangxi 1,500,000 270,000 180,000 1,915
Fujian 1,200,000 180,000 90,000 1,960
Guangdong 1,200,000 230,000 170,000 1,930
Guangxi 1,600,000 100,000 18,000 1,917
Total 34,200,000 3,570,000 2,088,000
EXHIBIT 2: 2010 YEARLY CONTRACT WITH KEY SUPPLIERS (INCLUDING SINOFERT FACTORY PRODUCTION
Maximum Capacity (ton/year) Contract Qty (ton/year) Average Manufacturing Cost (RMB)
Supplier\quantity Minimum Qty (ton/year)
Sinofert Pingyuan* 980,000 980,000 980,000 1700
Sinofert Changshan* 300,000 300,000 300,000 1750
Shanxi Fengxi 900,000 300,000 100,000 1650
Shandong Lian Meng 1,000,000 350,000 200,000 1720
Shandong Ruixing 1,000,000 300,000 200,000 1700
Shandong Lunan 600,000 120,000 50,000 1700
Henan Xinlianxin 1,000,000 300,000 200,000 1700
Henan Pingdingshan 400,000 100,000 30,000 1700
Jiangsu Linggu 1,000,000 450,000 300,000 1750
Hebei Zhengyuan 600,000 120,000 60,000 1700
Hebei Jinghua 300,000 120,000 60,000 1700
Anhui Haoyuan 300,000 100,000 80,000 1750
Neimenggu Erduosi 1,000,000 350,000 200,000 1650
Totals 9,380,000 3,890,000 2,760,000
EXHIBIT 3: PLANNED SHIPMENT QUANTITIES 2009
Suppliers/ Supplied province Sinofert Pingyuan Sinofert Changshan Shanxi Fengxi Shandong Lianmeng Shandong Ruixing Shandong Lunan Henan Xinlianxin Henan Pingdingsh an Jiangsu Linggu Hebei Zhengyuan Hebei Jinghua Anhui Haoyuan Neimeng Erduosi total quantity per company Average Selling Prices (RMB/ton) total revenue in 2009
Heilongjiang 54,171 0 13,533 14,610 30,000 30,000 13,920 0 0 13,920 0 0 50,456 220,610 1,917 422909370
Jilin 44,143 292,479 6,189 3,780 20,000 10,000 16,080 0 0 14,640 4,860 0 14,280 426,451 1,864 794904664
Liaoning 49,886 0 9,344 9,900 12,000 0 25,920 0 0 18,004 4,500 0 28,140 157,694 1,880 296464720
Hebei 15,574 0 31,291 0 10,000 20,000 0 0 0 30,132 47,892 0 39,060 193,949 1,798 348720302
Henan 18,926 0 67,792 0 10,000 10,000 24,360 50,000 0 0 0 0 18,032 199,110 1,787 355809570
Shandong 498,726 0 5,000 103,860 20,000 10,000 0 0 0 0 5,754 0 47,880 691,220 1,812 1252490640
Jiangsu 76,900 0 19,920 44,280 10,000 0 27,000 0 248,024 0 4,848 0 0 430,972 1,866 804193752
Anhui 84,857 0 21,351 36,360 5,000 5,000 0 0 0 0 4,500 31,410 21,000 209,478 1,842 385858476
Hubei 6,886 0 51,656 0 30,000 0 600 20,000 0 2,192 0 0 0 111,334 1,837 204520558
Hunan 17,634 0 51,440 8,100 20,000 0 7,000 0 0 10,560 0 0 0 114,734 1,901 218109334
Jiangxi 17,426 0 12,960 5,040 10,000 0 29,260 0 0 14,160 0 0 0 88,846 1,915 170140090
Fujian 33,600 0 726 7,020 0 40,000 0 0 0 0 0 42,750 31,080 155,176 1,960 304144960
Guangdong 22,229 0 4,152 6,498 0 0 14,652 0 0 15,200 0 8,700 52,983 124,414 1,930 240119020
Guangxi 9,514 0 0 5,220 0 0 2,400 0 0 0 0 0 8,400 25,534 1,917 48948678
Manuf Qty 950,471 292,479 295,353 244,668 177,000 125,000 161,192 70,000 248,024 118,808 72,354 82,860 311,311 3,149,520 5847334134
Average Manufacturing Cost (RMB) 1700 1750 1650 1720 1700 1700 1700 1700 1750 1700 1700 1750 1650 Total Manufacturing Cost
Total manufacturing cost for each supplier 1,615,800,700 511,838,250 487,332,450 420,828,960 300,900,000 212,500,000 274,026,400 119,000,000 434,042,000 201,973,600 123,001,800 145,005,000 513,663,150 5,359,912,310
TOTAL FREIGHT
71,413,895 11,699,160 18,880,217 19,112,592 17,939,000 9,975,000 22,569,564 1,880,000 10,913,056 11,238,840 3,082,464 5,852,010 36,117,221 240,673,019
NET PROFIT = TOTAL REVENUE - TOTAL MANUFACTURING COST - TOTAL FREIGHT 246,748,805
EXHIBIT 4: AVERAGE FREIGHT FROM FACTORY TO EACH PROVINCIAL MARKET
Supplier Sinofert Sinofert Shanxi Shandong Shandong Shandong Henan Henan Jiangsu Hebei Hebei Anhui Neimeng
Pingyuan Changshan Fengxi Lianmeng Ruixing Lunan Xinlianxin Pingdingshan Linggu Zhengyuan Jinghua Haoyuan Erduosi
Heilongjiang 119 70 129 136 145 119 185 143 164 120 108 151 155
Jilin 101 40 110 119 128 102 167 123 147 101 90 133 137
Liaoning 81 60 91 98 107 82 148 105 126 82 69 112 118
Hebei 45 100 45 64 73 47 105 58 92 40 32 69 75
Henan 83 130 39 85 77 49 84 22 81 70 69 46 59
Shandong 58 110 50 53 61 36 119 64 77 62 46 58 89
Jiangsu 67 150 56 72 65 31 117 54 44 74 57 46 84
Anhui 69 140 53 73 65 31 114 41 43 75 58 36 81
Hubei 96 150 56 98 90 60 109 39 77 86 82 61 85
Hunan 122 160 86 122 114 81 141 74 85 114 107 80 110
Jiangxi 114 160 88 115 107 72 153 70 66 114 100 71 110
Fujian 129 170 102 129 124 86 171 84 66 128 114 85 124
Guangdong 164 170 126 164 161 126 162 119 117 158 149 125 151
Guangxi 156 160 109 157 168 133 152 93 127 148 142 132 144
Profit Coefficient= Average selling price of Heilongjiang – Average manufacturing price of Sinofert Pingyua – Average freight cost from Exhibit 4
Supplier/supplied province Sinofert Sinofert Shanxi Shandong Shandong Shandong Henan Henan Jiangsu Hebei Hebei Anhui Neimeng
Pingyuan Changshan Fengxi Lianmeng Ruixing Lunan Xinlianxin Pingdingshan Linggu Zhengyuan Jinghua Haoyuan Erduosi
Heilongjiang 98 97 138 61 72 98 32 74 3 97 109 16 112
Jilin 63 74 104 25 36 62 -3 41 -33 63 74 -19 77
Liaoning 99 70 139 62 73 98 32 75 4 98 111 18 112
Hebei 53 -52 103 14 25 51 -7 40 -44 58 66 -21 73
Henan 4 -93 98 -18 10 38 3 65 -44 17 18 -9 78
Shandong 54 -48 112 39 51 76 -7 48 -15 50 66 4 73
Jiangsu 99 -34 160 74 101 135 49 112 72 92 109 70 132
Anhui 73 -48 139 49 77 111 28 101 49 67 84 56 111
Hubei 41 -63 131 19 47 77 28 98 10 51 55 26 102
Hunan 79 -9 165 59 87 120 60 127 66 87 94 71 141
Jiangxi 101 5 177 80 108 143 62 145 99 101 115 94 155
Fujian 131 40 208 111 136 174 89 176 144 132 146 125 186
Guangdong 66 10 154 46 69 104 68 111 63 72 81 55 129
Guangxi 61 7 158 40 49 84 65 124 40 69 75 35 123
Average Selling Prices (RMB/ton) Maximum Capacity (ton/year) Contract Qty (ton/year) Average Manufacturing Cost (RMB)
Province\Quantity Market size (ton/year) Sales Budget (ton/year) Min Sales Qty (ton/year) Supplier\quantity Minimum Qty (ton/year)
Heilongjiang 1,300,000 220,000 150,000 1,917 Sinofert Pingyuan* 980,000 980,000 980,000 1700
Jilin 1,200,000 400,000 300,000 1,864 Sinofert Changshan* 300,000 300,000 300,000 1750
Liaoning 1,100,000 180,000 120,000 1,880 Shanxi Fengxi 900,000 300,000 100,000 1650
Hebei 3,000,000 200,000 100,000 1,798 Shandong Lian Meng 1,000,000 350,000 200,000 1720
Henan 6,000,000 210,000 200,000 1,787 Shandong Ruixing 1,000,000 300,000 200,000 1700
Shandong 4,500,000 500,000 270,000 1,812 Shandong Lunan 600,000 120,000 50,000 1700
Jiangsu 4,500,000 450,000 200,000 1,866 Henan Xinlianxin 1,000,000 300,000 200,000 1700
Anhui 2,200,000 230,000 110,000 1,842 Henan Pingdingshan 400,000 100,000 30,000 1700
Hubei 2,600,000 200,000 100,000 1,837 Jiangsu Linggu 1,000,000 450,000 300,000 1750
Hunan 2,300,000 200,000 80,000 1,901 Hebei Zhengyuan 600,000 120,000 60,000 1700
Jiangxi 1,500,000 270,000 180,000 1,915 Hebei Jinghua 300,000 120,000 60,000 1700
Fujian 1,200,000 180,000 90,000 1,960 Anhui Haoyuan 300,000 100,000 80,000 1750
Guangdong 1,200,000 230,000 170,000 1,930 Neimenggu Erduosi 1,000,000 350,000 200,000 1650
Guangxi 1,600,000 100,000 18,000 1,917 Totals 9,380,000 3,890,000 2,760,000
Total 34,200,000 3,570,000 2,088,000
Average Selling Prices (RMB/ton) Average Manufacturing Cost (RMB) 1700 1750 1650 1720 1700 1700 1700 1700 1750 1700 1700 1750 1650
1,917
1,864
1,880
1,798
1,787
1,812
1,866
1,842
1,837
1,901
1,915
1,960
1,930
1,917
Supplier/supplied province Sinofert Sinofert Shanxi Shandong Shandong Shandong Henan Henan Jiangsu Hebei Hebei Anhui Neimeng Total Demand Demand
Pingyuan Changshan Fengxi Lianmeng Ruixing Lunan Xinlianxin Pingdingshan Linggu Zhengyuan Jinghua Haoyuan Erduosi
Heilongjiang 0 220,000
Jilin 0 400,000
Liaoning 0 180,000
Hebei 0 200,000
Henan 0 210,000
Shandong 0 500,000
Jiangsu 0 450,000
Anhui 0 230,000
Hubei 0 200,000
Hunan 0 200,000
Jiangxi 0 270,000
Fujian 0 180,000
Guangdong 0 230,000
Guangxi 0 100,000
Total Supply 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Supply 980,000 300,000 300,000 350,000 300,000 120,000 300,000 100,000 450,000 120,000 120,000 100,000 350,000
0