AutoSave OFF PC... Assignment 8.2 Tell me ✓ ✓ BD Share Comments ✓ ✓ Sort & Filter Find & Select Analyze Data Sensitivity T U V W X Y Home Insert Draw Page Layout Formulas Data Review View Automate Calibri (Body) ✓ 11 ✓ Aˆ A Wrap Text General ✓ Insert ✓ Σ Delete ▾ Paste B I U ✓ ✓ ✓ Merge & Center $ ✓ % 0- .00 Conditional Format Cell Formatting as Table Styles Format M17 fx B C D E F G H | J K L M N O P 0 R S A 1 2 3 4 5 6 7 8 9 Dana Johnson Corp has four plants in Decatur, Minneapolis, Carbondale, and E. St. Louis with different production capacities. Note that production capacity is the maximum amount the plant can produce, but the plant does not necessarily have to produce that many. The finished goods are shipped from four plants to three warehouses in Blue Earth, Ciro, and Des Moines according to the demands expected at these warehouses. The table below shows the production capacities and estimated demands for next quarter, along with the shipping costs per unit for each shipping lane. The warehouse demand should be met, but the warehouse can receive more than demanded, given their available space for storage. Plan the shipping quantities on each shipping lane between plants and warehouses, so that the total shipping cost is lowest between the plants and warehouses. (Solver is required) 10 11 12 13 14 15 Warehouses (To) Plants (From) Decatur Minneapolis Carbondale E. St. Louis Demand 16 Blue Earth $20 $17 $21 $29 250 17 Ciro $25 $27 $20 $30 200 18 Des Moines $22 $25 $22 $30 350 19 Capacity 300 200 150 150 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Ready Problem 1-A Problem 1-B Accessibility: Investigate + A + 100% AutoSave OFF PC... Assignment 8.2 Tell me Home Insert Draw Page Layout Formulas Data Review View Automate Calibri (Body) ✓ 11 ✓ Aˆ A Wrap Text General Paste B I U ✓ ✓ ✓ Merge & Center $ ✓ % 9 A1 fx Insert ✓ Σ ✓ Delete ▾ .00 0- Conditional Format Formatting as Table Cell Styles Format A B C D E F G H J K L M N 1 2 (Continued from Problem 1-A) Dana Johnson Corp was just notified that 3 4 5 -- the estimated demands should be revised to: 250 units, 240 units, and 375 units for Blue Earth, Ciro and DesMoines, respectively (according to the new data from market research); -- The production capacities at four plants provided in Problem 1-A are for regular-hour (i.e., regular-time, RT) production. Production using overtime (OT) hours will be available only at the Decatur plant with OT production cost = $65 per unit and OT capacity = 40 units, and at the Minneapolis plant with OT production cost = $70 per unit and OT capacity = 50 units; -- the shipment from Decatur to Des Moines will not be available next quarter due to disagreement over the contract between the management and the carrier. 6 7 8 9 10 11 The manager considers that in Problem 1-A, how you plan the shipping schedule will have an impact on not only the shipping quantities, but also next quarter's production at each plant. So, he would like to evaluate shipping costs together with production costs when designing the plan. The table below shows the production cost per unit at each plant. The shipping costs per unit for each shipping lane are provided already in Problem 1-A. As previously stated, the warehouse demands should be met, but the warehouse can receive more than demanded, given their available space for storage. Should OT prduction be considered in your plan? If your answer is yes, how can it be incorporated into the optimal plan where the total shipping and production cost is the lowest? You need to create the template to use Solver. Then, summarize your plan into Tables 2 and 3 below. $50 18 TABLE 1 20 Decatur 21 RT 26 28 30 36 38 40 46 48 50 121992222222222222≈≈≈33333327734444455 16 Production cost per unit ✓ ✓ BD Share Comments ✓ ✓ Sort & Filter Find & Select Analyze Data Sensitivity P Q R S T U V W X Y Z AA TABLE 2 TABLE 3 E. St. Louis Warehouses (To) Shipping quantities for each shipping lane Plants (From) Decatur Production RT/OT quantities at each plant Minneapolis Carbondale E. St. Louis Decatur Minneapolis Carbondale E. St. Louis RT Blue Earth $70 $40 Ciro RT OT Des Moines Minneapolis Minneapolis Carbondale Decatur от RT $65 Ready от $60 RT $70 Problem 1-A Problem 1-B Accessibility: Investigate + A + 89%