Homework #1 (Summer 2016)
Suzuki Electronics Manufacturing Co.
Suzuki Electronics Manufacturing Co. produces five different models of telecommunications interface cards for personal computers (models A, B, C, D, and E). As summarized in the following table, each of these devices requires different amount of PC board, resistors, memory chips, and assembly labor time. The price and cost are also different across models.
In the next production period, the company has 76,000 square inches of PC board, 102,000 resistors, 35,000 memory chips, and 4,900 hours of assembly labor time available. They can sell all the products they manufacture, but the historical sales record suggests that the production be performed such that the share of each model within the total production units is at least the following figures:
The company’s objective is to maximize its profit by a finding the optimal number of production unit for each model. Your task is to help the company management make the optimal production decision. Use integer tolerance of zero for all questions.
Questions
1. Solve the problem by using the Spreadsheet (Excel) solver. Use the template provided. What was the maximized profit? What is the optimal production for each model?
2. Could the company make more money if they schedule their assembly time such that workers would work overtime (i.e., if the available labor time is expanded)? Why?
3. The marketing department came to you and said that they want you to produce such that the ratio of model C production to model D production is at least 0.5. How does your answer change?
Note: You must provide the followings: (1) a sheet of paper showing your answers (recommendations) to each question, (2) a hard copy of the spreadsheet for each question with correct numbers filled in, (3) a hard copy of the spreadsheet with cell formulas shown (only one copy is needed), and (4) a sheet showing your Risk Solver specifications (including “engine” tab). If you do not submit all of these as a package, you will not get full credits.