Home /
Expert Answers /
Operations Management /
use-the-microsoft-excel-solver-template-if-one-is-provided-show-calculation-working-steps-and-alway-pa474
(Solved): Use the Microsoft Excel Solver template if one is provided. Show calculation working steps and alway ...
- Use the Microsoft Excel Solver template if one is provided.
- Show calculation working steps and always state your answer explicitly.
Whenever tables are used for calculations, make sure that you label all rows and columns appropriately and provide captions for all tables, diagrams, and screenshots
Specific:
Hands-On Task
Reconsider the prototype example of the Job Shop Company problem in section 9.3, page 542. Do the following:
- Use the cost data table, Table 9.22 on page 543, to formulate an assignment problem mathematical model for the Job Shop company.
- Study and understand the Sellmore Company problem in section 3.6, page 599 and the Watch item, Assignment Problem - Formulation and Excel Solver.
- Download and open the Excel file named Job Shop Company Template located under the Resources section on the Hands-On: Linear Programming Applications (Transportation and Assignment Problems) Assignment page.
- Enter the data from the formulated mathematical model in (a) and table 9.22 page 543 into the appropriate cell in the Excel spreadsheet and make modifications where necessary.
- Obtain the optimal solution for the assignment problem using Solver as explained in the Watch item,
- Provide the mathematical model from (a) with all variables described.
- Draw and explain a well labeled network representation of the assignment problem.
- List (itemize) the steps you took to obtain the optimal solution in (e) with Excel solver.
- Upload the Excel and Word files to the assignment page.
Please solve this problem and obtain the optimal solution. Show all steps
TABLE 9.22 Materials-handling cost data (\$) for Job Shop Co. To formulate this problem as an assignment problem, we must introduce a dummy machine for the extra location. Also, an extremely large cost should be attached to the assignment of machine 2 to location 2 to prevent this assignment in the optimal solution. The resulting assignment problem cost table is shown in Table 9.23. This cost table contains all the necessary data for solving the problem. The optimal solution is to assign machine 1 to location 4, machine 2 to location 3, and machine 3 to location 1 , for a total cost of per hour. (Since the shop operates approximately 2.000 hours per year, this total cost will be approximately on an annual basis.) The dummy machine is assigned to location 2, so this location is available for some future real machine. We shall discuss how this solution is obtained after we formulate the mathematical model for the general assignment problem. The Assignment Problem Model The mathematical model for the assignment problem uses the following decision variables:
for and . Thus, each is a binary variable (it has value 0 or 1). As discussed at length in the chapter on integer programming (Chap. 12), binary variables are important in OR for representing yes/no decisions. In this case, the yes/no decision is: Should assignee perform task ? By letting denote the total cost, the assignment problem model is
Expert Answer
Assignment Problems are special types of Transportation Problems which deals in allocating resources...