DSME6620: Decision Models and Applications Term 1, 2023-24 Assignment 1
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
DSME6620: Decision Models and Applications
Term 1, 2023-24
Assignment 1
Due date: 6:00 pm, October 20, 2023
Important Notes:
1. This is an individual assignment. Please write down your name and student ID clearly in your submission.
2. For each problem, please present a detailed mathematical formulation and solve it with Excel Solver. Please provide your formulations and solutions in a PDF file (attaching a signed declaration form) and submit the PDF and Excel files to the Blackboard before the due date. Please do NOT email the files to the instructor or TA.
3. You must submit your assignment on time. Late submissions will NOT be accepted. 4. TA Mr. Huanyu Yin is responsible for grading this assignment.
Problem 1 [30 points]: An oil company produces three brands of oils: Regular, Multigrade, and Supreme. Each brand of oil is composed of one or more of four crude stocks, each having a different viscosity index. The relevant data concerning the crude stocks are:
|
1 |
Viscosity Index |
Cost ($/barrel) |
Supply Per Day (barrels) |
||||||||
|
20 |
7.20 |
1,000 |
|||||||||
|
2 |
40 |
8.50 |
1,000 |
||||||||
|
3 |
30 |
7.70 |
1,200 |
||||||||
|
4 |
55 |
9.00 |
1,000 |
||||||||
|
Each brand of oil must meet a minimum standard for viscosity index, and each brand sells at a different price. The relevant data concerning the three brands of oil are:
|
|||||||||||
|
Multigrade |
30 |
9.00 |
1,500 |
||||||||
|
Supreme |
50 |
10.00 |
800 |
||||||||
a) Define the decision variables and write down the detailed mathematical formulation of the oil company’s problem to maximize its total profit for a single day. [15 points]
b) Solve the optimization problem with Excel Solver. Write down the company’s
optimal production plan and optimal total profit. [15 points]
Problem 2 [40 points]: An automobile manufacturer needs to plan its production for the next 12 months. Demands for the next 12 months are forecasted in the table below:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
|||||||||||
|
940 |
790 |
360 |
720 |
270 |
130 |
160 |
300 |
970 |
290 |
280 |
790 |
Other relevant information is as follows:
> Workers are paid $5,000 per month.
> It costs $500 to hold a car in inventory for a month. The holding cost is based on each
month’s ending inventory.
> It costs $4,000 to hire a worker and $6,000 to fire a worker. Workers are hired and
fired at the beginning of each month.
> Each worker can make up to eight cars per month.
> At the beginning of month 1, there are 400 cars in inventory and 60 workers.
The manufacturer wants to minimize the total inventory and staffing cost of meeting demands for cars in the next 12 months.
a) Define the decision variables and write down the detailed mathematical formulation of the automobile manufacturer’s optimization problem. [20 points]
b) Solve the optimization problem with Excel Solver. Write down the manufacturer’s
optimal production and staffing plan for the next 12 months. [20 points]
Problem 3 [30 points]: Suppose Motorola solicits bids from five suppliers for eight products. The list price and the quantity needed for each product during the next year are as follows:
|
Product 1 2 3 4 5 6 7 8 |
||||||||
|
List price |
$87 |
$61 |
$96 |
$42 |
$98 |
$51 |
$83 |
$55 |
|
Quantity |
592 |
446 |
548 |
647 |
245 |
797 |
603 |
401 |
Each supplier has submitted the percentage discount offered on each product. The data are as follows:
|
Supplier |
Product |
|||||||
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
|
1 |
7% |
30% |
21% |
27% |
31% |
23% |
6% |
17% |
|
2 |
22% |
18% |
30% |
29% |
25% |
32% |
21% |
18% |
|
3 |
25% |
18% |
34% |
30% |
10% |
15% |
18% |
6% |
|
4 |
34% |
31% |
12% |
60% |
13% |
60% |
60% |
8% |
|
5 |
35% |
26% |
14% |
6% |
30% |
9% |
28% |
31% |
For example, Supplier 1 offers a 7% discount on product 1 and a 30% discount on product 2. The following considerations also apply:
> An administrative cost of $4,000 is associated with setting up a supplier’s account. For example, if Motorola uses three suppliers, it incurs an administrative cost of $12,000.
> To ensure reliability, no supplier can supply more than 80% of Motorola’s demand for any product.
> A supplier must supply an integer amount of each product it supplies.
Motorola wants to minimize its total purchase and administrative costs.
a) Define the decision variables and write down the detailed mathematical formulation of
Motorola’s optimization problem. [15 points]
b) Solve the optimization problem with Excel Solver. Write down Motorola’s optimal purchase plan and optimal total cost. [15 points]
2023-10-20