Question

The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods:

Current assets as of March 31:
Cash	$	8,400
Accounts receivable	$	23,600
Inventory	$	45,000
Building and equipment, net	$	123,600
Accounts payable	$	26,925
Common stock	$	150,000
Retained earnings	$	23,675

The gross margin is 25% of sales.

Actual and budgeted sales data:

March (actual)	$	59,000
April	$	75,000
May	$	80,000
June	$	105,000
July	$	56,000

Sales are 60% for cash and 40% on credit. Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result of March credit sales.

Each month’s ending inventory should equal 80% of the following month’s budgeted cost of goods sold.

One-half of a month’s inventory purchases is paid for in the month of purchase; the other half is paid for in the following month. The accounts payable at March 31 are the result of March purchases of inventory.

Monthly expenses are as follows: commissions, 12% of sales; rent, $3,200 per month; other expenses (excluding depreciation), 6% of sales. Assume that these expenses are paid monthly. Depreciation is $927 per month (includes depreciation on new assets).

Equipment costing $2,400 will be purchased for cash in April.

Management would like to maintain a minimum cash balance of at least $4,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 $20,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:

Using the preceding data:

1. Complete the following schedule: schedule of expected cash collections

2. Complete the following: merchandise purchases budget

3. Complete the following cash budget:

4. Prepare an absorption costing income statement for the quarter ended June 30.

5. Prepare a balance sheet as of June 30.

Answer 1

Sales Budget
	April	May	June	Total
Sales	75,000	80,000	105,000	260,000
Cash Sales - 60%	45,000	48,000	63,000	156,000
Credit Sales - 40%	30,000	32,000	42,000	104,000
Answer 1.
Schedule of Expected Cash Collections
	April	May	June	Total
Cash Sales	45,000	48,000	63,000	156,000
Credit Sales	23,600	30,000	32,000	85,600
Total cash Collections	68,600	78,000	95,000	241,600
Answer 2.
Merchandise Purchase Budget
	April	May	June	Total
Budgeted Cost of Goods Sold - 75% of Sales	56,250	60,000	78,750	195,000
Add: Ending Inventory	48,000	63,000	33,600	33,600
Total Needs	104,250	123,000	112,350	228,600
Less: Beginning Inventory	(45,000)	(48,000)	(63,000)	(45,000)
Required Purchases	59,250	75,000	49,350	183,600
Schedule of Expected Cash Disbursements - Merchandise Purchases
	April	May	June	Total
Cash Payment
March Purchases	26,925			26,925
April Purchases	29,625	29,625		59,250
May Purchases		37,500	37,500	75,000
June Purchases			24,675	24,675
Total Cash Payment to Suppliers	56,550	67,125	62,175	185,850
Selling & Admn. Budget
	April	May	June	Total
Sales Comm. - 12% of Sales	9,000	9,600	12,600	31,200
Rent (Fixed)	3,200	3,200	3,200	9,600
Other Expense - 6% of Sales	4,500	4,800	6,300	15,600
Depreciation	927	927	927	2,781
Total	17,627	18,527	23,027	59,181
Schedule of Cash payments of Selling & Admn. Budget
	April	May	June	Total
Sales Comm. - 12% of Sales	9,000	9,600	12,600	31,200
Rent (Fixed)	3,200	3,200	3,200	9,600
Other Expense - 6% of Sales	4,500	4,800	6,300	15,600
Total	16,700	17,600	22,100	56,400
Answer 3.
Cash budget
	April	May	June	Total
Beginning cash Balance	8,400	4,350	4,625	8,400
Add: Cash Collection	68,600	78,000	95,000	241,600
Total Cash available	77,000	82,350	99,625	250,000
Less: Cash Disbursements
For Inventory	56,550	67,125	62,175	185,850
For Expenses	16,700	17,600	22,100	56,400
For Equipment	2,400	-	-	2,400
Total Cash Disbursement	75,650	84,725	84,275	244,650
Cash Balance Closing	1,350	(2,375)	15,350	5,350
Add: Finance from Bank	3,000	7,000		10,000
Less: Payment to Bank	-	-	(10,000)	(10,000)
Less: Payment of interet - Bank loan	-	-	(230)	(230)
Net Cash Balance Closing	4,350	4,625	5,120	5,120
Income Statement
For the Qtr Ending June 30
Sales		260,000
Cost of Goods Sold - 75% of sales		195,000
Gross Margin		65,000
Less: Selling & Administration Expense
Sales Comm. - 12% of Sales	31,200
Rent (Fixed)	9,600
Other Expense - 6% of Sales	15,600
Depreciation	2,781	59,181
Operating Profit		5,819
Less: Interest Expenses		230
Net Income		5,589
Balance Sheet
As on June 30
Assets
Current Assets
Cash	5,120
Accounts receivables	42,000
Inventory	33,600	80,720
Fixed Assets
Building & Equipment ($123,600 + $2,400)	126,000
Less: Dep.	(2,781)	123,219
Total Assets		203,939
Liabilities
Accounts Payable		24,675
Total liabilities		24,675
Shareholders's Equity
Common Stock	150,000
Retained Earnings	29,264
Total Stockholders equity		179,264
Total liabilities & Stockholders' Equity		203,939		-
Schedule of Retained Earnings
As on June 30
Opening Balance		23,675
Add: net income		5,589
Less: Dividend declared		-
Closing Balance		29,264