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	$	500,000	$	700,000	$	400,000	$	300,000
Cost of goods sold		350,000		490,000		280,000		210,000
Gross margin		150,000		210,000		120,000		90,000
Selling and administrative expenses:
Selling expense		70,000		90,000		51,000		30,000
Administrative expense*		40,000		52,800		32,000		28,000
Total selling and administrative expenses		110,000		142,800		83,000		58,000
Net operating income	$	40,000	$	67,200	$	37,000	$	32,000

*Includes $12,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; 80% collected in the first month following the month of sale; and the remaining 10% collected in the second month following the month of sale. February’s sales totaled $140,000, and March’s sales totaled $200,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 $91,000.

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 $70,000.

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

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

The cash balance at March 31 is $42,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			100000	140000	80000	320000
Sales on account
February	(140000*80%*10%)		11200			11200
March	(200000*80%*80%;10%)		128000	16000		144000
April	(400000*10%;80%;10%)		40000	320000	40000	400000
May	(560000*10%;80%)			56000	448000	504000
June	(320000*10%)				32000	32000
Total cash collections			279200	532000	600000	1411200
2)		Merchandise purchase budget
			April	May	June
Budgeted cost of goods sold			350,000	490,000	280,000
Add: ending inventory			98000	56000	42000
Total needs			448,000	546,000	322,000
less:Beginning inventory			70,000	98,000	56,000
Required inventory purchases			378,000	448,000	266,000
b)	Schedule of Expected Cash Disbursements for Merchandise purchase
			April	May	June	Quarter
Beginning accounts payable			91,000			91,000
April purchases			189000	189000		378000
May purchases				224000	224000	448000
June purchases					133000	133000
total cash disbursements			280,000	413000	357000	1,050,000
3)			Cash budget
				April	May	June	Quarter
Beginning cash balance				42,000	40,200	40,400	42,000
Add collections from customers				279200	532000	600000	1411200
total cash available				321,200	572200	640400	1453200
less cash Disbursements
purchase of inventory				280,000	413000	357000	1,050,000
selling expenses				70,000	90,000	51,000	211,000
administrative expenses				28000	40800	20000	88800
land purchases				0	28,000	0	28000
dividends paid				20,000	0	0	20,000
total cash disbursements				398,000	571800	428000	1,397,800
Excess(Deficiency)of cash available				-76,800	400	212400	55,400
Financing
Borrowing				117,000	40,000	0	157,000
Repayment				0	0	-157000	-157000
interest				0	0	-4,310	-4310
total financing				117,000	40,000	-161310	-4,310
ending cash balance				40,200	40,400	51090	51,090
		interest = 117000*1%*3			3510
			40000*1%*2		800
					4310