Schedule of expected cash collection | |||||||
April | May | June | Quarter | ||||
Sales | 72,000 | 77,000 | 1,02,000 | 2,51,000 | |||
Cash sales | 43,200 | 46,200 | 61,200 | 1,50,600 | |||
Credit Sales | 22,400 | 28,800 | 30,800 | 82,000 | |||
Total Collection | 65,600 | 75,000 | 92,000 | 2,32,600 | |||
Merchandize purchase budget | |||||||
April | May | June | Quarter | ||||
Sales | 72,000 | 77,000 | 1,02,000 | 2,51,000 | |||
Cost of Goods sold (75% of sales) | 54,000 | 57,750 | 76,500 | 1,88,250 | |||
Ending inventory | 46,200 | 61,200 | 31,800 | 31,800 | This is the inventory on 30 June | ||
Total need | 1,00,200 | 1,18,950 | 1,08,300 | 3,27,450 | |||
Less Opening Inventory | 43,200 | 46,200 | 61,200 | 43,200 | This is the inventory on April 1 | ||
Required Purchases | 57,000 | 72,750 | 47,100 | 1,76,850 | |||
Schedule of expected cash disbursement | |||||||
April | May | June | Quarter | ||||
March purchases | 25,800 | 25,800 | |||||
April purchases | 28,500 | 28,500 | 57,000 | ||||
May purchases | 36,375 | 36,375 | 72,750 | ||||
June purchases | 23,550 | 23,550 | |||||
Total disbursement | 54,300 | 64,875 | 59,925 | 1,79,100 | |||
Cash Budget | |||||||
April | May | June | Quarter | ||||
Beginning Cash Balance | 8100 | 4,440 | 4,805 | 8100 | |||
Add Collection from customer | 65,600 | 75,000 | 92,000 | 2,32,600 | |||
Total Cash available | 73,700 | 79,440 | 96,805 | 2,49,945 | |||
Less Cash disbursement | |||||||
For inventory | 54,300 | 64,875 | 59,925 | 1,79,100 | |||
For Expenses | |||||||
Commission 12% of sales | 8640 | 9240 | 12240 | 30,120 | |||
Rent | 2900 | 2900 | 2900 | 8,700 | |||
Other expenses 6% of sales | 4320 | 4620 | 6120 | 15,060 | |||
Equipment purchase | 2100 | 0 | 0 | 2,100 | |||
Total cash disbursement | 72,260 | 81,635 | 81,185 | 2,35,080 | |||
Excess( Deficiency of cash available for disbursement | 1,440 | -2,195 | 15,620 | 14,865 | |||
Borrowing | 3,000 | 7,000 | 0 | 10,000 | |||
Repayment | 0 | 0 | 10,000 | 10,000 | |||
Interest | 0 | 0 | 230 | 230 | |||
Total financing | 3,000 | 7,000 | -10,230 | -10,230 | |||
Ending Cash Balance | 4,440 | 4,805 | 5,390 | 5,390 | |||
Interest=(3000*1%*3)+(7000*1%*2) | 230 |
For the quarter ending 30 June | ||
Sales | 2,51,000 | |
Cost of goods sold | 1,88,250 | |
Gross Profit | 62,750 | |
Selling & Administrative expenses | ||
Commission | 30,120 | |
Rent | 8,700 | |
Other expenses | 15,060 | |
Depreciation | 2916 | |
Interest | 230 | |
57,026 | ||
Net Operating Income | 5,724 | |
Assets | ||
Cash | 5,390 | |
Accounts receivable | 40,800 | |
Inventory | 31,800 | |
Total current assets | 77,990 | |
Building & Equipment-net | 128784 | |
Total assets | 2,06,774 | |
Liabilities & Shareholders Equity | ||
Accounts Payable | 23,550 | |
Common stock | 1,50,000 | |
Shareholders Equity: | ||
Retained Earning | 27500 | |
Add: Net Operating Income | 5,724 | 33,224 |
Total liabilities & shareholders equity | 2,06,774 |
Can anyone help me with the ones that are wrong? Also explain how you get the...
Can you do this in excel and show what functions you use to get the quarter totals and other totals. Can you also do the sales budget I need to create an entire master budget. 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...
This is for shilow company. Can you show the sales budget sheet as well. Thank you TULUNJ UI JMOW CUMPally, 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 c. Sales are 60% for cash and 40% on credit. Credit sales are collected in the month following sale....
Can you please show me what the sales budget for this information would be in excel. Thank you 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 a. The gross margin is 25% of sales. b. Actual and...
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 $ 7,500 $ 20,00 $ 39,60 $ 127,200 $ 23,550 $ 150, een $ 20,750 a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) April May June July $ 50,000 $ 66,000 $ 71,00 $ 96, cea...
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...
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 $ 7,200 $ 18,800 $ 37,800 $ 123,600 $ 22,425 $ 150,000 $ 14,975 a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) April May June July $ $ $ $ $ 47,000 63,000 68,000 93,000 44,000...
The following dala relate to the operations of Stilow Company, a wholesale distributor of consumer goous: $ $ $ Current Assets AS of March 31: Cash Accounts receivable Inventory Ruilding and guipment, net Accounts payable Cornon stock Retained earnings 7,302 19,202 38,482 174,882 22,902 150.000 16,902 $ $ $ a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) April $ 48.939 $ 64,900 $ 69, en $ 94,we $ 45,080 June July c....
The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods: Current assets as of March 311 Cash Accounts receivable Inventory Building and equipment, net Accounts payable Common stock Retained earnings 8,300 $ 23,200 $ 44,400 $ 126,000 $ 26,550 $ 150,000 $ 25,350 a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) April May June July $ 58,000 $ 74,000 $ 79,000 $ 104,000 $ 55,000 c....
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 $ 7,200 $ 18,800 $ 37,800 $ 123,600 $ 22,425 $ 150,000 $ 14,975 a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) April May June July $ 47,000 $ 63,000 $ 68,000 $ 93,000 $ 44,000...
The following data relate to the operations of Shilow Company, a wholesale distributor of consumer goods: Current assets as of March 31: Accounts receivable Inventory Building and equipment, net Accounts payable Common stock Retained earnings $ 8,400 $ 23,600 $ 45,000 $ 123,600 $ 26,925 $ 150,000 $ 23,675 a. The gross margin is 25% of sales. b. Actual and budgeted sales data: March (actual) Apr 11 May June July $ 59,000 $ 75,000 $ 80.000 $ 105,000 $ 56,000...