Question

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:

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

	April		May		June		July
Sales	$	510,000	$	1,040,000	$	490,000	$	390,000
Cost of goods sold		357,000		728,000		343,000		273,000
Gross margin		153,000		312,000		147,000		117,000
Selling and administrative expenses:
Selling expense		99,000		99,000		60,000		39,000
Administrative expense*		44,500		60,000		37,400		37,000
Total selling and administrative expenses		143,500		159,000		97,400		76,000
Net operating income	$	9,500	$	153,000	$	49,600	$	41,000

*Includes $22,000 of depreciation each month.

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

3. 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 $205,000, and March’s sales totaled $245,000.

4. 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 $104,300.

5. 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 $71,400.

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

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

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

9. 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

The company’s president is interested in knowing how reducing inventory levels and collecting accounts receivable sooner will impact the cash budget. He revises the cash collection and ending inventory assumptions as follows:

1. Sales continue to be 20% for cash and 80% on credit. However, credit sales from April, May, and June are collected over a three-month period with 25% collected in the month of sale, 65% collected in the month following sale, and 10% in the second month following sale. Credit sales from February and March are collected during the second quarter using the collection percentages specified in the main section.

2. The company maintains its ending inventory levels for April, May, and June at 15% of the cost of merchandise to be sold in the following month. The merchandise inventory at March 31 remains $71,400 and accounts payable for inventory purchases at March 31 remains $104,300.

Required:

1. Using the president’s new assumptions in (a) above, prepare a schedule of expected cash collections for April, May, and June and for the quarter in total.

2. Using the president’s new assumptions in (b) above, 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. Using the president’s new assumptions, prepare a cash budget for April, May, and June, and for the quarter in total.

Answer 1

	April	May	June	July
Sales	510000	1040000	490000	390000
Cash sales [20%]	102000	208000	98000	78000
Credit sales [80%]	408000	832000	392000	312000
Cash collections
Cash sales	102000	208000	98000	78000
Credit sales
Feb	32800
Mar	137200	39200
April	102000	265200	40800
May		208000	540800	83200
June			98000	254800
July				78000
Total cash collections	374000	720400	777600	494000
Purchases
	April	May	June	July
COGS	357000	728000	343000	273000
Desired ending inventory	109200	51450	40950	0
Total needed	466200	779450	383950	273000
Beginning inventory	71400	109200	51450	40950
Total Purchases	394800	670250	332500	232050
Cash payments for purchases
Mar	104300
April	197400	197400
May		335125	335125
June			166250	166250
July				116025
Total cash payments	301700	532525	501375	282275
Cash Budget
				Total
Beginning cash balance	51000	40800	40395	51000
Cash collections	374000	720400	777600	1872000
Total cash available	425000	761200	817995	1923000
Cash payments
For inventory purchase	301700	532525	501375	1335600
For selling expense	99000	99000	60000	258000
For admin expnse	22500	38000	15400	75900
For dividends	29000			29000
For land		37000		37000
Total cash payments	452200	706525	576775	1735500
Minimum cash balance	40000	40000	40000	40000
Surplus(deficit)	-67200	14675	201220	147500
Finance costs
Borrowings	68000			68000
Repayments		-14000	-54000	-68000
Interest		-280	-1620	-1900
Total finances	68000	-14280	-55620	-1900
Ending balance	40800	40395	185600	185600

Old:

	April	May	June	July
Sales	510000	1040000	490000	390000
Cash sales [20%]	102000	208000	98000	78000
Credit sales [80%]	408000	832000	392000	312000
Cash collections
Cash sales	102000	208000	98000	78000
Credit sales
Feb	32800
Mar	137200	39200
April	40800	285600	81600
May		83200	582400	166400
June			39200	274400
July				31200
Total cash collections	312800	616000	801200	550000
Purchases
	April	May	June	July
COGS	357000	728000	343000	273000
Desired ending inventory	145600	68600	54600	0
Total needed	502600	796600	397600	273000
Beginning inventory	71400	145600	68600	54600
Total Purchases	431200	651000	329000	218400
Cash payments for purchases
Mar	104300
April	215600	215600
May		325500	325500
June			164500	164500
July				109200
Total cash payments	319900	541100	490000	273700

Cash Budget
Beginning cash balance	51000	40400	40300
Cash collections	312800	616000	801200
Total cash available	363800	656400	841500
Cash payments
For inventory purchase	319900	541100	490000
For selling expense	99000	99000	60000
For admin expnse	22500	38000	15400
For dividends	29000
For land		37000
Total cash payments	470400	715100	565400
Minimum cash balance	40000	40000	40000
Surplus(deficit)	-146600	-98700	236100
Finance costs
Borrowings	147000	99000		exceeds 200000
Repayments			-194000
Interest
Total finances	147000	99000	-194000
Ending balance	40400	40300	82100