Excel requirements
1. Prepare a correlation table for all variables and use the conditional formatting tool to highlight correlations that are greater than or equal to 0.4 and that are less than or equal to -0.4. Use different colors for the positive and negative correlations. 1s should not be highlighted.

2. Run the summary descriptive statistics for all the products (do this at one time, not item by item).

3. Graphs – all graphs should have a title, a legend, and axis labels. Everything should look professional.

A. Prepare the following graphs:
• Scatter plot of unit drink sales on temperature
• Line graph of unit drink sales on date

B. Create a pivot table and determine sales of each product by day of week. Prepare two column graphs based on this data: one for food sales by day of week and one for drink sales by day of week. Please note that if you build your graphs inside the pivot table area, you’ll have to have separate pivot tables for food and drink in order to create separate graphs.

C. Create a pivot table and determine revenue, cost of goods sold, waste cost and gross margin by day of week. Prepare on line graph that shows all four items on day of week.

D. Create at least one additional graph (type is your choice) with at least three variables that you believe will be useful for understanding product flow in the café.

4. Regressions (Hint: All Y variables must be in contingent columns.) Using multiple regression, determine the three or four net unit product sales that have the greatest influence on gross margin. Net unit product sales means sales minus waste. Gross margin means revenue minus COGS minus waste. Select the 99th percentile confidence level. Keep track of the number of models run.

5. Group differences

• Create new variables containing gross margins grouped by MWF and TTH.

o You may not just cut and paste values. You must select and use an intermediate/advanced technique or formula to create the separate groups. Explain how you created the two groups, and why you selected the method chosen.

Run an f-test (two sample).
• Select and run the appropriate t-test to determine if gross margin on MWF is significantly different than total sales on TTH.

Excel requirements 1. Prepare a correlation table for all variable...