Engineering Science 311 Models for Optimization Assignment 2 2023
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Engineering Science 311
Assignment 2 2023
Due: September 12, 2023, 11:59pm
Models for Optimization
Instructions
The assignment has two questions. For each question you need to write a formulation using
mathematics and use this to create an Excel model. To support any answers to questions you should include screenshots from your Excel model and Solver screenshot inserted in a Word document that is then converted into a PDF.
What to hand in
• Prepare your answers to each question in a separate document.
• Submit the PDF for each question into the correct assignment dropbox on Canvas.
• You must also submit your Excel file(s) with your Solver models into the separate Canvas assignment for this. We will primarily mark your PDF submissions and screenshots, but will look at your Excel files if there are any doubts. If you do not submit your Excel file, you will score 0 for all question parts requiring the use of Excel.
QUESTION ONE (5 marks)
Consider the problem of shipping beer from warehouses to bars. Suppose there are three warehouses (A, B, C) and seven bars (j=1,2,…,7). The warehouses have 1000, 4000, and 2000 cases of pilsner lager and the bars require 500, 900, 700, 400, 600, 500, 300 cases of lager per day. The cost per crate (in $) of shipping pilsner from each warehouse I (A,B,C) to each bar j is given by the following table.
|
|
Bar 1 |
Bar 2 |
Bar 3 |
Bar 4 |
Bar 5 |
Bar 6 |
Bar 7 |
|
Warehouse A |
2 |
3 |
2 |
5 |
4 |
3 |
1 |
|
Warehouse B |
6 |
2 |
4 |
7 |
1 |
6 |
3 |
|
Warehouse C |
1 |
4 |
6 |
2 |
7 |
3 |
5 |
(a) [ 2 marks] Formulate the problem of meeting the demand for pilsner lager from the warehouses at least costas a linear program.
(b) [ 1 mark] Input the linear program to Excel and solve the problem. Display a screenshot of the Solver screen and the worksheet showing the optimal transportation plan.
(c) [ 1 mark] Suppose each route has a maximum of three trucks allocated, and each truck can carry only 200 cases of beer per day. Re-solve the problem when each route is limited to 600 cases/day.
(d) [ 1 mark] The shadow price on Bar 1 demand is $1. This gives the extra cost if demand at Bar 1 were 501 cases, and we re-solved the problem. The extra case comes from Warehouse C at cost $1. The shadow price on Bar 5 demand is $5. Increase the demand at Bar 5 to 601 andre-solve the problem. Use the solution to explain why the least cost way of getting another case to Bar 5 costs $5.
QUESTION TWO (3 marks)
Hauwai Energy want to locate some hydrogen refuelling depots throughout the North Island of New Zealand. There are 20 possible sites to locate these, indexed i = 1,2,…,20. Hauwai need to choose
the location and the capacity of the depots to meet the demand from a fleet of trucks that is fuelled by hydrogen.
(a) [ 1 mark] Suppose the cost of locating a depot at location i is c(i), and the capacity of each depot is a(i) (measured in tonnes of hydrogen per year) and annual demand for hydrogen is H tonnes. Formulate the problem of choosing the locations to site a depot as a binary integer program in terms of c(i), a(i) and H.
(b) [ 1 mark] Solve the problem using Excel when the values of c and a are given by the following table (which is available on Canvas as MFOAssignmentData.xslsx) and H = 100.
(c) [ 1 mark] So that trucks are assured of refuelling options within their range, the sites chosen
must be such that each chosen site is no further than 100 km from some other chosen site. For each site i, we list the sites j ≠ i that are less than 100 km away, and put in a set N(i). Let z(i) = 1 if site i is chosen and z(i) = 0 if it is not. Then we have for each i, the constraints.
z(i) ≤ ΣjϵN(i)z(j), i = 1,2,…,20.
Add appropriate constraints to the Excel model in (b) when N(i) is given by the table
Table of N(i): first column is i and first row is j and the entry in cell (i, j) is 1 if j ϵ N(i).
The table is also in Canvas. Re-solve the Excel model with these extra constraints, and describe the new locations of depots.
2023-09-03