Fred's Fine Feathered Friends
- This week we will analyze data related to a fictional veterinarian that
specializes in birds.
- Begin by downloading this
- 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
- 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
- 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
Costin 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
- In column K compute the final charge (base charge + base charge * percent
- 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
- 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
- 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.