Problem Details

In return, the university would receive $20\%$ of the on$-$campus revenues and an additional lump sum of $$1,000,000$ per year. The contract discussions have reached the point that Cola Co must decide within $2$ weeks whether it wants to accept the terms of the agreement. Cola Co needs to know if its profits from sales of beverages at the university are expected to be higher with the exclusivity agreement than without the exclusivity agreement.

$$ The management at Sugarland has identified what they know about sales at the University.

$$ The market for beverages is measured in terms of the equivalent of $20-$ounce bottles.

$$ For all intents and purposes, the entire market for beverages on the university campus is held by Cola Co and one other major beverage company, Popsi$-$pop.

$$ Cola Co is currently selling an average of $22,000$ bottles, or the equivalents, per week without the exclusivity agreement.

$$ Students and staff total $35,000.$

$$ The university operates for $45$ weeks of the year.

$$ The bottles sell for $$2\mathrm{.}00$ each.

$$ The costs, including labor, amount to $$0\mathrm{.}75$ per bottle.

$$ Cola Co does not know its current market share $(\%$ beverage sales$)$ at the university.

$$ The annual net profit without the agreement must be determined. Annual net profit is defined to be revenue per year less costs per year. Annual Revenue $=$ # bottles sold per year $\backslash$times selling price per bottle. Annual Costs $=$ # bottles sold per year $\backslash$times cost per bottle.

$$ The annual net profit with the agreement must be determined. Again, the annual net profit is the revenue per year less costs per year. Annual Revenue $=$ # bottles sold per year $\backslash$times selling price per bottle. Annual costs include # bottles per year $\backslash$times cost per bottle, $20\%$ of revenues, and the $$1,000,000$ lump sum.

$$ The management at Cola Co has identified what they do not know about sales at the University:

$$ They do not know their market share.

$$ Since they do not know their market share, they do not know the total number of beverages sold weekly $($and by extension, annually$)$ at the university; that is$,$ they do not know Popsi$-$pop$$s sales.

Requirements $($Rubric for these requirements listed below can be found on page $7)$

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.

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.

Problem $2$ Rubric: The write$-$up of your solution should have $3$ distinct parts as detailed below.

$($Remember$,5$ of the $60$ assignment points are allocated to general format$/$construction of your document.$)$

Total $26$ points

PLAN

Briefly summarize the objective of the analysis, important variable$($s$),$ data collection and characteristics. This should be written as though the reader has no prior knowledge of the problem specifics, but it cannot simply be a $$Copy and Paste$$ of the case problem.

Identify analysis type $($method$)$ for the statistical analysis you need.

State and check required data conditions for any and all statistical methods used. Include all graphs and numerical summary used in this process.