Fluent Essays

Read the brief case below. Using the Excel spreadsheet provided, complete the questions.Steams CoffeeSteams 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.20 points1. 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 two 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 each worksheet, 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 worksheet. 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 worksheet.