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,700
Accounts receivable	$	24,800
Inventory	$	46,800
Building and equipment, net	$	116,400
Accounts payable	$	28,050
Common stock	$	150,000
Retained earnings	$	18,650

The gross margin is 25% of sales.

Actual and budgeted sales data:

March (actual)	$	62,000
April	$	78,000
May	$	83,000
June	$	108,000
July	$	59,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,500 per month; other expenses (excluding depreciation), 6% of sales. Assume that these expenses are paid monthly. Depreciation is $873 per month (includes depreciation on new assets).

Equipment costing $2,700 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:

How do I find the beginning inventory?

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

Prepare a balance sheet as of June 30.

Answer 1

	Solution:
Shilow Company
Sales Budget
For the Quarter ended June 30
						April	May	June	Quarter
	Budgeted Sales in Dollars					78000	83000	108000	269000
	Total Budgeted Sales					78000	83000	108000	269000
Shilow Company
Expected Cash Collection
For the Quarter ended June 30
						April	May	June	Quarter
	Account Receivable 3/31(40% of 62000)					24800			24800
	April- Sales
	60%	of	78000			46800			46800
	40%	of	78000				31200		31200
	May- Sales
	60%	of	83000				49800		49800
	40%	of	83000					33200	33200
	June - Sales
	60%	of	108000					64800	64800
	Total Cash Collection					71600	81000	98000	250600
Shilow Company
Inventory Purchase Budget
For the Quarter ended June 30
						April	May	June	Quarter
	Budgeted Cost of Goods Sold(75% of Sales)					58500	62250	81000	201750
	Add: Desired Ending Inventory(80% of Next Month's COGS)					49800	64800	35400	35400
	Total Needs					108300	127050	116400	237150
	Less: Beginning Inventory					46800	49800	64800	46800
	Required Inventory Purchase					61500	77250	51600	190350
Shilow Company
Cash Disbursement for Purchase
For the Quarter ended June 30
						April	May	June	Quarter
	March Accounts Payable-Purchase					28050			28050
	April Purchase(50% 0f 61500)					30750	30750		61500
	May Purchase(50% 77250)						38625	38625	77250
	June Purchase(50% of 51600)							25800	25800
	Total cash Disbursement for Purchase					58800	69375	64425	192600
	*Account Payable of December - for Purchase Tk88150 will be paid in January
Shilow Company
Cash Disbursement for operating Expenses
For the Quarter ended June 30
						April	May	June	Quarter
	Commission (12% of Sales)					9360	9960	12960	32280
	Rent					3500	3500	3500	10500
	Other Expenses(6% of Sales)					4680	4980	6480	16140
	Total Cash Disbursement					17540	18440	22940	58920
Shilow Company
Cash Budget
For the Quarter ended June 30
						April	May	June	Quarter
	Beginning Cash Balance					8700	4260	4445	8700
	Add: Cash Collection					71600	81000	98000	250600
	Total Cash Available					80300	85260	102445	259300
	Less: Cash Disbursement
		Inventory Purchase				58800	69375	64425	192600
		Operating Expense				17540	18440	22940	58920
		Equipment Purchase				2700	0	0	2700
	Total Cash Disbursement					79040	87815	87365	254220
	Excess/Deficiency					1260	-2555	15080	5080
	Financing:
		Borrowing				3000	7000		10000
		Repayments				0	0	-10000	-10000
		Interest(3000*1%*3+7000*1%*2)				0	0	-230	-230
	Total Financing					3000	7000	-10230	-230
	Ending Cash Balance					4260	4445	4850	4850
Shilow Company
Budgeted Income Statement
For the Quarter ended June 30
			Sales					269000
			Less:	Cost of Goods Sold				201750
			Gross Margin					67250
			Less:	Operating Expenses				58920
				Depreciation				2619
			Operating Income					5711
			Less:	Interest Expense				230
			Net Income					5481
Shilow Company
Budgeted Balance Sheet
For the Quarter ended June 30
		Current Assets
			Cash					4850
			Account Receivable(40% of 108000)					43200
			Inventory					35400	(59000 x 0.75 x 0.80)
		Total Current Assets						83450
		Building and Equipment						116481
		(Beginning 116400+New purchase 2700-Depreciation 2619)
		Total Assets						199931
		Liabilities and Equity
			Account Payable(50% of 51600)					25800
		Equity:
			Common Stock					150000
			Retained Earnings:
				Beginning		18650
			add:	Net Income		5481
				Total		24131		24131
		Total Liability and Equity						199931