Hi, Im in need of assistance of completing this problem. I've completed it until the Cash budget June, please help thanks

all info is within the excel file

Given Data CRAVAT SALES COMPANY CRAVAT SALES COMPANY Budgets Minimum ending cash balance Selling price (per unit) April 1a. Sales budget: Budgeted sales in units Selling price per unit 33,000 $ 8.10 Total sales May $ June 41,000 $ 8.10 65,000 $ 8.10 - $ Total cash collections $ - $ Correct! 19,440 136,080 22,680 80,190 160,380 $ 99,630 Correct! $ 19,440 $ 158,760 26,730 $ 267,300 199,260 $ 298,890 157,950 $ 157,950 $ 235,710 $ 383,940 $ 902,340 Correct! 1c. Merchandise purchases budget: Budgeted sales in units Add budgeted ending inventory Total needs Less beginning inventory Required unit purchases Unit cost $ 282,690 Correct! Correct! Correct! 33,000 30,750 41,000 48,750 - 24,750 (24,750) $ 4.85 $ Required dollar purchases 30,750 (30,750) 4.85 $ ### Correct! 1d. Budgeted cash disbursements for inventory purchases: $ 76,993.75 March purchases April purchases 94,575 May purchases June purchases Total cash payments 65,000 30,000 ### Correct! 139,000 30,000 169,000 24,750 144,250 4.85 ### ### Correct! $ 76,994 $ 189,150 $ 286,150 112,156.25 255,231.25 664,450.00 Correct! Correct! Correct! $ 143,075 112,156.25 $ 94,575 143,075 $ 171,568.75 $ 237,650 Correct! 48,750 (48,750) 4.85 $ Correct! 2. CRAVAT SALES COMPANY Cash Budget For the Three Months Ending June 30 April $ 14,000 $ 235,710 Cash balance, beginning Add receipts from customers Total cash available Less disbursements: Purchase of inventory Sales commissions Salaries and wages Utilities Miscellaneous Dividends paid May June Quarter $(26,000.00) $ 7,000.00 $ 38,323 $ 282,690 $ 383,940 $ 902,340 (26,000) 7,000 940,663 4,000 Land purchases Total disbursements Excess (deficiency) of receipts over disbursements Financing: Borrowings 4,000 April 33,000 May June July August September 41,000 65,000 40,000 36,000 32,000 Desired ending inventories (percentage - Interest Total financing 10,000 4,000 664,450 139,000 66,000 42,000 9,000 12,000 - 22,000 - (1,680) 69,000 20,320 22,000 (1,680) 55,320 $(26,000.00) $ 7,000.00 $ 12,570.00 $ 12,570 Correct! Correct! 3. CRAVAT SALES COMPANY Budgeted Income Statement For the Three Months Ended June 30 Sales in units 139,000 Sales Variable expenses: Cost of goods sold Commissions Contribution margin Fixed expenses: Salaries and wages Utilities $ 1,125,900 $ 699,613 139,000 66,000 42,000 75% of next month's sales) Cost of ties (per unit) $ 838,613 287,288 Try again! Try again! 4.85 Purchases paid as follows: In month of purchase In following month 50% 50% Collection on sales: Sales collected current month 30% Sales collected following month 60% Sales collected 2nd month following 10% Variable monthly expenses: Sales commissions (per tie) $ 1.00 Wages and salaries $ 22,000 Utilities Insurance Depreciation Miscellaneous $ $ $ $ 14,000 1,200 1,500 3,000 $ $ 30,000 12,000 $ 14,000 Fixed monthly expenses: Balance sheet at March 31: Assets Cash Accounts receivable February sales March sales Inventory (24,750 units) Prepaid insurance Fixed assets, net of depreciation Total assets - 30,000 30,000 36,000.00 36,000.00 36,000.00 962,450 Liabilities and Stockholders' Equity Accounts payable (36,000.00) (62,000.00) (29,000.00) (21,787) Dividends payable Capital stock 10,000 69,000 - 79,000 Retained earnings Repayments Cash balance, ending 20,000 24,000 28,000 Land purchased in May Dividends declared each quarter $ 171,568.75 $ 237,650 255,231.25 33,000 41,000 65,000 22,000 22,000 22,000 14,000 14,000 14,000 3,000 3,000 3,000 10,000 8.10 Recent and forecast sales (in units): January (actual) February (actual) March (actual) 139,000 $ 8.10 - $ Correct! 1b. Schedule of expected cash collections: February sales March sales April sales May sales June sales Quarter $ $ Total liabilities and stockholders' equity $ 19,440 158,760 178,200 120,037.50 14,400 172,700 $ 499,337.50 $ 76,993.75 12,000 300,000 110,343.75 $ 499,337.50 Agreement with Bank: Borrowing increments $ 1,000 Maximum borrowing amount Interest rate per month Repayment increments Total of interest paid each quarter Required minimum cash balance $300,000 1% $ 1,000 100% $ 12,000 Insurance expired Depreciation Miscellaneous Net operating income Less interest expense 3,600 4,500 9,000 125,100 162,188 (1,680) Net income $ 163,868 Try again! 4. CRAVAT SALES COMPANY Budgeted Balance Sheet June 30 Assets Cash Accounts receivable Inventory Unexpired insurance Fixed assets, net of depreciation 24,750 30,000 1,500 Total assets Liabilities and Stockholders' Equity Accounts payable, purchases Dividends payable Loans payable, bank Capital stock, no par Retained earnings $ 76,933.75 12,000 79,000 300,000 Total liabilities and equity Accounts receivable at June 30: May sales June sales Total Retained earnings at June 30: Balance, March 31 Add net income Total Less dividends declared Balance, June 30 $ 332,100 526,500