Yeopay Plumbing Supply accepts bank credit cards and offers established plumbers charge accounts with terms of 1/eom,

Question:

Yeopay Plumbing Supply accepts bank credit cards and offers established plumbers charge accounts with terms of 1/eom, n/45. Yeopay's experience is that 25% of its sales are for cash and bank credit cards. The remaining 75% are on credit. Of the cash sales, 40% pay cash and the remaining 60% pay with bank credit cards. Yeopay receives payments from the bank on credit card sales at the end of the day. However, Yeopay has to pay 3% for these services. An aging schedule for accounts receivable shows the following pattern on credit sales:

20% pay in the month of sale.

50% pay in the first month following the month of sale.

15% pay in the second month following the month of sale.

12% pay in the third month following the month of sale.

3% are never collected.

All accounts not paid by the end of the second month following the month of sale are considered overdue and are subject to a 2% monthly late charge. Yeopay has prepared the following sales forecasts:

June ................... $60,000

July ..................... 80,000

August ................ 90,000

September ............ 96,000

October ............... 88,000

Required

1. Use Excel to prepare a schedule of cash receipts for September and October.

At the top of a new spreadsheet, create an "Original Data" section with four subheads: Sales Data, Sales Breakdown and Terms, Breakdown of Cash/Bank Credit Card Sales, and Collection of Credit Sales.

Enter all pertinent data listed above.

Create a new section to calculate cash receipts for September with rows for: Cash Sales; Bank Credit Card Sales; Collections of Accounts Receivable (4 rows); and Total Cash Receipts. Next, prepare columns for: Total Sales; Percentage of Sales for cash/bank credit card sales versus credit sales (Sales %); Payment Percentage for the proportions collected (i.e., percentage collected to allow for cash discounts or late charges [Payment %]); and Cash Receipts.

Program your spreadsheet to perform all necessary calculations for determining cash receipts for September. Do not type in any amounts. All the amounts you enter into this new section should derive from data from the Original Data section using a formula.

Verify the accuracy of your spreadsheet by calculating the total cash receipts in September: $86,082.

Create a new section titled October. Program your spreadsheet to perform all necessary calculations for determinations of cash receipts for October and verify the accuracy of your spreadsheet by showing that the amount of total cash receipts for October is $88,141.

2. What is the appropriate accounting treatment for the bank service fees and the cash discounts allowed on collection of receivables?

Accounts Receivable
Accounts receivables are debts owed to your company, usually from sales on credit. Accounts receivable is business asset, the sum of the money owed to you by customers who haven’t paid.The standard procedure in business-to-business sales is that... Aging Schedule
Aging schedule is an accounting table that shows a company’s account receivables. It is an summarized presentation of accounts receivable into a separate time brackets that the rank received based upon the days due or the days past due. Generally...