Case Problem: C & M Veterinary Clinic
C & M Veterinary Clinic has been treating small and medium-sized animals in the Marlow, Oklahoma, and area for more than 10 years. The staff veterinarians perform all checkups and surgeries, and the staff technician handles all other visits. With the population explosion area, the clinic's patient list has increased beyond the current staff's capabilities. Bessie Neal, the manager of the clinic, has been tracking the clinic's activity by month for the past year. Before meeting with the clinic's veterinarians to discuss adding staff or facilities, she wants you to compile the data she has collected and create some preliminary charts. Complete the following:
1. Open the Clinic workbook located in the Excel ? Case3 folder included with your Data Files, and then save the workbook as Vet Clinic 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 Totals 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. Make sure that you have included a lower border in the ranges A4:E4 and A10:E10. Ungroup the worksheets.
5. In the Service Analysis by Month worksheet, enter formulas with worksheet references in the range B5:B16. (=January! B11 through =December! B 11 ) for the total checkup appointments for each month. Copy these formulas to the range C5:C16 (Surgery) and the range D5:D16 (Technician).
6. In cells A17 and E4, enter the label Total. Enter formulas to add the total by type of appointment (the range B17:E17) and total appointments each month (the range E5:E17). Bold the ranges A4:A17 and B4:E4. Center the range A4:E4. Place a bottom border in the ranges A4:E4 and A16:E16.
7. Create a bar or column chart to compare the type of service by month (the range A4:D16). Include an appropriate chart title and a legend. Make any formatting changes to the chart that you feel necessary to develop an attractive and effective chart. Position the chart below the data.
8. In the Service Analysis by Animal worksheet, create 3-D cell references to total the appointment type for each animal for each month of the year. Formulas for Small Dog would be Checkup =SUM(January:December!B5), Surgery =SUM(January:December!C5), and Technician =SUM(January:December!D5). These formulas can be copied down through all animal types (the range B5:D10).
9. In cells A11 and E4, enter the label Total. Enter formulas to add the total by type of appointment (the range 811:D11) and the total by type of animal (the range E5:E11). Bold the ranges A4:A11 and B4:E4. Center the range A4:E4. Place a bottom border in the ranges A4:E4 and A10:E10.
10. Create a pie chart based on the annual total for each type of animal. Include an appropriate chart title and a legend. Make any formatting changes to the chart that you feel necessary. Position the pie chart below the data in the Service Analysis by Animal worksheet.
11. Group all worksheets except Documentation. Prepare the workbook for printing with the name of the workbook and the name of the worksheet on separate lines in the right section of the header. Display your name and the date on separate lines in the right section of the footer.
Attachment:- clinic.xlsx