APPLY: Case Problem
Data File needed for this Case Problem: Pledges.xlsx
Hewart Zoo Marvis Chennard is the director of fund-raising for the Hewart Zoo. The zoo relies on donations to fund operations, temporary exhibits, and special programs. Marvis 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 located in the Excel5\Case1 folder included with your Data Files, and then save the workbook as Zoo Pledges in the same folder.
2. In the Documentation worksheet, enter your name and the date, 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.
4. Make a copy of the Pledges worksheet, and then rename the copied worksheet as Q4-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 amt pledged (largest first).
5. In the Q4-6 worksheet, insert a Total row. Display the number of pledges in the Donor Name column, the average pledge in the Amt Pledged column, the total received in the Amt Received column, and the total owed in the Amt Owed column.
6. In the Q4-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.
7. Make a copy of the Pledges worksheet, and then rename the copied worksheet as Q7. Filter the data to display individual donors whose amt owed is greater than zero. Sort the filtered data by pledge date, with the oldest date displayed first.
8. Make a copy of the Pledges worksheet, and then rename the copied worksheet as Q8. Filter the data to display records that have a pledge date in October through December. Sort the filtered data by amt pledged (largest first).
9. Make a copy of the Pledges worksheet, and then rename the copied worksheet as Q9. Filter the data to display only records with an amt received greater than zero. Then use the Subtotal command (SUM) to display the total amt received by fund name.
10. Create a PivotTable that displays the total and average amt owed by each donor type and fund name. Place the PivotTable in a new worksheet, and then rename the worksheet as Q10. Select an appropriate report layout and format. Insert a Donor Type slicer and filter using the Organization donor type.
11. Using Figure 5-55 as a guide, create a PivotTable in a new worksheet that shows the amt pledged by month and fund name. Format the PivotTable appropriately, and then rename the worksheet as Q11.
Figure 5-55: PivotTable for the amount pledged
12. Save and close the workbook. Submit the finished workbook to your instructor, either in printed or electronic form, as requested.
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂