Question

The following data have been provided to you: (all amounts are in dollars)

                                                                  April   May
Sales 150,000 157,500
Merchandise purchases 107,000 112,400
Operating expenses:        
     Payroll 13,600         14,280
     Advertising 5,400           5,700
     Rent 2,500           2,500
     Depreciation 7,500           7,500

End of April balances:        
     Cash 30,000    
     Bank loan payable     26,000

Additional data:

Sales are 40% cash and 60% on credit for each month. The collection pattern for credit sales is 50% in the month following the sale and 50% in the 2nd month following the sale. Total sales in March were $125,000.

Purchases are all on credit, with 40% paid in the month of purchase and the balance paid in the following month.

Operating expenses are paid in the month they are incurred.

The company declared a dividend of $8,000 in April that will be paid in May.

In May, the company signed a 3-month note payable when purchasing a machine costing $12,000.

A minimum cash balance of $25,000 is required at the end of each month.

Loans are used to maintain the minimum cash balance when necessary. At the end of each month, interest of 1% per month is accrued on the outstanding loan balance that exists as of the beginning of the month. Repayments are made along with any interest accrued whenever excess cash is available.

Prepare the company's cash budget for May.

Show the ending loan balance at May 31.

Answer 1

		CASH BUDGET
		Month of May
			April	May	June	July
	A	Sales	150,000	112,400
	B=0.4*A	Cash sales	60,000	44,960
	C=0.6*A	Credit sales	90,000	67,440
	D	Collection for April credit sales		45,000	45,000
	E	Collection for May credit sales			33,720	33,720
	F=B+D	Total Cash receipt in May		89,960
	G	Beginning of the month balance		30,000
	H=F+G	TotalCash vailable		119,960
		Cash disbursements:
	I	Merchandise purchase	107,000	112,000
	J=0.4*I	Payment within the month of purchase	42,800	44,800
	K	Payment in the following month		64,200	67,200
	L=J+K	Total Cash payment for purchase		109,000
		Cash payment for Operating expenses:
	M	Payroll		14,280
	N	Advertising		5,700
	P	Rent		2,500
		Note: For depreciation there is no cash payment
	Q=M+N+P	Total cash payment for operating expenses		22,480
	R	Cash payment for dividend		8,000
	S=L+Q+R	Total Cash disbursement in May		139,480
	T=H-S	Excess /(Shortage )of Cash		(19,520)
	U	Minimum Ending Balance required		25,000
	V=U-T	Amount of Loan to be taken for min.balance		44,520
	W	Outstanding Loan of April		26,000
	X=0.01*W	Accrued interest for April outstanding		260
	Y	Note payable for machine purchase		12,000
	Z=V+W+X+Y	Ending Loan Balance		82,780
		Ending Cash balance		25,000