Question

Problem 8-29 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	$	7,400
Accounts receivable	$	19,600
Inventory	$	39,000
Building and equipment, net	$	126,000
Accounts payable	$	23,175
Common stock	$	150,000
Retained earnings	$	18,825

The gross margin is 25% of sales.

Actual and budgeted sales data:

March (actual)	$	49,000
April	$	65,000
May	$	70,000
June	$	95,000
July	$	46,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, $2,200 per month; other expenses (excluding depreciation), 6% of sales. Assume that these expenses are paid monthly. Depreciation is $945 per month (includes depreciation on new assets).

Equipment costing $1,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 (Expected Cash Collection):

2. Complete the following (Merchandise Purchase Budget and Schedule of Expected cash disbursments - Merchandise Purchase Budget) :

3. Complete the following cash budget:

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

Answer 1

Schedule of Expected cash Collections
	april	may	june	Quarter
Sales	65000	70000	95000	230000
Cash Sales 60%	39000	42000	57000	138000
Credit sales 40% in next month collection	19600	26000	28000	73600
Total collections	58600	68000	85000	211600
2
Merchandise purchase BUDGET
	april	may	june	Quarter	July
Budgeted cost of goods sold (75% of sales)	48750	52500	71250	172500	34500
Add: desired ending inventory (80% of next month)	42000	57000	27600	27600
Total Needs	90750	109500	98850	299100
Less: beginning inventory	-39000	-42000	-57000	-57000
required purchases	51750	67500	41850	161100
Schedule of cash disbursements- Merchandise purchases
	april	may	june	Quarter
March purchases	23175			23175
April Purchases (50% in current and balance in next month)	25875	25875		51750
May purchases		33750	33750	67500
June purchases			20925	20925
Total Disbursements	49050	59625	54675	163350
3
SHILOW COMPANY
CASH BUDGET
	april	may	june	Quarter
Beginning cash Blance	7400	4650	4225	7400
Add: Collections	58600	68000	85000	211600
Total Cash Available	66000	72650	89225	219000
Less: Cash Disbursements
For Inventory	49050	59625	54675	163350
For Expenses (2200+(12%+6%) of sales	13900	14800	19300	48000
For Equipment	1400	0	0	1400
Total Cash Disbursements	64350	74425	73975	212750
Excess / Deficiency of cash	1650	-1775	15250	6250
Financing:
Borrowings	3000	6000		9000
repayments			9000	9000
Interest			120	120
Total Financing	3000	6000	-9120	-120
Ending cash Balance	4650	4225	6130	6130
SHIL COMPANY
Budgeted Income statement
For the quarter ended june 30
Particulars	Amount	Amount
Sales		230000
Less:
Beginning inventory	39000
Add: Purchases	161100
Goods available for sale	200100
Less: ending inventory	-27600	172500
Gross Margin		57500
Less: selling and administration
Commission	27600
Rent	6600
other expenses	13800
Depreciation	2835
Total selling and administrative Expenses		50835
Net operating income		6665
Interest Expenses		-120
Net Income		6545
Balance Sheet
For month ended, june 30
Assets	Amount	Amount
Current Assets
Cash	6130
Accounts receivable	38000
Inventory	27600
Total Current assets		71730
Building and equipment net		124565
Total Assets		196295
Liabilities and Equity
Accounts payable		20925
Stockolders equity
Capital Stock	150000
Retained Earnings	25370	175370
Total liabilities and Equity		196295