Optimisation
model/Excel Solver
The solutions to Problem 3 (i) should have
a separate spreadsheet (that uses the Excel Solver to find the solution). You should also
include the optimisation model in your written response, clearly stating
decision variables, objective function and all constraints.
You
should construct your spreadsheet model with readability in mind. Features that
improve readability include:
· a clear, logical layout to the overall model
· separation of different parts of a model
· clear headings for different sections of the model and for all inputs,
decision variables and outputs
· use of Excel formatting features such as boldface, italics, larger font
size, colouring, indentation, and cell comments.
Company A is trying to create one or more
warehouses to ship products to five customers. The location of the five
customers and the number of shipments per year needed by each customer are
given in Table 1. All locations are in miles, relative to the point x = 0 and y
= 0.
Table 1: Company A’s customers’
location and shipments.
Customer
Location
Shipments/year
1
(5,
10)
200
2
(10,
5)
150
3
(0,
12)
200
4
(12,
0)
300
5
(7, 8)
250
Following
initial investigation, the company has identified two possible warehouse
locations:
· L1 (3, 4) with an annual capacity of 750 shipments
· L2 (100, 110) with an annual capacity of 1500 shipments.
Company
A can build either or both of these warehouses. The annualised costs to build a
warehouse are £50,000 in L1 and £30,000 in L2 respectively. If only one
warehouse is built, it will ship to all customers. However, if both warehouses
are built, then the company must decide which warehouse will ship to each
customer. There is a travelling cost of £1 per mile.
1.
Develop a single model to minimise total
annual cost, where a straight-line distance is used to represent
the distance between two locations and then use the Excel Solver to
optimise it.
2.
Discuss the limitations of the optimisation
model developed in (i) (the recommended word count is 300 words).
Hint: use a (binary) decision variable
(for example, y1 and y2) corresponding to whether
location L1 and L2 will be used to build a warehouse respectively. Therefore,
the constraint for building at least one warehouse is y1+y2
≥ 1.
Last Completed Projects
| topic title | academic level | Writer | delivered |
|---|