Department of Operations Management \& Business Statistics POMG6724: Analytics for Business Decisions Group Project Assessment (40\%) Instructions Project deliverables For this project, each group must produce the following files and submit them on Moodle page of the course on 19/11/2023 before 16:00: -Spreadsheet Model (A workbook Excel file). -Technical Report (A word file). -Presentation (A power point file). [Group Presentations will be held during the class time on 19/11/2023 at 4.15pm] Note: All files must be submitted by only one group member (links are provided on Moodle). You must write group members names and IDs on all the files. This project assessment contains Three parts. Groups should attempt all parts. Marks for each part/subpart are shown in square brackets. This assessment is worth 40% of your module mark for POMG6724. Note: All group members are required to contribute on all the parts of the assessment in their group. Plagiarism Policy: It must be your own work, including your own developed excel templates. Do not share your work or discuss your work with other groups/students. If you have been found to have plagiarised your work, your case will be dealt as per University rules. Marking Scheme: marks will be given proportionally to the correctness of the answers. [Total marks 40] Case Problem: Anderson Electronics is considering the production of four potential products: VCRs, stereos, TVs, and DVD players. The input for all products can be viewed in terms of just three main resources: electronic components, nonelectronic components, and assembly time. The composition of the four products in terms of these three inputs is shown in the table below, along with the unit selling prices of the products. Electronic components can be obtained at 7 per unit; nonelectronic components can be obtained at 5 per unit; assembly time costs 10 per hour. Each resource is available in limited quantity as shown in the table. Part I (A) Build a spreadsheet model of the above problem to identify the product mix which maximizes Anderson Electronics profit. Your Excel model must include setting up Solver "Objective function", "By changing Variable cells", Constraints", etc. [10] Part I (B) Solve the spreadsheet model using Excel Solver to generate Answer and Sensitivity reports. By performing analysis of the software output of the problem answer the following questions with brief explanations. [Note: Answers without brief explanations would not qualify for full marks] i. What is the impact on profit of a change in the supply of nonelectronic components? [1] ii. What is the impact on profit if we could increase the supply of electronic components by 400 units (to a total of 5,100 )? [1] iii. In the previous question what would happen, if we could increase the supply of electronic components by 4,000 units (to a total of 8,700 units)? [1] iv. Refer to the question about getting an additional 400 units of electronic components. What would happen if the supplier of these 400 units wanted 8 per unit rather than the current cost of 7 per unit? [1] v. Assume that we have the opportunity to get 250 additional hours of assembly time. However, this extra time will cost us 15 per hour rather than the current cost of 10 per hour. Should we take it? [2] vi. If we force the production of VCRs, what would be the impact on total profit? vii. How profitable must VCRs become before Anderson would consider producing them? [1] viii. Assume that there is some uncertainty in the price for DVD players. For what range of price will the current production remain optimal? If DVD players sold for 106, what would be Anderson's new total profit? [2] ix. In the solution of the above problem, identify the decision variables (products) with zero values. Let us suppose the company would like to enforce the production of those products with zero values regardless of any impact on the production of the other products. What steps would you take to help the company to enforce the production of those products. Provide your new solution and sensitivity report on a separate sheet in the Excel workbook with brief comments on the new solution. [4] Part II Prepare a short technical report on the above problem focusing on what should be the product mix strategy of the company. The report should not exceed one page of A4 (12-point font, single spacing). Note: You may like to base your report on the analysis of Part I (A \& B). [6] Part III Prepare a presentation on the above problem. (A presentation of 7 minutes in power point slides to be presented during the class time, plus 3 minutes for questions/answers). Note: You may like to cover any important technical aspects in your presentation. [10] Department of Operations Management \& Business Statistics POMG6724: Analytics for Business Decisions Group Project Assessment (40\%) Instructions Project deliverables For this project, each group must produce the following files and submit them on Moodle page of the course on 19/11/2023 before 16:00: -Spreadsheet Model (A workbook Excel file). -Technical Report (A word file). -Presentation (A power point file). [Group Presentations will be held during the class time on 19/11/2023 at 4.15pm] Note: All files must be submitted by only one group member (links are provided on Moodle). You must write group members names and IDs on all the files. This project assessment contains Three parts. Groups should attempt all parts. Marks for each part/subpart are shown in square brackets. This assessment is worth 40% of your module mark for POMG6724. Note: All group members are required to contribute on all the parts of the assessment in their group. Plagiarism Policy: It must be your own work, including your own developed excel templates. Do not share your work or discuss your work with other groups/students. If you have been found to have plagiarised your work, your case will be dealt as per University rules. Marking Scheme: marks will be given proportionally to the correctness of the answers. [Total marks 40] Case Problem: Anderson Electronics is considering the production of four potential products: VCRs, stereos, TVs, and DVD players. The input for all products can be viewed in terms of just three main resources: electronic components, nonelectronic components, and assembly time. The composition of the four products in terms of these three inputs is shown in the table below, along with the unit selling prices of the products. Electronic components can be obtained at 7 per unit; nonelectronic components can be obtained at 5 per unit; assembly time costs 10 per hour. Each resource is available in limited quantity as shown in the table. Part I (A) Build a spreadsheet model of the above problem to identify the product mix which maximizes Anderson Electronics profit. Your Excel model must include setting up Solver "Objective function", "By changing Variable cells", Constraints", etc. [10] Part I (B) Solve the spreadsheet model using Excel Solver to generate Answer and Sensitivity reports. By performing analysis of the software output of the problem answer the following questions with brief explanations. [Note: Answers without brief explanations would not qualify for full marks] i. What is the impact on profit of a change in the supply of nonelectronic components? [1] ii. What is the impact on profit if we could increase the supply of electronic components by 400 units (to a total of 5,100 )? [1] iii. In the previous question what would happen, if we could increase the supply of electronic components by 4,000 units (to a total of 8,700 units)? [1] iv. Refer to the question about getting an additional 400 units of electronic components. What would happen if the supplier of these 400 units wanted 8 per unit rather than the current cost of 7 per unit? [1] v. Assume that we have the opportunity to get 250 additional hours of assembly time. However, this extra time will cost us 15 per hour rather than the current cost of 10 per hour. Should we take it? [2] vi. If we force the production of VCRs, what would be the impact on total profit? vii. How profitable must VCRs become before Anderson would consider producing them? [1] viii. Assume that there is some uncertainty in the price for DVD players. For what range of price will the current production remain optimal? If DVD players sold for 106, what would be Anderson's new total profit? [2] ix. In the solution of the above problem, identify the decision variables (products) with zero values. Let us suppose the company would like to enforce the production of those products with zero values regardless of any impact on the production of the other products. What steps would you take to help the company to enforce the production of those products. Provide your new solution and sensitivity report on a separate sheet in the Excel workbook with brief comments on the new solution. [4] Part II Prepare a short technical report on the above problem focusing on what should be the product mix strategy of the company. The report should not exceed one page of A4 (12-point font, single spacing). Note: You may like to base your report on the analysis of Part I (A \& B). [6] Part III Prepare a presentation on the above problem. (A presentation of 7 minutes in power point slides to be presented during the class time, plus 3 minutes for questions/answers). Note: You may like to cover any important technical aspects in your presentation. [10]