Coffee Company Quarterly Product Sales 2014
Product Name	Q1	Q2	Q3	Q4	Total
Chai tea	705.6	878.4	1174.5	2128.5
Dark roast special blend	2720.8	228	2061.5	2028.25
Light roast decaf	590.4	360	1100.7	2424.6
Lafayette medium roast	5127.36	6806.1	3312.12	1317.5
Nutmeg vanilla blend	529.2	467.55	219.37	337.5
Rooibos blueberry tea	1398.4	4496.5	1196	3979
White herbal tea	1141.92	1774.08	3261.6	1705.5
Holiday blend coffee	5283.52	2518	3350	5242
Costa Rican dark roast	1508.4	384	1252.5	2683.5
Hawaiian blue coffee	214.52	1508.92	1233.8	1233.8
City blend medium roast	179.2	1037.4	1125.5	750.4
Special Early Grey tea	1742.4	1008	1683	1273.5

Assignment: Excel Fundamentals (150 points)

Read the brief case below. Using the Excel spreadsheet provided, complete the questions.

Steams Coffee

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 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 excle spreadheet provided, complete the tasks below. Upon completion you will submit a single, completed, Excel workbook.

30 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 - 2016 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.

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 - 2016 Quarterly Sales determine the Min and Max total sales for each. Add 1 column titled Highest Sales FY 14. 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 14.

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. 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 - 2016 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 - 2016 Quarterly Sales. Create a hyperlink to link to that particular workbook.