A pharmaceutical company can produce a drug in batches of 1,000 pounds, at an average cost of $40,000 per batch. The drug synthesis process is not perfect and yields less finished product per batch than the 1,000-pound batch size. The drug is new, and this yield is uncertain. The company es!mates a best-case yield of 90%, a worst-case yield of 70%, and a most-likely yield of 85%. The batch cost is uncertain, with a standard devia!on of $5,000, and higher yields tend to result in higher batch costs. The company es!mates a 0.75 correla!on between yield and batch cost.

The annual demand for the drug is also uncertain. Based on market research, the company es!mates a best-case demand of 22,000 pounds, a worst-case demand of 12,000 pounds, and a most-likely demand of 18,000 pounds.

The drug sells for $60 per pound and any le"over amounts are sold for $8 per pound to another company that can reprocess it into another compound.

The company must decide how much to produce before knowing what the demand for the drug will be. They are considering producing either 12, 16, 20, or 24 batches.

For this problem, note the following:

Use at least 3,000 replications in your simulations. The starter file provides the general structure as before, but now you will need to enter all of the informa!on from the problem statement yourself. Enter the input values or distribu!ons in the grey and blue cells (the correlation matrix is provided) Enter the formulas for your model in the green cells

Use this model shell to get started: Pharmaceutical Produc!on Shell.xlsx

Insert Draw Page Layout Formulas Data Review View Acrobat DADM_UT 8 Tell m A B C D E G H Pharmaceutical Production Decision Variable \begin{tabular}{|l|c|c|c|} \hline 4 & Name & Value & Units \\ \hline 5 & Batch Prod & & batches \\ \hline \end{tabular} Number of Batches Considered 12162024 8 Inputs \begin{tabular}{|c|c} \hline 9 & Name \\ \hline 10 & Size of One Batch \\ \hline 11 \end{tabular} Value Inputs for Question 5 11 12 Yield from Production 13 Cost of One Batch 14 Total Demand 15 16 Regular Price \$/pound S/pound 17 Leftover Price 18 19 Model 20 Total Production Production Yield 23 Regular Price Volume 24 Leftover Price Volume \begin{tabular}{|c|c|} \hline Value & \begin{tabular}{c} Units \\ pounds \\ pounds \\ pounds \\ \hline pounds \\ \hline \end{tabular} \\ \hline & $ \\ \hline & $ \\ \hline \end{tabular} To maximize annual expected (mean) profit, how many batches of the drug should the company produce? 12 16 20 24 Question 2 2 pts What is the expected (mean) profit of the optimal batch size from question 1 ? Answer in dollars, not thousands or millions of dollars, for example: 500000 (dollars) instead of 500 (thousands) or 0.5 (millions). Question 3 What number of batches best mitigates downside risk, in terms of having the highest 5 th percentile for profit? For the number of batches that minimized downside risk in the previous question, which uncertainty is most impactful to the profit? Batch Yield Batch Cost Demand Question 5 There is a technology that can guarantee the best-case yield of 90% with certainty but costs an extra $5,000 per batch. Is it worth it for the company to use this technology? Note that you should use the separate input area for Question 5 (to the right of the main input area) in the spreadsheet for the new inputs. You will also need to update some of the formulas in your model to use this new information. Hint: you do not need to use correlation for this part