I need help figuring out how to solve Sheet 1 and Sheet 2 using Excel Spreadsheets with the provided information.
Like many businesses, the Electrotate Company must decide whether to make their products inhouse or buy them from other suppliers. Electrotate manufactures three types of slip rings (A, B, and C), which are electrical coupling devices that allow current to pass through a rotating connection. Electrotate recently received a large order for various quantities of slip rings, each ofwhich requires a certain amount of time to wire and to harness, as shown in the following table. Type A Type B Type C Number of slip rings ordered 3,000 2,000 1,000 Wiring required per slip ring (hours) 2 1.5 3 Harnessing required per slip ring (hours) 1 2 1 Electrotate only has 9,000 hours of wiring capacity and 5,000 hours of harnessing capacity to devote to this order not enough capacity to completely make the order in-house by its due date. However, Electrotate can buy any portion of this order from one of its competitors. The unit costs of producing each type in-house and buying the finished version from a competitor are given below. Type A Type B Type C Cost to make a unit $50 $85 $130 Cost to buy a unit $60 $100 $150 On Sheet 1: Formulate Electrotate's problem as a linear program that determines the best number of slip rings to make inhouse and to buy to fulfill the recent order at lowest possible cost. The model should meet or exceed the number of slip rings ordered of each type while using no more than the amount of wiring and harnessing capacity available. It should contain 6 decision variable cells ' Do M constrain the decision variables to be integers. A) What is Electrotate's optimal solution and minimum total cost? On Sheet 2: Generate Solver's Sensitivity Report, and use it to answer the following (separate, independent) questions also on Sheet 2. B) By how many$ will the optimal total cost change if Electrotate's order requires 1,200 Type C slip rings instead of 1,000? How can you predict this based on information in the Sensitivity Report? C) Will Electrotate's optimal plan (f.e., the set bf decisions, not its total cost) change if the cost of making one unit of Type B increases from $85 to $90? Yes No How can you predict this based on the information in the Sensitivity Report? D) By how many 5 will the optimal total cost change if Electrotate has 400 fewer hours of harnessing capacity available? How can you predict this based on information in the Sensitivity Report? E) Will Electrotate's optimal plan (17.9., the set of decisions, not its total cost) change if the cost of buying one unit of Type C drops by $10 to $140? Yes No How can you predict this based on the information in the Sensitivity Report? F) How does the optimal solution and its total cost change as the wiring required per Type A slip ring changes from 2 to 3 hours in increments of 0.25 hours? What's the relationship between total cost and wiring hours required per Type A slip ring (positiveegative, linearonlinear)
