QM 160: Animal Rescue Project

The Tri-State area, which is a population of approximately 120,000 people in the city limits and more than 300,000 in the metropolitan area, has roughly 12 animal rescue shelters/groups to address the over population of cats and dogs in the area. There are also Spay & Neuter Clinics within the area at the Vanderburgh Humane Society and Warrick Humane Society that are also working to help with ending shelter overpopulation (and consequently saving animal lives). This is done with a network of veterinarians who generously provide low-cost spay-neuter surgeries for limited-low income Hoosiers. Locally, over last 20 years the outcomes of local animals in shelters has improved significantly with the opening of these clinics that also provide robust adoption & foster care programs, and a wide range of humane education and assistance services. In 2018 the live release rate was 89% which is a significant improvement from even 5 years prior of 74%. Live Release Rate (also known as the Save Rate) is Live Outcomes (Adoptions + Transferred Out + Returned to Owner) divided by Total Outcomes. Consider adopting a rescue animal in the future.

You are going to analyze the current dogs and cats that are up for adoption through local rescues and shelters in the Evansville area. If it’s a mixed breed just leave off the word “mix” in the name when you list it such as Shephard mix would just be listed as Shephard. If the dog is listed as two breed types such as “Shephard Husky” just list the first descriptor so in this case “Shephard”.

For dogs:

· You will retrieve your information for the dogs on petfinder.com where you will use 47630 zip code for the search criteria to pull up all those available for a 50-mile radius. You are to list all dogs within this mile radius in your spreadsheet (up to 100 dogs). You need to include their name, breed, age (whether adult/young/puppy), gender, size and shelter/rescue their available through. The shelter is listed at the end of the website information.

For cats:

· You will retrieve your information for the cats available from petango.com and include those for a 50-mile radius as well and from the 47630 zip code. You are to list the first 125 cats within this mile radius (up to 100 cats). You need to include their name, breed, age, gender, color, spayed/neutered, declawed, shelter/rescue they’re available through.

· I want you to build a formatted spreadsheet for each- one for dogs and one for cats with the information above. I expect you to proofread your work so will be deducted for typographical errors and/or misspellings.

Keep in mind some functions and formatting have to be in a data range and others in an excel table. I have color coded the grade sheet to help you know what falls within each as a reminder. If I cannot easily see or find what you’ve done you won’t get credit for it- the information needs to be laid out clearly and should be easily understood.

The following information is required: You will need to create multiple worksheets possibly even a workbook dedicated to each animal if the number of worksheets becomes too large for one workbook.

Neatly formatted spreadsheets need to contain the following information:

a. Borders

b. Centering (including within the table)

c. Merged cells

d. Theme applied

e. Freeze panes in first row and first column of range and table (one for cats and one for dogs)

f. Named range for the entire data range table and excel tables- one for cats and one for dogs

g. All graphs should be on a separate summary sheet and clearly labeled. Pivot Charts need to be applied to those indicated on the grade sheet and pie/column/bar charts must be completed for those tasks also indicated on the grade sheet. If you use a pivot chart where I have stated to create a regular chart you will not receive credit for it. Use the appropriate type chart to match your data set. If you create a pie chart that has 20 slices within it you will not receive credit. Pie charts are for 6 or less categories. There should be at least one pie chart, one bar chart and one column chart within this project.

Charts should include:

i. grid lines, data labels, chart title, axis titles, legends, no field buttons should be present. All charts and tables must be clearly labeled, if I can’t figure out what you’re showing you will not receive credit. If you use a Mac computer please indicate somewhere as you won’t be able to remove the field buttons on your pivotcharts.

1. Create a pet ID for each animal using the first three letters of the shelter they are available through and a sequential numerical system using autofill. You will need to sort your data by shelter first in order to use autofill for it.

2. Hyperlink for each rescue/shelter to their website (can be in a separate table)

3. Hyperlink to your email address to contact for more information

4. Grouped worksheets (don’t have to do functions just group them prior to submission)

5. A 3D reference (not an external reference) to a cell on another worksheet (one for cats and one for dogs)

6. Conditional formatting, highlight “young dogs” and “cats available through Another Chance for Animals” and provide a key for the rule on the document

7. Incorporate the AND function (for cats and for dogs)

8. Incorporate an IF statement (one for cats and one for dogs)

9. Incorporate Today function

10. Incorporate an absolute cell reference (one for cats and one for dogs)

11. Apply a graphic object within your project (one for cats and one for dogs)

12. Sort the data to show by shelter then by breed (one for cats and one for dogs)

13. Create subtotals and totals in your data range to show the number of dogs by breed then shelter and the number of cats by breed then shelter.

14. Regular Charts (NOT pivot charts) (hint: you will need to build your own table for the data series)

a. Breakdown of dogs by shelter

b. Breakdown of cats by shelter

c. Breakdown of cats by gender

15. Use filters in your data range to show: These each have to be on a separate range

a. Domestic shorthair cats

b. Black cats with Another Chance for Animals

c. Dogs available through PAAWS

d. Small male dogs

16. Create Excel Table (one for cats and one for dogs) to show: should have formatting and banded rows including a table style and should also include a table name. Should also have Total Rows to calculate:

a. Total number of dogs

b. Total number of cats

17. Use Slicers to show:

a. Male Pitbull/Staffordshire Terrier with It Takes A Village

b. Adult beagle or beagle mix dogs

c. Grey female cats

d. Domestic longhair cats with PAAWS

e. Slicers should be formatted

f. Create a dashboard sheet for one set of these

18. Create two summary tables (one for cats and one for dogs) using VLOOKUP functions using the animal’s Pet ID for your lookup value and include name, breed, age, gender and shelter information. Hint: you will need to have the data in range format and list the animal’s pet ID on a separate line above your VLOOKUP table. You will also need to have your Pet ID in the first column of your data range.

19. Insert PivotTables and Pivot Charts for: These should also include formatting

a. Number of dogs by breed then age

b. Number of cats by breed then color

20. Custom Headers (name, date and worksheet name) and Footers (page number) for all sheets. Use header and footer button to populate worksheet name and page number.

21. Incorporate print titles to one sheet for cats and one sheet for dogs

22. The rescue is considering building a larger newer facility to better meet the needs of the community. The cost of the new building is $350,000. The interest rate is 9% for a 15-year note and 7% for a 30-year note. Payments would be made monthly. Calculate the mortgage payment for the building if the group were to finance the cost of the building providing both the 15-year and 30-year payment. They anticipate that they can do fundraising to raise some of this money, along with applying for grants and getting sponsors to reach a total of $150,000 to apply towards it. Calculate this new monthly total if they can reach this goal using both interest rates for the 15- and 30-year notes.

Bonus Question

1. If you were going to adopt a dog or cat that is currently available at one of the shelters or is with a rescue, which one would you choose and why? Must be a well thought out answer that is more than 1 sentence.

2. What is your favorite Disney or Marvel Trivia fact shared with you in the lecture videos?