SOLVE IN EXCEL WITH SOLVER

SHOW THE ALGEBRIAC FUNCTION USED

A sudden increase in the demand for smoke detectors has left Newman Alarms with insufficient capacity to meet demand. The company has seen monthly demand from its retailers for its electronic and battery-operated detectors rise to 20,000 and 10,000, respectively, and Newman wishes to continue meeting demand. Newman's production process involves three departments: fabrication, assembly, and shipping. The relevant quantitative data on production and prices are summarized as follows:

Department	Monthly Hours Available	Hours/Unit (electronic)	Hours/Unit (battery)
Fabrication	2000	0.15	0.1
Assembly	4200	0.2	0.2
Shipping	2500	0.1	0.15
Variable cost/unit ($)		18.8	16
Retail price ($)		29.5	28

The company also has the option to obtain additional units from a subcontractor, who has offered to supply up to 20,000 units per month in any combination of electronic and battery-operated models, at a charge of $21.50 per unit. For this price, the subcontractor will test and ship its models directly to the retailers without using Newman's production process. In the worksheet provided, develop and solve an integer programming model to determine how many electronic and battery-operated units Newman should produce and how many of each they should buy from the subcontractor in order to maximize the total profit. Also, answer the following questions in the highlighted cells: a) What is the maximum profit? b) Compare the maximum profit in question A to the maximum profit achievable without integer constraints. By how much (in percentage terms) do the integer restrictions alter the value of the optimal objective function?