1. Management would like to know when their busiest month for sales is.
   1. 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.
   2. List only the months in the 1^st column and the sales in the 2^nd column.
   3. Sort the sales in Descending order.
   4. Format the numbers to reflect the $ symbol before the figures and 2 decimal places.
   5. Change the column headings to be meaningful.
   6. Add a meaningful title to the worksheet at the top and center it across the pivot table.
   7. Use to conditional formatting to highlight the top month for sales in green, the bottom month for sales in red.

8. Next, add a filter at the top so that you can filter out product sub categories.

2. 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.
   1. 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 Q5 worksheet.
   2. List all the Product Categories in the 1st column.
   3. Split the Quarter across the categories [ Hint: Drag the Date field in the Column Label area] Use the following to group the date field into quarters: Select any cell in the Date column in the Pivot Table. Go to Pivot Table Tools > Analyze > Group > Group Selection.
   4. Add the profit by category and quarter. Format the data in currency format.
   5. Add meaningful column labels.
   6. Add a meaningful title to the worksheet at the top and center it across the pivot table.
   7. Select the Pivot Table and Click on Options> Pivot Chart
   8. Insert a Column Chart of your choice.

3. One useful function of spreadsheet software is the ability to change input values and see the impact on outcome variables. For example, you can change sales projections and see the impact on net income. Excels Scenario Manager makes performing these sorts of sensitivity analyses much easier. Goal-Seek Analysis Scenario Manager helps examine how changes in input values impact outcome variables. To perform this analysis, go to the Data ribbon and select What-If Analysis, then Goal-Seek.
   1. Change the title of worksheet Revenue Analysis to Q7 - Revenue Analysis and move it after Q6 worksheet.
   2. Answer the following questions directly into the worksheet:
      1. What do sales need to be in order to reach 100,000 in revenue?
      2. What does the profit margin need to be to reach 100,000 in revenue?

5 points

4. Prepare your analysis for Management. Create a Table of Contents.
   1. 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 current date and the name of the person who created the workbook. Be sure to have titles for each of these.
   2. Make each list item a hyperlink to that particular workbook. For example, in the table of contents you will have an item titled Q1 - Quarterly Sales. Create a hyperlink to link to that particular workbook.
   3. Move the Raw Data to the last position.
   4. User proper elements to add the file name and current date to the header.