Accounting. Please complete all steps in excel and show cell reference formula. Show data sheet you will be taking numbers from.

Instructions

TAB/1

TAB/2

TAB 3 and 4 are blank no template. Need to create Income statement and balance sheet from scratch.

Your required tasks are as follows: TAB 1/DATA 1. Insert all the data given below. This is the only TAB which can have hard coded entries. The balance sheet from the last accounting period is given. TAB 2/BUDGET Prepare a master budget for the t formulas in to the data from TAB 1 or completed data from T the following detailed budgets: 1. a A schedule of expected cash collections from sales, by month and in total. hree-month period ending June 30, 2018. You MUST use all cells, not constant numbers. That means all cells in your budget must be linked AB 2. A template has been provided. Include A merchandise purchases budget in dollars. Show the budget by month and in total. A schedule of expected cash disbursements for merchandise purchases, by month and in total. A schedule 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. You 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) 85,000 April May June July 93,000 $162,000 $165,000 $140,000 2 3 4 Newport Tie Company Balance Sheet 31-Mar-18 Assets 6 7 Cash 8 Accounts Receivable 9 Inventory 10 Buildings and equipment (net) 11 Total Asset:s 9,000 21,250 6,045 214,100 250,395 Liabilities and Stockholders' Equity 13 Liabilities 14 Accounts Payable $27,885 16 Stockholders' Equity 17 Common stock 18 Retained Earnings 19 Total Stockholders' Equity 20 Total Liabilities and Stockholders' Equity 21 190,000 32,510 222,510 $250,395 NEWPORT TIE COMPANY April May June Quartei 41A: Schedule of Expected Cash Collections 5 Cash Sales 6 Credit Sales 7 Total Cash Collections 91B: Merchandises Purchases Budget 10 Budgeted Cost of Goods Sold 11 Add desired ending inventory 12 Total Needs 13 Less beginning inventory 14 Required dollar purchases 1C: Schedule of cash disbursements for 16 purchases 17 March Purchases 18 April Purchases 19 May Purchases 20 June Purchases 21 Total cash disbursements for purchases 1C: Schedule of cash disbursements for selling 23 and administrative expenses 24 Salaries and wages 25 Shipping 26 Advertising 27 Other expenses Total cash disbursements for selling and 28 administrative expenses 29 2 34 35 36 37 38 39 40 Cash balance beginning 41 Add cash collections 42 Total cash available 43 44 Less disbursements 45 Purchase of inventory 46 Selling and Administrative A/ Equipment 48 Dividends 49 Total Disbursements 50 Excess (deficiency) of receipts over disbursements 51 NEWPORT TIE COMPANY Cash Budget For the Three Months Ended June 30, 2018 April May June Quarteir Financing 53Borrowings 54 Repayments 55 Interest 56 Total financing 57 Cash balance ending 58