Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? Yes H41 X V fx A B C D E F G H K M N 0 P Q R Purchase Manufacture In-House Step 1. Primary Variables: Fixed Cost Range Name Cell Unit Cost Manufacture FixedCost D2 Objective Functions Total Cost Manufacture TotalCost D4 Manufactured Total Cost = ManufactureFixed Cost+ManufactureUnitCost*Q Manufacture UnitCost D3 Purchased Total Cost = =PurchaseFixedCost+PurchaseUnitCost*Q Hamesses to make (Q) Purchase FixedCost B2 Profit/Loss = Purchase TotalCost-Manufacture TotalCost Profit/Loss if In-House Purchase TotalCost B4 Purchase UnitCost B3 Q C6 Graph Points Production Qty Manufacture Cost Purchase CostProblem 5. Rockwall Industries is a technology subcontractor for the United States Navy. They are providing a new computer system to be installed throughout the Navy installations. However, the computer system requires a unique harness which attaches to legacy systems. The problem is that manufacturing harnesses is not a core competency of Rockwall Industries. Accountants form Rockwall Industries have calculated that if they were manufacture the harnesses in-house, each harness would cost approximately $35 (from data from previous builds) to manufacture with fixed costs being $225,000 to establish a set-up to produce. Rockwall has also subcontracted with Steadfast Harnesses in the past. Steadfast Harnesses has quoted each unit cost to be $50 if they were to be contracted for the job. In addition, Rockwall has determined that there would be a fixed one-time administration charge from the vendor of $50,000 to do business. A) Place the data in the appropriate data cells. B) Place the appropriate formulas given in column | to the appropriate place in columns B, C and D. C} Using Excel, how many harnesses must Rockwall make themselves to be feasible versus purchasing them from an external vendor? (Cell C6) Make sure this is a whole number!!!! The forecast is that Rockwell may need between 11,600 and 11,800 for them to be feasible to do in-house. What is the profit at this point if they do it in house? Make sure this is a whole number!!!! 0) Create a Scatter Line Chart on this worksheet. Using the Production Qty, Manufacturing Cost vs. the Purchasing Cost... create a scatter chart with lines to show the breakeven crossover point. Label your chart "Make or Buy\". Use the Production Quantity Range of 8,000 + 17,000 in 1,000 increments