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,800		448,000
June (budgeted)	4,500		720,000
July (budgeted)	3,500		719,000
August (budgeted)	3,700		592,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, 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 25% of the next month’s unit sales plus a safety stock of 90 units. The April 30 and May 31 actual inventory levels are consistent with this policy. Selling and administrative expenses for the year are $1,236,000 and are paid evenly throughout the year in cash. The company’s minimum cash balance at month-end is $130,000. This minimum is maintained, if necessary, by borrowing cash from the bank. If the balance exceeds $130,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 $35,500, and the company’s cash balance is $130,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		Schedule of cash collections
					Percent collected in
					April	May	June	July	August
	Credit sales from:				560000	448000	720000	719000	592000
	Cash collected-
	24% collected in the month of sale				134400	107520	172800	172560	142080
	46% collected in the month after sale					257600	206080	331200	330740
	28% collected in the second month after sale						156800	125440	201600
	Total						535680	629200
2		Budgeted ending inventories
					April	May	June	July	August
	Unit sales				3500	2800	4500	3500	3700
	Ending inventory				790	1215	965	1015
	(25% of next month unit sales+90)				(2800*25%)+90	(4500*25%)+90	(3500*25%)+90	(3700*25%)+90
3		Budgeted merchandise purchase
					May	June	July
	Unit sales				2800	4500	3500
	Add: Ending inventory				1215	965	1015
	Total				4015	5465	4515
	Less; beginning inventory
	(Ending inventory of last month)				790	1215	965
	Units to be purchased				3225	4250	3550
	Purchase price per unit				110	110	110
	Total purchase price				354750	467500	390500
4		Schedule of cash payments for purchase
					May	June	July
	Total purchase price				354750	467500	390500
	60% paid in the month of purchase				212850	280500	234300
	40% paid in next month					141900	187000
	Total					422400	421300
5		Cash budget
						June	July
	Beginning cash balance					130000	130000
	Add: Collections from customers					535680	629200
	Total cash available		a			665680	759200
	Less: Cash disbursements:
	Purchases for inventory					422400	421300
	Selling and administrative expenses				(1236000/12)	103000	103000
	Total cash disbursements        b					525400	524300
	Excess (deficiency) of cash available over disbursements c=(a-b)					140280	234900
	Financing:
	Borrowings
	Repayment					-10280	-25220
							(35500-10280)
	Interest		(35500*12%*2/12)				-710
	Total financing		d			-10280	-25930
	Ending cash balance		e=c+d			130000	208970