Hi, I’m having difficulty creating a spreadsheet with the below information in Excel Solver.
ABC Manufacturing makes two products: rockers and chairs. Each product produced goes through the following 3 manufacturing processes:
Each rocker produced requires 1 hour in cutting, 30 minutes in sanding, and 30 minutes in finishing. Each chair requires 30 minutes in cutting, 45 minutes in sanding, and 1 hour in finishing. In the coming week, ABC manufacturing has 40 hours of cutting capacity available, 40 hours of sanding capacity, and 60 hours of finishing capacity. Assume all rockers produced can be sold for a profit $500 each, while each chair can be sold for a profit of $400 each. ABC manufacturing would like to determine the optimal production mix of rockers and chairs in order to maximize its weekly profit.
a. Formulate this problem as a linear programming problem. That is, make sure to specify what the decision variables are, what the objective function is, and what the relevant constraints are.
b. Set up the problem in a spreadsheet and solve it using Solver.