Supply chain management-optimization

SCM 421 Supply Chain Analytics

Optimization Problem Set

Notes:

1. This handout has 3 problems.

2. Please turn in your solutions in a Word document via Canvas.

3. Your solutions should contain a snapshot of your Excel model showing the optimal solution and all associated calculations as well as a screenshot of the completed solver setup dialog. The excel screenshot should clearly indicate the row and column headers and should contain formulatext() output to show all the calculations.

4. This is a group assignment and only one submission per group is required. However, I strongly recommend that you try to work on these problems individually before discussing them as a group. Try to come up with your own spreadsheet models, run solver and interpret the results on your own first. It will go a long way in helping you prepare for the exam.

Problem 1. The Truck Packing Problem: This is a simplified version of large-scale problems that trucking folks face on a daily basis. Atlas Transportation company needs to transport 2400 chairs and 1200 tables to a warehouse. Unfortunately, one truck cannot accommodate everything. So, the company is planning to use a small truck and a large truck to transport the furniture. The large truck will leave tomorrow and must have at least 650 chairs and 35 tables. The small truck will leave the day after tomorrow. The large truck can take weight of 100,000 lbs maximum while the small truck can carry 50,000 lbs. Each chair weighs 10 lbs while each table weighs 100 lbs. Every chair shipped tomorrow accrues $5, every table accrues $15; every chair shipped day after tomorrow accrues $4 and every table accrues $9. What is the correct shipment strategy to maximize profit?

Problem 2: Let’s revisit the Acme transportation network optimization problem discussed in the class. Recall that Acme had three plants and was trying to determine how to satisfy the demand in ten markets from these three plants.

How would you model the following situations? Please include a screenshot of the model containing the optimal solution (clearly showing the changes you made with the corresponding formulatext where necessary) and a screenshot of the solver dialog for each part below.

a) Due to a recent tornado, no shipments can be made from Chicago and Atlanta to Indianapolis.

b) The products manufactured at the Atlanta plant meet a higher safety standard and only these products can be sold in Fremont.

c) New York has a diverse market, and to cater to this diverse market, at least 20,000 products each must be shipped from the three plants to New York.

d) Due to a trade agreement with the Georgia government, the plant in Atlanta must ship at least 50,000 units outside of the state.

Problem 3: Please solve the following problem. I have not provided a template for this problem. Please make your own, using the Acme problem as a rough outline. As a good practice, please write down the business objective, decision variables and constraints in plain words, and then solve it in Excel.