ATC Inc. is a manufacturing company that produces industrial valves.

ATC Inc. is a manufacturing company that produces industrial valves. As the operations manager, you need to determine the production schedule for manufacturing three valves in the new 102 series, which is used in oil refineries. Producing 102 series valves is a three-part process. During the casting phase, the body of the valve and some of the attaching pieces are created; during the machining phase, the casting surface are finished and inner valve flange is created; and during the final phase, the valve are assembled and inspected. The plant manager provided you with a workbook that contains the production times of each production phase, and the unit cost and list price and for each 102 series valve. The plant manager also told you that your time is limited to 500 hours in the casting department, 500 hours in the machining department, and 100 hours in the final assembly department. The workbook also includes the set-up costs associated the production run for each valve; you must account for these costs in the profit amounts for each valve. Based on historical demand for these valves, the company has a policy that any single valve in the 102 series should comprise at least 10 percent of the production run and not more than 50 percent. Your goal is to maximize the total profit of producing the valves while not exceeding the time available in each department.

Complete the following:

1. Open ATCValve.xls workbook from the BB and save on your computer
2. Add test values to the changing cells (values of 1,2,3 to the cells H5-h7) to ensure that your worksheet is set up correctly and produce the expected results. These values will be changed anyway when you activate the solver.
3. Add formulas to the worksheet to calculate the total production time for casting, Machining, and final assembly (sum of production time * Number of product)
4. Write the formulae for Percentage and profit (Cells I & J)
5. Enter a mathematical formula to sum the target cell and then enter formulas to link the target cell with the changing cells.
6. Create constraints table in the worksheet that identifies the constraints in this problem. Derive the constraints from the case.
7. Use solver to determine the optimal combination of valves to build to maximize profit.
8. Produce an answer report for your solution. Which constraints could you modify to maximize the total profit?
9. Save your solution as scenario_1.
10. Modify the constraints in your constraints table and in solver that you determined would further improve the solution, and then run the solver again. Save this as scanrio_2 .