Question

Problem 8-27 Completing a Master Budget [LO8-2, LO8-4, LO8-7, LO8-8, LO8-9, LO8-10]

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

  

Current assets as of March 31:
Cash	$	8,000
Accounts receivable	$	20,000
Inventory	$	36,000
Building and equipment, net	$	120,000
Accounts payable	$	21,750
Capital stock	$	150,000
Retained earnings	$	12,250

  

a.	The gross margin is 25% of sales.
b.	Actual and budgeted sales data:

  

March (actual)	$50,000
April	$60,000
May	$72,000
June	$90,000
July	$48,000

  

c.	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.
d.	Each month’s ending inventory should equal 80% of the following month’s budgeted cost of goods sold.
e.	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.
f.	Monthly expenses are as follows: commissions, 12% of sales; rent, $2,500 per month; other expenses (excluding depreciation), 6% of sales. Assume that these expenses are paid monthly. Depreciation is $900 per month (includes depreciation on new assets).
g.	Equipment costing $1,500 will be purchased for cash in April.
h.	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 data above:
1.	Complete the following schedule.

        

2.	Complete the following:

      

	Budgeted cost of goods sold for April = $60,000 sales × 75% = $45,000.
	Add desired ending inventory for April = $54,000 × 80% = $43,200.

       

3.	Complete the following cash budget: (Borrow and repay in increments of $1,000. Cash deficiency, repayments and interest should be indicated by a minus sign.)

     

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

        

5.	Prepare a balance sheet as of June 30.

     

References

eBook & Resources

Answer 1

1)			Shilow company
			Schedule of Expected cash collections
			April	May	June	Quarter
Cash sales			36000	43200	54000	133200
credit sales			20,000	24000	28800	72,800
total collections			56000	67200	82800	206000
	Accounts receivable = 90000*40%=				36000
2)			Merchandise purchase budget
			April	May	June	Quarter
Budgeted cost of goods sold			45000	54000	67500	166500		36000
Add Desired ending inventory			43200	54000	28,800	28,800
total needs			88200	108000	96300	195300
less beginning inventory			36,000	43,200	54,000	36,000
Required purchases			52,200	64,800	42,300	159,300
		cost of goods sold = 75% of sales
		ending inventory = 80% of following months budgeted cost of goods sold
3)		Schedule of Cash disbursements-Merchandise purhcase
			April	May	June	Quarter
March purchases			21,750			21,750
April purchases			26100	26,100		52200
May purchases				32400	32,400	64800
June purchases					21150	21150
total disbursements			47,850	58500	53550	159,900
	Accounts payable june 30 = 21,150
4)			Cash budget
			April	May	June	Quarter
Beginning cash balance			8,000	4,350	4,590	8,000
Add Cash collectiosn			56000	67200	82800	206000
total cas h available			64,000	71,550	87,390	214,000
less cash disbursements
for inventory			47,850	58500	53550	159,900
for expenses			13300	15460	18700	47460
for equipment			1,500	0	0	1,500
total cash disbursements			62,650	73960	72250	208,860
Excess(Deficiency)of cash			1,350	-2,410	15,140	5,140
Financing:
Borrowings			3,000	7,000	0	10,000
Repayments			0		-10,000	-10,000
interest			0		-230	-230
total financing			3,000	7,000	-10230	-230
Ending cash balance			4,350	4,590	4,910	4,910
	interest = 3000*1%*3=			90
		7000*1%*2=		140
				230
5)			income statement
	Sales					222000
	cost of goods sold
	Beginning inventor				36,000
	Add purchases				159,300
	goods available for sale				195,300
	ending inventory				28,800	166,500
	Gross margin					55,500
	Selling and administrative expense
	commissions				26640
	rent		(2500*3)		7500
	Depreciation		(900*3)		2700
	other expenses				13320	50160
	net operating					5,340
	interest expense					230
	net income					5,110
		Balance sheet
		Assets
		current assets
		Cash					4,910
		Accounts receivable					36,000
		inventory					28,800
		total current assets					69,710
		Building And equipment ,net (120,000+1500-2700)					118800
		total Assets					188,510
		liabilities And stockholder 's Equity
		Accounts payable					21,150
		total current assets					21,150
		Stockholder's Equity
		Capital stock				150,000
		Retained earnings(12,250+5110)				17360	167,360
		total liabilites & stockholders Equity					188,510