Operation Research (Need proper solution)
Instructions: 1. This is a GROUP work: Each member should contribute to it. However, collaboration between groups of the same section or other sections is PROHIBITED. Any answer that is found to be not authentically yours (cheated from a website, obtained through help from a tutor or a friend, etc..) will get you ZERO in this assignment. Please adhere to these regulations for your own individual benefit. 2. Each group are expected to submit TWO files: (1) The MS EXCEL file with the solution, and (2) a report written on MS Word (in DOC or PDF format). The files should have proper formatting and labeling. 3. Use the final report requirement bellow to prepare your report. Final Report Requirements: - Cover page: It includes course name, project title, students' names and ID numbers. - Problem Formulation (Modeling): - Define the decision variables. - Write and explain the objective function. Identify and explain all objective function coefficients (ci). - Identify and explain all constraints, which includes: - Identifying and explaining all right-hand side values ( bi). - Identifying and explaining any other supporting data, if available. - Problem Solution: Using Microsoft Excel Solver, generate: - An optimal solution for the problem and, - The sensitivity report, if needed. - Results and discussion: Answer all the questions from the case study here. [Important] Generation of Sensitivity Analysis Report: You may encounter a problem in generating the sensitivity analysis report on Excel. In order to successfully do so please follow the steps below: 1. Formulate and run your model to get the optimal solution. 2. For ALL integer decision variables only: Add a constraint for each variable where you set it equal to its optimum value obtained in step 1 (for example, if x1 is an integer variable that has a value of 5 at the optimum solution, define a constraint as x1=5 ). Then, redefine all integer variables as non-negative. IE311 Project Case Study Sportano is a famous brand that makes sports equipment such as boots, jerseys, footballs, and more. Four different sizes of balls designed for different age groups are made in three manufacturing sites. Each site has labor and machine-hour limitations, and the values for each are given in Tables 1-3. The process of manufacturing the balls in all production sites is similar, but not quite identical. Hence, each production site uses different amounts of raw material in creating the same ball size. The material requirements for each football are given in the last column of Tables 1-3. The amount of raw material available for the entire production during the planning period is 3,500 pounds. Sportano has 3 major customers for its products: Royal Spanish Football Federation (RFEF), The Football Association (TheFA), and The French Football Federation (FFF). The maximum demand for each customer is given in Table 4 for each product. Tables 5-7 show the selling price of each ball size, the transportation costs, and the manufacturing costs of each product for each production site. Sites 1 and 2 are experiencing a problem with production that is causing an unusual rate of defects in the balls. Based on that, all shipments manufactured in sites 1 and 2 that are used to fulfill the demand for the customers RFEF or TheFA must go through a special inspection to ensure that the balls conform to the standards. This is done by sending the manufactured balls to a central inspection site where they get tested, and then they get sent to their final destination. The capacity of this inspection site is 1,500 balls during one planning period. Answer the following questions: a. Determine Sportano's optimum plan for the production and demand fulfillment. b. Find the total cost and revenue associated with the total production for each manufacturing site. c. Suppose that you are given the chance to acquire more material, how much would you add? What would be the maximum dollar amount you are willing to pay for the increase? d. Suppose that you are given the chance to increase inspection capacity, how much would you add? What would be the maximum dollar amount you are willing to pay for the increase? e. Suppose that you are given the chance to increase machine hours in any of the production sites. Which of them would you increase the hours for? What would be the maximum dollar amount you are willing to pay for the increase? How many machine hours would you add? f. Marketing is trying to convince RFEF to purchase 50% more footballs in a period. Can the current resources in hand accommodate this increase or do we need to add more resources? How much more money can we make if we take on the additional demand? Table 1: Product-Resource Constraints: Site 1 Table 2: Product-Resource Constraints: Site 2 Table 3: Product-Resource Constraints: Site 3 Table 4: Maximum Product Sales Table 5: Product Sales Price (\$) per Unit Football Type Customers IE311 - Operations Research I - Winter 2023 Table 6: Shipping Costs (\$) per Unit Table 7: Production Costs (\$) per Unit Instructions: 1. This is a GROUP work: Each member should contribute to it. However, collaboration between groups of the same section or other sections is PROHIBITED. Any answer that is found to be not authentically yours (cheated from a website, obtained through help from a tutor or a friend, etc..) will get you ZERO in this assignment. Please adhere to these regulations for your own individual benefit. 2. Each group are expected to submit TWO files: (1) The MS EXCEL file with the solution, and (2) a report written on MS Word (in DOC or PDF format). The files should have proper formatting and labeling. 3. Use the final report requirement bellow to prepare your report. Final Report Requirements: - Cover page: It includes course name, project title, students' names and ID numbers. - Problem Formulation (Modeling): - Define the decision variables. - Write and explain the objective function. Identify and explain all objective function coefficients (ci). - Identify and explain all constraints, which includes: - Identifying and explaining all right-hand side values ( bi). - Identifying and explaining any other supporting data, if available. - Problem Solution: Using Microsoft Excel Solver, generate: - An optimal solution for the problem and, - The sensitivity report, if needed. - Results and discussion: Answer all the questions from the case study here. [Important] Generation of Sensitivity Analysis Report: You may encounter a problem in generating the sensitivity analysis report on Excel. In order to successfully do so please follow the steps below: 1. Formulate and run your model to get the optimal solution. 2. For ALL integer decision variables only: Add a constraint for each variable where you set it equal to its optimum value obtained in step 1 (for example, if x1 is an integer variable that has a value of 5 at the optimum solution, define a constraint as x1=5 ). Then, redefine all integer variables as non-negative. IE311 Project Case Study Sportano is a famous brand that makes sports equipment such as boots, jerseys, footballs, and more. Four different sizes of balls designed for different age groups are made in three manufacturing sites. Each site has labor and machine-hour limitations, and the values for each are given in Tables 1-3. The process of manufacturing the balls in all production sites is similar, but not quite identical. Hence, each production site uses different amounts of raw material in creating the same ball size. The material requirements for each football are given in the last column of Tables 1-3. The amount of raw material available for the entire production during the planning period is 3,500 pounds. Sportano has 3 major customers for its products: Royal Spanish Football Federation (RFEF), The Football Association (TheFA), and The French Football Federation (FFF). The maximum demand for each customer is given in Table 4 for each product. Tables 5-7 show the selling price of each ball size, the transportation costs, and the manufacturing costs of each product for each production site. Sites 1 and 2 are experiencing a problem with production that is causing an unusual rate of defects in the balls. Based on that, all shipments manufactured in sites 1 and 2 that are used to fulfill the demand for the customers RFEF or TheFA must go through a special inspection to ensure that the balls conform to the standards. This is done by sending the manufactured balls to a central inspection site where they get tested, and then they get sent to their final destination. The capacity of this inspection site is 1,500 balls during one planning period. Answer the following questions: a. Determine Sportano's optimum plan for the production and demand fulfillment. b. Find the total cost and revenue associated with the total production for each manufacturing site. c. Suppose that you are given the chance to acquire more material, how much would you add? What would be the maximum dollar amount you are willing to pay for the increase? d. Suppose that you are given the chance to increase inspection capacity, how much would you add? What would be the maximum dollar amount you are willing to pay for the increase? e. Suppose that you are given the chance to increase machine hours in any of the production sites. Which of them would you increase the hours for? What would be the maximum dollar amount you are willing to pay for the increase? How many machine hours would you add? f. Marketing is trying to convince RFEF to purchase 50% more footballs in a period. Can the current resources in hand accommodate this increase or do we need to add more resources? How much more money can we make if we take on the additional demand? Table 1: Product-Resource Constraints: Site 1 Table 2: Product-Resource Constraints: Site 2 Table 3: Product-Resource Constraints: Site 3 Table 4: Maximum Product Sales Table 5: Product Sales Price (\$) per Unit Football Type Customers IE311 - Operations Research I - Winter 2023 Table 6: Shipping Costs (\$) per Unit Table 7: Production Costs (\$) per Unit
