Assume that we are assigned the task of (re) designing the supply chain for bamboo wood flooring products that could come from four different origins and should be distributed to the United States as said in tab 1 named "Network" in the Excel file. The first worksheet (Network) will give you an image of how the product slows from origin to destination. The second worksheet shows the prepared data set with costs and relevant variables for building and analyzing the supply chain.

You can also be creative and select the variables in a way that those states closer to the ports will not need to get decision variables from the ports away (black cells in the file DO NOT need decision, therefore DO NOT select these cells when calling the variables). This is taken care of in the update file upload.

Download the updated excel file loaded in the Canvas section for Supply Network Design - week 7 named "US Supply Network Design for Evaluation II Part B." Your assignment follows:

(1) Use the Solver ADD-IN in Excel to design your US Network. You must formulate within the solver window the following objectives and constraints (5 points):

• Objective function should be total cost to serve for the whole network.
• Select the variables for the optimization problem
• Constraints should be:
  • (a) capacity, i.e., the sales from each origin (CN, CN-VN, CO, CR) must not exceed (Less than or equal to) the total capacity for that origin.
  • (b) Balance of Demand, This is, the sales for each state must be equal to the demand at that same state
  • (c) Balance of flow at the Ports, this is, the sum of influx of TEU's at each Port must be EQUAL to the sum outflux of TEU's from each Port to destinations.
  • The model must be linear, this is, when you are about to run, use the simplex option (or the LP option if it shows this way)

(2) Analyze your results. Explain origins, flows and destination of products in narrative. Highlight the most relevant features. (5 points)

(3) Imagine now that you want to reduce your purchases from CN-VN by means of applying a tax tariff. How big of a tax tariff would you increase the manufacturing price to buy the least amount of product possible from China-Vietnam to increase the use (leave the total capacity unchanged at 150,000 for both CR, CO) for applying Near-Shoring practices (CO, and CR)? What percentage of cargo CN-VN loose after the selected Tax Tariff? (5 points)

You need to upload ONE EXCEL file with both, the problem solution, and the analysis in one tab/worksheet.