A producer has three products, A, B, and C, which are composed from many of the same raw materials and subassemblies by the same skilled workforce. Each unit of product A uses 15 units of raw material X, a single purge system subassembly, a case, a power cord, three labor hours in the assembly department, and one labor hour in the finishing department. Each unit of product B uses 10 units of raw material X, five units of raw material Y, two purge system subassemblies, a case, a power cord, five labor hours in the assembly department, and 90 minutes in the finishing department. Each unit of product C uses five units of raw material X, 25 units of raw material Y, two purge system subassemblies, a case, a power cord, seven labor hours in the assembly department, and three labor hours in the finishing department. Labor between the assembly and finishing departments is not transferable, but workers within each department work on any of the three products. There are three full-time (40 hours/week) workers in the assembly department and one full-time and one half-time (20 hours/week) worker in the finishing department. At the start of this week, the company has 300 units of raw material X, 400 units of raw material Y, 60 purge system subassemblies, 40 cases, and 50 power cords in inventory. No additional deliveries of raw materials are expected this week. There is a $90 profit on product A, a $120 profit on product B, and a $150 profit on product C. The operations manager doesn't have any firm orders, but would like to make at least five of each product so he can have the products on the shelf in case a customer wanders in off the street. Formulate the above problem in MS Excel, and solve using MS Solver.
A producer has three products, A, B, and C, which are composed from many of the same raw materials and subassemblies by the same skilled workforce. Each unit of product A uses 15 units of raw material X, a single purge system subassembly, a case, a power cord, three labor hours in the assembly department, and one labor hour in the finishing department. Each unit of product B uses 10 units of raw material X, five units of raw material Y, two purge system subassemblies, a case, a power cord, five labor hours in the assembly department, and 90 minutes in the finishing department. Each unit of product C uses five units of raw material X, 25 units of raw material Y, two purge system subassemblies, a case, a power cord, seven labor hours in the assembly department, and three labor hours in the finishing department. Labor between the assembly and finishing departments is not transferable, but workers within each department work on any of the three products. There are three full-time (40 hours/week) workers in the assembly department and one full-time and one half-time (20 hours/week) worker in the finishing department. At the start of this week, the company has 300 units of raw material X, 400 units of raw material Y, 60 purge system subassemblies, 40 cases, and 50 power cords in inventory. No additional deliveries of raw materials are expected this week. There is a $90 profit on product A, a $120 profit on product B, and a $150 profit on product C. The operations manager doesn't have any firm orders, but would like to make at least five of each product so he can have the products on the shelf in case a customer wanders in off the street.
Formulate the above problem in MS Excel, and solve using MS Solver.
Step by step
Solved in 4 steps with 14 images
