Steams coffee is a leading vendor or coffee and tea beverages across the Southwest region of the United States. As part of their business expansion initiatives, they opened new stores in the State of California in the year 2016. As the numbers for 2017 come in, the management wants to see how the company did in the new territory. Their decisions would solely be based on the sales numbers that they have. Instructions: In the Excel spreadheet provided, complete the tasks below. Upon completion you will submit a single, completed, Excel workbook (in .xlsx format). 20 points each

1. Setup the spreadsheet with proper formatting and formulas so it can be presented to management.

a. Create a new worksheet titled Q1 - 2017 Quarterly Sales and copy the data from the Main worksheet onto the sheet.

b. Add a column to calculate the total for each product. Use the SUM function.

c. Add rows to calculate the Total, Average and Median sales for each quarter. Use the SUM, AVG, and MEDIAN functions.

d. Format the title at the top so it stands out. Add distinct formatting to the column and row headings. Format the data in the cells to show currency (in whole US dollars no cents).

2. Management would like to make a decision on whether or not they should discontinue any products. Any product which has sales less than $ 500 in a quarter in 2 quarters over the year should be discontinued. Are there any products that they should discontinued? List the product(s) and your recommendation in the worksheet.

a. Copy the data from the worksheet you created in step 1 and preserve the formatting. Name the worksheet Q2 - Discontinued Products [Hint: Copy the worksheet.]

b. Change the title at the top to be descriptive of the problem.

c. Use conditional formatting to highlight the cells that are below $500.

3. Management would like to know for each of the products, what was the highest and lowest sales for that product across all 4 quarters. Which was the most profitable product?

a. Create a new worksheet titled Q3 MaxMin Sales by Product.

b. Add a meaningful title.

c. Copy the Product names.

d. Using the data in worksheet titled Q1 - 2017 Quarterly Sales determine the Min and Max total sales for each. Add 1 column titled Highest Sales FY 17. Use a function to determine the highest sales each product made across the 4 quarters. [Hint: Use MAX and refer to the Q1 sheet to calculate your numbers.] Do the same thing to calculate the minimum add a column titled Lowest Sales FY 17.

e. Add the ability to filter and sort each column. Then, sort the products based on highest sales, descending order. [Hint: Use the sort and filter feature.]

f. Add a column titled Highest Gainers to determine the range between the highest and the lowest sales for each product. Highlight the top three cells. [Hint: Use the conditional formatting feature to highlight the top 3 cells post calculation .]

4. Management would like to know for each of the products, what was the contribution of each product in terms of sales per quarter? Which product had the most sales in Q4?

a. Create a new worksheet titled Q4 % Sales for each Product.

b. Add a meaningful title.

c. Copy the Product names.

d. Using the data in worksheet titled Q1 - 2017 Quarterly Sales, represent all the numbers as percentages (percent of total revenue for that quarter). Round to the nearest whole number if needed. e. Create a pie chart to illustrate the contribution of each product for Q4. The pie chart should have a title, data labels, and a legend. Format the chart to display the data clearly. f. Write the name of the product that had the most sales in Q4.

5. Prepare your analysis for Management. Create a Table of Contents.

a. Create a new worksheet and move it to the beginning. This worksheet should list the contents of the workbook along with the purpose of the workbook, the date the workbook was last modified, and the name of the person who created the workbook. Be sure to have titles for each of these. [Hint: For the author name and last modified date, you will need to create a VBA function.]

b. Make each list item a hyperlink to that particular workbook. For example, in the table of contents you will have an item titled Q1 - 2017 Quarterly Sales. Create a hyperlink to link to that particular workbook.

Need Help on 3-5

1 Coffee Company Quarterly Product Sales 2017 (K SUSD) 2 Product Name 3 Chai tea 4 Dark roast special blend 5 Light roast decaf 6 Lafayette medium roast 7 Nutmeg vanilla blend 8 Rooibos blueberry tea 9 White herbal tea 10 Holiday blend coffee 11 Costa Rican dark roast 12 Hawaiian blue coffee 13 Jamaican Blue Mountain 14 Civet 15 Dallas blend medium roast 16 Special Early Grey tea 17 Q1 Q2 Q3 Q4 705.6 2720.8 590.4 5127.36 529.2 1398.4 1141.92 5283.52 1508.4 214.52 58.1 102.6 179.2 1742.4 878.4 228 360 6806.1 467.55 4496.5 1774.08 2518 384 1508.92 65.3 85 1037.4 1008 1174.5 2061.5 1100.7 3312.12 219.37 1196 3261.6 3350 1252.5 1233.8 78.9 97 1125.5 1683 2128.5 2028.25 2424.6 1317.5 337.5 3979 1705.5 5242 2683.5 1233.8 92.4 106.3 750.4 1273.5