Question 5. (22 points) OPTIMAL PRODUCT MIX AND WHAT-IF QUESTIONS AU Electronics manufactures is the supplier of components C1, C2 and C3 for the production of screens and components D1 and D2 for the production of frames for 8KTVs. Screens and frames are produced by other manufacturers. Demand for the components has increased recently and meeting this demand will test the company's production capacities. Any of the components C1, C2, C3, D1 and D2 may need to be purchased by AU Electronics from outside suppliers because there is limited in-house capacity. The variable in-house production cost per unit is compared with the outside purchase cost in the following table. Component Outside Variable Cost (per unit) 1.21 Inside Production Cost (per unit) 0.81 C1 2.50 C2 C3 2.30 1.45 1.95 D1 0.74 0.42 D2 1.10 0.70 Table-25.1. Variable in-house and outside cost of the components AU Electronics Company's plant consists of three departments. The labor requirements in hours of each component in each department if manufactured in-house are summarized in the following table. The labor hours available for a motor production are listed in the last row. Availability of labor limits the in- house production capacity for the components. However, there is unlimited supply if the company decides to purchase the components from outside vendors. Component Cutting Department (hours per unit) 0.4 0.8 Shaping Department (hours per unit) 0.6 0.2 Fabrication Department (hours per unit) 0.4 0.5 C1 C2 C3 0.7 0.9 0.6 D1 0.3 0.3 0.1 D2 0.2 0.4 0.2 Capacity (total available labor hours) 250 325 175 Table-25.2. Labor hour requirements per unit produced and monthly available labor hours at each department The company is already in a contract with clients for the next month. The confirmed number of components to be delivered are given in Table-25-3. These numbers show the number of components the company has committed to sell and is the minimum demand AU Electronics must meet. Component C1 C2 C3 Minimum number of products to be sent to clients next month 200 250 150 45 50 Table-25.3. Confirmed demand for next month D1 D2 You are asked to determine the optimal product mix (i.e., the monthly number of each product type to be provided) that minimizes AU Electronics' production total production and purchasing costs for next month. You will formulate a linear programming model to determine the optimal solution using Excel Solver NOTE: SHOW ALL YOUR WORK. a) (3 points) What are the decision variables? How many decision variables are there? Clearly explain the decision variables in English and provide the mathematical notation used in the algebraic formulation of the decision problem. b) (2 points) What is the objective function? Clearly explain the objective function in English and provide the mathematical formulation. c) (2 points) What are the constraints of the problem? Clearly explain each constraint in English and provide the mathematical formulation. d) (1 points) Are there any sign or type restrictions in your formulation? Why or why not? Clearly explain the sign and type restrictions (if any). e) Prepare a spreadsheet to determine the optimal production plan using Excel Solver. i. (2 points) Provide a screenshot of your spreadsheet model. The screenshot should show all the cells involved in formulating the optimization problem. NOTE: Do not copy and paste your spreadsheet to your answer report as an Excel object. Instead, capture a screenshot in Excel and paste this as an image. ii. (2 points) Provide a screenshot of your spreadsheet with the formula view. You can switch from normal view to formula view in Excel by clicking the CTRL and ~ keys on your keyboard simultaneously. iii. (2 points) Prepare the Solver model by logging all the information to the Solver Parameters window in Excel. Provide a screenshot of the Solver Parameters window in Excel. iv. (2 points) Finally, solve the optimization problem using the Simplex LP method in Excel Solver. What is the optimal solution, i.e., the optimal values of the objective function and the decision variables? f) (1 point) Compute the labor utilization at each department. g) (2 points) Suppose the company hires new employees and the available labor hours for the fabrication department goes up to 220 hours per month. What effect does this have on the optimal product mix and the optimal cost? What is the change in the optimal cost value? Can you explain clearly why the cost changes (or does not change) when more employees are available at the fabrication department? h) (3 points) Answer this question independent of part g: Suppose the company did not have the capacity limits requirements as stated in Table Q5.3. What would be the optimal product mix for the company? Use Excel Solver to find the new solution. What is the new optimal product mix and the new optimal cost? Is the new optimal cost lower or higher than the one in part (e)? Why do you think this is? Explain clearly. Is having contracts with clients and committing to deliveries to clients beneficial in this problem? Explain clearly why or why not
