Please complete this assignment using the Windows version of Excel. ATC Inc. is a manufacturing company that produces industrial valves. As the operations manager, you need to determine the production schedule (Number Produced) for manufacturing valves in the new 102 Series, which is used in oil refineries. Producing the 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 surfaces are finished and the inner valve flange is created; and during the final phase, the valves are assembled and inspected.

The plant manager has provided you with a data file(

ATC Valve Production ,,,,,,Markup ,,Production Time per Unit (in Hours),,,,#,,Original Values,, Item,Set-Up Cost,Casting,Machining,Final Assembly,Total Assembly,Unit Cost,List Price,Number Produced,% of Total # Produced,Total Profit Valve 102-45,950,1.20,1.70,0.30,?,35,?,75.00,?,? Valve 102-46,1000,3.20,1.50,0.30,?,44,?,75.00,?,? Valve 102-47,1000,3.00,1.20,0.30,?,41,?,55.00,?,? , ,Total Time Used ,?,?,?,?,,Total,?,?,? ,(based on # of each valve produced) ,,,,,,,,,, CONSTRAINTS,,Casting,Machining,Final Assembly,Total Assembly,,,Number Produced,% of Total # Produced, ,Min Total Time Used,#,#,#,#,,Min for Each Valve,#,#, ,Max Total Time Used,#,#,#,#,,Max for Each Valve,#,#,

) that contains the production times for each production phase, and the unit cost and markup for each 102 Series valve. The plant manager also informed 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 (for a total limit of 1,100 hours for Total Assembly). The workbook also includes the set-up costs associated with a production run for each valve; you must account for these costs in the profit amounts for each valve. Due to resource constraints, the number of valves produced for each valve type cannot exceed 1,000. Based on historical demand for these valves, the company has a policy that the number of any single model of valves produced in the 102 Series should comprise at least 10 percent of the production run and that the number produced of any single valve model should not comprise more that 50 percent of the total production run. Your goal is to maximize the total profit of producing these valves while not exceeding the time available in each department. The ? marks in the data file you download are formulas that you need to figure out. (You do not need to use any formulas for your constraints...enter values only.) You will use the information in the paragraph above and the numbered steps below to help you complete the spreadsheet.

Complete the following:

1. Using the provided data file (linked above), copy the data into an Excel sheet, and name the worksheet "ATC".

2. Enter formulas where necessary throughout the spreadsheet. To calculate the List Price, use 3x as the Markup. (and make the markup show up with an x at the end...), and make your formulas flexible!

3. Add formulas to the worksheet to calculate the total production time for casting, machining, and final assembly.

4. Using the data provided, create a constraints table in the worksheet that identifies the constraints in this problem. Use the cells shown in the screenshot for your constraints table.

5. You plan to experiment with the spreadsheet by plugging in different "Number[s] Produced" (and possibly new constraints) to see how the changes affect the resulting Profit. However, you always want to be able to revert back to the original values even while experimenting with various new values. Use a built-in "feature" of Excel to "save" these values so you have the ability to revert back to the original values, and which will also allow you to easily switch between the different "sets" of values. (Do NOT simply make a copy of the worksheet, or a copy of the file.) ((You will need to save/remember only one set of the values for this part of the problem--the original values--but there will be 2 other sets of values that you will create and be able to switch between as well.)) Hint: Use the Scenario Manager. (Data, What-If Analysis, Scenario Manager). Include the Numbers Produced, ALL of the constraints, and an extra cell I3, which should include the name of your current scenario (i.e. "Original Values").

6. Determine the optimal combination of valves to build in order to maximize profit.

7. Produce an answer report of your solution, and be sure to enable the checkbox next to Outline Reports. Rename the sheet containing the Answer Report to 1st_Run. With regards to the "Time Used" constraints, which "time used" constraint(s) could you modify to maximize the total profit? Enter the name(s) of the Time Used Binding Constraint(s) (as shown in the screenshot) that could be modified in order to increase profit (Your 1st Run answer report should compare the Original Values to the new, Final Values that Solver found for the First Run.)

8. Save this new set of values as the second set of "remembered" values, so that you can easily alternate between this "1st Run" set of values and the "Original Values". Use the same Excel feature you used in step 5 to save this second set of values (along with the accompanying constraints; cell I3, which should contain the value"1st Run"; and also add the cell which contains the name of the Time Used Binding Constraint as one of your changing cells). Hint: After solving, you have the option to click the Save Scenario button...however creating a scenario that way does not include the constraints or the "Scenario name heading" that I want you to include. Therefore, it is less confusing to just activate the Scenario Manager as you usually would (Data, What-If Analysis, Scenario Manager).

9. The plant manager has told you that he is unable to hire any more employees and is therefore unable to grant you any more production hours beyond 1,100 total. However, he is willing to cross-train current employees, and therefore will allow you to reallocate the per-phase hours on an as-needed basis. Modify the constraints in your constraints table to reflect this change, and then solve the problem again in order to maximize profit using a Total Assembly maximum, rather than a "per Phase" maximum.

10. Produce an answer report of your revised solution and name the sheet "2nd Run". This answer report will compare your solutions from the 1st run with the new solutions from the 2nd run. Make sure your new solution did indeed increase Total Profit.

11. Save this new set of values as the third set of "remembered" values, so that you can easily alternate between this "2nd Run" set of values, the "1st Run" values and the "Original Values". Use the same Excel feature you used in step 5 when saving this third set of values (along with the accompanying constraints, and cell I3, which should contain the value "2nd Run". If this solver results contains no Time Used Binding Constraint, then ensure that the cell containing the constraint (from Step 8), is blank in this set of remembered values.

12. Create a Scenario Summary, using the Total Profit cells as your Result Cells. (Include Total Profit for each valve and Total Overall Profit for all valves.) Make sure the Scenario Summary is clear. (The list of Changing Cells and Result Cells should have descriptive names rather than simple cell references...) Hide the "Current Values" column (since that info is likely repeated in another column).

DE WN ATC Valve Production Markup Production Time per Unit (in Hours) Final Total Casting Machining Assembly Assembly Unit Cost # # # # ## $# ## #. #.## ###### S# ###### ###### $### Original Values # of Units Produced # Unit List Price $# $# # $# *** 4 5 6 7 Profit $# Item Valve ## Valve ##-## Valve ## # % of Total # Produced ### #.##% # % Set-Up Cost $# # $### $#### $# ### 8 9 ###### ###### ###### ###### Total #.##% 10 Total Time Used (all valves) (based on # of units produced) Final Assembly Total Assembly % of Total # Produced Machining 12 CONSTRAINTS Min Total Time Used Max Total Time Used Casting ### #### Min for Each Valve Max for Each Valve # of Units Produced #### ### # ###% 16 17 Time Used Binding Constraint (s) @ 18 DE WN ATC Valve Production Markup Production Time per Unit (in Hours) Final Total Casting Machining Assembly Assembly Unit Cost # # # # ## $# ## #. #.## ###### S# ###### ###### $### Original Values # of Units Produced # Unit List Price $# $# # $# *** 4 5 6 7 Profit $# Item Valve ## Valve ##-## Valve ## # % of Total # Produced ### #.##% # % Set-Up Cost $# # $### $#### $# ### 8 9 ###### ###### ###### ###### Total #.##% 10 Total Time Used (all valves) (based on # of units produced) Final Assembly Total Assembly % of Total # Produced Machining 12 CONSTRAINTS Min Total Time Used Max Total Time Used Casting ### #### Min for Each Valve Max for Each Valve # of Units Produced #### ### # ###% 16 17 Time Used Binding Constraint (s) @ 18