Optimization Question

Problem 3. PAPharma is a pharmaceutical company, and they need to plan the procurement and shipment of a major chemical component for producing their primary drug. The drug is produced at four different plants, and the chemical can be supplied from six factories. For the upcoming planning period, we know the chemical's demand (in batches) for each of the plants (P1 up to P4), as well as the capacity for each of the supplying factories (S1 up to S6 ): We are also given the unit cost of shipping one batch of the chemical from each of the suppliers to each of the plants: Unit procurement and shipment cost (\$/batch) These data are also provided in problem_set1_S23PT . xlsx. PAPharma wants to know how many batches to ship from each supplier to each plant so that the total cost is minimized. We must meet all demand exactly and we cannot exceed the given capacity of each supplier. In this problem, we will allow fractional batches as feasible solutions. 9. Formulate this problem as a linear program: Define your variables, the objective, and the constraints. Since this is a large model, you do not need to write down every individual term in the objective or the constraints (can use dots, or summation symbols, instead). 10. Implement and solve your LP model in Excel, and generate the Answer Report. For your convenience, the data is provided in the spreadsheet problem_set1_S23PT .x1sx. (Remember to solve your model as an LP by selecting Simplex LP as the solver engine.) What is the optimal objective value? Management realizes that the batches received at each of the plants need to be stored and that space is very tight at plant P4 (on the other hand, there are no space concerns at plants P1 to P3). In particular, plant P4 has storage space of 2000 square feet. The amount of space each batch takes up depends on the packaging material used, and the suppliers differ in this respect. For each supplier, the storage space required (in square feet) per batch is listed in the table below-see also the Excel file problem_set1_S23PT.xlsx. 11. Update your Excel implementation to take into account the space limitation at plant P4, and solve it to optimality. What is the new optimal objective value? PAPharma For Question 11 only