SOLUTION AVAILABLE AT: http://libraay.com/downloads/office-2016-myitlab-ms-excel-grader-ex16_xl_ch09_grader_ml1_hw-sales-data-1-1/
Problem:
You are an accountant for a pharmaceutical sales company. As one of your tasks, you compile an annual report that documents regional sales information into one standardized worksheet. As part of this process, you group the worksheets and insert descriptive rows and columns, apply formatting, and insert functions. Your last step is to create a sales summary worksheet and provide basic information for management to evaluate.
Steps to Perform:
| Step | Instructions | Points Possible |
| 1 | Open exploring_e09_grader_h2_Sales.xlsx and save it as exploring_e09_grader_h2_Sales_LastFirst. | 0 |
| 2 | Group the five regional worksheets. Insert a new row above row 1 of the existing data. Type Agent in cell A1. Apply the Heading 2 style. | 5 |
| 3 | With the five regional sheets grouped, type Qtr1 in cell B1. Use the fill handle to add Qtr2-Qtr4 in cells C1:E1. Apply the Heading 3 style and apply center horizontal alignment to cells B1:E1. | 10 |
| 4 | With the five regional sheets grouped, type Total in cell F1. Apply the Heading 2 style and horizontal center alignment. | 10 |
| 5 | With the five regional sheets grouped, click cell F2, type =SUM(B2:E2), and press ENTER. Use the fill handle to complete the summary information to the range F3:F19. | 10 |
| 6 | With the five regional sheets grouped, type Total in cell A20. In cell B20, insert a function to total Qtr1 sales. Copy the function to the range C20:F20. | 10 |
| 7 | With the five regional sheets grouped, format the ranges B2:F2 and B20:F20 with Accounting Number Format and zero decimal places. Format the range B3:F19 with Comma Style and zero decimal places. Add Top and Double Bottom Border to cells B20:F20. | 10 |
| 8 | Ungroup the worksheets and click the Summary sheet tab. Click cell B2 and enter a formula that refers to cell B20 in the North Region worksheet. Click cell B3 and enter a formula that refers to cell B20 in the East Region worksheet. Click cell B4 and enter a formula that refers to cell B20 in the South Region worksheet. Click cell B5 and enter a formula that refers to cell B20 in the Midwest Region worksheet. Click cell B6 and enter a formula that refers to cell B20 in the West Region worksheet. | 5 |
| 9 | In the Summary sheet, select the range B2:B6 and copy the formulas to the range C2:F6. | 5 |
| 10 | In the Summary sheet, apply the Accounting Number Format with zero decimal places to the range B2:F2 and Comma Style with zero decimal places to the range B3:F6. Ensure that the column widths B:F are set to 14.57 to display the formula results. | 6 |
| 11 | In the Summary sheet, create a hyperlink in each cell in the range A2:A6 to cell A1 in the respective worksheet. | 5 |
| 12 | Edit the hyperlink in cell A2 in the Summary sheet to create the ScreenTip Click to see North Region sales. Edit the hyperlink in cell A3 in the Summary sheet to create the ScreenTip Click to see East Region sales. Edit the hyperlink in cell A4 in the Summary sheet to create the ScreenTip Click to see South Region sales. Edit the hyperlink in cell A5 in the Summary sheet to create the ScreenTip Click to see MidWest Region sales. Edit the hyperlink in cell A6 in the Summary sheet to create the ScreenTip Click to see West Region sales. Do not include the periods. | 5 |
| 13 | On the Summary sheet, set a Watch Window to watch the formulas in the range B2:F6. Note, Mac users continue to the next step. |
0 |
| 14 | Use the data in the Summary sheet to answer the summary questions 1, 3, and 4 in the Q&A Sheet (for questions 3 and 4, use one-word answers only). | 9 |
| 15 | Click the North Region sheet tab, change the value in cell B2 to 3,000,000, and observe the changes in the Watch Window. Go to the Q&A sheet and answer question 2. | 4 |
| 16 | Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of the Summary and Q&A sheets. | 6 |
| 17 | Save and close the workbook, and submit the file as directed. | 0 |
| Total Points | 100 |
SOLUTION AVAILABLE AT: http://libraay.com/downloads/office-2016-myitlab-ms-excel-grader-ex16_xl_ch09_grader_ml1_hw-sales-data-1-1/