Question 1: A manufacturer produces two products, A and B, and is trying to work out his production schedule for the next two months. He curently has on hand 1,000 units of A and 800 units of B and estimates sales over the period as shown in the following table: Estimated Sales, Units Months | A 2,000 3,000 2 3,000 4,000 Regular Shift Production Cost, $10 S/Unit $6 If the production facilities were devoted entirely to producing product A, they could turn out a maximum of 4,000 units/month on regular shift operation and up to 1,000 units/month additional on overtime operation. Similarly, if only product B was produced, regular shift capacity would be 6,000 units month with 2,000 units/months additional available from overtime operation The production facilities can be devoted to any proportions of products A and B-e.g, 1,000 units of A (1/4 capacity) plus 4,500 units of B (3/4 capacity) would just use the total regular shift capacity for a month. Overtime production of product A costs $5/unit more than regular shift production, while for product B the added cost is $3/unit. Management would like to end the two-month period with at least an inventory of 500 units of product A and 1,500 units of product B. It costs $3 to store one unit of Product A for a month and $2 to store one unit of product B for a month a. Formulate algebraically the linear programming model for this production scheduling problem. Define the decision variables, objective function, and constraints. (12 points) b. Formulate this same linear programming problem on a spreadsheet and SOLVE using Excel solver (Provide the corresponding "Excel Spreadsheet" and the "Answer Report"). Include "managerial statements" that communicate the results of the analyses. (8 points) Question 1: A manufacturer produces two products, A and B, and is trying to work out his production schedule for the next two months. He curently has on hand 1,000 units of A and 800 units of B and estimates sales over the period as shown in the following table: Estimated Sales, Units Months | A 2,000 3,000 2 3,000 4,000 Regular Shift Production Cost, $10 S/Unit $6 If the production facilities were devoted entirely to producing product A, they could turn out a maximum of 4,000 units/month on regular shift operation and up to 1,000 units/month additional on overtime operation. Similarly, if only product B was produced, regular shift capacity would be 6,000 units month with 2,000 units/months additional available from overtime operation The production facilities can be devoted to any proportions of products A and B-e.g, 1,000 units of A (1/4 capacity) plus 4,500 units of B (3/4 capacity) would just use the total regular shift capacity for a month. Overtime production of product A costs $5/unit more than regular shift production, while for product B the added cost is $3/unit. Management would like to end the two-month period with at least an inventory of 500 units of product A and 1,500 units of product B. It costs $3 to store one unit of Product A for a month and $2 to store one unit of product B for a month a. Formulate algebraically the linear programming model for this production scheduling problem. Define the decision variables, objective function, and constraints. (12 points) b. Formulate this same linear programming problem on a spreadsheet and SOLVE using Excel solver (Provide the corresponding "Excel Spreadsheet" and the "Answer Report"). Include "managerial statements" that communicate the results of the analyses. (8 points)
