1A: Schedule of Expected Cash Collections Cash sales Credit sales Total Cash Collections 13: Merchandise Purchases Budget Budgeted Cost of Goods Sold Add desired ending inventory Total Needs Less beginning inventory Required dollar purchases 10: Schedule of cash disbursements for merchandise purchases March Purchases April Purchases May Purchases June Purchases Total cash disbursements for purchases 1 D: Schedule of cash disbursements for selling and administrative expenses Salaries and wages Shipping Advertising Other expenses Total cash disbursements for selling and administrative expenses April May June Quarter 26,845 26.845 Newport Tie Company Balance Sheet At June 30, 2021 For cells in this color, use Excel formulas and calculate the answer based on information found in other cells. Assets Current assets: Cash Accounts Receivable (Hint: 70% of budgeted sales in June will be collected in July.) Inventory Total current assets (Excel formula has been inserted into cell D11. Number in D11 will be automatically updated after the above cells are completed.) Buildings and equipment, net Total Assets (Excel formula has been inserted into cell D13. Number in D13 will be automatically updated after the above cells are completed.) Liabilities and Stockholders' Equity Liabilities: Accounts Payable Note Payable (Hint: If the loan is not fully repaid, the remaining balance of the borrowings at the end of the quarter is recorded as Note Payable here.) Total Liabilities $0 (Excel formula has been inserted into cell D18. Number in D18 will be automatically updated after the above cells are completed.) Stockholders' Equity: Common stock 103.555 (Given information in this template) Retained Earnings Total Stockholders' Equity 103.555 (Excel formula has been inserted into cell D23. Number in D23 will be automatically updated after the above cells are completed.) Total Liabilities and Stockholders' EquityNEWPORT TIE COMPANY Cash Budget For the threemonth period ending June 30, 2021 Beginning cash balance Add cash collections Total cash available Less cash disbursements: Inventory purchases Selling and Administrative Equipment purchases Dividends Total cash disbursements Excess (deficiency) of cash available over disbursements Financing: Borrowings Repayments Interest Total financing Ending cash balance April May June Quarter 9000 9,000 TAB1B Newport Tie Company Actual Sales for March and budgeted sales ($) for April-July are as follows: March [actual] 82,000 April 85,000 May 95,000 June 90,000 July 75,000 Collection on Sales Sales collected Current Month 30% Sales collected Following Month 70% Cost of Goods Sold (percentage of sales) 652 Desired ending invenories (Percentage of following months COGS ) 20% Inventory Purchases Paid as Follows In month of purchase 50% In following month 50% Variable Monthly Expenses (percentage of sales) Shipping 42 Other expenses Fixed Monthly Expenses Salaries and wages $10,000 Advertising 8,000 Fixed Quarterly Expenses Depreciation 6.000 Equipment Purchases during the Quarter April 11,500 May 2.000 Dividends declared and paid in June 5,500 Agreem ent with Bank Borrowing increments $1,000 Maxim um borrowing amount $25,000 Interest Rate per Month 1% Repayment increments 1,000 Required minimum balance $7.000TAB1A Newport Tie Company Balance Sheet 31-Mar-21 As sets Current assets: Cash 9.000 Accounts Receivable 57.400 Inventory 11.050 Total current assets 77.450 Buildings and equipment, net 254.100 Total Assets 331.550 Liabilities and Stockholders' Equity Liabilities: Accounts Payable 26.845 Stockholders' Equity: Common stock 103.555 Retained Earnings 201.150 Total Stockholders' Equity 304.705 Total Liabilities and Stockholders' Equity 331.550Newport Tie Company For cells i Income Statement based on i For the Quarter Ended June 30, 2021 Sales $ 270,000 (Given inf Cost of Goods Sold (Hint: The Gross Margin Selling and Administrative Expenses: Salaries and wages Shipping Advertising Depreciation Other expenses (Excel for Net operating income Other Expenses and Losses: Interest Expense Net Income (loss)
