Case Problem 3

Data File needed for this Case Problem: RoomGroom.xlsx

Room and Groom Room and Groom has been kenneling and grooming small, medium, and large

cats and dogs in Topeka, Kansas, since June 2010. The standard kennel program includes access to

the outside fenced play area, healthy meals, and private rooms. With the deluxe kennel program, the

animal also has a daily playtime with a kennel employee, daily treats, and music or video playing

in its room. Grooming services can occur during a kennel stay or as a standalone service. Samuel

Wooten, the manager of Room and Groom, has been tracking the kennel and grooming services

by month for the past year. Samuel wants you to analyze the data he has collected and create some

preliminary charts. Complete the following:

1. Open the RoomGroom workbook located in the Excel6 > Case3 folder included with your

Data Files, and then save the workbook as RoomGroom 2017 in the location specied by your

instructor.

2. In the Documentation worksheet, enter your name and the date.

3. Group the 12 monthly worksheets to ensure consistency in headings and for ease in entering

formulas. Enter the heading Total in cells A11 and E4. For each month (January through December),

enter formulas to calculate the total for each type of visit (the range B11:D11) and the total for each

type of animal (the range E5:E11).

4. Improve the formatting of the monthly worksheets using the formatting of your choice. Be sure to

include a bottom border in the ranges A4:E4 and A10:E10. Ungroup the worksheets.

5. In the Service by Month worksheet, in the range B5:B16, enter formulas with worksheet references

to display the total grooming services for each month (the formulas will range from =January!B11

through =December!B11). Copy these formulas to the range C5:C16 (Room-Standard) and the

range D5:D16 (Room-Deluxe).

6. In cells A17 and E4, enter the label Total. In the range B17:D17, enter formulas to add the total

for each type of service, and then in the range E5:E17, enter formulas to add the total services

each month by animal type.

7. Add a bottom border to the ranges A4:E4 and A16:E16. Improve the formatting of the Service by

Month worksheet using the formatting of your choice.

8. Create a bar chart or a column chart that compares the types of services by month (the rangeA4:D16).

Include an appropriate chart title and a legend. Format the chart so that it is attractive and effective.

Position the chart below the data.

9. In the Service by Animal worksheet, in the range B5:D10, enter formulas using 3-D cell references

to sum the services for the year for each animal. For example, in cell B5, the formulas for SmallDog

Groom would be =SUM(January:December!B5).

10. In cells A11 and E4, enter the label Total. In the range B11:D11, enter formulas to add the total

by type of service, and then in the range E5:E11, enter formulas to add the total services and

total services by animal type.

11. Add a bottom border to the ranges A4:E4 and A10:E10. Improve the formatting of the Service by

Animal worksheet using the formatting of your choice.

12. Create a pie chart based on the annual total for each animal type. Include an appropriate chart

title and a legend. Format the chart so that it is attractive and effective. Position the pie chart

below the data in the Service by Animal worksheet.

13. Group all of the worksheets except Documentation. Prepare the workbook for printing by

displaying the workbook name and the worksheet name on separate lines in the right section

of the header. Display your name and the current date on separate lines in the right section of

the footer.

14. Save the workbook, and then close it.