PLEASE SHOW IN EXCEL THANK YOU!

The SD BIKE Plant can potentially produce five types of bicycles. Each type requires manufacturing operations in three departments: Preparation, Assembly, and Finishing. The labor-hour requirements, by department, are as follows: Department Type 1 Type 2 Type 3 Type 4 Type 5 Preparation 1.3 2.2 2.5 1.8 1.3 Assembly 1.8 1.2 2.2 1.6 1.9 Finishing 0.2 0.1 0.3 0.2 0.3 For the next production period, the labor-hours available are 2000 in Preparation department, 1900 in Assembly department, and 250 in Finishing department. The profit contributions per unit are $23 for type 1, $25 for type 2, $30 for type 3, $20 for type 4, $20 for type 5, and $15 for type 6. The setup costs are $800 for type 1, $650 for type 2, $900 for type 3, $1000 for type 4, $1000 for type 5, and $750 for type 6. There are five operational and market considerations: 1) From types 2 and 3, at least one type should be manufactured, 2) Types 3 and 1 are mutually exclusive (cannot be manufactured together), 3) There is a demand of 300 for type 5 that should be satisfied, 4) Maximum demand for each type is 600 units, 5) The minimum production batch size is 200 units for all types of bicycles (i.e., either no production or more than 200 units if produced) a. Formulate a Binary Linear Programming (BILP) to find the optimal production plan in order to maximize the total profit. b. Provide a spreadsheet model and use Excel solver to solve it