Question

Aztec Company sells its product for $170 per unit. Its actual and budgeted sales follow.

	Units	Dollars
April (actual)	5,000	$	850,000
May (actual)	2,400		408,000
June (budgeted)	5,000		850,000
July (budgeted)	4,000		849,000
August (budgeted)	4,400		748,000

All sales are on credit. Recent experience shows that 28% of credit sales is collected in the month of the sale, 42% in the month after the sale, 28% in the second month after the sale, and 2% 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 24% of the next month’s unit sales plus a safety stock of 135 units. The April 30 and May 31 actual inventory levels are consistent with this policy. Selling and administrative expenses for the year are $1,692,000 and are paid evenly throughout the year in cash. The company’s minimum cash balance at month-end is $120,000. This minimum is maintained, if necessary, by borrowing cash from the bank. If the balance exceeds $120,000, the company repays as much of the loan as it can without going below the minimum. This type of loan carries an annual 13% interest rate. On May 31, the loan balance is $30,500, and the company’s cash balance is $120,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.

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.

	Percent Collected in April May June July August Credit sales from: April May June July August Amount Collected in Total April May June July August Credit sales from: April $850,000 May 408,000 June 850,000 July 680,000 August 748,000

Prepare a schedule that shows the computation of budgeted ending inventories (in units) for April, May, June, and July.

	AZTEC COMPANY Budgeted Ending Inventory For April, May, June and July April May June July Next month's budgeted sales (units) Ratio of inventory to future sales Budgeted "base" ending inventory

Answer 1

	April	May	June	July	August
	Actual	Actual	Budgeted	Budgeted	Budgeted
Units	5000	2400	5000	4000	4400
price per unit	170	170	170	170	170
Total Sales value	850000	408000	850000	680000	748000
Ending inventory	576	1200	960	1056	0
Safety stock	135	135	135	135	135
Current month's sale	5000	2400	5000	4000	4400
Purchases	5711	3024	4760	4096	3344
cost per unit	110	110	110	110	110
Total purchase costs0	628210	332640	523600	450560	367840
Selling and administrative	141000	141000	141000	141000	141000

	April	May	June	July	August	Sept	Oct
Amount collected in cash	Actual	Actual	Budgeted	Budgeted	Budgeted
Total Sales value	850000	408000	850000	680000	748000
April collection	28%	42%	28%
May collection		28%	42%	28%
June collection			28%	42%	28%
July collection				28%	42%	28%
August collection					28%	42%	28%
April collection	238000	357000	238000
May collection		114240	171360	114240
June collection			238000	357000	238000
July collection				190400	285600	190400
August collection					209440	314160	209440
	238000	471240	647360	661640	733040	504560	209440

	April	May	June	July	August
Budgeted ending inventories
Sales	5000	2400	5000	4000	4400
Safety stock	135	135	135	135	135
Ending inventory (24% of sales of next month)	576	1200	960	1056	0
Total	711	1335	1095	1191	135

	April	May	June	July	August
Merchandise Purchase
Ending Inventory	576	1200	960	1056	0
Safety stock	135	135	135	135	135
Sales	5000	2400	5000	4000	4400
Beginning inventory	0	711	1335	1095	1191
Materials to be procured	5711	3024	4760	4096	3344
Cost per unit	110	110	110	110	110
Costs	628210	332640	523600	450560	367840

	April	May	June	July	August	Sept
Amount collected in cash	Actual	Actual	Budgeted	Budgeted	Budgeted
Total purchase costs	628210	332640	523600	450560	367840
April payments	60%	40%
May payments		60%	40%
June payments			60%	40%
July payments				60%	40%
August payments					60%	40%
April payments	376926	251284
May payments		199584	133056
June payments			314160	209440
July payments				270336	180224
August payments					220704	147136
	376926	450868	447216	479776	400928	147136

Cash Budget	June	July	August
Beginning Balance	120000	148644	189508
Collections from sales	647360	661640	733040
Payments for purchases	-447216	-479776	-400928
Selling and administrative expenses	-141000	-141000	-141000
Loans repaid	-30500
Balance	148644	189508	380620
Less: Minimum Balance to be maintained	-120000	-120000	-120000
Balance	28644	69508	260620