Your consulting group was hired by Sugarland to obtain the missing information, perform the necessary analysis, and provide a recommendation about the agreement. Your group has already organized a survey that asked students to keep track of the number of bottled beverages they purchased on campus over the next 7 days. Students were encouraged to complete the survey with a promise of having their name entered into a raffle to win a gift certificate. The 468 responses (number of beverages per week) from the survey can be found in the Worksheet titled "Case 1 Part 2 Beverage" in the Excel file titled Group Case #1 Data. Your group's task is to assist the market analyst in completing the analysis: 1. Assume that Cola Co and Popsi-pop drinkers would be willing to buy either company's product in the absence of their first choice. 2. Perform a statistical analysis, using 99% confidence, to extract the needed information from the collected sample data. Round the result to 3 decimal places. 3. On the basis of maximizing annual net profit from sales of beverages, is Cola Co better off with the exclusivity agreement (based on 99% certainty)? Provide detailed support for your conclusion. a. Format an Excel worksheet in which you will the necessary calculations to determine the annual net profit. The Excel sheet should be easily editable for future changes. For example, the lump sum payment should be saved in a cell that can be "called\" by your formulas. In that way, should it change in the future, you will only need to change the one cell value, not your formulas. n b. Copy the portion of the Excel sheet that contains all of your information/work, and paste it into the "Do section of your Problem 1 write-up. c. You will also upload the Excel file separately. 4, Use the results of the statistical analysis to also estimate Cola Co's current market share