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