Your required tasks are as follows: TAB 1/DATA 1. coded entries. The balance sheet from the last accounting period is given. Insert all the data given below into TAB18. This is the only TAB which can have hard TAB 2/BUDGET Prepare a master budget for the three-month period ending June 30, 2019. You MUST use formulas in all cells, not constant numbers. That means all cells in your budget must to the data from TAB 1 or completed data from TAB 2. A template has been provided. Include the following detailed budgets: 1. a. A schedule of expected cash collections from sales, by month and in total. be linked A merchandise purchases budget in dollars. Show the budget by month and in total. Aschedule of expected cash disbursements for merchandise purchases, by month and in total. Aschedule of cash disbursements for selling and administrative expenses, by month and in total. b. c. d. 2. A cash budget. Show the budget by month and in total. TAB 3/INCOME STATEMENT 1. Prepare an absorption costing income statement for the quarter ending June 30. You MUST use formulas in all cells, not constant numbers. TAB 4/BALANCE SHEET 1. Prepare a budgeted balance sheet as of June 30. ou MUST use formulas in all cells, not constant numbers. DATA: a. Actual sales for March and budgeted sales for April-July are as follows: March (actual) 80,000 90,000 110,000 80,000 75,000 April May June July 670 words English (United States) Focus Sales are 30% for cash and 70% on credit. All payments on credit sales are collected in the month following the sale. The accounts receivable at March 31 are a result of March credit sales. The company's gross margin percentage is 35% of sales. (in other words, cost of goods sold is 65% of sales.) The company's monthly selling and administrative expenses are as follows: Variable: Shipping Other expenses Fixed: Wages and salaries Advertising Depreciation: Depreciation for the entire quarter, including new assets acquired during the quarter will be $5,000. Each month's ending inventory should equal 30% of the following month's cost of goods sold. Inventory purchases are paid as follows: 50% in the month of purchase and the remaining 50% in the following month Equipment purchases during the quarter will be as follows: April $16,500, and May $3,000. Dividends totaling $4,500 will be declared and paid in June. The company desires a minimum ending cash balance each month of $5,000. The company has an agreement with a bank that allows it to borrow in increments if $1,000 at the beginning of each month, up to a total loan balance of$25,000. The interest rate on these loans is 1% per month, and for simplicity, we will assume that interest is not compounded. At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan as possible (in increments of $1,000), while still retaining at least $5,000 in cash. b. C. d. 4% of sales 6% of sales $12,000 8,000 e. f. g. h. i. j. The company's balance sheet at March 31 is given on TAB 1A. TAB1B Newport Tie Company 3 Actual Sales for March and budgeted sales for April-July are as follows: 4 March(actual) 5 April 6 May 7 June 8 July 10 Collection on Sales 11 Sales collected Current Month 12 Sales collected Following Month 13 4 Cost of Goods Sold (percentage of sales) 15 16 Desired ending invenories (Percentage of next COGS) 17 18 Purchases Paid as Follows 19 In month of purchase 20 In following month 21 22 Variable Monthly Expenses 23 Shipping 24 Other expenses 25 26 Fixed Monthly Expenses 27 Wages and Salaries 28 Advertising 29 30 Fixed Quarterly Expenses 31 Depreciation 32 33 Equipment Purchased in Quarter 34 April 35 May 36 37 Dividends declared and paid in June 38 39 Agreement with Bank 40 Borrowing increments 41 Maximum borrowing amount 42 Interest Rate per Year 43 Repayment increments 44 Required minimum balance 45 Home Insert Draw Page Layout Formulas Data Review View L' MS Sans Serif Paste BIU NEWPORT TIE COMPANY April May June Quarter 1A: Schedule of Expected Cash Collections Cash Sales Credit Sales Total Cash Collections 1B: Merchandises Purchases Budget Budgeted Cost of Goods Sold Add desired ending inventory Total Needs Less beginning inventory Required dollar purchases 1C:Schedule of cash disbursements for purchases March Purchases April Purchases May Purchases June Purchases Total cash disbursements for purchases 1C: Schedule of cash disbursements for selling and administrative expenses Salaries and wages Shipping Advertising Other expenses Total cash disbursements for selling and administrative expenses 1 2 L" v110 vIA" Av | 11 MS Sans Serif 12 NEWPORT TIE COMPANY Cash Budget For the Three Months Ended June 30, 2019 Apri May June Quarter Cash balance beginning Add cash collections Total cash available Less disbursements Purchase of inventory Selling and Administrative Dividends Total Disbursements Excess(deficiency) of rececipts over disbusements Financing Repayments Interest Total financing 7 Cash balance ending 3 76