A company produces a variety of electrical components, including a remote controller for televisions and a remote controller for DVD recorders. Each controller consists of three subassemblies that can be manufactured by the company: a base, a cartridge, and a keypad. Both controllers use the same base subassembly, but different cartridge and keypad subassemblies.

The companies sales forecast indicates that 7000 TV controllers and 5000 DVD controllers will be needed to satisfy the demand during the upcoming Christmas season. Because only 600 hours of in-house manufacturing time are available, the company is considering purchasing some, or all, of the subassemblies from outside suppliers. If Roadrunner manufactures a subassembly in-house, it incurs a fixed setup cost as well as a variable manufacturing cost. The following table shows the setup cost, the manufacturing time per subassembly, the manufacturing cost per subassembly, and the cost to purchase each of the subassemblies from an outside supplier.

a. Define the decision variables.

b. Formulate the objective function using the variables defined.

c. Formulate all the constraints using the variables defined.

Can you please solve the problem by hand and using Excel Solver? When you use the Excel Solver, can you please type why you are using those inputs and where you got them from in the Excel Spread Sheet? I need to be able to solve this problem with different numbers and possibly slight changes to the scenario. Essentially, I need to be able to replicate your Excel Spread Sheet and Solver, and use it to solve similar problems. I don't just want the answer. I would like an explanation if possible. Thank you!

Subassembly Setup Cost Manufacturing Time Manufacturing ($) per unit (Minutes) Cost ($) per unit Purchase Cost ($) per unit Base 1000 0.40 0.65 TV cartridge 1200 2.90 3.45 DVD cartridge 1900 3.15 3.70 TV Keypad 1500 0.30 0.50 DVD Keypad 1500 0.55 0.70