need help with preparing budget on excel and cant put direct answers so please explain how to get each answer. Will rate positively !

Required: Prepare a master budget for the three-month period ending June 30. Include the following detailed budgets: 1. a. A sales budget, by month and in total. b. A schedule of expected cash collections from sales, by month and in total. c. A merchandise purchases budget in units and in dollars. Show the budget by month and in total d. A schedule of expected cash disbursements for merchandise purchases, by month and in total 2. A cash budget. Show the budget by month and in total. Determine any borrowing that would be needed to maintain the minimum cash balance of $50,000. 3. A budgeted income statement for the three-month period ending June 30. Use the contribution approach 4. A budgeted balance sheet as of June 30. 3 EARRINGS UNLIMITED $50,000 $10 20.000 26.000 40,000 65,000 100,000 50.000 30,000 28,000 25,000 40% 5 50% 50% 20% 70% 10% 5 Minimum ending cash balance 6 Selling price 7 8 Recent and forecast sales (in units): 9 January (actual) 10 February (actual) 11 March (actual) 12 April 13 May 14 June 15 July 16 August 17 September 18 19 Desired ending inventories (percentage 20 of next month's sales) 21 Cost of earrings 22 23 Purchases paid as follows: 24 In month of purchase 25 In following month 26 27 Collection on sales: 28 Sales collected current month 29 Sales collected following month 30 Sales collected 2nd month following 31 32 Variable monthly expenses: 33 Sales commissions (% of sales) 34 35 Fixed monthly expenses: 36 Advertising 37 Rent 38 Salaries 39 Utilities 40 Insurance (12 months paid in November) 41 Depreciation 42 43 Equipment purchased in May 44 Equipment purchased in June 45 Dividends declared each quarter 46 47 Balance sheet at March 31: 48 Assots 49 Cash 50 Accounts receivable 51 Inventory 52 Prepaid insurance 53 Property and equipment (net) 54 Total assets 55 56 Liabilities and Stockholders' Equity 57 Accounts payable 58 Dividends payable 59 Capital stock 60 Retained earnings 4% $ 200.000 18,000 106,000 7,000 3.000 14.000 $ $ 16.000 40,000 15.000 $ 574.000 345.000 104.000 21.000 950.000 $1495.000 $100,000 15,000 800,000 ny > 30 Sales collected 2nd month following 10% 31 32 Variable monthly expenses: 33 Sales commissions (% of sales) 4% 34 35 Fixed monthly expenses: 36 Advertising $ 200,000 37 Rent $ 18,000 38 Salaries $ 106,000 39 Utilities $ 7,000 40 Insurance (12 months paid in November) $ 3,000 41 Depreciation $ 14,000 42 43 Equipment purchased in May $ 16,000 44 Equipment purchased in June $ 40.000 45 Dividends declared each quarter 15,000 46 47 Balance sheet at March 31: 48 Assets 49 Cash $74,000 50 Accounts receivable 346,000 51 Inventory 104,000 52 Prepaid insurance 21,000 53 Property and equipment (net) 950,000 54 Total assets $1,495.000 55 56 Liabilities and Stockholders' Equity 57 Accounts payable $100,000 58 Dividends payable 15,000 59 Capital stock 800,000 60 Retained earnings 580,000 61 Total liabilities and stockholders' equity $1,495,000 62 63 Agreement with Bank: 64 Borrowing increments $1,000 65 Interest rate per month 66 Repayment increments 67 Total of interest paid each quarter $1,000 100% 68 Required minimum cash balance $50,000 69 70 1% 71 EARRINGS UNLIMITED Budgets Quarter 215,000 $10 $2,150,000 Correct April May June Requirement 1a. Sales budget: Budgeted sales in units 65,000 100.000 50,000 1 Selling price per unit $10 $10 $10 2 Total sales $650.000 $1,000,000 $500,000 3 Correct! Corect Correct! Requirement 1b. Schedule of expected cash collections: 5 February sales $26,000 3 March sales 40.000 7 April sales 65,000 May sales June sales Total cash collections 1 2 Requirement 1c. Merchandise purchases budget: 3 Budgeted unit sales 4 Add desired ending inventory Total needs Sess beginning inventory 7 Required purchases in units 3 Jnit cost Required dollar purchases 1 Requirement 1d. Budgeted cash disbursements for merchandise purchases: 2 Accounts payable 3 April purchases 4 May purchases 5 June purchases 5 Total cash disbursements 7 3 Requirement 2: 0 EARRINGS UNLIMITED 1 Cash Budget 2 For the Three Months Ending June 30 3 4 April May June 5 Cash balance, beginning B Add receipts from customers 7 Total cash available 3 less disbursements: 9 Merchandise purchases b Advertising 1 Rent 2 Salaries 3 Commissions (4% of sales) Quarter B June Quarter 42 For the Three Months Ending June 30 43 44 April May 45 Cash balance, beginning 46 Add receipts from customers 47 Total cash available 48 Less disbursements: 49 Merchandise purchases 50 Advertising 51 Rent 52 Salaries 53 Commissions (4% of sales) 54 Utilities 55 Equipment purchases 56 Dividends paid 57 Total disbursements 58 Excess (deficiency) of receipts 59 over disbursements 60 Financing: 61 Borrowings 62 Repayments 63 Interest 64 Total financing 65 Cash balance, ending 66 67 68 Requirement 3: 69 EARRINGS UNLIMITED 70 Budgeted Income Statement 71 For the Three Months Ending June 30 72 73 Sales in units 74 Sales 75 Variable expenses: 76 Cost of goods sold 77 Commissions 78 Contribution margin 79 Fixed expenses: 80 Advertising 81 Rent 82 Salaries 83 Utilities Insurance 85 Depreciation 86 Net operating income 87 Less interest expense 88 Net income 89 90 84 90 91 EARRINGS UNLIMITED 92 Budgeted Balance Sheet 93 June 30 94 95 Assets 96 Cash 97 Accounts receivable 98 Inventory 99 Prepaid insurance 100 Property and equipment, net 101 Total assets 102 103 Liabilities and Equity 104 Accounts payable, purchases 105 Dividends payable 106 Capital stock, no par 107 Retained eamings 108 Total liabilities and equity 109 110 Accounts receivable at June 30: 111 May sales 112 June sales 113 Total 114 115 Retained eamings at June 30: 116 Balance, March 31 117 Add net income 118 Total 119 Less dividends declared 120 Balance, June 30 121 122