A pharmaceutical company needs to fully satisfy the vaccine demand, which is uniformly distributed between 1,000,000 and 2,000,000 ounces. The unit revenue is $10 per ounce.

The company's production is subject to the random yield. That is, only a random percentage of production quantity is qualified for demand satisfaction. For instance, if the production yield rate is 50% and the company plans to produce 2,000,000 ounces, the qualified output is 1,000,000 (= 2,000,000 50%) ounces. The firm has the historical data on the production yield rate, which can be used to estimate the distribution of the random yield rate.

For each ounce that the firm plans to produce, $5 is incurred as the production cost regardless if it can be used or not at the end.

If the qualified final output from the production is less than the demand, the firm has to outsource from a more expensive source to cover the gap. The corresponding cost is $9.5 per unit.

If the qualified output is more than the demand, the firm can salvage the leftover at the unit price of $4.

Address the following questions:

1. The production manager claims that the production yield rate follows Normal distribution. Use the data to create the Histogram and check if the production manager's statement is supported by the historical data.
2. Estimate the mean and the standard deviation of production yield rate, and use them as the parameters of its Normal distribution.
3. Set up the model to simulate the vaccine demand and the company's production yield rate, and evaluate the profit when the company plans to produce 2,000,000 ounces of vaccine.
4. Use the model set up in Question 3, test the different production quantity, starting from 1,000,000 ounces to 4,000,000, with increment of 200,000 ounces. Build two-way data table to collect the data from 1000 simulation, and determine the production quantity based on the best average profit criterion. Use conditional formatting to highlight the highest average profit.