- Solve Question B Using Excel Solver 1 (38.14 KiB) Viewed 40 times
Comfort Inc. is a furniture company that manufactures desks and chairs for offices. They have forecasted the demand of desks and chairs for the second half of this year, and currently they are at the planning phase for production. They figured out the manufacturing plan for their chairs, however they are still struggling to determine the optimal production schedule for their desks. Each desk is made up of three main assembly parts: A, B and C. Part C is assembled from two other parts: D and E. Figure 1 shows the hierarchy of parts and the number of each used in assembling a single desk. DESKS Qty: 1 Part A Qty: 2 Part B Qty: 1 Part C Qty: 3 Part D Qty: 1 Part E Qty: 2 Figure 1: Product Structure Tree Table 1 shows the monthly inventory cost per unit, the lead time, which is the time between when the part is ordered until it is received, the current inventory, and finally the ordering policy for each part. Notice that desks do not have inventory cost. Table 1: Parts Requirements Part B Part C Part D Part A 5 Part E 2 8 4 7 Inventory Cost: Lead Time: Current Inventory: 1 month 2 months 2 months 1 month 2 months 100 30 0 40 0 Ordering Policy: Minimum 500 parts (If an order is made for this part in a month, it will be for at least 500 parts) Any Amount Any Amount Any Amount Any Amount
The forecasted monthly demand for desks and the monthly production cost per unit of each part is given in table 2. Each desk is sold for 3000 Saudi Riyals. Table 2: Monthly Demand and Production Cost Month, Year Forecasted Production Cost / Unit 2022 Demand Desk Part A Part B Part C Part D Part E July 120 400 170 220 70 110 50 August 160 400 140 215 80 130 40 September 210 450 150 205 90 170 55 October 230 500 140 195 20 210 45 November 310 600 130 200 80 180 60 December 360 600 160 200 80 190 35 ! Answer the following questions: a. Formulate this problem as a linear, integer, or mixed integer program to maximize the total profit and determine the optimum number of parts to be produced each month. Make sure to define and explain the decision variables, objective function, and constraints. b. How many desks and parts of each should be made available monthly, and when should production start considering the Lead Time?