Office 2016 MyITLab MS-Excel Grader EX16_XL_CH10_GRADER_CAP_HW – Pledge Drive 1.2
You are a volunteer for Health Right, a nonprofit company that provides free physical fitness classes to schools that do not have a physical education facility. As part of your duties, you generate weekly reports that detail donation pledge calls placed, as well as pledge dollars received. You also maintain pledge drive agent personal information and manage the thank-you gifts agents receive for volunteering their time. This week you have decided to overhaul your report by updating agent contact information, importing data previously stored outside your old report, implementing a PivotTable using Power Pivot, and utilize Power-Add-Ins to edit and visualize the week’s data.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
| Step | Instructions | Points Possible |
| 1 | Open the workbook e10c1PledgeDrive.xlsx and save it as e10c1PledgeDrive_LastFirst. | 0 |
| 2 | Import the file e10c1VolunteerInfo.csv. Be sure to use a method that creates a connection to the external file. Import the data into cell A1 of the existing worksheet. Hint: On the Data tab, click Get External Data, and then click From Text. |
10 |
| 3 | Rename the worksheet Volunteer Information. Hint: Double-click the worksheet name and type Volunteer Information. |
3 |
| 4 | Use Text to Columns to separate the values in column D into three separate columns for City, State, and Zip. Resize the columns as needed.
Hint: Click the Data tab, and click Text to Columns in the Data Tools group. |
6 |
| 5 | Set the External Data Range Properties to Refresh data when opening the file.
Hint: Click the Data tab, click Connections in the Connections group, and click Properties. |
0 |
| 6 | Copy the range B2:B7, paste it in the range I2:I7, and delete the original range B2:B7. | 0 |
| 7 | Enter a function in cell B2 that formats the data in cell I2 in a traditionally formal format. Copy the function down to complete the column.
Hint: Use the PROPER function to display the text with a capital letter at the beginning of each word. |
0 |
| 8 | Copy the range B2:B7 and paste the values back into the range B2:B7 to remove the underlying functions. Then delete the values in column I. | 6 |
| 9 | Create a new worksheet and name it Rewards. Hint: Click the New Sheet button to add a new worksheet to the workbook. |
4 |
| 10 | Create a connection to the e10c1Rewards.xml file in the Rewards worksheet. Hint: On the Data tab, click Get External Data, click From Other Sources, and then select From XML Data Import. |
10 |
| 11 | Apply Accounting Number Format to the dollar amounts imported into column A.
Hint: Select the range A2:A12, and click Accounting Number Format in the Numbers group. |
4 |
| 12 | Open the document e10c1Rewards using Notepad. Locate the spelling error in line 18 (Sweet Shirt) and edit the data to Sweat Shirt. Save and close the XML file. Refresh the data in the Rewards worksheet.
Hint: To refresh the data, on the Data tab, in the Connections group, click the Refresh All arrow, and then click Refresh. |
2 |
| 13 | Use Power Pivot to import the Access database e10c1PledgeDollars.accdb into a PivotTable. Place the PivotTable on a new worksheet named Pledge Dollars. Hint: Click the Power Pivot tab, and click Manage. Click Get External Data, click From Database, and select Access. |
8 |
| 14 | Add the Date field to the Filters area, add Operator_ID to the Rows area, and add Pledge_Amount to the Values area. Add appropriate row and column headings and save the workbook. | 6 |
| 15 | Use Power Query to open the database e10c1Contacts.accdb. Hint: On the Data tab, in the Get & Transform group, click New Query, and select From Database. |
2 |
| 16 | Use the Query Editor to remove the Income and Dependents fields.
Hint: In the Query Editor, select the column you would like to remove. Click Remove Columns in the Manage Columns group, and select Remove Column. |
4 |
| 17 | Close & Load the data, then rename the worksheet Pledge Leads. Hint: Click Close & Load in the Close group. |
4 |
| 18 | Create a new Power View report (add the Power View button to the Quick Access Toolbar, if necessary). The new sheet will be named Power View1 automatically. If necessary, rename the sheet Power View1. Hint: Click Power View on the Quick Access Toolbar. If you are unable to add Power View to the Quick Access Toolbar, add it to a tab on the ribbon. |
8 |
| 19 | Create a visualization table in the upper left corner that displays Operator_ID and corresponding Pledge_Amount dollars. Do not summarize the Operator_ID column. Format the Operator_ID column with General Number Format.
Hint: To add a visualization to a Power View, drag the desired field onto the canvas. Next drag each additional field into the newly created visualization. Aggregation settings can be edited in the Power View Fields pane. |
5 |
| 20 | Create a visualization pie chart that displays Pledge_Amount by operator in the upper right corner.
Hint: To create a pie chart from tabular data, click the tabular visualization you want to edit and select the desired chart option from the Switch Visualization group on the DESIGN tab. |
6 |
| 21 | Create a visualization stacked column chart that spans the lower half of the canvas. The chart should display Pledge_Amount and Transaction_ID.
Hint: To create a column chart from tabular data, click the tabular visualization you want to edit and select the desired chart option from the Switch Visualization group on the DESIGN tab. |
7 |
| 22 | Add an appropriate title to the dashboard.
Hint: Click the title area in the Power View canvas and add the desired title. |
0 |
| 23 | 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 each worksheet.
Hint: On the Page Layout tab, in the Page Setup group, click the Page Setup Dialog Box Launcher. |
5 |
| 24 | Save and close the file. Submit e10c1PledgeDrive_LastFirst as directed. Hint: Click Save on the Quick Access Toolbar to save the workbook. |
0 |
- File Format: .xslx
- Version: 2016