Downtown Office Supply Downtown Office supply is a small family owned store in a small suburb. In order to maintain and grow the business the owners want to do some analysis to see how their sales are doing. Management wants to see how the company did last year and make some decisions about what products to keep along with staffing for busy times of the year.

Instructions: In the Excel spreadsheet provided, complete the tasks below. Upon completion you will submit a single, completed, Excel workbook. (No Google Sheets files, nor Apple Numbers files, nor external links to files will be accepted.)

4. Use the data in the sheet titled Raw data" to calculate the profit for each product category and product sub category (ability to drill down). (15 pts) a. Create a new worksheet titled Q4 Profit by Category and insert a new pivot table for the data in the sheet - "Raw Data. Move the worksheet to the 4th position. b. List the total profit for product categories and subcategories in this table C. Format the numbers to reflect the "$" symbol before the figures and 2 decimal places. d. Change the column headings to be meaningful. e. Add a meaningful title to the worksheet at the top and center it across the pivot table. Sum of Profit 6241.52 3 Row Labels 4 FURNITURE 5 BOOKCASES 6 CHAIRS & CHAIRMATS -5042.15 7245.16 g. Add another column to the pivot table display the profit or loss as a percent of the total. h. Use conditional formatting to display the top 3 products that had the highest percentage. percentage. 5. Management would like to know when their busiest month for sales is. (10 pts) a. Create a new worksheet titled - Q5 -Sales by Month and insert a new pivot table for the data in the sheet Raw Data. Move the worksheet after Q4 worksheet. b. List only the months in the 1st column and the sales in the 2nd column. To do this click on one of the dates. Then do a right mouse click to display a menu. Click the Group option. Another menu will display. Click on Months and Quarters. C. Sort the sales in Descending order. d. Format the numbers to reflect the "$ symbol before the figures and 2 decimal places. e. Change the column headings to be meaningful. f. Add a meaningful title to the worksheet at the top and center it across the pivot table. g. Use to conditional formatting to highlight the top month for sales in green, the bottom month for sales in red. Date 2018 Nov + Sales for 2018 713932.373 $ 90,117.27 $ 84,843.94 $ 84,339.36 $ 78,405.74 Jun Oct Jul h. Next, add a filter at the top so that you can filter out product sub categories. 6. Management would like to know - what is the split in terms of profit, across the Product Categories, for each quarter. Use a Line Chart to show the numbers. Rank the Product categories in terms of profit. (15 pts) a. Create a new worksheet titled - "Q6 - Qtr. Profit and insert a new pivot table for the data in the sheet - "Raw data. Move the worksheet after 25 worksheet. b. List all the Product Categories in the 1st column. c. Split the Quarter across the categories [ Hint: Drag the Date field in the "Column Label area]. To group the date by Quarter - Click on the date 6. Management would like to know what is the split in terms of profit, across the Product Categories, for each quarter. Use a Line Chart to show the numbers. Rank the Product categories in terms of profit. (15 pts) a. Create a new worksheet titled - Q6 - Qtr. Profit and insert a new pivot table for the data in the sheet Raw data. Move the worksheet after 25 worksheet. b. List all the Product Categories in the 1st column. c. Split the Quarter across the categories [ Hint: Drag the Date field in the "Column Label area). To group the date by Quarter - Click on the date field. Next, right mouse click to display a menu. Click the Group option. Another menu will display. Click on Months and Quarters. d. Add the profit by category and quarter. Format the data in currency format. e. Add meaningful column labels. f. Add a meaningful title to the worksheet at the top and center it across the pivot table. g. Select the Pivot Table and click on Options> Pivot Chart h. Insert a Column Chart of your choice. 4. Use the data in the sheet titled Raw data" to calculate the profit for each product category and product sub category (ability to drill down). (15 pts) a. Create a new worksheet titled Q4 Profit by Category and insert a new pivot table for the data in the sheet - "Raw Data. Move the worksheet to the 4th position. b. List the total profit for product categories and subcategories in this table C. Format the numbers to reflect the "$" symbol before the figures and 2 decimal places. d. Change the column headings to be meaningful. e. Add a meaningful title to the worksheet at the top and center it across the pivot table. Sum of Profit 6241.52 3 Row Labels 4 FURNITURE 5 BOOKCASES 6 CHAIRS & CHAIRMATS -5042.15 7245.16 g. Add another column to the pivot table display the profit or loss as a percent of the total. h. Use conditional formatting to display the top 3 products that had the highest percentage. percentage. 5. Management would like to know when their busiest month for sales is. (10 pts) a. Create a new worksheet titled - Q5 -Sales by Month and insert a new pivot table for the data in the sheet Raw Data. Move the worksheet after Q4 worksheet. b. List only the months in the 1st column and the sales in the 2nd column. To do this click on one of the dates. Then do a right mouse click to display a menu. Click the Group option. Another menu will display. Click on Months and Quarters. C. Sort the sales in Descending order. d. Format the numbers to reflect the "$ symbol before the figures and 2 decimal places. e. Change the column headings to be meaningful. f. Add a meaningful title to the worksheet at the top and center it across the pivot table. g. Use to conditional formatting to highlight the top month for sales in green, the bottom month for sales in red. Date 2018 Nov + Sales for 2018 713932.373 $ 90,117.27 $ 84,843.94 $ 84,339.36 $ 78,405.74 Jun Oct Jul h. Next, add a filter at the top so that you can filter out product sub categories. 6. Management would like to know - what is the split in terms of profit, across the Product Categories, for each quarter. Use a Line Chart to show the numbers. Rank the Product categories in terms of profit. (15 pts) a. Create a new worksheet titled - "Q6 - Qtr. Profit and insert a new pivot table for the data in the sheet - "Raw data. Move the worksheet after 25 worksheet. b. List all the Product Categories in the 1st column. c. Split the Quarter across the categories [ Hint: Drag the Date field in the "Column Label area]. To group the date by Quarter - Click on the date 6. Management would like to know what is the split in terms of profit, across the Product Categories, for each quarter. Use a Line Chart to show the numbers. Rank the Product categories in terms of profit. (15 pts) a. Create a new worksheet titled - Q6 - Qtr. Profit and insert a new pivot table for the data in the sheet Raw data. Move the worksheet after 25 worksheet. b. List all the Product Categories in the 1st column. c. Split the Quarter across the categories [ Hint: Drag the Date field in the "Column Label area). To group the date by Quarter - Click on the date field. Next, right mouse click to display a menu. Click the Group option. Another menu will display. Click on Months and Quarters. d. Add the profit by category and quarter. Format the data in currency format. e. Add meaningful column labels. f. Add a meaningful title to the worksheet at the top and center it across the pivot table. g. Select the Pivot Table and click on Options> Pivot Chart h. Insert a Column Chart of your choice