Design a linear programming model

、Introduction

The following represents the supply chain network connecting the supplier sites to customers for a single product in a single period setting. There are four supplier sites to satisfy four different customers. Supplier sites capacity are 150, 120, 130 and 100, respectively. 

Design a linear programming model

The customers demand levels are shown in Table 1 below. 

Table 1 – Customer demand 

 Customer 1Customer 2Customer 3Customer 4
Demand110140120130

The unit costs of transportation from supplier sites to customers are presented in Table 2 as follows. 

Note: When using the digits from your student number, convert all zeros (0) to ones (1). For example, if your student number ends on 0125, convert it to 1125.

Table 2 – Unit cost of transportation from supplier sites to customers

 Customer 1Customer 2Customer 3Customer 4
Supplier site 1$5$4$3$6
Supplier site 2$2$1$9$4
Supplier site 3$3$7$5$8
Supplier site 46th digit of your student number7th digit of your student number8th digit of your student number9th digit of your student number

Optimization Problem

What is the most optimal way to deliver the products from all supplier sites to the customers while minimizing the total transportation cost?

Tasks:

  1. Design a linear programming model (i.e., define the decision variables, objective function, and the associated constraints) to minimize the transportation cost of the presented supply chain, and clearly identify all components of the model. (2 marks)
  2. Solve the model using the Excel Solver add-in. (3 marks)
  3. Suppose the demand of customer 2 changes to 130 (i.e. supply > demand). Then, redesign your model and solve the problem in a new worksheet. (1 mark)
  4. Suppose the demand of customer 2 changes to 150 (i.e. supply < demand). Then, redesign your model and solve the problem in a new worksheet. (1 mark) 
  5. Write one paragraph to explain the solutions to the three Solver models you created. Refer to specific numbers and facts in your answer. (1 mark)
  6. Format your Excel file with similar formatting to the questions we solve in class, and ensure that your file is easy to read and follow (i.e. worksheets are named, appropriate cells are color coded, etc.). Each Excel worksheet must also include a screenshot of the Solver parameters. (2 Marks).

Instructions on how to submit your answers

You must upload one Excel file with three worksheets to Avenue. The file name must follow this format: {Your full name, Your student number}.