Question

Can you also do a sales budget for the given information? Thank you in advance

The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods: Current assets as of March 31: Cash Accounts receivable Inventory Building and equipment, net Accounts payable Common stock Retained earnings $ 8,500 $ 24,000 $ 45, 600 $ 121,200 $ 27,300 $ 150,000 $ 22,000 June Quarter Shilow Company Cash Budget April May $ 8,500 69,600 78,100 a. The gross margin is 25% of sales b. Actual and budgeted sales data: 0 0 March (actual) April May June July $ 60,000 $ 76,000 $ 81,000 $ 106,000 $ 57,000 Beginning cash balance Add collections from customers Total cash available Less cash disbursements: For inventory For expenses For equipment Total cash disbursements Excess (deficiency) of cash available over disbursements Financing: Borrowings Repayments Interest Total financing Ending cash balance 57,300 16,980 2,500 76,780 0 0 1,320 000 0 0 0 0 0 $ 1,320 $ $ $ 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, $3,300 per month; other expenses (excluding depreciation), 6% of sales. Assume that these expenses are paid monthly. Depreciation is $909 per month (includes depreciation on new assets). g. Equipment costing $2,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.

Answer 1

Required Budgets are as prepared below:

Shillow Company
Schedule of expected Cash collections
For the quarter ended June 30
	Month
Particulars	April	May	June	Total	July
Sales	76,000	81,000	106,000	263,000	57,000
Beginning Accounts Receivable
March Credit sales (60,000*.4)	24,000			24,000
April Cash Sales	45,600			45,600
April Credit Sales		30,400		30,400
May Cash sales		48,600		48,600
May Credit Sales			32,400	32,400
June Cash sales			63,600	63,600
June Credit Sales				0
Total collections	69,600	79,000	96,000	244,600
Account receivable for June sale			42,400
Shillow Company
Merchandise Purchase Budget
For the quarter ended June 30
	Month
Particulars	April	May	June	Total	July
Cost of goods sold (75% of sales)	57,000	60,750	79,500	197,250	42,750
Add: Desired Ending merchandise inventory (80% of next month COGS)	48,600	63,600	34,200	34,200
Total needs	105,600	124,350	113,700	231,450
Less: beginning merchandise inventory	45,600	48,600	63,600	45,600
Required purchase	60,000	75,750	50,100	185,850
Shillow Company
Schedule of expected Cash payments
For the quarter ended June 30
	Month
Particulars	April	May	June	Total
Beginning Accounts Payable (a)	$27,300			$27,300
April Purchases (b)	$30,000	$30,000		$60,000
May Purchases (c )		$37,875	$37,875	$75,750
June Purchases (d)			$25,050	$25,050
Total payments (a+b+c+d)	$57,300	$67,875	$62,925	$188,100
Shillow Company
Cash Budget
For the quarter ended June 30
	Month
Particulars	April	May	June	Total
Beginning Cash balance	8,500	4,320	4,565	8,500
Add: Collection from customers	$69,600	$79,000	$96,000	244,600
cash available for use	$78,100	$83,320	$100,565	$253,100
Less: cash Disbursements
Merchandise purchase	$57,300	$67,875	$62,925	188,100
Commissions (12% of sales)	9,120	9,720	12,720	31,560
Rent	3,300	3,300	3,300	9,900
Other exp (6% of sales)	4,560	4,860	6,360	15,780
Equipment purchase	2,500	0	0	2,500
Total disbusrement	76,780	85,755	85,305	247,840
Cash surplus/Deficit	1,320	-2,435	15,260	5,260
Financing
Borrowing	3,000	7,000		10,000
Repayment			-10,000	-10,000
Interest			-130	-130
Net cash from Financing	3,000	7,000	10,130	-130
Budgeted ending cash balance	4,320	4,565	5,130	5,130
Shillow Company
Budgeted Income Statement
For the quarter ended June 30
Particulars	Amount ($)	Amount ($)
Sales		263,000
Less: Cost of goods sold (75% of sales)		197,250
Gross margin		65,750
Less: Selling and administartive exp
Depreciation (909*3)	2,727
Commissions (12% of sales)	31,560
Rent	9,900
Other exp (6% of sales)	15,780	59,967
Net operating Income		5,783
Interest expense		130
Net Income		5,653
Shillow Company
Budgeted balance Sheet
Jun-30
Assets
Cash	5,130
Accounts Receivable	42,400
Inventory	34,200
Building and equipment Net	120,973
Total assets	202,703
Liabilities and Stockholders' Equity
Accounts Payable purchases	25,050
Common Stock	150,000
Retained earnings (22,000+5,653)	27,653
Total liabilities and stockholders' equity	202,703