Question

Aztec Company sells its product for $160 per unit. Its actual and budgeted sales follow. Units Dollars April (actual) 3,500 $ 560,000 May (actual) 2,600 416,000 June (budgeted) 5,500 880,000 July (budgeted) 4,500 879,000 August (budgeted) 3,600 576,000 All sales are on credit. Recent experience shows that 24% of credit sales is collected in the month of the sale, 46% in the month after the sale, 24% in the second month after the sale, and 6% proves to be uncollectible. The product’s purchase price is $110 per unit. 60% of purchases made in a month is paid in that month and the other 40% is paid in the next month. The company has a policy to maintain an ending monthly inventory of 21% of the next month’s unit sales plus a safety stock of 75 units. The April 30 and May 31 actual inventory levels are consistent with this policy. Selling and administrative expenses for the year are $1,932,000 and are paid evenly throughout the year in cash. The company’s minimum cash balance at month-end is $140,000. This minimum is maintained, if necessary, by borrowing cash from the bank. If the balance exceeds $140,000, the company repays as much of the loan as it can without going below the minimum. This type of loan carries an annual 12% interest rate. On May 31, the loan balance is $49,500, and the company’s cash balance is $140,000. Required: 1. Prepare a schedule that shows the computation of cash collections of its credit sales (accounts receivable) in each of the months of June and July. 2. Prepare a schedule that shows the computation of budgeted ending inventories (in units) for April, May, June, and July. 3. Prepare the merchandise purchases budget for May, June, and July. Report calculations in units and then show the dollar amount of purchases for each month. 4. Prepare a schedule showing the computation of cash payments for product purchases for June and July. 5. Prepare a cash budget for June and July, including any loan activity and interest expense. Compute the loan balance at the end of each month.

Answer 1

1.

Aztec Company
Expected Cash Collections
	June	July
April sales	134400
May sales	191360	99840
June sales	211200	404800
July sales		210960
Expected cash collections $	536960	715600

2.

Aztec Company
Budgeted Ending Inventory (Units)
	April	May	June	July	August
Sales (units)	3500	2600	5500	4500	3600
Desired ending inventory as a percent of next month's unit sales	21%	21%	21%	21%
Ending inventory	546	1155	945	756
Add: Safety stock	75	75	75	75
Budgeted ending inventory (units)	621	1230	1020	831

3.

Aztec Company
Merchandise Purchases Budget
	May	June	July
Sales (units)	2600	5500	4500
Add: Budgeted ending inventory	1230	1020	831
Total merchandise required	3830	6520	5331
Less: Beginning inventory	621	1230	1020
Total budgeted merchandise purchases (units)	3209	5290	4311
Purchase price per unit $	110	110	110
Total budgeted merchandise purchases $	352990	581900	474210

4.

Aztec Company
Expected Cash Payments for Merchandise Purchases
	June	July
May purchases	141196
June purchases	349140	232760
July purchases		284526
Expected cash payments $	490336	517286

5.

Aztec Company
Cash Budget
	June	July
Beginning cash balance	140000	140000
Add: Cash receipts	536960	715600
Total cash available	676960	855600
Less: Cash payments
For merchandise purchases	490336	517286
Selling and administrative expense	161000	161000
Interest expense	495	1644
Total cash payments	651831	679930
Preliminary cash balance	25129	175670
Loan borrowings/(repayments)	114871	-35670
Ending cash balance	140000	140000
Loan balance
Loan balance - Beginning of month	49500	164371
Additional loan (loan repayment)	114871	-35670
Loan balance - End of month	164371	128701

Interest expense:

For June = $49500 x 12% x 1/12 = $495

For July = $164371 x 12% x 1/12 = $1643.71 = $1644