Question

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

Current assets as of March 31:
Cash	$	9,100
Accounts receivable	$	26,400
Inventory	$	49,200
Building and equipment, net	$	106,800
Accounts payable	$	29,550
Common stock	$	150,000
Retained earnings	$	11,950

The gross margin is 25% of sales.

Actual and budgeted sales data:

March (actual)	$	66,000
April	$	82,000
May	$	87,000
June	$	112,000
July	$	63,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,900 per month; other expenses (excluding depreciation), 6% of sales. Assume that these expenses are paid monthly. Depreciation is $801 per month (includes depreciation on new assets).

Equipment costing $3,100 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 schedule of expected cash collections.

2. Complete the merchandise purchases budget and the schedule of expected cash disbursements for merchandise purchases.

3. Complete the 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

1) Schedule of Expected Cash Collections (Amounts in $)

Particulars	April	May	June	Total
Cash Sales (60% of sales)	49,200	52,200	67,200	168,600
Collection from Accounts Receivable (40% of previous month sales)	26,400	32,800	34,800	94,000
Total expected cash collections	75,600	85,000	102,000	262,600

2) Merchandise Purchase Budget (Amounts in $)

Particulars	April	May	June	Total
Budgeted Cost of goods sold (75% of Sales)	61,500	65,250	84,000	210,750
Add: Ending Inventory (80% of next month COGS)	52,200	67,200	37,800	37,800
Less: Beginning Inventory	(49,200)	(52,200)	(67,200)	(49,200)
Purchase Budget	64,500	80,250	54,600	199,350

Schedule of Expected Cash Disbursements (Amounts in $)

Particulars	April	May	June	Total
Expected Cash Disbursements:
For March (50% of Purchases in March)	29,550	-	-	29,550
For the month of April	32,250	32,250	-	64,500
For the month of May	-	40,125	40,125	80,250
For the month of June	-	-	27,300	27,300
Total Cash Disbursements for Purchases	61,800	72,375	67,425	201,600

3) Shilow Company   

Cash Budget for the Quarter Ending June 30 (Amounts in $)

Particulars	April	May	June	Total
Beginning Cash Balance	9,100	4,140	4,205	9,100
Add: Expected Cash Collections	75,600	85,000	102,000	262,600
Total Cash Available (A)	84,700	89,140	106,205	271,700
Less: Expected Cash Disbursements
For Purchases	61,800	72,375	67,425	201,600
Commission (12% of sales)	9,840	10,440	13,440	33,720
Rent	3,900	3,900	3,900	11,700
Other Expenses (6% of sales)	4,920	5,220	6,720	16,860
Purchase of Equipment	3,100	-	-	3,100
Total Cash Disbursements (B)	83,560	91,935	91,485	266,980
Excess or (Deficiency) of cash (A-B)	1,140	(2,795)	14,720	4,720
Financing:
Add: Borrowings (Minimum cash balance-Excess/deficiency) rounding up to next thousand	3,000	7,000	-	10,000
Less: Repayments	-	-	(10,000)	(10,000)
Less: Interest	-	-	(230)	(230)
Ending Cash Balance	4,140	4,205	4,490	4,490

4) Absorption Costing Income Statement (Amounts in $)

Particulars	Amounts
Sales (82,000+87,000+112,000)	281,000
Less: Cost of Goods Sold (75% of sales)	(210,750)
Gross Profit (A)	70,250
Less: Expenses
Commission (12% of sales)	(33,720)
Interest on Loan [(3,000*3%)+(7,000*2%)]	(230)
Depreciation (801*3)	(2,403)
Rent	(11,700)
Other Expenses (6% of sales)	(16,860)
Total Expenses (B)	(64,913)
Net Income	5,337