70.2K

Verified Solution

Link Copied!

How to solve it by using solver tool in excel?

Problem Description: ElectroCorp manufactures two types of electrical fan products - ceiling fans and pedestal fans. The manufacturing process for each is similar in that both require a certain amount of wiring, assembly, and packaging. The number of process hours required per unit of production for both product types is provided in the Production Data worksheet, along with the profit per unit of production of each type of product. The spreadsheet also includes the maximum time available for wiring, assembly, and packaging processes in the next production period. Given the time requirements for production of each product type, the profit yield from each product type, and the time availability constraints for each process, your task is to determine the optimal mix of products that Electrocorp should produce in the next time period. Toward this, you will use the Solver tool as follows: Tasks: - You can setup the Solver solution in the same worksheet (Production Data) or a new one. Setup placeholder cells for the production quantities of ceiling fans and pedestal fans. These cells will act as the changing cells in the solver tool. - Using the placeholder cells and the profit values of each product, designate and formulate a cell that holds the total profit from the production of the two types of products. This cell will act as the target cell (objective function) in the solver tool. - Using the placeholder cells for the production quantities, designate and formulate three cells that contain the total number of hours required for wiring, assembly, and packaging processes. These cells will act as the comparison cells for the process time constraints identified in the spreadsheet. Open the solver tool and specify the target cell, changing cells, and constraints along with other parameters for obtaining a linear programming solution to this optimization problem. Packaging Hours per Unit Ceiling Fan Pedestal Fan Wiring Hours per Unit 3 4 Assembly Hours per Unit 5 2 Profit per Unit 30 20 Total Wiring Hours Available Total Assembly Hours Available Total Packaging Hours Available 250 200 175

Answer & Explanation Solved by verified expert

See Answer