Download the Assignment data file located in eConestoga and save the workbook as YourName_Assignment in your COMP1631 Folder.

In the Documentation worksheet, enter your name and the date. Use a function to display the current date. Review the worksheets in the workbook.

Group the Quarter 1 through Quarter 4 worksheets.

For the sheets in the worksheet group, in the range E6:E11, enter a function to calculate the total sales for each line item.

In the range B12:E12, enter a function to calculate the total sales for each month

NOTE: Be sure to use the Fill Without Formatting paste option, so that you keep the bottom border on the range B11:F11 and $ does not appear in every cell.

Ungroup the worksheets.

On the Summary worksheet, complete the formulas in the Summary worksheet as follows: NOTE: Be sure to use the Fill Without Formatting paste option, so that you keep the bottom border on the range B11:F11 and $ does not appear in every cell.

In the range B6:E11, create formulas that reference cells in other worksheets to display the quarterly totals by type. For example, the formula in cell B6 will reference cell E6 in the Quarter 1 worksheet.

For each cell in the range F6:F11, enter a function that uses 3-D cell references to calculate the total for each type of sales.

In cell A8 of the Documentation worksheet, insert a hyperlink that points to cell A1 on the Summary worksheet, as follows:

Text to display is Click here to go to Summary worksheet

ScreenTip is Click to view the details in Summary worksheet

Create headers and footers for the Summary worksheet as follows:

Display the file name field in the left and the sheet name field in the right section of the header.

Display your name and the date field on separate lines in the right section of the footer.

Go to Sales worksheet, create a table as follows:

Format the range A3:E116 as a table with headers using Blue, Table Style Medium 2.

Use SalesData as the name of the table.

Create a multilevel sort that sorts by Day (use the custom list order of Sunday, Monday), then by Segment (A-Z), then by Amount (largest to smallest)

On the Sales worksheet, insert a slicer based on the Segment field. Resize it to a height of 2 and a width of 1.4. Move the slicer so its upper-left corner is in Cell G2. Use the slicer to show the Blends segment only.

Go to the Adv. Filter worksheet.

Use a conditional format to highlight the Amount over $300 with Light Red Fill and Dark Red Text.

Add a Total row to the table and calculate the Average for Amount field. Change the label for the Total row from Total to Average.

Copy the field names in the range A6:E6 to the range A2:E2, and make sure row 2 has the same formatting as row 6. This establishes the criteria for advanced filtering.

Use AND criteria to select Green Segment that has Amount greater than $100.

Use advanced filtering to copy the filtered results to a new location within the same worksheet starting in cell H6.

On the Subtotals worksheet, convert the data to a range and then sort the range by Segment.

Create Subtotals that calculate the subtotal for each Segment under the Amount column. Display only the subtotal and Grand Total results.

On Request Form worksheet, use VLOOKUP function in the Description and Cost column to retrieve the Description and Cost from the range H8:J12. Use appropriate cell referencing to allow your formula to be copied.

Calculate Total (Qty*Service Cost), Subtotal, Tax Amount and Grand Total.

Save and close your file and submit it to the Dropbox.