Final project Excel -PERKY DOG SHELTER

The Final Project for this course integrates many of the spreadsheet skills and functions you have learned this semester. It also makes use of presentation and word processing skills, including the use of Mail Merge .

The purpose of the project is to use these skills to solve a business problem that you might encounter in the real world. Please read through the entire assignment before proceeding. An Excel start file has been provided to you to use to start this project.

Problem Statement

Available Data

160 Points

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.

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 dog 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 1 of 6

Page 2 of 8

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 8

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. For the formula in cell I11, you will use an IF() function and check if the value in F11 (the rabies shot?) is TRUE or not. If it is TRUE, then use the value in call B1 (the rabies cost/shot), otherwise use a value of 0. For the formula in cell J11, you will simply multiply the value in G11 (Number of Immunizations) by the value in cell B2 (Immunization cost/shot). For the formula in call K11, you will use the SUM() function to add I11 (Rabies shot cost) and J11 (Immunization costs). Be careful to use proper absolute or mixed cell referencing where appropriate so these formulas work when copied down.

• 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. For the formula in L11, you will multiply C11 (weight in pounds) by B3 (food and body weight) by B4 (cost food per pound dog). For the formula in cell M11, you will multiply G11 (number of immunizations) by B5 (shot treat cost). Be careful to use proper absolute or mixed cell referencing where appropriate so these formulas work when copied down.

• 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. For the formula in cell N11, you will use the SUM() function to sum the K11 (total cost shots and L11(food cost). You will then use the ROUND() function around the results of the SUM() function to properly round the value to the nearest cent.

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 above as the table array. Use Accounting format for these cells. So, for the formula in cell O11, you will use the VLOOKUP() function with a lookup value of B11 (Age in months), a table array of cells in the range of E4 to F6, a column_index_num of 2, and a range_lookup of TRUE since you are using a range of values). Be careful to use proper absolute or mixed cell referencing where appropriate so these formulas work when copied down.

C. Write an Excel formula or function to calculate the total profit from each dog adoption (cell P11 and

Part I – Profit Analysis – Adoptions

Page 4 of 8

copied down to cell P60). The total profit is adoption costs – total costs. Use Accounting format for these cells. For the formula in cell P11, simply subtract N11 (total costs) from O11 (adoption costs).

D. Write an Excel formula or function to calculate the total profit for the month (cell P62 - not copied down or across). Use Accounting format for these cells. For the formula in cell P62, you will use the SUM() function and add all values in the range of P11 through P60 (profit).

E. Consider that this month’s profit is an average month. Write an Excel formula or function to calculate how much money the Perky Pup Dog Shelter will make this year (cell P64 - not copied down or across). Use Accounting format for these cells. For the formula in cell P64, you will use multiply P62 (total profit for the month) by 12.

F. Write an Excel formula or function to calculate the number of dogs that need Rabies shots (cell B62 … not copied down or across). For the formula in cell B62, you will use the COUNTIF() function with a range of F11 through F60 (rabies shot?) and a criteria value of TRUE.

G. Write an Excel formula or function to calculate the average profit by city that the Shelter has earned this month from adoptions where each dog was found (cell B65 and then copied down to cell B69). Use Accounting format for these cells. For the formula in cell B65, you will use the AVERAGEIF() function with a range of E11 through E60 (city), criteria of A65 (city), and average range of P11 through P60 (profit). Be careful to use proper absolute or mixed cell referencing where appropriate so these formulas work when copied down. H. Write an Excel formula or function to calculate the total average profit by city (cell B70 - not copied down or across). Use Accounting format for these cells. For the formula in cell B70, you will use the SUM() function and sum the range from B65 to B69 (Average Profit per City).

The Perky Pup Dog Shelter does not only do adoptions but it is also a local pet store. There are several pet owners who like to shop at the shelter to support the shelter. You will use the Sales worksheet and various Excel formulas or functions to calculate the Sales profit from the store, as follows:

A. First, you need to fill in the proper data for the description, category, and cost items in the Sales worksheet table with existing data that appears in the Items worksheet table. You will do this using a reference function that references the table in the Items worksheet using Item ID as the lookup value. You will write a separate reference function for each column B, C, and D (using a different index number for each) that will populate each column with the data from the table array in the Items worksheet. Each function will be copied down from cell B7 to cell B22, from cell C7 to cell C22, and from cell D7 to cell D22 for each reference function written. The data, once populated, will show all items alphabetically by description. Use Accounting format for the cost. So, for the formula in cell B7, you will use the VLOOKUP() function, with A7 (ItemID) for the lookup value, a range of A2 to B17 in the Items worksheet for the table array, 2 for the column_index_num, and FALSE because you are looking for an exact match only for the ItemID. For the formula in cell C7, you will use the VLOOKUP() function, with A7 (ItemID) for the lookup value, a range of A2 to C17 in the Items worksheet for the table array, 3 for the column_index_num, and FALSE. For the formula in cell D7, you will use the VLOOKUP() function, with A7 (ItemID) for the lookup value, a range of A2 to D17 in the Items worksheet for the table array, 4 for the column_index_num, and FALSE. Be careful to use proper absolute or mixed cell referencing where appropriate so these formulas work when copied down (i.e. in the table array).

B. Using the Individual Sales worksheet that has a line of each item sold for the month by Sale ID

Part II – Profit Analysis – Dog Supply Sales

Page 5 of 8

and Item ID, use an Excel formula or function to count the number sold of each item based on the Item ID (cell E7 and copied down to cell E22). For the formula in cell E7, you will use the COUNTIF() function with a criteria range of B2 to B100 in the Individual Sales worksheet and a criteria of A7 (ItemID). Be careful to use proper absolute or mixed cell referencing where appropriate so this formula works when copied down (i.e. in the criteria range).

C. Each product sold in the store is marked up so that the Perky Pup Dog Shelter will profit from the sale. Tamara has been adding a 14.5% markup to all the products that she carries. Write an Excel formula or function that will compute the selling price of the item (cell F7 and copied down to cell F22). [Hint: To figure this out, you must find (100% of the cost + markup) * the cost of each item]. Be sure to use a cell reference for the markup % and use it in your calculations (a cell has been reserved for this purpose in the Sales worksheet, cell C2). This value will need to be modified later in your analysis. Use Accounting format for these F7 through F22 cells. So, for cell F7, you would use the formula D7 (Cost) multiplied by (1 plus C2 (the markup for items)). Use proper absolute or mixed cell referencing for the C2 cell.

D. Write an Excel formula or function to determine the total revenue from sales of this item (selling price * number sold). Write this formula or function in cell G7 and copy it down to cell G22. Use Accounting format for these cells. For this formula, you would simply multiply F7 (selling price) by E7 (number sold).

E. Write an Excel formula or function to determine the total expenses (cost) from the distributor from the sales of this item (cost * number sold). Write this formula or function in cell H7 and copy it down to cell H22. Use Accounting format for these cells. For this formula, you would simply multiply D7 (cost) by the E7 (number sold).

F. Write an Excel formula or function to determine the profit from the sales of this item (revenue – expenses). Write this formula or function in cell I7 and copy it down to cell I22. Use Accounting format for these cells. For the formula in cell I7, you would subtract H7 (Expenses or cost) from G7 (revenue).

G. Write an Excel formula or function to determine the total monthly profit from all sales (cell I24 - not copied down). Again assume that this month’s data represents an average month. Use Accounting format for these cells. For this formula, you would use the SUM() function and add the range I7 to I22.

H. Write an Excel formula or function to determine the estimated yearly profit for dog supply sales (cell I25 - not copied down). Use Accounting format for this value. For this formula, you would simply multiply I24 (total monthly profit) by 12.

I. Write an Excel formula or function to calculate the total profit for each Sales Category (cell L7

and copied down to cell L10). Use Accounting format for these cells. For the formula in cell L7, you would use the SUMIF() function with a range of C7 to C22 (category), criteria of K7, and sum range of I7 to I22 (profit). Use proper absolute or mixed cell referencing for the ranges so they work when copied down.

J. Create a 3-D pie chart to show the percentage of total profits for each Sales Category (toy, food, etc.). Include a title (Total Profit Sales Items), data labels with category name (food, other, supplies, toy) and percentage for each ($44.60, $127.13, $5.26, and $14.65 respectively).To create the chart, select from cell K6 to L10, then click the insert tab, select the 3-D pie chart symbol from the recommended chart options and choose the 3-D option to it. A chart will appear. Double-click the chart title that is there and then change it to what is specified above.

Page 6 of 8

Part III – Alternate Scenarios

Part IV – Financial Problems

Click the + plus sign next to the chart and click the arrow next to the data labels and then click more options. Select the label options to include the value and category name with a newline as separator and label position outside end. Exit out of this side bar. Move the chart so its upper left corner is at cell N6.

As part of your analysis, you need to estimate the effect of two different possible scenarios on the existing data you have calculated.

Alternate Scenario 1

Copy your original Adopted Dogs worksheet to a new worksheet named Alt Scenario 1 and make the changes there. Move the Alt Scenario 1 worksheet so it comes after the Customers worksheet in this workbook. Tamara would like to reduce the cost of adoptions to $175 for puppies, $125 for teens, and $55 for adults. Find out how these changes will affect the Perky Pup Dog Shelter profits. What are the new monthly and yearly profits using the reduced adoption fees? To do this, simply go into the Alt Scenario 1 worksheet and make the changes to cells F4, F5, and F6 as indicated above. Highlight your new adoption amounts in cells F4, F5, and F6 AND your answers in cells P62 and P64 in yellow.

The community is a big supporter of the Perky Pup Dog Shelter and is having a fund raiser. How much money would have to be raised from the fund raiser to make up the difference in profits if the adoption fees are reduced? Put your answer in cell P65 and highlight your answer in yellow. The formula for cell P65 would simply subtract the P64 cell value in the current worksheet from the Adopted Dogs worksheet cell P64 value.

Alternate Scenario 2

Copy your original Adopted Dogs worksheet to a new worksheet named Alt scenario 2 and make the changes there. Move the Alt Scenario 2 worksheet so it comes after the Alt Scenario 1 worksheet in this workbook. Tamara has been talking to different veterinarians lately to try to reduce the costs of the immunization shots for the dogs. Given Tamara’s important role in the community, these veterinarians are willing to give a price break on the cost of their services to the dogs that are in the shelter. If Tamara would like her monthly profit to be $4850 in cell P62, what would the new fee have to be for the immunization cost per shot in cell B2? Use a goal seek to answer this question. Click on cell P62 in the Alt Scenario 2 worksheet. Click on the Data tab and select the What-If Analysis icon and then the Goal Seek option. A new window pops up. Fill it in as shown below. Highlight your answer in cells B2 and P62 and P64 in green.

The Perky Pup Dog Shelter has been thinking about expanding the shelter building to be able to take in more dogs. The expansion would cost $95,000.

Option 1: USA Bank has offered a loan for a 3.5% annual interest rate compounded monthly for 5 years. What would be the monthly payment? Write a PMT() function in the Financial worksheet in cell

Page 7 of 8

Part V – Pivot Table and Pivot Chart

What To Turn In

H2 to determine the payment and put your cell values and calculation for the financial function in row 2. The present value should be $95,000 (cell B2), the future value should be $0 (cell C2), the annual interest rate should be 3.5% (cell D2), the # compounding periods should be 12 (cell E2), the type 0 (cell F2), and the years 5 (cell G2). Your PMT() function should go in cell H2 and should use as the first argument the annual interest rate divided by the # compounding periods (cell D2 divided by cell E2), the second argument would be the number of years (cell G2) multiplied by the # compounding periods (cell E2), the third argument is the present value (cell B2), the fourth argument is the future value (cell C2), and the last is the type (cell F2). Highlight your payment amount in yellow in cell H2. Does the shelter make enough monthly adoption profits to cover this monthly payment (use original values) TRUE or FALSE? Use a formula in cell I2 to calculate this by taking the monthly profit in the adopted dogs worksheet (cell P62) and check it to see if it is greater than or equal to the payment in cell H2 in this worksheet. Note that the payment in cell H2 is a negative value because it is a payment, so you will need to take the negative of it (-H2) to compare it properly to the profit. Add a text description of the outcome (Yes or No and explanation) in cell J2. Highlight these cells (H2, I2, J2) in yellow. Option 2: As another possibility, Tamara is considering cashing in a CD that she has had for 10 years to pay for the expansion. She invested $50,000 in a CD 10 years ago that paid 6.25% per year compounded monthly. The present value of $50,000 should go in cell B3, the annual interest rate of 6.25% should go in cell D3, the # compounding periods (12) should go in cell E3, the type (0) should go in cell F3, the years (10) should go in cell G3, and the payment (0) should go in cell H3. Use the FV() function in cell C3 to determine the current value of the CD. Your FV() function should go in cell C3 and should use the rate (cell D3 divided by E3) as the first argument, the number of periods (years multiplied by the # compounding periods) as the second argument (cell G2 multiplied by cell E3), the payment as the third argument (cell H3), the present value in the fourth argument (the negative of the cell B3), and the type as the fifth argument (cell F3). Highlight your future value in cell C3 in yellow. Does the CD accrue enough to pay for the expansion? TRUE or FALSE? Use a formula to calculate this in cell I3 by checking if the value in cell C3 is greater than or equal to the $95,000 it would take to complete the expansion as stated above. Add a text description of the outcome (Yes or No and explanation) in cell J3. Highlight these cells (C3, I3, J3) in yellow.

Tamara would like to see the cost, number sold, selling price, revenue, expenses and profit by category. In order to do that, you will need to create a Pivot Table in Excel. Since we feel like having both a Pivot Table and a corresponding Pivot Chart to go with it is beneficial for her analysis, we are going to create both at the same time. To do this, simply go to the Sales worksheet select all data from A6 to I22 in this worksheet. Click on the Insert tab, then on the PivotChart dropdown and select PivotChart & PivotTable. A new window will display. Click the option to put the PivotTable in the existing worksheet (Sales) at location A27. Click OK. The beginnings of a pivot table will appear at A27. Select Category, Cost, Number Sold, Selling Price, Revenue, Expenses (Cost), and Profit in the Pivot Table Field List on the right in this worksheet.

Since Tamara only asked to be able to group these items by Category (Food, Other, Supplies, Toy) and since it is already set to filter on Category, there is nothing else to do. Exit out of the PivotTable Field List sub-window on the right by clicking the X in the upper right corner of this sub-window. Click on and drag the PivotChart down to have its upper left corner at cell H27. Try out the filter by clicking on it under the Row Labels and select only Food and see how both the PivotTable and Pivot Chart change. Reset it to all before completing this task. You are done!

On or before the stated due date, you should submit the following files to Canvas under the Final

Page 8 of 8

Final Notes

Project assignment:

• Your Excel file with all of your analyses in it named PerkyPupDogShelterABC.xlsx where ABC is your initials.

• Start early!! Plan on spending 5-10 hours on this project.

• You may ask your instructor for general advice, but the actual work must be your own. This is a real-world test of the skills you have learned during the semester.

• This is an individual project. You may consult other students for general questions, but your final work must be your own. If two projects look similar or parts of any files are copied from any other source other than what is provided, it will be considered Academic Misconduct. You will receive zero credit.

• Good Luck!!