MGOC10    Fall 2025

Assignment 1. Worth: 10%. Due: October 17, 2025, by the end of the day

(11:59 pm). Please submit via Quercus under ASSIGNMENTS. This assignment is individual, every student should submit their own work.

You should write managerial reports for the following two problems:

Problem 1. A firm makes three components for sale to refrigeration companies. Each component must be processed on two machines: a shaper and a grinder. The times (in minutes) per unit required on each machine are as follows:

The shaper is available for 120 hours, and the grinder is available for 110 hours. No more than 200 units of component 3 can be sold, but up to 1000 units of each of the other two components can be sold. In fact, the company already has orders for 600 units of component 1 that must be satisfied. The profit contributions for components 1, 2, and 3 are $8, $6, and $9, respectively.

a)  Formulate a linear programming model to maximize profit and solve for the recommended production quantities.

b)  What are the objective coefficient ranges for the three components? Interpret these ranges for company management.

c)  What are the right-hand-side ranges? Interpret these ranges for company management.

d)  If more time could be made available on the grinder, how much would it be worth?

e)   If more units of component 3 can be sold by reducing its sales price by $4, should the firm reduce the price?

Problem 2. A company manufactures kitchen cabinets. Because of a large backlog of orders for oak and cherry cabinets, the company decided to contract with three smaller cabinetmakers to do the final finishing operation. For the three cabinetmakers, the number of hours required to complete all the oak cabinets, the number of hours required to complete all the cherry cabinets, the number of hours available for this final finishing operation, and the cost per hour to perform. the work are shown here.

For example, Cabinetmaker 1 estimates it will take 50 hours to complete all the oak cabinets and 60 hours to complete all the cherry cabinets. However, Cabinetmaker 1 only has 40 hours available. Thus, Cabinetmaker 1 can only complete 40/50=0.80, or 80%, of the oak cabinets ifit worked only on oak cabinets. Similarly, Cabinetmaker 1 can only complete 40/60=0.67, or 67%, of the cherry cabinets ifit worked only on cherry cabinets.

a.   Formulate a linear programming model that can be used to determine the percentage of  the oak cabinets and the percentage of the cherry cabinets that should be given to each of the three cabinetmakers in order to minimize the total cost of completing both projects.

b.   Solve the model formulated in part (a). What percentage of the oak cabinets and what  percentage of the cherry cabinets should be assigned to each cabinetmaker? What is the total cost of completing both projects?

c.   If Cabinetmaker 1 has additional hours available, would the optimal solution change? Explain.

d.   If Cabinetmaker 2 has additional hours available, would the optimal solution change? Explain.

e.    Suppose Cabinetmaker 2 reduced its cost to $38 per hour. What effect would this change have on the optimal solution? Explain.

The problems should be addressed in your assignment in this order. Please clearly separate the problems, starting the second problem on a new page. Each page should have a header indicating which problem this page corresponds to. The assignment is individual. On the front page, put your name, student number, course (MGOC10 “Analytics for Decision Making”), term (Fall 2025), professor’s name (Igor Averbakh), and list your section (e.g., L01, L02, L03, L04).

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

1)   Executive summary (approximately one-third to one-half of a page for each problem, any font any spacing, but should look nice);

2)   Detailed report.

The executive summary for each problem should include a brief verbal description of the situation in your words (without numerical data) and a summary of your work and conclusions (the summary can include numerical results). Place the executive summary at the beginning of your report for the problem, under the heading “Executive Summary” . The executive summary is the summary of your MAIN findings: this is what your boss would read first, and if (s)he doesn’t like it, (s)he may not want to read the rest. So, it must be very clear, well-written and polished.

The detailed report should include all details of the model (clearly define your decision variables!), then answer the questions posed in the problem. Answers to the questions should be clearly identified (e.g., “Question b”) so that it is easy for the grader to find them. 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. Theappendices should be placed at the end of the assignment, after the reports for both problems, and should be clearly identified.

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. 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 nor the 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.