Please fill out the green boxes by referencing the same cells. Thank you.

Mistwood Pharmaceuticals produces 8 different types of drugs which must be refrigerated at all times. Due to the nature of their demand, Mistwood's demand is very irregular so they must keep a large stockpile of the drugs in their warehouse. These drugs are typically stored in refrigeration units which are unique to each drug and the product must be rotated each day within each unit, so the drugs reach their maximum shelf life. Each of these refrigeration units have a weight and size (measured in ft2) which limit how much of each drug can be stored. At the moment, Mistwood's warehouse has a size of 5,420 ft2 and can have a maximum weight of 52,160 lbs. Also, the current warehousing staffing can spend 250 hours/day rotating the drugs. The following table contains information regarding the size, weight, and manpower requirements for each refrigeration unit along with the maximum storage (i.e. they have no capacity to store more than this amount of each drug) and profit for each refrigeration unit: Drug Space (ft2) Weight (ib.) Manpower (hour) Max Storage (units) Profit Alphadox 2.4 22.6 0.18 132 5.10 Biolight 5.43 55.1 0.40 149 4.87 Solostock 3.31 28.6 0.21 156 4.80 Doubletech 3.79 29.2 0.25 126 5.06 Hattex 8.09 80.5 0.42 190 7.18 Air-Dox 9.83 97.7 0.28 168 7.25 Aptech 6.78 64.5 0.28 197 7.08 Scotdox 14.57 143.4 0.39 116 6.90 Mistwood wants to determine how many of each refrigeration unit of drugs should be stored in the warehouse given all of the limitations such that they achieve the maximum possible profit. 1. Develop, implement, and solve an optimization model in Excel to address Mistwood's needs. At this point, do not require your variables to be integers. Once the model is solved, run the answer and sensitivity reports. Which constraints are binding? 2. Since fractional refrigeration units cannot be stored, please make the adjustments necessary and solve an integer optimization model. Once the model is solved, state the number of drugs stored of each refrigeration unit and the expected profit. A B D E F 1 Drug Space Weight Manpower Profit 2 3 4 Alphadox 2.4 Biolight 5.43 Solostock 3.31 Doubletech 3.79 Hattex 8.09 Air-Dox 9.83 Aptech 6.78 Scotdox 14.57 5 6 22.6 55.1 28.6 29.2 80.5 97.7 64.5 143.4 Max Storage 132 149 156 126 190 0.18 0.4 0.21 0.25 0.42 0.28 0.28 0.39 5.1 4.87 4.8 5.06 7.18 7.25 7.08 6.9 7 8 9 10 168 197 116 B 13 14 15 Decision Variables: 16 17 18 19 20 21 22 23 24 25 26 Objective Funtion 27 28 29 Constraints 30 L.H. Side Type R.H. Side 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Mistwood Pharmaceuticals produces 8 different types of drugs which must be refrigerated at all times. Due to the nature of their demand, Mistwood's demand is very irregular so they must keep a large stockpile of the drugs in their warehouse. These drugs are typically stored in refrigeration units which are unique to each drug and the product must be rotated each day within each unit, so the drugs reach their maximum shelf life. Each of these refrigeration units have a weight and size (measured in ft2) which limit how much of each drug can be stored. At the moment, Mistwood's warehouse has a size of 5,420 ft2 and can have a maximum weight of 52,160 lbs. Also, the current warehousing staffing can spend 250 hours/day rotating the drugs. The following table contains information regarding the size, weight, and manpower requirements for each refrigeration unit along with the maximum storage (i.e. they have no capacity to store more than this amount of each drug) and profit for each refrigeration unit: Drug Space (ft2) Weight (ib.) Manpower (hour) Max Storage (units) Profit Alphadox 2.4 22.6 0.18 132 5.10 Biolight 5.43 55.1 0.40 149 4.87 Solostock 3.31 28.6 0.21 156 4.80 Doubletech 3.79 29.2 0.25 126 5.06 Hattex 8.09 80.5 0.42 190 7.18 Air-Dox 9.83 97.7 0.28 168 7.25 Aptech 6.78 64.5 0.28 197 7.08 Scotdox 14.57 143.4 0.39 116 6.90 Mistwood wants to determine how many of each refrigeration unit of drugs should be stored in the warehouse given all of the limitations such that they achieve the maximum possible profit. 1. Develop, implement, and solve an optimization model in Excel to address Mistwood's needs. At this point, do not require your variables to be integers. Once the model is solved, run the answer and sensitivity reports. Which constraints are binding? 2. Since fractional refrigeration units cannot be stored, please make the adjustments necessary and solve an integer optimization model. Once the model is solved, state the number of drugs stored of each refrigeration unit and the expected profit. A B D E F 1 Drug Space Weight Manpower Profit 2 3 4 Alphadox 2.4 Biolight 5.43 Solostock 3.31 Doubletech 3.79 Hattex 8.09 Air-Dox 9.83 Aptech 6.78 Scotdox 14.57 5 6 22.6 55.1 28.6 29.2 80.5 97.7 64.5 143.4 Max Storage 132 149 156 126 190 0.18 0.4 0.21 0.25 0.42 0.28 0.28 0.39 5.1 4.87 4.8 5.06 7.18 7.25 7.08 6.9 7 8 9 10 168 197 116 B 13 14 15 Decision Variables: 16 17 18 19 20 21 22 23 24 25 26 Objective Funtion 27 28 29 Constraints 30 L.H. Side Type R.H. Side 31 32 33 34 35 36 37 38 39 40 41 42 43 44