Please follow the instructions off the two word docs use the excel doc to fill in the info from the two word docs. Thank you!

'I'pur required taslts are as tulips-rs: TAB IMTA. 1. Insert all the data given belpw into TABlB. This is. the unlyr TAB 1.II.rhich can have hard cpded entries. The balance sheet frpm the last accpunting pericd isgiiren. TAE EIBLIDIEEI' Prepare a master budget ar d1e three~mpnth peripd ending June BB, 201?. Tpu MUST use fprmulas in all cells, nut cpnstant numbers. That means all cells in your budget must be linked tn the data frpm TAB 1 ur cam pleted data frpm TAB 1. Atemplate has been prmlided. Include the tulips-ring detailed budgets: 1. a. A schedule at expected cash cpllectipns frpm sales, by mpnth and in total. b. A merchandise purchases budget in dpllars. ShclinI the budget by mpnth and in tptal. c. A schedule at expected cash disbursements fur merchandise purchases, by mpnth and in tptal. d. A sched ule pf cash disbursements fpr selling and administrative expenses, by mpnth and in tptal. 1. A cash budget. Shpw the budget by munth and in Intel. TAB B HCDME STATEMENT 1. Prepare an absprptipn cnsng incpme statement fur the c uarter ending June BB. 'I'nu MUST use formulas in all cells, ncnt cpnstant numbers. TABdeALAHEE SHEET 1. Prepare a budgeted balance sheet as anune BB. "l'u MUEI' use armulas in all cells, npt cpnstant numbers. DATA: a. Actual sales for March and budgeted sales fpr April-Jul? are as fullpws: Marci-Iiactuali 75mm April Ei-IIIU Inlay msmc June 1|}.ch Jury csmc Eales are 35$ for cash and T5545 on credit. All pavments on credit sales are collected in the month following the sale. The accounts receivable at March 31 are a result of I'v'la rch credit sales. The company' s gross margin percentage is ABSE- of sales. {In other words, cost ofgoods sold is EDS-E of sales.:| The company's monihhr selling and administrative expenses are as follows: 'v'ariahle: Ehipping 45s of sales {itl'ler expenses 5% of sales Fixed: 1lI'lI'ages and salalies SILIII] Advenjsing ELIIID Depreciation: Depreciation for the anti-e quarter, including new assets acquired during the quarter will be SEMI}. Each month's ending inventorv should equal 3556- ofthe following month's cost of goods sold. Inventorv purchases are paid as follows: SHEE- in the month of purchase and the remaining EMS in the following month. Equipment purchases during the quarter will be as follows: ApIiI 515.511\". and May ELEM. Dividends totaling 553]] will be declared and paid in June. The companv desires a minimum ending cash balance each month of $5.00!]. The companv has an agreement with a bank: that allows it to borrow in increments if 51,000 at the beginning of each rnonth, up to a total loan balance ofSI,[[I]. The interest rate on these loans is 1% per monthJ and for simplicitv, we will assume that interest is not compounded. At the end of the cluarterJ the companv would pav the banlt all of the accumulated interest on the loan and as much of the loan as possible {in increments of SLEHI}, while still retaining at least 55.00:: in cash. The company's balance sheet at March 31 is given on TAB ILA. TAB1B Newport Tie Company Actual Sales for March and budgeted sales for April-July are as follows: March(actual) April May June July Collection on Sales Sales collected Current Month Sales collected Following Month Cost of Goods Sold (percentage of sales) Desired ending invenories (Percentage of next COGS) Purchases Paid as Follows In month of purchase In following month Variable Monthly Expenses Shipping Other expenses Fixed Monthly Expenses Wages and Salaries Advertising Fixed Quarterly Expenses Depreciation Equipment Purchased in Quarter April May Dividends declared and paid in June Agreement with Bank Borrowing increments Maximum borrowing amount Interest Rate per Year Repayment increments Required minimum balance TAB 1A Newport Tie Company Balance Sheet 31-Mar-17 Assets Cash Accounts Receivable Inventory Buildings and equipment (net) 9,000 56,250 16,800 214,100 296,150 Total Assets Liabilities and Stockholders' Equity Liabilities Accounts Payable $23,025 Stockholders' Equity Common stock Retained Earnings Total Stockholders' Equity Total Liabilities and Stockholders' Equity 197,005 76,120 273,125 296,150 TAB1B (March 2017) Budgeted Cost of Goods Sold Add desired ending inventory Total Needs Less beginning inventory Required dollar purchases $ NEWPORT TIE COMPANY April May June 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 2 NEWPORT TIE COMPANY Cash Budget For the Three Months Ended June 30, 2017 April Cash balance beginning Add cash collections Total cash available Less disbursements: Purchase of inventory Selling and Administrative May June Equipment Dividends Total Disbursements Excess(deficiency) of receipts over disbursements Financing: Borrowings Repayments Interest Total financing Cash balance ending Quarter Quarter