Kamm Industries is one of the leading carpet producers in the USA. Its owner, Geoff Kamm, has asked for your assistance in planning the production schedule for the next quarter (13... Kamm Industries is one of the leading carpet producers in the USA. Its owner, Geoff Kamm, has asked for your assistance in planning the production schedule for the next quarter (13 weeks). The company has orders for 15 different types of carpets that the company can produce on either of two types of looms: Dobbie looms and Pantera looms. Pantera looms produce standard carpeting; Dobbie looms can also produce standard carpeting but they also allow the incorporation of designs into the carpeting. The following table summarizes the orders for each type of carpet that must be produced in the coming quarter along with their production rates and costs on each type of loom, and the cost of subcontracting each order. Note the first 4 orders can only be produced on a Dobbie loom or via subcontracting. Assume that any portion of an order may be subcontracted. Kamm currently owns and operates 15 Dobbie looms and 80 Pantera looms. To maximize efficiency and keep pace with demand, the company operates 24 hours a day, 7 days a week. Each machine is down for routine maintenance for approximately 2 hours per week. Create a spreadsheet model for this problem that can be used to determine the optimal production plan and answer the following questions. Use the Sensitivity Report and Answer Report (do not re-solve the problem to answer questions). (The data for this problem is provided in Homework4S25data.xlsx.) (a) What is the cost of the optimal production plan? Which carpets should be outsourced (completely or partially)? (b) Is the solution unique? Explain briefly. (c) What would happen to the total cost if a Dobbie machine were to break down for one hour? Would the cost increase/decrease? By how much? Explain.
Understand the Problem
This question is a production planning problem for Kamm Industries. It requires creating a spreadsheet model to determine the optimal production plan for 15 different carpet types, considering Dobbie and Pantera looms, subcontracting options, machine capacity, and maintenance downtime. You need to use the Sensitivity and Answer Reports (generated from the spreadsheet model, which is not provided) to answer the specific questions about cost, outsourcing, solution uniqueness, and the impact of machine downtime.
Answer
Needs Homework4S25data.xlsx file to solve accurately. Solver in Excel is needed.
Due to the reliance on the Homework4S25data.xlsx file, which I do not have access to, I am unable to provide specific numerical answers. However, I can outline the general approach to solving this problem using the Solver tool in spreadsheet software like Excel.
(a) To find the cost of the optimal production plan and determine which carpets should be outsourced, you would use Solver to minimize the total cost of production while meeting the demand for each type of carpet. The decision variables would be the amount of each carpet produced on each type of loom and the amount subcontracted. The constraints would include meeting demand for each carpet type, loom capacity, and any restrictions on which looms can produce specific carpets. The carpets that should be outsourced (completely or partially) would be identified by non-zero subcontracting quantities in the optimal solution.
(b) To determine if the solution is unique, you would examine the Solver report. If the reduced costs for all non-basic variables are non-zero, the solution is unique. If any reduced cost is zero, there may be multiple optimal solutions.
(c) To determine the impact of a Dobbie machine breaking down for one hour, you would refer to the sensitivity report. The shadow price for the Dobbie loom capacity constraint would indicate the change in the total cost if the capacity of Dobbie looms were reduced by one hour. If the shadow price is positive, the cost would increase; if it is negative, the cost would decrease. The magnitude of the shadow price would indicate the amount of the change in cost.
Answer for screen readers
Due to the reliance on the Homework4S25data.xlsx file, which I do not have access to, I am unable to provide specific numerical answers. However, I can outline the general approach to solving this problem using the Solver tool in spreadsheet software like Excel.
(a) To find the cost of the optimal production plan and determine which carpets should be outsourced, you would use Solver to minimize the total cost of production while meeting the demand for each type of carpet. The decision variables would be the amount of each carpet produced on each type of loom and the amount subcontracted. The constraints would include meeting demand for each carpet type, loom capacity, and any restrictions on which looms can produce specific carpets. The carpets that should be outsourced (completely or partially) would be identified by non-zero subcontracting quantities in the optimal solution.
(b) To determine if the solution is unique, you would examine the Solver report. If the reduced costs for all non-basic variables are non-zero, the solution is unique. If any reduced cost is zero, there may be multiple optimal solutions.
(c) To determine the impact of a Dobbie machine breaking down for one hour, you would refer to the sensitivity report. The shadow price for the Dobbie loom capacity constraint would indicate the change in the total cost if the capacity of Dobbie looms were reduced by one hour. If the shadow price is positive, the cost would increase; if it is negative, the cost would decrease. The magnitude of the shadow price would indicate the amount of the change in cost.
More Information
This is a production planning problem that can be solved using linear programming. The Solver tool in Excel is a common tool for solving linear programming problems.
Tips
Common mistakes include not considering all constraints, such as loom capacity and demand for each carpet type. Also, ensure that the objective function (total cost) is correctly defined.
AI-generated content may contain errors. Please verify critical information
