Excel Project GO! ECH02 A2 - Commercial Compensation 2.2

Office 2013 – myitlab:grader – Instructions Excel Project GO! ECH02 A2 - Commercial Compensation 2.2
Project Description:
In the following project, you will edit a worksheet that summarizes the compensation for the
commercial salespersons who qualified for bonuses in the Arizona and Washington state areas.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Points
Possible Instructions 1 Start Excel. Download and open the file named go_e02_grader_a2.xlsx. 0.000 2 Rename Sheet1 as Arizona and change the Tab Color to Dark Red, Accent 1.
Rename Sheet2 as Washington and change the Tab Color to Light Green,
Background 2. 8.000 3 Click the Arizona sheet tab to make it the active sheet, and then group the
worksheets. In cell A1, type Marisol Desserts and Spices and then Merge &
Center the text across the range A1:F1. Apply the Title cell style. Merge & Center
the text in cell A2 across the range A2:F2, and then apply the Heading 3 cell style. 10.000 4 With the sheets still grouped, in cell D5 calculate the Commission for Moreland by
multiplying the Sales times the Commission Rate. Copy the formula down through
cell D8. 5.000 5 In cell F5, calculate Total Compensation by summing the Commission and Expense
Allowance for Moreland. Copy the formula down through the cell F8. 5.000 6 In row 9, sum the columns for Sales, Commission, Expense Allowance, and Total
Compensation. Apply the Accounting Number Format with two decimal places to
the appropriate cells in row 5 and row 9 (do not include the percentages). 4.000 7 Apply the Comma Style with two decimal places to the appropriate cells in rows
6:8 (do not include the percentages). Apply the Total cell style to the appropriate
cells in the Total row. 4.000 8 Click the Washington sheet tab to ungroup the sheets, and then insert a new
worksheet. Change the sheet name to Summary and then change the Tab Color to
Orange, Accent 2. Widen column A to 25.63 (210 pixels), widen columns B:E to
18.75 (155 pixels). 9 Move the Summary sheet so that it is the first sheet in the workbook. In cell A1 of
the Summary sheet, type Marisol Desserts and Spices and then Merge &
Center the title across the range A1:E1. Apply the Title cell style. 8.000 In cell A2, type November Commercial Salesperson Bonus Summary and
then Merge & Center the text across the range A2:E2. Apply the Heading 1 cell
style. 3.000 10
11 In the range A5:A7, type the following row titles (in the following order) and then
apply the Heading 4 cell style to the range:
Commission
Expense Allowance Updated: 01/05/2015 1 10.000 4.000 Current_Instruction.docx Office 2013 – myitlab:grader – Instructions Step Excel Project Points
Possible Instructions Total Compensation 12 In the range B4:E4, type the following column titles (in the following order), and
then Center and apply the Heading 3 cell style to the range:
Arizona/Washington
Arizona
Washington
Total 5.000 13 In cell C5, enter a formula that references cell D9 in the Arizona worksheet so that
the total commission amount for Arizona displays in C5. Create a similar formula to
enter the total expense allowance for Arizona in cell C6. Create formulas in the
range D5:D6 so that the Washington totals display. 8.000 14 In E5:E6, use the SUM function to total the Commission and Expense Allowance
rows. In C7:E7, use the SUM function to calculate Total Compensation for the
Arizona, Washington, and Total columns. 6.000 15 In cell B5, insert a Column Sparkline for the range C5:D5. In cells B6 and B7, insert
Column sparklines for the appropriate ranges to compare Arizona totals with
Washington totals. 16 To the sparkline in cell B5, apply the style Sparkline Style Accent 2, (no dark or
light). To the sparkline in cell B6, apply the style Sparkline Style Dark #1. To the
sparkline in cell B7, apply the style Sparkline Style Accent 1, Lighter 40%. 4.000 17 Group the three worksheets, and then from the Page Setup dialog box, center the
worksheets Horizontally on the page. Change the Orientation to Landscape. 4.000 18 Insert a custom footer on the grouped sheets in the left section with the File Name
element. 2.000 19 Ensure that the worksheets are correctly named and placed in the following order
in the workbook: Summary, Arizona, Washington. Save and close the workbook.
Exit Excel. Submit the file as directed. 0.000 Total Points Updated: 01/05/2015 2 10.000 100.000 Current_Instruction.docx