Accounting. Please complete all steps in excel and show formula. Complete all tabs: Data, Budget, Income statement and Balance Sheet.
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 Sales are 75% for cash and 25% 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: d. 1% of sales 6% of sales $28,000 2,500 Depreciation for the entire quarter, including new assets acquired during the quarter will be $6,000 Each month's ending inventory should equal 10% of the following month's cost of goods sold. e. f. Inventory purchases are paid as follows: 50% in the month of purchase and the remaining 50% in the following month. g. Equipment purchases during the quarter will be as follows: April $3,200, and May $18,000. h. Dividends totaling $4,700 will be declared and paid in June. The company desires a minimum ending cash balance each month of $7,500. 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$20,000. The interest rate on these loans is 4% per year, 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 $7,500 in cash. j. The company's balance sheet at March 31 is given on TAB 1 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
