Reference no: EM132552717
Optimization
You must use the basic Excel Solver. You may not use any other optimization tool/program,
e.g. Excel OM/QM.
Question 1. Fast needs a lot of copper to make its traditional hard discs. Currently, Fast buys copper for its four plants from five different companies. These companies have submitted their bids in the form of price per ton for the coming year and the total number of tons they can supply.
|
Suppliers
|
Company 1
|
Company 2
|
Company 3
|
Company 4
|
Company 5
|
|
Copper prices (per ton)
|
$50
|
$47
|
$48
|
$45
|
$43
|
|
Available supply (tons)
|
350
|
250
|
210
|
300
|
490
|
Each of Fast's plants has also submitted their required tonnage for the coming year.
|
Fast Plants
|
Copper Demand (tons)
|
|
Plant 1
|
430
|
|
Plant 2
|
350
|
|
Plant 3
|
400
|
|
Plant 4
|
370
|
Fast's analysts have determined the shipping cost per ton from each company to each plant:
|
Cost of shipping from companies to plants (per ton)
|
|
|
Company 1
|
Company 2
|
Company 3
|
Company 4
|
Company 5
|
|
Plant 1
|
$9
|
$4
|
$5
|
$4
|
$4
|
|
Plant 2
|
$7
|
$6
|
$3
|
$2
|
$4
|
|
Plant 3
|
$7
|
$3
|
$7
|
$5
|
$2
|
|
Plant 4
|
$8
|
$4
|
$5
|
$6
|
$7
|
Your assignment is to use Solver to find the optimum quantity to buy from each company for each Fast plant such that the total cost is minimized, i.e. minimize Total Cost = Total cost of copper + Total shipping cost. All Fast plants must receive all their required tonnage of copper. Suppliers cannot provide more tonnage to Fast than the available supply. Note: Do not use an Integer constraint for this problem.
a. What is the Total Cost for the optimum mix of orders from the companies?
b. Does the Answer report show any non-binding constraints? If yes, which constraint(s) and now much slack is there?
c. What would a Shadow Price for Total Company 2 of "-7" mean?
Do all work on problem #1 on tab #1 in the M8A1 Fast Data file, with the logical exception being the Answer and Sensitivity Reports.
Question 2. Fast is allocating next year's budget among its divisions. The R&D (Research and Development) division must determine which research projects, named creatively 1 through 7, to fund. Each project requires various software, hardware, and outside consulting expenses which are termed "Additional Costs." Each project also requires certain Fast engineers.
Fast R&D has been given a budget allocation of $1.2 million to cover Additional Costs and has 35 Fast staff engineers available for the potential projects.
R&D has developed the following table of projected costs and staffing for the seven potential projects as well as a prediction of the dollar return to Fast if the project succeeds. "Return" implies all costs have been deducted.
|
Project
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
Engineers
Available / Budget
|
|
Return
|
$600,000
|
$680,000
|
$750,000
|
$400,000
|
$350,000
|
$725,000
|
$340,000
|
|
Fast Engineers
Required
|
8
|
10
|
7
|
4
|
8
|
10
|
8
|
35
|
|
Additional Costs
|
$190,000
|
$400,000
|
$370,000
|
$180,000
|
$225,000
|
$275,000
|
$130,000
|
$1,200,000
|
Due to scheduling conflicts, R&D has determined that at most, one of projects 1 and 2 should be pursued. Additionally, if project 2 is chosen, project 4 must also be chosen.
Develop a model to select the best projects within the budget, i.e. the combination of projects that delivers the greatest total return. Use of integer or binary constraints may be necessary. Use Simplex LP if possible.
Note: This problem can be solved without the use of If statements. However, should you choose to use If statements, you will have to use the Evolutionary engine and that may not find a true, optimum solution. In that case, initialize your decision variables to 0.
Question 3. Fast is moving some employees into a newly renovated floor in one of their buildings. There are 14 employees involved and there are 10 offices on the renovated floor, four of which seat two people. The Fast HR (Human Resources) director knows happy employees are productive employees and let the 14 staff members visit the new space and give their preference by a rank of 10 (first choice) to 1 (last choice).
|
Office Rank
|
Employee
|
|
Office
|
Capacity
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
10
|
11
|
12
|
13
|
14
|
a. How should the HR manager assign the offices to maximize the preference of all 14 employees? Hints:
• The decision variables are the employee office assignments.
• The objective function would be to maximize the sum of preferences the employees get, e.g. if employee 1 gets office 1, and employee 3 gets office 2, the sum of their preferences would be 3+8=11.
• Assignments must be binary.
• Employees and rankings are integers.
• No employee can have more than 1 office assignment.
• Every employee must have an assignment.
• Every office must be used to capacity, i.e. a 2-person office must have 2 employees assigned and 1- person offices can only have 1 employee assigned.
• Some of the hints are constraints.
b. The HR manager has decided to include consideration of the employees' seniority in the decision. Revise your decision model to corporate employee seniority and rerun Solver to find a new solution.
Question 4. Organize and format your Excel file to make it easy for Connie (and your instructor) to find and understand your work and results.
Attachment:- Student Instructions.rar