Budgeted Cash Receipts: Cash Discounts Allowed on Receivables Yeopay PlumbingSupply accepts bank credit cards and offers established plumbers charge accounts with terms of1/eom, n/45. Yeopay’s experience is that 25% of its sales are for cash and bank credit cards. Theremaining 75% are on credit. Of the cash sales, 40% pay cash and the remaining 60% pay with bankcredit 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 showsthe 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 salesforecasts:June $60,000July 80,000August 90,000September 96,000October 88,000Required1. 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 CreditSales.b. Enter all pertinent data listed above.c. Create a new section to calculate cash receipts for September with rows for: Cash Sales, Bank CreditCard Sales, Collections of Accounts Receivable (4 rows), and Total Cash Receipts. Next, preparecolumns 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 cashdiscounts or late charges [Payment %]), and Cash Receipts.d. Program your spreadsheet to perform all necessary calculations for determining cash receipts forSeptember. Do not type in any amounts. All the amounts you enter into this new section shouldderive 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 calculationsfor 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.
Master Budget
A master budget can be defined as an estimation of the revenue earned or expenses incurred over a specified period of time in the future and it is generally prepared on a periodic basis which can be either monthly, quarterly, half-yearly, or annually. It helps a business, an organization, or even an individual to manage the money effectively. A budget also helps in monitoring the performance of the people in the organization and helps in better decision-making.
Sales Budget and Selling
A budget is a financial plan designed by an undertaking for a definite period in future which acts as a major contributor towards enhancing the financial success of the business undertaking. The budget generally takes into account both current and future income and expenses.
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
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.
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.
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 3 images
