Fred's Fine Feathered Friends

  1. This week we will analyze data related to a fictional veterinarian that specializes in birds.
  2. Begin by downloading this file.
  3. Begin by Inserting the Problem Description and the Base Costin columns H and I
  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. In Column N calculate the interest rate based on the Finance Period and the Payment Plan table.
  9. In Column O compute the percent down based on the the Finance Period and the Payment Plan Table.
  10. In Column P compute the down payment based on the Cost of the visit and the percent down.
  11. In column Q compute the Loan amount based on the Cost of the visit and the loan down payment.
  12. In column R compute the monthly payment, based on the Finance Period, Interest Rate (this is an annual rate) and the Loan Amount
  13. In column S decide if the visit qualifies for the the payment plan.
  14. In column T compute the Payment Due
  15. Insert a new sheet called Summary
  16. Count the number of customers, small, medium and large birds.
  17. Count the number of customers that qualify for a loan.
  18. Calculate the sum of all loan payments.
  19. Calculate the sum of all amounts paid in full (did not receive a loan)
  20. Calculate the average of all loan payments.
  21. Calculate the average of all amounts paid in full (did not receive a loan)
  22. Make sure all charts are properly labeled/titled ...
  23. Make sure all data is well formatted and visible.

When you are finished compare to the video of the solution.