Please use Excel Solver

Problem 2: Make-Buy Problem with Set-up Cost Electro-Poly Corporation is the world's leading manufacturer of slip rings. A slip ring is an electrical coupling device that allows current to pass through a spinning or rotating connection. The company received a $750,000 order for various quantities of three types of slip rings. The following table summarizes the requirements for three models of slip rings. Model 1 3,000 Model 2 2,000 1.5 Model 3 900 3 Number ordered Hours of wiring required per unit | Hours of harnessing required per unit Unfortunately, Electro-Poly does not have enough wiring and harnessing capacity to fill the order by its due date. The company only has 10,000 hours of wiring capacity and 5,000 hours of harnessing capacity available to devote to the order. However, the company can subcontract any portion of this order to a subcontractor so long as it complies with their sourcing policy (appears later in the problem below). The unit costs of buying each model in-house and buying the finished product from a subcontractor are summarized below. Cost to Make Purchase Cost to Buy Model 1 $50 $61 Model 2 $83 $97 Model 3 $130 $145 Both Electro-Poly and its supplier incur a set-up cost to produce these models as shown below. Assume that Electro-Poly and supplier will produce their allocation for each model (if allocated) in a single set-up. The supplier passes the entire set-up cost to the buyer (Electro-Poly). In order to limit its dependence on suppliers, Electro-Poly wants to outsource at most two models. Set-up cost to Make | Set-up cost to Buy Model 1 $5000 $4000 Model 2 $8000 $6000 Model 3 $6000 $3200 Electro-Poly wants to determine the number of slip rings to make and the number to buy in order to fill the customer order at the least possible cost