Madeleine Bohne, president of the retailer Bohne Products, hasjust approached the company's bank with a request for a $34,000,90-day loan. The purpose of the loan is to assist the company inacquiring inventories in support of peak April sales. Because thecompany has had some difficulty in paying off its loans in thepast, the loan officer has asked for a cash budget to helpdetermine whether the loan should be made. The following data areavailable for the months AprilâJune, during which the loan will beused:
a.
On April 1, the start of the loan period, the cash balance willbe $29,000. Accounts receivable on April 1 will total $135,000, ofwhich $127,500 will be collected during April and $5,000 will becollected during May. The remainder will be uncollectible.
b.
Past experience shows that 19% of a month's sales are collectedin the month of sale, 74% in the month following sale, and 4% inthe second month following sale. The other 3% represents bad debtsthat are never collected. Budgeted sales and expenses for thethree-month period follow:
April May June
Sales (all on account) $ 206,000 $316,000 $ 346,000
Merchandise purchases $ 119,500 $169,500 $ 149,500
Payroll $ 9,000 $ 9,000 $ 8,000
Lease payments $ 13,300 $13,300 $ 13,300
Advertising $ 71,500 $ 74,200 $ 57,200
Equipment purchases $ 8,600 â â
Depreciation $ 9,600 $ 9,600 $ 9,600
c.
Merchandise purchases are paid in full during the monthfollowing purchase. Accounts payable for merchandise purchases onMarch 31, which will be paid during April, total $108,200.
d.
In preparing the cash budget, assume that the $34,000 loan willbe made in April and repaid in June. Interest on the loan willtotal $820.
Required:
1.
Prepare a schedule of expected cash collections for April, May,and June and for the three months in total. (Leave no cells blank -be certain to enter "0" wherever required.)
Schedule of Expected Cash Collections
April May June Quarter -Total
From accounts receivable $ $ $ $
From April sales
From May sales
From June sales
Total cash collections $ $ $ $
2.
Prepare a cash budget, by month and in total, for thethree-month period. Assume that expenses are paid in the monthincurred. (Input all amounts as positive values except cashdeficiency, repayments and interest which should be indicated by aminus sign. Leave no cells blank - be certain to enter "0" whereverrequired. Total Financing should be indicated with a minus signwhen the company is repaying amounts that were previouslyborrowed.)
Bohne Products
Cash Budget
April May June Quarter - Total
Cash balance, beginning $ $ $ $
Add receipts:
Collections from customers
Total available
Less disbursements:
Merchandise purchases
Payroll
Lease payments
Advertising
Equipment purchases
Total disbursements
Excess (deficiency) of receipts
over disbursements
Financing:
Borrowings
Repayments
Interest
Total financing
Cash balance, ending $ $ $ $
Madeleine Bohne, president of the retailer Bohne Products, hasjust approached the company's bank with a request for a $34,000,90-day loan. The purpose of the loan is to assist the company inacquiring inventories in support of peak April sales. Because thecompany has had some difficulty in paying off its loans in thepast, the loan officer has asked for a cash budget to helpdetermine whether the loan should be made. The following data areavailable for the months AprilâJune, during which the loan will beused:
a.
On April 1, the start of the loan period, the cash balance willbe $29,000. Accounts receivable on April 1 will total $135,000, ofwhich $127,500 will be collected during April and $5,000 will becollected during May. The remainder will be uncollectible.
b.
Past experience shows that 19% of a month's sales are collectedin the month of sale, 74% in the month following sale, and 4% inthe second month following sale. The other 3% represents bad debtsthat are never collected. Budgeted sales and expenses for thethree-month period follow:
April May June
Sales (all on account) $ 206,000 $316,000 $ 346,000
Merchandise purchases $ 119,500 $169,500 $ 149,500
Payroll $ 9,000 $ 9,000 $ 8,000
Lease payments $ 13,300 $13,300 $ 13,300
Advertising $ 71,500 $ 74,200 $ 57,200
Equipment purchases $ 8,600 â â
Depreciation $ 9,600 $ 9,600 $ 9,600
c.
Merchandise purchases are paid in full during the monthfollowing purchase. Accounts payable for merchandise purchases onMarch 31, which will be paid during April, total $108,200.
d.
In preparing the cash budget, assume that the $34,000 loan willbe made in April and repaid in June. Interest on the loan willtotal $820.
Required:
1.
Prepare a schedule of expected cash collections for April, May,and June and for the three months in total. (Leave no cells blank -be certain to enter "0" wherever required.)
Schedule of Expected Cash Collections
April May June Quarter -Total
From accounts receivable $ $ $ $
From April sales
From May sales
From June sales
Total cash collections $ $ $ $
2.
Prepare a cash budget, by month and in total, for thethree-month period. Assume that expenses are paid in the monthincurred. (Input all amounts as positive values except cashdeficiency, repayments and interest which should be indicated by aminus sign. Leave no cells blank - be certain to enter "0" whereverrequired. Total Financing should be indicated with a minus signwhen the company is repaying amounts that were previouslyborrowed.)
Bohne Products
Cash Budget
April May June Quarter - Total
Cash balance, beginning $ $ $ $
Add receipts:
Collections from customers
Total available
Less disbursements:
Merchandise purchases
Payroll
Lease payments
Advertising
Equipment purchases
Total disbursements
Excess (deficiency) of receipts
over disbursements
Financing:
Borrowings
Repayments
Interest
Total financing
Cash balance, ending $ $ $ $
Related questions
P8-23 - Please explain how you arrived at your answers.Thank you!
| Problem 8-23 Schedule of Expected Cash Collections; Cash Budget[LO2, LO8]
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Garden Sales, Inc., sells garden supplies. Management isplanning its cash needs for the second quarter. The company usuallyhas to borrow money during this quarter to support peak sales oflawn care equipment, which occur during May. The followinginformation has been assembled to assist in preparing a cash budgetfor the quarter: |
| a. | Budgeted monthly absorptioncosting income statements for AprilâJuly are: |
| April | May | June | July | |||||
| Sales | $ | 550,000 | $ | 750,000 | $ | 450,000 | $ | 350,000 |
| Cost of goodssold | 385,000 | 525,000 | 315,000 | 245,000 | ||||
| Gross margin | 165,000 | 225,000 | 135,000 | 105,000 | ||||
| Selling andadministrative expenses: | ||||||||
| Selling expense | 75,000 | 95,000 | 56,000 | 35,000 | ||||
| Administrativeexpense* | 42,500 | 56,800 | 35,000 | 33,000 | ||||
| Total selling andadministrative expenses | 117,500 | 151,800 | 91,000 | 68,000 | ||||
| Net operatingincome | $ | 47,500 | $ | 73,200 | $ | 44,000 | $ | 37,000 |
| *Includes $17,000 ofdepreciation each month. |
| b. | Sales are 20% for cash and 80%on account. |
| c. | Sales on account are collected over a three-month period with10% collected in the month of sale; 80% collected in the firstmonth following the month of sale; and the remaining 10% collectedin the second month following the month of sale. Februaryâs salestotaled $165,000, and Marchâs sales totaled $225,000. |
| d. | Inventory purchases are paid for within 15 days. Therefore, 50%of a monthâs inventory purchases are paid for in the month ofpurchase. The remaining 50% is paid in the following month.Accounts payable at March 31 for inventory purchases during Marchtotal $101,500. |
| e. | Each monthâs ending inventory must equal 20% of the cost of themerchandise to be sold in the following month. The merchandiseinventory at March 31 is $77,000. |
| f. | Dividends of $25,000 will bedeclared and paid in April. |
| g. | Land costing $33,000 will bepurchased for cash in May. |
| h. | The cash balance at March 31 is $47,000; the company mustmaintain a cash balance of atleast $40,000 at the end of eachmonth. |
| i. | The company has an agreement with a local bank that allows thecompany to borrow in increments of $1,000 at the beginning of eachmonth, up to a total loan balance of $200,000. The interest rate onthese loans is 1% per month and for simplicity we will assume thatinterest is not compounded. The company would, as far as it isable, repay the loan plus accumulated interest at the end of thequarter. |
| Required: | |
| 1. | Prepare a schedule of expected cash collections for April, May,and June, and for the quarter in total. |
| 2. | Prepare the following for merchandise inventory: |
| a. | A merchandise purchases budget for April, May, and June. |
| b. | A schedule of expected cash disbursements for merchandisepurchases for April, May, and June, and for the quarter intotal. |
| 3. | Prepare a cash budget for April, May, and June as well as intotal for the quarter. (Cash deficiency, repayments andinterest should be indicated by a minus sign.) |
