just answer question 4 which is balance sheet for september. use excel and show your work please

Cash Budget with Supporting Schedules Assignment Smith Products Inc. is a merchandising company that sells binders, paper, and other school supplies. The company is planning its cash needs for the third quarter. the past, Smith Products has had to borrow money during the third quarter to support peak sales of back-to-school materials, which occur during August. The following information has been assembled to assist in preparing a cash budget for the quarter: a. Budgeted monthly absorption costing income statements for July through October are as follows: July August September October $40,000 24,000 $70,000 42.000 28,000 $50,000 30000 20000 $45.000 27,000 18,000 16,000 11700 Sales. Cost of goods sold. Gross margin: Selling and administrative expenses: Selling expense. Administrative expense" Total expenses Operating income. *hdudes $2,000 depreciation each month. 7,200 5600 12,800 $ 3,200 7,200 18,900 $ 9.00 8,500 6,100 14,600 $ 5,400 7,300 5,900 13,200 $ 4800 b. Sales are 20% for cash and 80% on credit. C. Credit sales are collected over a three-month period, with 20% collected in the month of sale, 70% in the month following sale, and 10% in the second month following sale. May sales totalled $30,000, and June sales totalled $36,000. d. Inventory purchases are paid for within 15 days. Therefore, 50% of a month's inventory purchases is paid for in the month of purchase. The remaining 50% is paid in the following month. Accounts payable for inventory purchases at June 30 total $11,700. e. The company maintains its ending inventory levels at 75% of the cost of the merchandise to be sold in the following month. The merchandise inventory at June 30 is $18,000. f. Land costing $14,500 will be purchased in July. g. Dividends of $1,000 will be declared and paid in September. h. The cash balance on June 30 is $8,000; the company must maintain a cash balance of at least this amount at the end of each month. i. The company has an agreement with a local bank that allows the company to borrow up to a total loan balance of $40,000. The interest rate on these loans is 1% per month. If the loan balance exceeds $40,000 interest is charged at 5% on the excess over $40,000. All borrowing is done at the beginning of a month. The company would, as far as it is able, repay the loan at the end of each month. Interest must be paid at the end of each month based on the outstanding loans for that month and is recorded as interest expense. There are no loans outstanding as at June 30. Required: Prepare a schedule of expected cash collections for July, August, and September and for the quarter in total 2. Prepare the following for merchandise inventory: a A merchandise purchases budget for July August and Sentember hill 19C Par 17 1:1:1:2:13:14:15:16:17:18:19: | 10 | 11 | 12 | 13 | 14 | 15 | 16 August September UCLUper JULY Sales... $40,000 24,000 16,000 $70,000 42,000 28,000 $50.000 30000 20.000 $45,000 27,000 18,000 Cost of goods sold... Gross margin Selling and administrative expenses: Selling expense. Administrative expense* Total expenses Operating income. *hdudes $2,000 depreciation each month. 7,200 5,600 12,800 $ 3,200 11,700 7,200 18,900 $ 9,10 8,500 6,100 14,600 $ 5,400 7,300 5,900 13,200 $ 4800 C. e. b. Sales are 20% for cash and 80% on credit. Credit sales are collected over a three-month period, with 20% collected in the month of sale, 70% in the month following sale, and 10% in the second month following sale. May sales totalled $30,000, and June sales totalled $36,000. d. Inventory purchases are paid for within 15 days. Therefore, 50% of a month's inventory purchases is paid for the month purchase. The remaining 50% is paid in the following month Accounts payable for inventory purchases at June 30 total $11,700. The company maintains its ending inventory levels at 75% of the cost of the merchandise to be sold in the following month. The merchandise inventory at June 30 is $18,000. f. Land costing $14,500 will be purchased in July. g. Dividends of $1,000 will be declared and paid in September. h. The cash balance on June 30 is $8,000; the company must maintain a cash balance of at least this amount at the end of each month. i. The company has an agreement with a local bank that allows the company to borrow up to a total loan balance of $40,000. The interest rate on these loans is 1% per month. If the loan balance exceeds $40,000 interest is charged at 5% on the excess over $40,000. All borrowing is done at the beginning of a month. The company would, as far as it is able, repay the loan at the end of each month. Interest must be paid at the end of each month based on the outstanding loans for that month and is recorded as interest expense. There are no loans outstanding as at June 30. Required: 1. Prepare a schedule of expected cash collections for July, August, and September and for the quarter in total. 2. Prepare the following for merchandise inventory: a.A merchandise purchases budget for July, August, and September. b.A schedule of expected cash disbursements for merchandise purchases for July, August, and September and for the quarter in total. 3. Prepare a cash budget for July, August, and September and for the quarter in total. 4. Prepare a balance sheet for the end of September. Ignore income taxes. E F N Total 160,000 32000 4800 25920 32000 44800 4000 143520 . B C D 1 schedule of expected cash collection from sales July Augest September 3 Total Sales 40,000 70,000 50000 4 cash sales 8000 14000 10000 5 6 sales on account: 7 May sales 4800 8 June sales 20160 5760 9 July sales 3200 22400 6400 10 Augest sales 5600 39200 11 September sales 4000 12 Total Cash Collection 36160 47760 59600 13 14 15 16 17 Merchandise Purchase Budget 18 July Augest September 19 Budget cost of good sold 24000 42000 30000 20 Add: Closing inventory 31500 22500 20250 21 Total Needs 55500 64500 50250 22 Less: opening inventory 18000 31500 22500 23 Required inventory purchase 37500 33000 27750 24 25 Total 96000 31500 127500 18000 109500 A B D E 26 27 schedule of cash payment to supplies 28 July Augest September Total 29 Cash payment: 30 Account payable-June 30 11700 11700 31 Jul purchaes (50%) 18750 18750 37500 32 Aug purchaes 16500 16500 33000 33 Sep purchaes 13875 13875 34 Total Cash payment 30450 35250 30375 96075 35 Cash Budget July Augest September Quarter 8000 8000 8000 8000 36160 47760 59600 143520 44160 55760 67600 151520 30450 35250 7200 3600 11700 5200 30375 8500 4100 36 37 38 39 40 Opening balance 41 Cash collection 42 Cash Availabe 43 Less: payment 44 Inventory purchase 45 Selling expense 46 Administrative expenses 47 Land 48 Dividend 49 Total disbursements 50 Payment minus receipt 51 Excess/(deficiency) 52 Financing activities 53 Borrowing 54 Repayment 55 Interest 56 Total Financing 57 Ending balance 58 96075 27400 12900 4500 1000 141875 4500 1000 43975 45750 52150 - 1590 3610 23625 9645 4252 0 9495 0 94.95 -13747 -137 -13884 9741 85.47 4337.47 7947.47 13747 -13747 43.42 43.42 9688.42 9589.95 7999.95 59 Cash Budget with Supporting Schedules Assignment Smith Products Inc. is a merchandising company that sells binders, paper, and other school supplies. The company is planning its cash needs for the third quarter. the past, Smith Products has had to borrow money during the third quarter to support peak sales of back-to-school materials, which occur during August. The following information has been assembled to assist in preparing a cash budget for the quarter: a. Budgeted monthly absorption costing income statements for July through October are as follows: July August September October $40,000 24,000 $70,000 42.000 28,000 $50,000 30000 20000 $45.000 27,000 18,000 16,000 11700 Sales. Cost of goods sold. Gross margin: Selling and administrative expenses: Selling expense. Administrative expense" Total expenses Operating income. *hdudes $2,000 depreciation each month. 7,200 5600 12,800 $ 3,200 7,200 18,900 $ 9.00 8,500 6,100 14,600 $ 5,400 7,300 5,900 13,200 $ 4800 b. Sales are 20% for cash and 80% on credit. C. Credit sales are collected over a three-month period, with 20% collected in the month of sale, 70% in the month following sale, and 10% in the second month following sale. May sales totalled $30,000, and June sales totalled $36,000. d. Inventory purchases are paid for within 15 days. Therefore, 50% of a month's inventory purchases is paid for in the month of purchase. The remaining 50% is paid in the following month. Accounts payable for inventory purchases at June 30 total $11,700. e. The company maintains its ending inventory levels at 75% of the cost of the merchandise to be sold in the following month. The merchandise inventory at June 30 is $18,000. f. Land costing $14,500 will be purchased in July. g. Dividends of $1,000 will be declared and paid in September. h. The cash balance on June 30 is $8,000; the company must maintain a cash balance of at least this amount at the end of each month. i. The company has an agreement with a local bank that allows the company to borrow up to a total loan balance of $40,000. The interest rate on these loans is 1% per month. If the loan balance exceeds $40,000 interest is charged at 5% on the excess over $40,000. All borrowing is done at the beginning of a month. The company would, as far as it is able, repay the loan at the end of each month. Interest must be paid at the end of each month based on the outstanding loans for that month and is recorded as interest expense. There are no loans outstanding as at June 30. Required: Prepare a schedule of expected cash collections for July, August, and September and for the quarter in total 2. Prepare the following for merchandise inventory: a A merchandise purchases budget for July August and Sentember hill 19C Par 17 1:1:1:2:13:14:15:16:17:18:19: | 10 | 11 | 12 | 13 | 14 | 15 | 16 August September UCLUper JULY Sales... $40,000 24,000 16,000 $70,000 42,000 28,000 $50.000 30000 20.000 $45,000 27,000 18,000 Cost of goods sold... Gross margin Selling and administrative expenses: Selling expense. Administrative expense* Total expenses Operating income. *hdudes $2,000 depreciation each month. 7,200 5,600 12,800 $ 3,200 11,700 7,200 18,900 $ 9,10 8,500 6,100 14,600 $ 5,400 7,300 5,900 13,200 $ 4800 C. e. b. Sales are 20% for cash and 80% on credit. Credit sales are collected over a three-month period, with 20% collected in the month of sale, 70% in the month following sale, and 10% in the second month following sale. May sales totalled $30,000, and June sales totalled $36,000. d. Inventory purchases are paid for within 15 days. Therefore, 50% of a month's inventory purchases is paid for the month purchase. The remaining 50% is paid in the following month Accounts payable for inventory purchases at June 30 total $11,700. The company maintains its ending inventory levels at 75% of the cost of the merchandise to be sold in the following month. The merchandise inventory at June 30 is $18,000. f. Land costing $14,500 will be purchased in July. g. Dividends of $1,000 will be declared and paid in September. h. The cash balance on June 30 is $8,000; the company must maintain a cash balance of at least this amount at the end of each month. i. The company has an agreement with a local bank that allows the company to borrow up to a total loan balance of $40,000. The interest rate on these loans is 1% per month. If the loan balance exceeds $40,000 interest is charged at 5% on the excess over $40,000. All borrowing is done at the beginning of a month. The company would, as far as it is able, repay the loan at the end of each month. Interest must be paid at the end of each month based on the outstanding loans for that month and is recorded as interest expense. There are no loans outstanding as at June 30. Required: 1. Prepare a schedule of expected cash collections for July, August, and September and for the quarter in total. 2. Prepare the following for merchandise inventory: a.A merchandise purchases budget for July, August, and September. b.A schedule of expected cash disbursements for merchandise purchases for July, August, and September and for the quarter in total. 3. Prepare a cash budget for July, August, and September and for the quarter in total. 4. Prepare a balance sheet for the end of September. Ignore income taxes. E F N Total 160,000 32000 4800 25920 32000 44800 4000 143520 . B C D 1 schedule of expected cash collection from sales July Augest September 3 Total Sales 40,000 70,000 50000 4 cash sales 8000 14000 10000 5 6 sales on account: 7 May sales 4800 8 June sales 20160 5760 9 July sales 3200 22400 6400 10 Augest sales 5600 39200 11 September sales 4000 12 Total Cash Collection 36160 47760 59600 13 14 15 16 17 Merchandise Purchase Budget 18 July Augest September 19 Budget cost of good sold 24000 42000 30000 20 Add: Closing inventory 31500 22500 20250 21 Total Needs 55500 64500 50250 22 Less: opening inventory 18000 31500 22500 23 Required inventory purchase 37500 33000 27750 24 25 Total 96000 31500 127500 18000 109500 A B D E 26 27 schedule of cash payment to supplies 28 July Augest September Total 29 Cash payment: 30 Account payable-June 30 11700 11700 31 Jul purchaes (50%) 18750 18750 37500 32 Aug purchaes 16500 16500 33000 33 Sep purchaes 13875 13875 34 Total Cash payment 30450 35250 30375 96075 35 Cash Budget July Augest September Quarter 8000 8000 8000 8000 36160 47760 59600 143520 44160 55760 67600 151520 30450 35250 7200 3600 11700 5200 30375 8500 4100 36 37 38 39 40 Opening balance 41 Cash collection 42 Cash Availabe 43 Less: payment 44 Inventory purchase 45 Selling expense 46 Administrative expenses 47 Land 48 Dividend 49 Total disbursements 50 Payment minus receipt 51 Excess/(deficiency) 52 Financing activities 53 Borrowing 54 Repayment 55 Interest 56 Total Financing 57 Ending balance 58 96075 27400 12900 4500 1000 141875 4500 1000 43975 45750 52150 - 1590 3610 23625 9645 4252 0 9495 0 94.95 -13747 -137 -13884 9741 85.47 4337.47 7947.47 13747 -13747 43.42 43.42 9688.42 9589.95 7999.95 59