Question

Problem 8-24 Cash Budget with Supporting Schedules [LO8-2, LO8-4, LO8-8]

Garden Sales, Inc., sells garden supplies. Management is planning its cash needs for the second quarter. The company usually has to borrow money during this quarter to support peak sales of lawn care equipment, which occur during May. The following information has been assembled to assist in preparing a cash budget for the quarter:

Budgeted monthly absorption costing income statements for April–July are:

	April		May		June		July
Sales	$	810,000	$	950,000	$	650,000	$	560,000
Cost of goods sold		567,000		665,000		455,000		392,000
Gross margin		243,000		285,000		195,000		168,000
Selling and administrative expenses:
Selling expense		95,000		114,000		76,000		56,000
Administrative expense*		52,500		74,200		47,000		53,000
Total selling and administrative expenses		147,500		188,200		123,000		109,000
Net operating income	$	95,500	$	96,800	$	72,000	$	59,000

*Includes $37,000 of depreciation each month.

Sales are 20% for cash and 80% on account.

Sales on account are collected over a three-month period with 10% collected in the month of sale; 70% collected in the first month following the month of sale; and the remaining 20% collected in the second month following the month of sale. February’s sales totaled $305,000, and March’s sales totaled $320,000.

Inventory purchases are paid for within 15 days. Therefore, 50% of a month’s inventory purchases are paid for in the month of purchase. The remaining 50% is paid in the following month. Accounts payable at March 31 for inventory purchases during March total $146,300.

Each month’s ending inventory must equal 20% of the cost of the merchandise to be sold in the following month. The merchandise inventory at March 31 is $113,400.

Dividends of $44,000 will be declared and paid in April.

Land costing $56,000 will be purchased for cash in May.

The cash balance at March 31 is $66,000; the company must maintain a cash balance of at least $40,000 at the end of each month.

The company has an agreement with a local bank that allows the company to borrow in increments of $1,000 at the beginning of each month, up to a total loan balance of $200,000. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. The company would, as far as it is able, repay the loan plus accumulated interest at the end of the quarter.

Required:

1. Prepare a schedule of expected cash collections for April, May, and June, and for the quarter in total.

2. Prepare the following for merchandise inventory:

a. A merchandise purchases budget for April, May, and June.

b. A schedule of expected cash disbursements for merchandise purchases for April, May, and June, and for the quarter in total.

3. Prepare a cash budget for April, May, and June as well as in total for the quarter.

Answer 1

1)			Schedule of Expected cash collection
			April	May	June	Quarter
Cash sales			162000	190000	130000	482000
Sales on account
February	(305000*80%*20%)		48800			48800
March	(320000*80%*70%;20%)		179200	51200		230400
April	(648000*10%;70%;20%)		64800	453600	129600	648000
May	(760000*10%;70%)			76000	532000	608000
June	(520000*10%)				52000	52000
Total cash collections			454800	770800	843600	2069200
2)		Merchandise purchase budget
			April	May	June
Budgeted cost of goods sold			567,000	665,000	455,000
Add: ending inventory			133000	91000	78400
Total needs			700,000	756,000	533,400
less:Beginning inventory			113,400	133,000	91,000
Required inventory purchases			586,600	623,000	442,400
b)	Schedule of Expected Cash Disbursements for Merchandise purchase
			April	May	June	Quarter
Beginning accounts payable			146,300			146,300
April purchases			293300	293300		586600
May purchases				311500	311500	623000
June purchases					221200	221200
total cash disbursements			439,600	604800	532700	1,577,100
3)			Cash budget
				April	May	June	Quarter
Beginning cash balance				66,000	40,700	40,500	66,000
Add collections from customers				454800	770800	843600	2069200
total cash available				520,800	811500	884100	2135200
less cash Disbursements
purchase of inventory				439,600	604800	532700	1,577,100
selling expenses				95,000	114,000	76,000	285,000
administrative expenses				15500	37200	10000	62700
land purchases				0	56,000	0	56000
dividends paid				44,000	0	0	44,000
total cash disbursements				594,100	812000	618700	2,024,800
Excess(Deficiency)of cash available				-73,300	-500	265400	110,400
Financing
Borrowing				114,000	41,000	0	155,000
Repayment				0	0	-155000	-155000
interest				0	0	-4,240	-4240
total financing				114,000	41,000	-159240	-4,240
ending cash balance				40,700	40,500	106160	106,160
		interest = 114000*1%*3			3420
			41000*1%*2		820
					4240