APPLY 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 specified 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 i 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 range A4: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 85: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 Small Dog 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.