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,000
Accounts receivable	$	26,000
Inventory	$	48,600
Building and equipment, net	$	109,200
Accounts payable	$	29,175
Common stock	$	150,000
Retained earnings	$	13,625

1. The gross margin is 25% of sales.

2. Actual and budgeted sales data:

March (actual)	$	65,000
April	$	81,000
May	$	86,000
June	$	111,000
July	$	62,000

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

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

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

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

7. Equipment costing $3,000 will be purchased for cash in April.

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

Schedule of expected cash collections:
	Actual
	March	April	May	June	Total
Total Sales	$65,000	$81,000	$86,000	$111,000	$343,000
Collections from:		April	May	June	Quarter
Cash Sales (20%)		$48,600	$51,600	$66,600	$166,800
Credit Sales (40%, 1-month delay)		26,000	32,400	34,400	92,800
Total Cash Collections		$74,600	$84,000	$101,000	$259,600

Merchandise purchases budget:
	March	April	May	June	July	Total
Total Sales	$65,000	$81,000	$86,000	$111,000	$62,000	$405,000
	March	April	May	June	Quarter
Budgeted COGS (75% of Sales)	$48,750	$60,750	$64,500	$83,250	$208,500
Add desired EI (80% next COGS)	48,600	51,600	66,600	37,200	37,200
Total Needs	$97,350	$112,350	$131,100	$120,450	$245,700
Less BI (80% of this month's COGS)	39,000	48,600	51,600	66,600	48,600
Purchases	$58,350	$63,750	$79,500	$53,850	$197,100
	$29,175

Schedule of expected cash disbursement for merchandise purchases:
	March	April	May	June	Quarter
50% paid in month of purchase	$29,175	$31,875	$39,750	$26,925	$98,550
50% paid In month after purchase		29,175	31,875	39,750	100,800
Total		$61,050	$71,625	$66,675	$199,350

Schedule of expected cash disbursements for selling & administrative
		April	May	June	Quarter
Commission		$9,720	$10,320	$13,320	$33,360
Rent		$3,800	$3,800	$3,800	$11,400
Other Expenses (6% of sales)		$4,860	$5,160	$6,660	$16,680
Total Selling & Admin.		$18,380	$19,280	$23,780	$61,440

Cash Budget:
		April	May	June	Quarter
Cash balance, beginning		$9,000	$4,170	$4,265	$9,000
Add cash collections		74,600	84,000	101,000	259,600
Total cash available		$83,600	$88,170	$105,265	$268,600
Less disbursements:
Inventory purchases		$61,050	$71,625	$66,675	$199,350
Selling & Administrative		$18,380	$19,280	$23,780	$61,440
Equipment purchases		$3,000			$3,000
Total disbursements		$82,430	$90,905	$90,455	$263,790
Net Cash Inflow before Financing		$1,170	($2,735)	$14,810	$4,810
Available for repayment		$0	$0	$14,810	$4,810
Financing:
Borrowings		$3,000	$7,000	$0	$10,000
Repayments	$1,000	0	0	10,000	($10,000)
Interest	1%	0	0	230	($230)
Total Financing		$3,000	$7,000	$10,230	$230
Cash balance, ending		$4,170	$4,265	$4,580	$4,580
Desired minimum cash balance		$4,000	$4,000	$4,000	$4,000
Excess Over Desired Minimum		$170	$265	$580	$580

Shilow Company Absorption Costing Income Statement For the Quarter Ended June 30th
Sales		$278,000
Cost of Goods Sold:
Beginning Inventory	$48,600
Add purchases	197,100
Goods available for sale	$245,700
Ending Inventory	37,200	208,500
Gross Margin		$69,500
Selling & Administrative Expenses		62,259
Net Operating Income		$7,241
Less Interest Expense		(230)
Net Income		$7,011

Shillow Company Balance Sheet
Assets:
Current Assets:
Cash	$4,580
Accounts Receivable	44,400
Inventory	37,200	$86,180
Long-Term Assets:
Net Building and Equipment		111,381
Total Assets		$197,561
Equity:
Current Liabilities:
Accounts Payable		$26,925
Stockholders' Equity
Capital Stock	$150,000
Retained Earnings**	20,636	$170,636
Total Equity		$197,561

**Retained Earnings:
Beginning Balance	$13,625
Plus: Net Income	7,011
Ending Balance	$20,636