EX16_XL_CH10_GRADER_ML2_HW - Animal Shelter 1.2 Project Description: You manage a small animal shelter in Dayton, Ohio. Your assistant created an XML document that lists some of the recent small animals that your shelter took in. In particular, the XML document lists the animal type (such as cat); the age, sex, name, color, and date the animal was brought in; and the date the animal was adopted. You want to manage the data in an Excel worksheet, so you will create a link to the original data source in case they change. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Download the asset file named e10m2Animals_LastFirst.xml and do not rename it. Open the downloaded workbook named exploring_e10_grader_h3_Animals.xlsx, and save it as exploring_e10_grader_h3_Animals_LastFirst. 0.000 2 Import the XML document e10m2Animals_LastFirst.xml into Sheet1 of the workbook beginning in cell A1. Rename Sheet1 Animals. 15.000 3 Create a PivotTable from the imported data on a new worksheet, placing the animal type and sex as row labels (in that order) and counting names as values. Use the adoption date as a report filter and set a filter to show those animals that have not been adopted. Rename the PivotTable worksheet PivotTable. 20.000 4 Open e10m2Animals_LastFirst.xml in Notepad. Edit the XML document by adding 3/25/2018 for the adoption date for Paws the cat. Edit Fido's data by entering his age: 6 months. Edit Twerpy's color as Orange. Edit Misty's adoption date of 3/31/2018. Save the XML file and close Notepad. Display the Animals worksheet and refresh the connection. 16.000 5 Display the PivotTable worksheet and refresh the PivotTable. 12.000 6 On the PivotTable worksheet, type Most Available Animals: in cell A15, Most Adopted Animals: in cell A16, and Cat Gender Most Adopted: in A17. Enter the appropriate answers to these questions in the range B15:B17 (choose from Cats, Dogs, Male, or Female). Resize the columns as necessary. If necessary, reset the filter in the PivotTable to display animals that have not been adopted yet. 18.000 7 Display the Animals worksheet and type Name, Type as a column label in cell H1. In cell H2, type Paws, Cat. Use Flash Fill to complete the rest of the data entry in this column. 12.000 8 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. 7.000 9 Save the workbook. Ensure that the following worksheets are present (in this order): PivotTable, Animals. Close the workbook, and then submit the file as directed. 0.000 Total Points 100.000