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,000		320,000
June (budgeted)	5,000		800,000
July (budgeted)	4,000		799,000
August (budgeted)	4,100		656,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, 25% in the second month after the sale, and 5% 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 23% of the next month’s unit sales plus a safety stock of 180 units. The April 30 and May 31 actual inventory levels are consistent with this policy. Selling and administrative expenses for the year are $1,596,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 12% interest rate. On May 31, the loan balance is $34,500, and the company’s cash balance is $120,000.

	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 May June July August

	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 ??? ???

	AZTEC COMPANY Merchandise Purchases Budgets For May, June, and July ??? May June July ??? ??? Required units of available merchandise ??? Budgeted purchases (units) ??? Budgeted cost of merchandise purchases

	Cash payments on product purchases (for June and July) ------------------ Percent Paid in--------------- May June July From purchases in: May June July ------------------ Amount Paid in--------------- Total May June July From purchases in: May June July

	AZTEC COMPANY Cash Budget June and July ??? June July Beginning cash balance ??? Total cash available Cash payments for: ??? ??? ??? ??? Total cash payments 0 0 Preliminary cash balance ??? Ending cash balance $0 $0 Loan balance ??? June July Loan balance - Beginning of month Additional loan (loan repayment) Loan balance - End of month

Answer 1

Part (1)

Calculation of percentage collection
Credit sales from	April	May	June	July	August
April	24%	46%	25%
May		24%	46%	25%
June			24%	46%	25%
July				24%	46%
August					24%

Part (2)

Amount Collected
Credit sales	April	May	June	July	August
April	560000	134400	257600	140000
May	320000		76800	147200	80000
June	800000			192000	368000	200000
July	799000				191760	367540
August	656000					157440
Total	134400	334400	479200	639760	724980

Part (3)

Budgeted Ending inventory
	April	May	June	July
Next month Unit sales	2000	5000	4000	4100
Ratio of inventory	23%	23%	23%	23%
Budgeted base ending inventory	460	1150	920	943
Add:Safety stock	180	180	180	180
Ending inventory	640	1330	1100	1123

Part (4)

Purchase Budget
	May	June	July
Unit sold	2000	5000	4000
Add:Ending inventory	1330	1100	1123
Total units available for sale	3330	6100	5123
Less:Opening invetory	640	1330	1100
Unit to be purchased	2690	4770	4023
Budgeted cost of purchase @ 110/ unit	295900	524700	442530
Opening inventory is the ending inventory of previous month

Part (5)

Cash payment of purchases for June and July
Payment for the months	June	July
May	(295900 x 40%)	118360
June	(524700 x 60%)	314820	(524700 x40%)	209880
July			(442530 x 60%)	265518
	Total	433180	Total	475398

Part (6)

Cash Budget
	June	July	Total
Opening balance	120000	120000	240000
Cllection from clients	479200	639760	1118960
Total	599200	759760	1358960
Payments			0
Purchases	433180	475398	908578
Selling & Admin expense (1596000 /12 months)	133000	133000	266000
Total payments	566180	608398	1174578
Excess cash in hand (599200-566180)	33020	151362	184382
Borrowing to maintain cash balance of $ 120000			0
(120000-33020)	86980	-	86980
Repayment (151362 -120000)	-	30747.06	30747.06
Interest payment		615	615
Ending cash balance	120000	120000	240000

Working notes

Lest 'X' be the loan repayment for calculation of interest

151362- X -0.02X =120000

1.02 X =31362

X=31362/1.02

X = 30747.06

Loan schedule
Loan balance opening	34500
Add:Loan taken in june	86980
Total borrowing	121480
Less:Loan repaid	30747.06
Laon balance in july	90732.94
Interest on repaid amount (30747.06 x 12% for 2 months)	614.9412