[SOLVED] Excel assignment must be proficient Tri state area pets worksheet

I’m trying to study for my Excel course and I need some help to understand this question.

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.

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 10-mile radius. For the 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
• You will retrieve your information for the cats available from petango.com and include those for a 10-mile radius as well and from the 47630 zip code. For cats include their name, breed, 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. I expect you to proofread your work so will be deducted for typographical errors and/or misspellings.

For cats:

Using this information that you have collected you need to provide the following:

Neatly formatted spreadsheets that display the information- it should include borders, centering, color code/themes, freeze panes for easy scrolling, merged cells possibly, etc. The formatting theme should be consistent throughout the entire worksheet. All graphs should be on a separate summary sheet versus imbedded. Charts should include grid lines, labels, legends, etc. All charts and tables must be clearly labeled, if I can’t figure out what you’re showing you will not receive credit- so make sure that it is clear to the reader the information you are providing.

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. The worksheet should include the following:

• Hyperlink for each rescue/shelter to their website
• Grouped worksheets (dogs together and cats together)
• A 3D reference to a cell in another worksheet
• Named ranges
• Vertical and Horizontal panes or freeze panes to more easily view data within data range
• Table names for each table
• Conditional formatting- show (1) for puppies (2) for cats under 1 year of age
• Incorporate the COUNTIF function
• 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 flash fill.
• Sort the data to show by breed then by shelter
• Create subtotals and totals in your data table to show the number of dogs by breed and the number of cats by breed
• Calculate the average age of the cats available and by breed. Write a formula to convert age into months incorporate Defined Names into your formula
• Charts (at least 2 different kinds) hint: you will need to build your own table for the data series)
  • Number of cats by color
  • Breakdown of breeds for dogs
  • Breakdown of breeds for cats
• Use filters in your data range to show:
  • Just those cats that have been declawed
  • Just medium dogs available through PAAWS and Another Chance for Animals
  • Cats by color and breed
  • Just lab or lab mix adult dogs
  • Just grey cats that have been spayed/neutered
• Create Excel Table 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:
  • Average age of cats by breed
  • Total number of dogs
  • Total number of cats
  • Use Slicers to show: Create a dashboard for one of your slicers
    • Female spayed pit bull terriers
    • Adult black male Labrador retriever
    • Male not declawed domestic long hair cats
    • Cats available through PAAWS No Kill Animal Rescue Group
  • Create a summary table using VLOOKUP functions using the animal’s name for your lookup value and include breed, age, gender and shelter information
• Insert PivotTables and Pivot Charts for: These should also include formatting
  • Number of dogs by age
  • Number of male/female cats compared to the number of male/female dogs
  • Number of dogs by color
• The rescue is considering building a larger newer facility to better meet the needs of the community. The cost of the new building is $400,000. The interest rate is 5% for a 5-year note and 8% for a 10-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 5-year and 10-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 5- and 10-year notes.

When completed you will need upload the document into blackboard. The pages should include custom header and footers with your name provided on one of them.

Bonus Question (10 points):

If you were going to adopt a dog or cat that is currently (or was previously) available at Warrick County Animal Control or with Warrick Animal Guardians, which one would you choose and why? Must be a well thought out answer that is more than 2 sentences.