CS代考 COMP4041-LDO Workshop 6 Selection, Assignment and Location Problems – cscodehelp代写

COMP4041-LDO Workshop 6 Selection, Assignment and Location Problems
Purpose: Write and solve optimization models for selection, assignment and location optimization problems including modelling constraints using binary decision variables.
The Excel file for this workshop contains two spreadsheets models for the INTERNET CONNECTION problem from the Lecture 6 notes. One is non-linear, and the non- linearity arises in the calculation of the fixed charge. The non-linear model cannot be solved using the Simplex LP method but it can be solved using the other methods available in the Excel solver (GRG Nonlinear or Evolutionary). The other model is linear and uses auxiliary binary decision variables. Make sure you understand these two models.
The Excel file for this workshop contains the data for an ASSIGNMENT problem with N=40. Develop the BIP spreadsheet model to obtain the optimal solution to this problem (see slides 16-18 of the Lecture 6 notes).
The standard Excel solver can handle 200 decision variables maximum. There is a premium version of the Excel solver but it comes at a cost, see more information of this at: http://www.solver.com/standard-excel-solver-dealing-problem-size-limits
It is time to use a more powerful spreadsheet solver. OpenSolver is an Excel add-in that extends Excel’s built-in Solver with a more powerful Linear Programming solver.
• OpenSolver uses several solvers including the excellent Open Source COIN-OR CBC optimisation engine, to quickly solve large optimization problems.
• It is compatible with your existing Solver models, so there is no need to change your spreadsheets.
• No artificial limits on the size of problem you can solve.
• OpenSolver is free, open source software licensed under the GPL.
OpenSolver should be ready for activation in the Excel installation of the University. However, you can also download it from the web and once installed, you need to activate it in the same way as you activate the standard Excel solver:
Select File → Options → Add-ins then click on the button Go… next to Excel Add-ins
Below are screenshots of the OpenSolver activation, its location in the Data tab next to the standard solver and a message you might get about enabling macros.

In order to install and use OpenSolver in your own computer, follow the instructions at: https://opensolver.org/installing-opensolver/
However, for the purposes of this module it is recommended that you use the linear version of OpenSolver which will ensure your models are solved only if they are linear.
Make sure to Unzip the file in a local folder in your computer because OpenSolver will not work if you run it from the zip file.
Develop the optimization model in Excel and LP-Solve to solve the following optimization problem.
SALESMEN Problem
The manager of a company wants to distribute 18 SALESMEN over the 4 branches of the company. For each salesman, the company has estimated the sales that the person would generate in each of the branches when the salesman is assigned to that branch. The table (see the workshop spreadsheet) gives the estimated sales per branch for each salesman. For example, if salesman 1 is assigned to branch B, the expected individual sales for that salesman is 16. Salesmen 1 to 5 are bilingual. Each salesman has to be assigned to exactly 1 of the branches, that is, a salesman cannot work simultaneously in more than 1 branch. Also, each of the branches should get at least 2 salesmen assigned. Salesmen 10 and 11 do not like each other so they should be assigned to different branches. Branches B and C should each have at most 3 salesmen assigned and 2 of them should be bilingual. Salesman 18 should be assigned to either branch A or branch D. Salesmen 15 and 16 are a good team so they should be assigned to the same branch. Finally, it is desirable that branch A is assigned either 4 salesmen or 8 salesmen in total. The manager wants to know exactly to which branch each salesman should be assigned maximize the overall expected sales.

Develop the optimization model in Excel and LP-Solve to solve the following optimization problem.
COPLA Problem
The COPLA company currently ships products from 5 plants to 4 warehouses. The company is considering the option of closing down one or more of the plants. This would increase distribution cost but perhaps lower overall cost which is made of distribution cost plus the fixed cost of operating each plant. The distribution costs, fixed costs, demand of each warehouse and capacity of each plan are given below. The problem is to decide which plants to close and which ones to keep open in order to minimize the overall cost.
Warehouse 1 Warehouse 2 Warehouse 3 Warehouse 4 Capacity Fixed Cost
15000 17000 13000
$4,500 1500 $4,000 1800 $3,000 1400 $3,200 2000
1800 16000
Transportation Costs (per 100 products)
$4,000 $2,500 $1,200 $2,200 2000 12000
$2,000 $2,600 $1,800 $2,600 2200
$2,500 $3,000 $4,100 $3,700 1900