Analysis of Pledge Data

Instructions:

Harold Dinklesdorf is the director of fund-raising for the Bobcat Symphony. The symphony relies on donations to fund operations and special programs. Harold created an Excel table to track information about donors and their pledges. You will analyze the data in the list. Complete the following:

1. Open the Pledges workbook with this assignment and then save the workbook as AA Symphony Pledges.
2. In the documentation worksheet, enter your AA 10/01/2012, and then switch to the Pledges worksheet.
3. Create an Excel table, apply the Medium 7 table style, and then rename the table as PledgeData. (Hint: to apply the style and rename the table, select the table then click on the Design tab)
4. Make a copy of the Pledges worksheet, and then rename the copied worksheet as Q.4-6 (for "Question 4-6")(Hint: Press the Ctrl key and drag the sheet tab to the right of the Pledges sheet tab to make a copy of the worksheet.) Sort the data in ascending order by donor type and fund name, and in descending order by amount pledged (largest first).
5. In the Q.4-6 worksheet, insert a Total row. Using the appropriate Excel functions, display the number of pledges in the Donor Name column, the average pledge in the Amount Pledged column, the total received in the Amount Received column, and the total owed in the Amount Owed column.
6. In the Q.4-6 worksheet, split the worksheet into two horizontal panes at row 23. In the top pane, display the pledge transactions. In the bottom pane, display the Total row. (Hint: Once you've split the worksheet into panes, you should be seeing the detail information in the top pane, and a few lines of detail in the bottom pane, along with the total row. You are welcome to leave the detail in the top pane as is: All you really need to do is adjust what the bottom pane is showing so that only the total row is displayed. Be sure to save your file after you do this so that the file doesn't revert back to the previous display.)
7. Make a copy of the Pledges worksheet, and then rename the copied worksheet as Q.7. Filter the data to display individual donors whose amount owed is greater than zero. Sort the filtered data by pledge date, with the oldest date displayed first. (In other words, display all of the donor records with donor type = individual and amount owed > zero. Donors who are organizations should not be displayed.)
8. Make a copy of the Pledges worksheet, and then rename the copied worksheet as Q.8. Filter the data to display records that have a pledge date in October through December. Sort the filtered data by amount pledged (largest first).
9. Make a copy of the Pledges worksheet and then rename the copied worksheet as Q.9. Filter the data to display only records with an amount received greater than zero. Then use the Subtotal command (SUM) to display the total amount received by fund name.
10. Create a PivotTable that displays the total and average amount owed by each donor type and fund name. Place the PivotTable in a new worksheet, and then rename the worksheet as Q.10. Select an appropriate report layout and format. (Hint: I suggest that you sort the data by the fund name column if necessary. In the Tools group on the Table Tools Design tab, click the Convert to Range button, and convert the table to a range. In the Outline group on the Data tab click the Subtotal button and follow the prompts from there, and you should be able to display the information as required.)

View the attached excel file to view the data.