Question

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

Current assets as of March 31:
Cash	$	7,800
Accounts receivable	$	21,200
Inventory	$	41,400
Building and equipment, net	$	130,800
Accounts payable	$	24,675
Common stock	$	150,000
Retained earnings	$	26,525

1. The gross margin is 25% of sales.

2. Actual and budgeted sales data:

March (actual)	$	53,000
April	$	69,000
May	$	74,000
June	$	99,000
July	$	50,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, $2,600 per month; other expenses (excluding depreciation), 6% of sales. Assume that these expenses are paid monthly. Depreciation is $981 per month (includes depreciation on new assets).

7. Equipment costing $1,800 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

Please give positive ratings so I can keep answering. It would help me a lot. Please comment if you have any query. Thanks!

Shilow Company
Answer 1
Sales Budget	March	April	May	June	Quarter	Note
Total sale	53,000.00	69,000.00	74,000.00	99,000.00	242,000.00	A
Cash sale at 60%	31,800.00	41,400.00	44,400.00	59,400.00	145,200.00	B=A*60%
Credit sale at 40%	21,200.00	27,600.00	29,600.00	39,600.00	96,800.00	C=A*40%

Collection Schedule	April	May	June	Quarter
Cash sales	41,400.00	44,400.00	59,400.00	145,200.00	See B
Credit sales	21,200.00	27,600.00	29,600.00	78,400.00	D=C of Previous month
Total Scheduled Collections	62,600.00	72,000.00	89,000.00	223,600.00	F

Answer 2
Merchandise Purchase Budget	April	May	June	Quarter	July
Total sales	69,000.00	74,000.00	99,000.00	242,000.00	50,000.00	See A
Gross margin %	25%	25%	25%		25%	G
Cost of goods sold %	75%	75%	75%		75%	H=1-G
Cost of goods sold @75%	51,750.00	55,500.00	74,250.00	181,500.00	37,500.00	J=A*H
Add: Ending inventory	44,400.00	59,400.00	30,000.00	30,000.00		K=80% of J of next month.
Total Merchandise needed	96,150.00	114,900.00	104,250.00	211,500.00
Less: Opening inventory	41,400.00	44,400.00	59,400.00	41,400.00		L=80% of J of same month.
Merchandise Purchase Budget	54,750.00	70,500.00	44,850.00	170,100.00		M

Schedule of expected cash disbursements for merchandise purchases	April	May	June	Quarter
Material Purchase Budget	54,750.00	70,500.00	44,850.00	170,100.00	See M
50% paid in same month	27,375.00	35,250.00	22,425.00	85,050.00	N=M*50%
50% paid in next month		27,375.00	35,250.00	62,625.00	O= 50% of N of previous month.
Payment of March, Accounts Payable	24,675.00			24,675.00
Expected cash disbursements for merchandise purchases	52,050.00	62,625.00	57,675.00	172,350.00	P
Answer 3
Cash budget	April	May	June	Quarter
Beginning Cash Balance	7,800.00	4,530.00	4,985.00	7,800.00
Plus: Collections	62,600.00	72,000.00	89,000.00	223,600.00	See F
Cash Available	70,400.00	76,530.00	93,985.00	231,400.00
Disbursements
Material Payment	52,050.00	62,625.00	57,675.00	172,350.00	See P
Sales Commission	8,280.00	8,880.00	11,880.00	29,040.00	Q=A*12%
Rent	2,600.00	2,600.00	2,600.00	7,800.00	R=A*6%
Other expenses	4,140.00	4,440.00	5,940.00	14,520.00
Equipment	1,800.00			1,800.00
Total cash payments	68,870.00	78,545.00	78,095.00	225,510.00
Ending Cash Balance before financing	1,530.00	(2,015.00)	15,890.00	5,890.00
Financing:
Borrowing	3,000.00	7,000.00		10,000.00
Repayments			10,000.00	10,000.00
Interest			230.00	230.00	S
Ending Cash Balance	4,530.00	4,985.00	5,660.00	5,660.00	X

Answer 4
Income Statement	Note
Sales Revenues	See A
Net Sales
Less: Cost of goods sold	See J
Gross Profit
Less: Operating Expenses
Sales Commission
Rent
Other expenses
Depreciation
Total Operating Expenses
Operating Income
Less: Interest Expense	See S
Net Income
Statement of Retained Earnings
Opening Balance- retained Earnings
Add: Net Income
Subtotal
Less: Dividends
Closing Balance	T
Workings for Balance Sheet
100% of June credit sales
Accounts receivable	U
50% of June purchases
Accounts payable	V
Buildings & Equipment, net- Opening
Add: Purchases
Less: Depreciation
Buildings & Equipment, net- Closing	W
Answer 5
Balance Sheet
Current Assets
Cash	See X
Accounts Receivable	See U
Inventory	See K
Buildings & Equipment	See W
Total Assets
Liabilities & Shareholder's Equity
Liabilities
Current Liabilities
Accounts Payable	See V
Total Current Liabilities
Non Current Liabilities
Total Non Current Liabilities
Total Liabilities
Shareholder's Equity
Common share
Retained Earnings	See T
Total Shareholder's Equity
Total Liabilities & Shareholder's Equity