MGOC10                   Fall 2024

Assignment 2. Worth: 9%. Due: by the end of the day (11:59 pm) on Friday November 29, 2024. The assignment should be submitted on Quercus via

ASSIGNMENTS.

Do not leave the submission of the assignment to the last hours. There maybe various unexpected circumstances (computer crash, system glitch, electricity down, etc.) Time    management, and submission of the assignment on time is your responsibility regardless of circumstances.

This is a group assignment, it should be done in groups of 1-3 people who submit a joint report. You can team up with students from different sections of the course. If you do the assignment individually, it will be graded by the same standards as assignments of groups. Groups of more than 3 people are not allowed. One member of a group should submit the report, indicating on the frontpage the names and student numbers of all group members. There will be a penalty if the assignment is submitted by more than one group member.

You should write managerial reports for the following two problems:

Problem 1. A firm produces widgets by combining purified elements A and B. At most 15,000 widgets per week can be sold for $200 per widget. Type 1 machines purify elements A and B; each hour that a Type 1 machine is working, it purifies 100 pounds of A and 120 pounds of B (the machine must work with both elements simultaneously and   cannot work with them separately). A Type 1 machine costs $8,000 per week to rent and  costs $25 per hour to operate. Unlimited amounts of unpurified (raw) elements A and B   can be purchased at a cost of $20 per pound for A and $30 per pound for B. Purified A and B can be turned into widgets by using either Type 2 machines or Type 3 machines. Each Type 2 machine costs $6,000 per week to rent and costs $35 per hour to operate. Each minute that a Type 2 machine is operated, it uses 2 pounds of purified A and 1.5 pounds of purified B to produce one widget. Each Type 3 machine costs $5,000 per week to rent and $22 per hour to operate. Every 1.5 minutes that a Type 3 machine is operated, it uses 1.5 pounds of purified A and 3.5 pounds of purified B to produce one widget. Widgets cannot be stored, so each week the company will produce the same number of widgets and operate machines for the same number of hours. Also, any purified A and B coming out of Type 1 machines that is not used to produce widgets must be disposed of (incinerated) at the end of the week at a cost of $3.5 per pound. Each machine can be operated up to 40 hours per week. Machines must be rented for the whole week and widgets are sold by the pound, so a fractional number of widgets can be produced each week. The number of machines of each type rented must be an integer.

a)  Develop a linear optimization model that will maximize the firm’s weekly profit. Use only the following variables: T1– the total (combined) number of hours that  all Type 1 machines will be operating per week; T2, T3 similarly; M1 – the number of Type 1 machines rented; M2, M3 similarly. Explain all constraints and the objective function clearly.

b)  Solve the model using Excel solver. State your recommendation and the optimal weekly profit. Put screenshots of supporting computer outputs in the appendix.

c)   Perform. some additional analysis of your choice. The additional analysis should be clearly marked (a separate section). It should be between half-page and a page, with supporting computer outputs in the appendix.

d)  Write an executive summary of your work (no more than one page). Place it at the beginning ofthe report for this problem.

Problem 2. Company Electrum produces electrical components, including a remote controller for TVs and a remote controller for VCRs. Each controller consists of three subassemblies that are manufactured by Electrum; a base, a cartridge, and a keypad. Both controllers use the same base subassembly, but different cartridge and keypad subassemblies.

Electrum’s sales forecast indicates that 7000 TV controllers and 5000 VCR controllers will be needed to satisfy demand during the next planning period. Because only 500 hours of in-house manufacturing time are available, Electrum is considering purchasing some, or all, of the units of each subassembly from outside suppliers. If Electrum manufactures a subassembly in-house, it incurs a fixed setup cost as well as a variable manufacturing cost. The table below shows the setup cost, the manufacturing time per unit of each subassembly, the manufacturing cost per unit of each subassembly, and the cost to purchase a unit of each subassembly from an outside supplier:

a)  Develop an optimization model to determine how many units of each

subassembly Electrum should manufacture and how many units of each subassembly Electrum should purchase to minimize the total cost of satisfying the demand. Solve it using Excel solver, state your recommendation, and put screenshots of the supporting computer outputs in the appendix. What is the total  cost (setup, manufacturing, and purchase) associated with your recommendation?

b)  Suppose Electrum is considering using new machinery to produce VCR

cartridges. For the new machinery, the setup cost is $3000; the manufacturing time is 2.5 minutes per cartridge, and the manufacturing cost is $2.60 per cartridge. Assuming that the new machinery will be used, develop an optimization model to determine how many units of each subassembly Electrum should manufacture and how many units of each subassembly Electrum should purchase.  Solve it using Excel solver, state your recommendation, and put screenshots of the supporting computer outputs in the appendix. What is the total cost associated with your recommendation? Do you think the new machinery should be used? Explain.

c)   Perform. some additional analysis of your choice. The additional analysis should   be clearly marked (a separate section). It should be between half-page and a page, with supporting computer outputs in the appendix.

d)  Write an executive summary of your work (no more than one page). Place it at the beginning of the report for this problem.

The problems should be addressed in your assignment in this order. Please indicate clearly where the second problem starts, for the convenience of the grader. On the front page, put your names, student numbers, course (MGOC10 “Analytics for Decision Making”), professor’s name (Igor Averbakh), and list your section(s) (e.g., L01, L02, L03, L04).

Your managerial report for each problem should have the following structure:

1)  Executive summary;

2)  Detailed report.

The detailed report should make clear references to computer outputs that must be also included as screenshots in appendices. Please include screenshots of the original Excel outputs, not summaries, not tables that you draw yourself based on the outputs. The screenshots should support your answers. Answers without the supporting screenshots of computer outputs may not be credited. Do not put the screenshots in the main body of the report, only in appendices. The appendices should be included after the  managerial reports for both problems (that is, you first put the managerial reports for both problems, then the appendices for both problems).

As for computer outputs for the models, please include (1) the spreadsheet, (2) the Solver Parameters screen.

Your assignments must be typed. Handwritten assignments will not be accepted.

Quality of presentation is a significant component in evaluation. The assignment will be graded based on correctness, logic, completeness, clarity and quality of presentation, grammatical and stylistic correctness, and overall impression (yes, in real life reports not only have to be correct, but also should make good impression!) Note that evaluation of presentation is based on the subjective impression of the grader; clarity and ease of finding all necessary information is an important component.

You should use the Excel solver for this assignment. Detailed explanations on how to use the solver are available in the book. Examples on using Excel solver are available in each of the Chapters 2-4, and in subsequent chapters. Also, you can see the posted Topic 3 on  Quercus. You should carefully work out these examples to understand how to use the solver. Appendix A in the book has some basic material on spreadsheet models.

To ensure that all students have equal chances, to make the setting closer to a real-life project and to introduce an element of independent learning to the course, the general policy with respect to the assignments is that neither the professor northe T.A. will give  you ANY help with using the software - this you should figure out yourself. Nor will we help with any specific questions about your problems. However, general conceptual questions about the course material are very welcome.