Problem: Super Pill Development Dilemma (20 points)

Matrix Inc. is a conglomerate with huge investments in technology, space travel, and virtual reality industries. Morpheus - the President of the company has ambitious goals for expansion. He is looking for opportunities in fields related to the company's existing businesses. He appointed Neo as the director of innovative product develop- ment with seed money of $30 million. After months and months of hard work, Neo's team has developed a Super Pill that they believe may enhance human brain capabil- ity. Controlled usage of the Pill will enable humans to learn new things 5x faster and process information as fast as a Pentium-I processor. He presented the findings at a board meeting and claimed that Matrix Inc. can make huge profits if it decided to market the pill. Furthermore, he told the team that with a little bit of more investment they could develop different versions of the pill.

Trinity questions the team if there are potential side effects of the drug. To every- one's surprise, no proven clinical tests have been done so far but Neo insists there are absolutely no side effects of the drug since it is made from all organic natural materi- als. Morpheus asks the team to provide cost analysis of four versions of the pill before any further decision can be made. Neo presents the cost data which is summarized in Table 1.

Table 1: Super Pill Cost Data

Pill Development Cost Variable Cost per shipping cost

Blue $1,850,000 $300

Orange $2,900,000 $250

Green $3,500,000 $170

Yellow $4,500,000 $125

1. Build an Excel model for this decision problem. Your model should be able to calculate total cost for each version of the pill. Construct a single data table to calculate costs for each option for shipping case quantity Q = 0, 1000, 2000,. . . ., 30,000 (10 points)
2. Which Pill would you recommend to develop if the sales quantity was estimated to be 10000 shipping cases? (5 points)
3. How will your recommendation change if the quantity was 21,000 shipping cases or 23500 cases? (5 points)