- This week we will analyze data related to a fictional veterinarian that specializes in birds.
- Begin by downloading this
file.
- All data is fictional
- 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.

- Begin by Inserting the
**Problem Description**and the**Base Cost**in columns H and I- This is based upon the Problem Code and the Problem Table.

- In column J compute the upcharge percentage. This is based upon the pet size (column E) and the size surcharge table.
- In column K compute the final charge (base charge + base charge * percent upcharge)
- 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.
- In column M calculate the cost of the visit. This is the Final Charge minus the Insurance Coverage.
- In Column N calculate the interest rate based on the Finance Period and the Payment Plan table.
- In Column O compute the percent down based on the the Finance Period and the Payment Plan Table.
- In Column P compute the down payment based on the Cost of the visit and the percent down.
- In column Q compute the Loan amount based on the Cost of the visit and the loan down payment.
- In column R compute the monthly payment, based on the Finance Period, Interest Rate (this is an annual rate) and the Loan Amount
- 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.

- 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.

- Insert a new sheet called
**Summary** - Count the number of customers, small, medium and large birds.
- Count the number of customers that qualify for a loan.
- Calculate the sum of all loan payments.
- Calculate the sum of all amounts paid in full (did not receive a loan)
- Calculate the average of all loan payments.
- Calculate the average of all amounts paid in full (did not receive a loan)
- Make sure all charts are properly labeled/titled ...
- Make sure all data is well formatted and visible.

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