Budgeted Cash Receipts: Cash Discounts Allowed on Receivables Yeopay Plumbing Supply accep...

Budgeted Cash Receipts: Cash Discounts Allowed on Receivables 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:

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:

Required

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

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

---

b. Enter all pertinent data listed above.

---

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

---

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

---

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

---

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