Homework 12, Fred's Fine Feathered Friends.

This assignment is due Nov 12 for the day class and Nov 18 for the night class.

Please note, I have a tendency to transpose and mix up cell numbers. The pictures are correct. If you are confused, please ask.

1. This week we will analyze data related to a fictional veterinarian that specializes in birds.
2. Begin by downloading this file.
   • All data is fictional
     • Human names were generated using this site.
     • Bird names were generated here
     • All other data was generated using =rand() and =randbetween()
   • There are two worksheets, Accounts and Assumptions.
   • Accounts contains records for 60 visits to the vet.
     • Column A is the pet name.
     • Column B and C are the owner's name.
     • If the owner has pet insurance, column D contains "Yes", otherwise it contains a "No"
     • Column E contains the pet size.
     • Column F contains a problem code. This describes the reason for the visit.
     • Column G contains the period of time for which the owner wishes to finance the cost of the appointment.
   • Assumptions contains information related to company fees.
     • The Size Surcharge is a table that states the upcharge for small and large birds.
     • Insurance coverage is the amount of the bill covered by insurance, if the owner has pet insurance.
     • The Problem Table describes the reason for the visit and the cost for the initial consultation.
     • The Payment Plan table describes terms for installment loans to cover the price of the visit.
       • Interest rates are annual percentage rates.
       • The customer must make the listed down payment.
       • The bill must exceed the eligible amount for the loan to be offered.
3. Begin by Inserting the Problem Description and the Base Costin columns H and I
   • This is based upon the Problem Code and the Problem Table.
   • 
4. In column J compute the upcharge percentage.
   This is based upon the pet size (column E) and the size surcharge table.
5. In column K compute the final charge (base charge + base charge * percent upcharge)
6. In column L compute the Insurance Payment, if the customer has insurance, this is the Final Charge times the Insurance Coverage amount, otherwise it is 0.
7. In column M calculate the cost of the visit. This is the Final Charge minus the Insurance Coverage.
8. 
9. In Column N calculate the interest rate based on the Finance Period and the Payment Plan table.
10. In Column O compute the percent down based on the the Finance Period and the Payment Plan Table.
11. In Column P compute the down payment based on the Cost of the visit and the percent down.
12. In column Q compute the Loan amount based on the Cost of the visit and the loan down payment.
13. In column R compute the monthly payment, based on the Finance Period, Interest Rate (this is an annual rate) and the Loan Amount
14. In column S decide if the visit qualifies for the the payment plan.
    • If the cost of the visit is less than the Eligible Amount then they do not.
15. In column T compute the Payment Due
    • If the customer qualifies for payments, this is the Monthly Payment, otherwise it is the Cost of the Visit.
16. 
17. Insert a new sheet called Summary
18. Count the number of customers, small, medium and large birds.
19. Count the number of customers that qualify for a loan.
20. Calculate the sum of all loan payments.
21. Calculate the sum of all amounts paid in full (did not receive a loan)
22. Calculate the average of all loan payments.
23. Calculate the average of all amounts paid in full (did not receive a loan)
24. 
25. Make sure all charts are properly labeled/titled ...
26. Make sure all data is well formatted and visible.
27. When you are finished email this document, as an attached excel workbook, to your instructor.