Perky Pup Dog Shelter

    Your friend Tamara is currently operating a dog adoption center called the Perky Pup Dog Shelter. This shelter brings in revenues from both the adoption fees it charges and for dog supplies it sells. She has asked you to help her create a summary of her profits/losses to make sure that she can continue to run the dog shelter. Being the good Samaritan, you told her you would put together an analysis of her shelter. Available Data Download the Excel Start file attached to this assignment description at the course. Rename the file PerkyPupDogShelterABC.xlsx where ABC is your initials. You must use this file to start your Final Project. In all of the worksheets in this Excel file, the shaded cells represent data or headings that are given to you. You will use formulas or functions learned in this course for the remaining items as explained below. Refer to the Excel Function Sheet attached to this assignment description at the course for a list of all of the functions we have learned in this course and their proper syntax. No other functions should be used other than what is listed on this sheet. Of course, for the formulas, those could be any mathematical equation that uses an arithmetic operator (+,-,*,/,^,%) or logical/comparison operator (=,<.<=.>,>=.<>) in it and could even use a function in it as well. Tamara has been keeping a workbook that contains all of last month’s Individual Sales, all sales items, all adopted dogs, and all customers who have adopted. A description of each of the worksheets is given below: Adopted Dogs worksheet: This worksheet includes information about each dig adoption that has already taken place in the past month for dogs that have been adopted. Most information is in a main table that includes each dog’s name, age, weight, sex, city, a TRUE or FALSE indication of whether or not the dog has been given a rabies shot, and the number of immunizations each dog has been given. This data is shaded because it is given to you. This table also includes columns to be filled in by you using formulas or functions for the rabies shot cost, immunization costs, total cost of shots, food costs, treat cost, total cost, adoption cost, and profit. Notice also that there are cells reserved for you to fill in for the rabies cost/shot, the immunization cost/shot, the food percent per body weight, the cost of food per pound of dog, and the shot treat cost. In addition, notice a small table for the adoption rates that will be filled in by you, a cell to identify the number of dogs needing rabies Page 2 of 6 shots, and a table which will show the average and total profit broken down by city. Following are some more important notes about this worksheet and the data in it: • Age contains the dog’s age in months. The age in this field is the age of the dog the last time it was at the Perky Pup Dog Shelter. • Weight contains the last recorded weight in pounds. • Sex contains a text field that will have either a M or an F, standing for Male and Female respectively. • City is a text field that tells where the dog was picked up at. • Rabies is a True/False field that contains whether or not the dog has had rabies shots while at the shelter. • Immunizations is a number field that tells how many Immunizations/Booster shots the dog has had while in the shelter. • CustomerID is the customer number of the person who adopted this dog. • Vet’s Expenses: Rabies shots cost $25 per dog, Immunization shots cost $18 per shot. • Dog Care Expenses: While the dog’s stay in the shelter, they will eat approximately 80% of their body weight a month in food at $0.86 per pound of food. • Every time a dog receives an immunization/booster shot, Tamara will give them a treat. Each treat costs $2.00. Sales worksheet: This worksheet contains a table with the sales of all dog accessories for the month grouped by item ID. It includes an item ID (shaded out and given to you). You will be obtaining the description, category, and cost of each type of item from the Items worksheet. You will be filling in the rest of the data in this table using formulas or functions for the number of each item sold, the selling price, the revenue, the expenses (cost), and the profit. Also note that this worksheet will be used to calculate the total profit by category of all sales for the month, the total profit for the month, and the yearly estimate based on this month’s total profit. A chart will also be inserted by you to show total profit for each type of sales item. Items worksheet: This worksheet includes a table of items for sale, including the item ID, a description, category for the item, and cost of the item. Cost represents the cost to the shelter for acquiring this item from their distributor. All data is shaded because it is given to you. Individual Sales worksheet: This worksheet includes a separate line for each and every item sold for the month. It includes the Sale ID and Item ID (shaded). Customers worksheet: This worksheet includes a table with the supplied data for each customer, specifically, the customer ID, last name, first name, and address (street, city, state and zip). This data is shaded because it is data given to you. Financial worksheet: This worksheet includes headings and cell spaces for you to enter the present value (PV), future value (FV), annual interest rate, number of compounding periods, type, years (years), and payment for a specific financial scenario the owner wants you to answer for them. The cells with yellow backgrounds are where answers to questions will be placed. Page 3 of 6 Part I – Profit Analysis – Adoptions You will use the Adopted Dogs worksheet to complete the following items in the main table in this sheet. A. Calculate the Expenses: • Write an Excel formula or function to calculate the costs of all shots (rabies cost shots (cell I11 and copied down to cell I60) & immunization costs (cell J11 and copied down to cell J60)) that have been given to each dog while in the shelter. Total these in cell K11 through K60 [called Total cost shots] using an Excel formula or function. Use Accounting format for these cells. • Write an Excel formula or function to calculate the food cost (cell L11 and copied down to cell L60) and treat cost (cell M11 and copied down to cell M60) that each dog will eat if applicable. Use Accounting format for these cells. • Write an Excel formula or function to total all expenses or costs for each dog (cell N11 through N60 [called total costs]). Round the value to the nearest cent. Use Accounting format for these cells. B. Calculate the Revenue: You will write an Excel formula or function to calculate the adoption fee for each dog but first you must fill in the Adoption Rates table in the Adopted Dogs worksheet (cells E4 to F6). Each adopted dog will fall into 3 distinct categories. Puppy, Teen, and Adult. Each category has its own adoption fees. For example, Puppies are more popular than Teens or Adults, so their cost is higher to offset the cost of holding on to an older dog. The prices for the three categories: Puppy (age 0-5 months) are $200, Teens (age 6-11 months) are $160, and Adults (age 12+ months) are $65. Now write an Excel formula or function that will calculate the cost of the adoption fees (cell O11 and copied down to cell O60) automatically using a reference function and the Adoption Rates table as the table array. Use Accounting format for these cells. C. Write an Excel formula or function to calculate the total profit from each dog adoption (cell P11 and copied down to cell P60). The total profit is adoption costs – total costs. Use Accounting format for these cells. D. Write an Excel formula or function