assignment should be completed using excel@risk add in tool. Delivery in two Excel files. Please show as much work as possible and include explanations on why certain assumptions/decisions were made....

assignment should be completed using excel@risk add in tool. Delivery in two Excel files. Please show as much work as possible and include explanations on why certain assumptions/decisions were made. Illustration of work is key. 1. Genetic Research has a new drug approved by the FDA. It has 10 years left for the patent to expire on this product. Genetic is planning to build a plant to produce this product. The discrete probability distribution of development time is provided in Table 1. Once the plant is built, the capacity cannot be changed. Each unit sold brings in $100 in revenue. The fixed cost (in dollars) of building a plant that produce “X” units is given by Fixed Cost = 5,000,000 + 75X. The costs are assumed be equally distributed over the development year. That is if the development cost is $6,000,000 and the development time is two years, at the end of each of the first two years a charge of $3,000,000 will be charged towards plant development. The year after the development of the plant, the drug will be ready for sales. The demand for the first is year the drug is on the market is a normal distribution with a mean of 111,000 units and standard deviation of 20,000 units. For the remaining years the demand for year T is defined by the equation, D(T)=6000+0.9D(T-1)+Error. The Error term is normally distributed with a mean of 0 and standard deviation of 2000. D(T-1) is the demand in year (T-1). If the demand is in excess of production capacity, all the demand in the excess of plant capacity is lost. For a plant capacity of X, the variable cost per unit is given by 20-0.1(X/10000). That is, if the capacity (X) is 100,000 units, the unit production cost is 19. Using @Risk simulation determine a production capacity that will maximize expected discounted profits (using an interest rate of 10%) for the patent life of the product. Table 1: Development Time Number of Years Probabilities 1 0.2 2 0.5 3 0.3 2. Kelley Agency is planning to buy ads for the whole season of a Reality show. They sell these ads to their customers throughout the season, as and when a customer requests for them subject to availability. Depending on off-screen scandals of stars involved in the show, Kelley believes there are three possible scenarios. Scenario 1: A star gets married to a rock star, another gets arrested for public indecency and two other stars have affairs. With this scenario the average demand for ads will be 40 with a standard deviation of 3. Scenario 2: One of the three above mentioned incidents happen; the demand will be an average of 30 with a standard deviation of 5. Scenario 3: None of the three situations mentioned in Scenario 1 happens, then the demand will be on average 20 and standard deviation is 7. Use the normal distribution to model the demands. The probability of scenario 1 happening is 0.3, scenario 2 is 0.5 and scenario 3 is 0.2. For every ad sold Kelley makes $30,000 profit. Unsold ads are sold to local cable operations at a loss of $7000. If a customer is turned away due to shortage of ads, the goodwill loss for each customer request is estimated to be $1000. Use simulation with 1000 iterations to analyze this problem. What will be the best policy for Kelley?