You are provided an Excel file entitled InvoicePayments_CSV.csv. The file contains 17,926 records of paid invoices with invoice and payment data in the following eight fields:

• Invoice_number: invoice number
• Invoice_date: date the invoice was created
• CustomerID: unique customer identifier
• Customer_name: name of customer
• Invoice_amount: amount of the invoice
• Payment_terms: payment terms*
• Paid_date: data payment was received
• Payment_amount: amount of the payment received

* Payment terms interpreted as: discount percent / # of days from invoice date to pay to receive discount, # of days from invoice date the net/full amount is due (e.g., 2/15, net 45 is a 2% discount if paid within 15 days of invoice date, otherwise the full amount is due within 45 days of the invoice date)

The current system does not flag payments that inappropriately take discounts or payments that are late. As long as invoices are paid at an amount that is at least the discount amount, no follow-up actions are taken (i.e., there is no check to make sure those that took the discount were entitled to take the discount). Also, payments received after the due date do not incur a late penalty.

As part of a project to redesign the current system, you are asked to determine the impact of these two system deficiencies. Your task is to quantify and highlight the issues by creating reports of the following (these should be four SEPARATE reports please place each one on a separate worksheet labeled as 1, 2, 3, 4 and 5):

1. Invalid discounts taken: payments made AFTER the discount period that still took the discount; you should include the number of such payments and the total amount of inappropriate discounts taken. Show total number and total dollar amount only.
2. The top 5 customers with the highest amount of invalid discounts taken (i.e., the highest dollar amount of invalid discounts taken); list the number of invoices involved and the total dollar amount of inappropriate discounts taken for each of the five customers. The report should include ONLY the top 5 customers.
3. Late payments: invoices paid AFTER the due date; you should include the number of invoices, the total amount of the invoices and the average number of days late.
4. The top 5 customers with the highest amount of late payments (i.e., the highest dollar amount of late payments); list the number of late payments and total dollar amount of the late payments for each of the five customers. The report should include ONLY the top 5 customers.
5. For both late payments and on-time payments, the # of payments and the average days to pay (average number of days between invoice date and payment date).