Case Problem 3 Textile Mill Scheduling The Scottsville Textile Mill* produces five different fabrics. Each fabric can be woven on one or more of the mill's 38 looms. The sales department's forecast of demand for the next month is shown in Table 4.16, along with data on the selling price per yard, variable cost per yard, and purchase price per yard. The mill operates 24 hours a day and is scheduled for 30 days during the coming month. Table 4.16 Monthly Demand, Selling Price, Variable Cost, and Purchase Price Data for Scottsville Textile Mill Fabrics Fabric Demand (yards) Selling price ($/yard) Variable Cost ($/yard) Purchase Price (Slyard) 16,500 0.99 0.66 0.80 22,000 0.55 62,000 0,49 0.70 0.60 0.70 0.70 7.500 0.51 62,000 0.50 The mill has two types of looms: dobby and regular. The dobbie looms are more versatile and can be used for all five fabrics. The regular looms can produce only three of the fabrics. The 7,500 62,000 1.24 0.70 0 $ The mill has two types of looms: dobby and regular. The dobbie looms are more versatile and can be used for all five fabrics. The regular looms can produce only three of the fabrics. The mill has a total of 38 looms: 8 are dobby and 30 are regular. The rate of production for each fabric on each type of loom is given in Table 4.17. The time required to change over from producing one fabric to another is negligible and does not have to be considered. Table 4.17 Loom Production Rates for the Scottsville Textile Mill Loom Rate (yards/hour) Dobby Regular Fabric 4.63 4.63 523 5.23 4.17 Note: Fabrics 1 and 2 can be manufactured only on the dobbie loom. The Scottsville Textile Mill satisfies all demand with either its own fabric or fabric purchased from another mill. Fabrics that cannot be woven at the Scottsville Mill because of limited loom 5.23 5.23 5.23 5.23 4.17 4.17 Note: Fabrics 1 and 2 can be manufactured only on the dobbie loom. The Scottsville Textile Mill satisfies all demand with either its own fabric or fabric purchased from another mill. Fabrics that cannot be woven at the Scottsville Mill because of limited loom capacity will be purchased from another mill. The purchase price of each fabric is also shown in Table 4.16. Create an Excel spreadsheet (using the Excel Style Template) to capture the LP formulation Solve the linear program using Excel Solver and add the appropriate sensitivity analysis sheets Write a management report (MS Word file) which includes: Executive summary of the key results and insights Problem description Review of results Address specific questions and recommendations The Scottsville Textile Mill satisfies all demand with either its own fabric or fabric purchased from another mill. Fabrics that cannot be woven at the Scottsville Mill because of limited loom capacity will be purchased from another mill. The purchase price of pach fabric is also shown in Table 4.16. Managerial Report Develop a model that can be used to schedule production for the Scottsville Textile Mill, and at the same time, determine how many yards of each fabric must be purchased from another mill. Include a discussion and analysis of the following items in your report: 1. The final production schedule and loom assignments for each fabric. 2. The projected total contribution to profit. 3. A discussion of the value of additional loom time. (The mill is considering purchasing a ninth dobbie loom. What is your estimate of the monthly profit contribution of this additional loom?) 4. A discussion of the objective coefficients' ranges. 5. A discussion of how the objective of minimizing total costs would provide a different model than the objective of maximizing total profit contribution. (How would the interpretation of the objective coefficients' ranges differ for these two models?) Case Problem 3 Textile Mill Scheduling The Scottsville Textile Mill* produces five different fabrics. Each fabric can be woven on one or more of the mill's 38 looms. The sales department's forecast of demand for the next month is shown in Table 4.16, along with data on the selling price per yard, variable cost per yard, and purchase price per yard. The mill operates 24 hours a day and is scheduled for 30 days during the coming month. Table 4.16 Monthly Demand, Selling Price, Variable Cost, and Purchase Price Data for Scottsville Textile Mill Fabrics Fabric Demand (yards) Selling price ($/yard) Variable Cost ($/yard) Purchase Price (Slyard) 16,500 0.99 0.66 0.80 22,000 0.55 62,000 0,49 0.70 0.60 0.70 0.70 7.500 0.51 62,000 0.50 The mill has two types of looms: dobby and regular. The dobbie looms are more versatile and can be used for all five fabrics. The regular looms can produce only three of the fabrics. The 7,500 62,000 1.24 0.70 0 $ The mill has two types of looms: dobby and regular. The dobbie looms are more versatile and can be used for all five fabrics. The regular looms can produce only three of the fabrics. The mill has a total of 38 looms: 8 are dobby and 30 are regular. The rate of production for each fabric on each type of loom is given in Table 4.17. The time required to change over from producing one fabric to another is negligible and does not have to be considered. Table 4.17 Loom Production Rates for the Scottsville Textile Mill Loom Rate (yards/hour) Dobby Regular Fabric 4.63 4.63 523 5.23 4.17 Note: Fabrics 1 and 2 can be manufactured only on the dobbie loom. The Scottsville Textile Mill satisfies all demand with either its own fabric or fabric purchased from another mill. Fabrics that cannot be woven at the Scottsville Mill because of limited loom 5.23 5.23 5.23 5.23 4.17 4.17 Note: Fabrics 1 and 2 can be manufactured only on the dobbie loom. The Scottsville Textile Mill satisfies all demand with either its own fabric or fabric purchased from another mill. Fabrics that cannot be woven at the Scottsville Mill because of limited loom capacity will be purchased from another mill. The purchase price of each fabric is also shown in Table 4.16. Create an Excel spreadsheet (using the Excel Style Template) to capture the LP formulation Solve the linear program using Excel Solver and add the appropriate sensitivity analysis sheets Write a management report (MS Word file) which includes: Executive summary of the key results and insights Problem description Review of results Address specific questions and recommendations The Scottsville Textile Mill satisfies all demand with either its own fabric or fabric purchased from another mill. Fabrics that cannot be woven at the Scottsville Mill because of limited loom capacity will be purchased from another mill. The purchase price of pach fabric is also shown in Table 4.16. Managerial Report Develop a model that can be used to schedule production for the Scottsville Textile Mill, and at the same time, determine how many yards of each fabric must be purchased from another mill. Include a discussion and analysis of the following items in your report: 1. The final production schedule and loom assignments for each fabric. 2. The projected total contribution to profit. 3. A discussion of the value of additional loom time. (The mill is considering purchasing a ninth dobbie loom. What is your estimate of the monthly profit contribution of this additional loom?) 4. A discussion of the objective coefficients' ranges. 5. A discussion of how the objective of minimizing total costs would provide a different model than the objective of maximizing total profit contribution. (How would the interpretation of the objective coefficients' ranges differ for these two models?)