Question

	April	May	June	July
Sales	600,000	900,000	500,000	400,000
Cost of Goods sold	420,000	630,000	350,000	280,000
Gross Margin	180,000	270,000	150,000	120,000
Selling and admin expenses
selling expense	79,000	120,000	62,000	51,000
administrative	45,000	52,000	41,000	38,000
total selling and administrative expenses	124,000	172,000	103,000	89,000
net operating income	56,000	98,000	47,000	31,000

a. sales are 20% for cash and 80% on account

b. sales on account are collected over a three month period with 10% collected in the month of sale: 70% in the first month following the sale, and the remaining 20% collected in the second month following the sale. Feb. sales were 200,000 and march was 300,000

c. Inventory purchases are paid 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 on March 31 for the month of March was 126,000

d. 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 was 84,000

e. dividends of 49,000 will be declared and paid in April

f. Land costing 16,000 will be purchased for cash in May

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

h. 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% each 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 each quarter.

1. Prepare a merchandise purchase budget for April, May and June.

2. Prepare 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 the month of May

Answer 1

Schedule of cash receipts						Calculation
Workings	April	May	June	Quarter		April	May	June
Sales	600,000	900,000	500,000	2,000,000
Cash receipts
Cash sales	120,000	180,000	100,000	400,000		600000*20%	900000*20%	500000*20%
Feb sales	32,000	-	-	32,000		=200000*80%*20%
March sales	168,000	48,000	-	216,000		=300000*80%*70%	=300000*80%*20%
April sales	48,000	336,000	96,000	480,000		=600000*80%*10%	=600000*80%*70%	=600000*80%*20%
May sales	-	72,000	504,000	576,000			=900000*80%*10%	=900000*80%*70%
June sales	-	-	40,000	40,000				=500000*80%*10%
Total collections	368,000	636,000	740,000	1,744,000
Inventory Purchase budget
Req 1	April	May	June	Quarter		April	May	June
Budgeted COGS	420,000	630,000	350,000	1,400,000
Add Desired End Inv	126,000	70,000	56,000	56,000		=630000*20%	=350000*20%	=280000*20%
Inventory Needed	546,000	700,000	406,000	1,456,000
Less Beginning Inv	84,000	126,000	70,000	84,000			End Inv of April	End Inv of May
Required Purchases	462,000	574,000	336,000	1,372,000
Schedule of cash payments budget for Inv Purchases
Req 2	April	May	June	Quarter		April	May	June
Accounts Payable	126,000	-	-	126,000
April purchases	231,000	231,000	-	462,000		=462000*50%	=462000*50%
May purchases	-	287,000	287,000	574,000			=574000*50%	=574000*50%
June purchases	-	-	168,000	168,000				=336000*50%
Total disbursements for pur	357,000	518,000	455,000	1,330,000
Cash budget
Req 3	April	May	June	Quarter		April	May	June
Cash balance, beginning	52,000	40,000	20,000	52,000
Add Cash collections	368,000	636,000	740,000	1,744,000
Total cash available	420,000	676,000	760,000	1,796,000
Less cash payments				-
Inventory purchases	357,000	518,000	455,000	1,330,000
Selling expense	79,000	120,000	62,000	261,000
Admin expense	45,000	52,000	41,000	138,000
Land	-	16,000	-	16,000
Dividends	49,000	-	-	49,000
Total budgeted payments	530,000	706,000	558,000	1,794,000
Payments minus receipts	(110,000)	(30,000)	202,000	2,000
Financing
Borrowings	150,000	50,000		200,000
Repayments			(156,500)	(156,500)
Interest			(5,500)	(5,500)				=-(150000*1%*3)-(50000*1%*2)
Total Financing	150,000	50,000	(162,000)	38,000
Cash balance, ending	40,000	20,000	40,000	40,000
Minimum Loan Balance	40,000	40,000	40,000	40,000
Excess (shortage)	-	(20,000)	-	-
Based on budgeted cash receipts and payments, the company will not be able to maintain $40,000 minimum balance
As the company can only have total loan balance upto $200,000.
Already $150,000 has been borrowed in April, so can only borrow remaining loan limit of $50,000