You are the marketing manager for a soft drink company. You need to determine the amount of drink to produce next year and maximize total revenue. The company predicts the following economic situations and their respective probabilities: recession 25%, recovering 30%, stable 15%, and booming 30%. The production cost per drink is estimated to be $0.45. The demand for the drink in each type of economy is shown in the following table: Economy | Probability | Demand Recession | 25% | 20,000 Recovering| 30% | 35,000 Stable | 15% | 85,000 Booming | 30% | 100,000 Price per unit is determined by the actual demand and is shown in the following table: Demand | Price/Unit 20,000 | $0.70 35,000 | $0.80 85,000 | $0.90 100,000 | $1.00 a. Create an Excel template that calculates the total revenue for various production levels. b. Use a combination of RAND() and LOOKUP() functions to allow the template to randomly suggest production levels and price levels considering the distribution of the state of the economy. c. Generate 1,000 data points and calculate the revenue for each of these data points. d. Use summary statistics to decide the best production level that provides the highest-possible