Bama Paintball Supply (BPS) manufactures paintballs at its facility in Sweetwater, Alabama and sells them for $8.00 per case. There is an uncertainty in demand for paintballs due to the COVID-19 conditions. The demand can be uniformly distributed between 9,000 and 17,000 cases. BPS manufacturing and storage conditions are also fluid. With 75% probability, it will have the capacity to produce and store up to 12,000 cases of paintballs; with 25% probability, this capacity will be reduced to 8,000 cases. Under normal conditions, BPS has a fixed cost of $10,000 and spends $5.00 in variable cost for each case it produces. However, there may be an increase both in fixed and variable cost. The rate of increase is expected to be distributed normally with a mean of 10% and standard deviation of 2%. BPS also has an outsource supplier that can provide up to 5,000 cases at a cost of $6.50 per case. BPS must give a fixed order quantity to its supplier at the beginning of the season and then any additional orders are filled from its own internal production.

1. (20 pts) Build a simulation analysis in a spreadsheet model for this problem using the base case from your midterm. You might need to modify your base case analysis appropriately. Please assume that BPS orders 2,000 cases from its supplier and estimate the total profit for this order.

2. (10 pts) Replicate the simulation analysis 200 times to obtain the distribution of the profit. Construct a 95% confidence interval for the average level of total profit the manager could expect for the next year. What is the probability that the profit will exceed $20,000?

3. (10 pts) If you were to vary your order quantity from 2000 cases to {3000;4000;5000} cases; which option would provide the best average profit?