You are required to work on a given case study
a. You must submit:
- A Word file to explain your mathematical models, to report your answers, and to write recommendations for decision makers based on the solutions; and
- An Excel file to demonstrate how you use Solver to identify the solutions. You must ensure that the file is in a workable condition before the submission. Each model must be implemented in a separate spreadsheet in the same file.
SCMD: Assignment
The company produces an apple product at three plants, which can be delivered directly to the two customers or it can first be shipped to the two warehouses and then to the customers. Shipments between plants are allowed. This also applies to between warehouses and between customers.
The cost of producing the apple product is the same at each plant; as a result, the company is only concerned with minimising the total shipping cost incurred in meeting customer demands. The production capacity of each plant (in tons per year) and the customer demand are summarised in the table below:
|
Plant
|
Capacity
|
|
Plant 1
|
300
|
|
Plant 2
|
300
|
|
Plant 3
|
350
|
|
Customer
|
Demand
|
|
Customer 1
|
400
|
|
Customer 2
|
500
|
The cost (in thousands of dollars) of shipping a ton of the product between each pair of locations is listed in the table below where a blank indicates that the company cannot ship on that route:
|
From node
|
To node
|
|
Plant 1
|
Plant 2
|
Plant 3
|
Warehouse 1
|
Warehouse 2
|
Customer 1
|
Customer 2
|
|
Plant 1
|
|
6
|
4
|
6
|
6
|
20
|
19
|
|
Plant 2
|
10
|
|
10
|
2
|
2
|
9
|
16
|
|
Plant 3
|
1.4
|
9
|
|
2
|
1.5
|
11
|
13
|
|
Warehouse 1
|
|
|
|
|
2.2
|
3
|
13
|
|
Warehouse 2
|
|
|
|
1.8
|
|
3
|
13
|
|
Customer 1
|
|
|
|
|
|
|
2
|
|
Customer 2
|
|
|
|
|
|
8
|
|
The management has decided that no more than 210 tons of the product can be transported between any two nodes. The company wants to determine a minimum-cost shipping strategy.