Question

Advanced Challenge
Currently, JustLee Books bills customers for orders by enclosing an invoice with each order when it’s shipped. A customer then has 10 days to send in the payment. Ofcourse, this practice has resulted in the company having to list some debts as “uncollectible.” By contrast, most other online booksellers receive payment through acustomer’s credit card at the time of purchase. With this method, although payment would be deposited within 24 hours into JustLee’s bank account, there’s adownside.

When a merchant accepts credit cards for payment, the company processing
the credit card sales (usually called a “credit card clearinghouse”) deducts a 1.5% processing fee from the total amount of the credit card sale.
The management of JustLee Books is trying to determine whether the surcharge for credit card processing is more than the amount usually deemed uncollectible whencustomers are sent an invoice. Historically, the average amount that JustLee Books has lost is about 4% of the total amount due from orders with a higher-than-averageamount due. In other words, usually customers who have an order with a larger-than-average invoice total default on payments.

To determine how much money would be lost or gained by accepting credit card payments, management has requested that you do the following:

1. Determine how much the surcharge would be for all recently placed orders if payment had been made by a credit card.

2. Determine the total amount that can be expected to be written off as uncollectible based on recently placed orders with an invoice total more than the average ofall recently placed orders.

3. Based on the results of these two calculations, you should determine whether the company will lose money by accepting payments via credit card. State your findingsin a memo to management.

Include the SQL statements for calculating the expected surcharge and the expected amount of uncollectible payments.

Answer 1

The total cost of 14 books is 18.75+14.2+37.8+31.4+12.5+47.25+21.8+37.9+48+19+5.32+17.85+15.4+21.85 =349.02

Credit card Surcharge = 349.02 * (1.5/100) = 5.2353

Average of 14 books cost = 349.02/14 = 24.93

Books having cost more than average are book numbers 3,4,6,8 and 9.

The sum of their costs is 37.8+31.4+47.25+37.9+48 = 202.35

Uncollectible amount based on history = 202.35 *(4/100) = 8.094

8.094 > 5.2353

Hence credit card usage is better than old invoice process.

SQL query to get credit card surcharge :

select sum(cost*0.015) from books; ((@@@@@@@@@1.5 percentage=1.5/100=0.015@@@))

SQL query to get uncollectible amount:

select sum(cost*0.04) from books where cost>avg(cost); ((@@@@@@ 4 percentage=4/100=0.04@@@@))

MEMORANDUM

TO : JustLee Books

FROM : ABC

DATE : 3 June 2019

SUBJECT : FINDINGS OF CREDIT CARD USAGE VS OLD BILLING PROCESS

I am writing to inform you that credit card billing process is comparitively better than the old billing process as per the sample data provided by you.

I did the calculations to check which method is comparitively better and it turned out be that the credit card billing process will be far better even with small sample data set provided by you. As per the dataset, if the credit card billing process is used, surcharge lost to the mediator will be 5.2353. If the old billing process is used, the uncollectible amount calculated as per history is 8.094 which is considerably high compared to the earlier one.

So, I suggest you to adopt the credit card billing process as per the data provided by you.