EX16_XL_CH10_GRADER_CAP_HW - Pledge Drive 1.4

Project Description:

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.	10
3	Rename the worksheet 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.	6
5	Set the External Data Range Properties to Refresh data when opening the file.	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.	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.	4
10	Create a connection to the e10c1Rewards.xml file in the Rewards worksheet.	10
11	Apply Accounting Number Format to the dollar amounts imported into column A.	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.	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.	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.	2
16	Use the Query Editor to remove the Income and Dependents fields.	4
17	Close & Load the data, then rename the worksheet Pledge Leads.	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.	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.	5
20	Create a visualization pie chart that displays Pledge_Amount by operator in the upper right corner.	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.	7
22	Add an appropriate title to the dashboard.	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.	5
24	Save and close the file. Submit e10c1PledgeDrive_LastFirst.xlsx as directed.	0
	Total Points The Excel file and additional files are in this rar download link. https://ufile.io/9x35r	100