Page 1 of 6 160 Points The Final Project for this course integrates many of the spreadsheet skills and functions you have learned this semester

Subject:AccountingPrice: Bought3

Share With

Page 1 of 6

160 Points

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

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 to calculate the total profit for the month (cell P62 - not copied down or across). Use Accounting format for these cells.

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.

F. Write an Excel formula or function to calculate the number of dogs that need Rabies shots (cell B62 … not copied down or across).

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.

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.

 

 

 

 

 

Page 4 of 6

Part II – Profit Analysis – Dog Supply Sales

 

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.

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

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).

 

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.

 

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.

 

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.

 

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.

 

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.

 

 

 

Page 5 of 6

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.

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.

J. Create a pie chart to show the percentage of total profits for each Sales Category (toy, food, etc.). Include a title, data labels with category name and percentage. Move the chart so its upper left corner is at cell N6.

Part III – Alternate Scenarios

 

For each alternate scenario below, begin from the original spreadsheet that you have copied and modify the inputs as needed.

As part of your analysis, you need to estimate the effect of two different possible scenarios on the existing data you have calculated. Assume that the changes are made to the data calculated in Part I and are not cumulative (each scenario should be considered independently). Copy your results from the Adopted Dogs worksheet before making changes and name the worksheet Alt Scenario 1 and Alt Scenario 2 so the original analysis is still available in Adopted Dogs. Keep everything in the same Excel workbook that you started with. Each alternate scenario should use the original Adopted Dogs worksheet to start with and should be placed after the Customers worksheet in your workbook.

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 come 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? Highlight your new adoption amounts AND your answers 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? Highlight your answer in yellow.

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 come 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 what would the new fee have to be for the immunization cost per shot? Use a goal seek. Highlight your answer in green.

Part IV – Financial Problems

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 an Excel formula or function in the Financial worksheet to determine the payment and put your cell values and calculation for the financial function

 

 

 

Page 6 of 6

in row 2 in cell H2. Highlight your payment amount in yellow. Does the shelter make enough monthly adoption profits to cover this monthly payment (use original values) TRUE or FALSE? Put the Excel formula or function calculation that results in TRUE or FALSE in cell I2 and a description of the outcome in cell J2. Highlight these cells 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. Write an Excel formula or function in the Financial worksheet to determine the current value (FV) of the CD and put your cell values and calculation for the financial function in row 3 in cell C3. Highlight your current value in yellow. Does the CD accrue enough to pay for the expansion - TRUE or FALSE? Put the Excel formula or function calculation that results in TRUE or FALSE in cell I3 and a description of the outcome in cell J3. Highlight these cells in yellow.

Part V – Pivot Table and Pivot Chart

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 and highlight or 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 it’s 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!

What To Turn In

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

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

 

Final Notes

 

• 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.