I only need

Tap #1 which includes the data from A through J

And the Cash Budget I have half of it solved

questions should be addressed to the stiuto A file entitled "Computer Project A" has been placed on MOODLE for you to download and save 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 three-month period ending June 30, 2018. You MUST use formulas in all cells, not constant numbers. That means all cells in your budget must be linked to the data from TAB 1 or completed data from TAB 2. A template has been provided. Include the following detailed budgets: a. A schedule of expected cash collections from sales, by month and in total. b. A merchandise purchases budget in dollars. Show the budget by month and c. A schedule of expected cash disbursements for merchandise purchases, by d. A schedule of cash disbursements for selling and administrative expenses, by in total. month and in total. month and in total. 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) April May June July $75,000 $100,000 $125,000 $80,000 $75,000 Sales are 65% for cash and 35% 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 50% of sales. (In other words, cost of goods sold is 50% 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 $4,000. b. C. d. 2% of sales 4% of sales $25,000 3,200 e. Each month's ending inventory should equal 15% of the following month's cost of goods Inventory purchases are paid as follows: 50% in the month of purchase and the remaining 50% in the following month. Equipmen $7,500. Dividends totaling $3,500 will be declared and paid in June. The company desires a minimum ending cash balance each month of $7,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$20,000. The interest rate on thes 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,000 in cash. f. g. t purchases during the quarter will be as follows: April $21,000, and May h. i. e loans is 6% per year, and for simplicity, we will assume that interest is not J. The company's balance sheet at March 31 is given on TAB 1. Due Date: Submit a finished file through MOODLE by Saturday, March 30th by 8:00 A.M. Your file should be named yourlastnamecP_A. Only files submitted by MoODLE will be accepted. No late projects will be accepted. Grading: Your grade will be based on correctness, statement form, and spreadsheet formatting. Newport Tie Company Balance Sheet 31-Mar-18 Assets Cash Accounts Receivable Inventory Buildings and equipment (net) 9,000 26.250 7.500 214.100 256,850 Total Assets Liabilities and Stockholders' Equity Liabilities Accounts Payable $19.688 Stockholders' Equity Common stock Retained Earnings 190,000 47,162 237.162 $256,850 Total Stockholders Equity Total Liabilities and Stockholders' Equity NEWPORT TIE COMPANY Cash Budget For the Three Months Ended June 30. 2018 April May June Quarter Cash balance beginning Add cash collections Total cash available 9,000 91.250 100.250 7.425 116,250 123,675 16,895 95,750 112,645 33.320 303.250 336,570 Less disbursements Purchase of inventory Selling and Administrative Equipment Dividends 7,500 28,500 3,500 21.000 3,500 Total Disbursements Excess(deficiency) of receipts over disbursements Financing 8,000 Borrowings Repayments Interest (8,000) 80 8,080 8,000 (8,000) 80) 80) Total financing Cash balance ending 8,000 8,000