Question

Follow instructions and create all parts on excel 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 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

Answer 1

A. 1 Please hit LIKE button if this helped. For any further explanation, please put your query in comment, will get back to y March April May 93,000 $ 162,000 une 85,000$ 4 Actual Sale 5 Cash Sale 6 Credit Sale 7 Gross Margin 8 COGS 165,000 140,000 25% 35% 65% Common Stock Retained Earning $190,000 32,510 Building and Equipment 214,100 Inventory Cash 10 Shipping 11 Other Expense 12 Wages and Salareis 13 Advertising 14 Depreciation 15 Inventory 16 Payment of inventory 17 18 Equipment Purchase 19 20 Dividend 21 1% of sale 6% of sale 6,045 9,000 28,000 per month 2,500 per month 6,000 quarterly 10% of following month COGS 50% Same month 50% next month April May June 3,200 18,000 4,700

23 24 1A Schedule of ex 25 Cash Sale 26 Cash collection from credit sale during the next month 27 28 Total Cash Recei d cash collection -D3 ЕЗ Recelvable SUMID25:F25) ES4 SB6 sSUM(D25:D27) =SUM (E25:E27) =SUM(F25:F27 SUMIG25:G27]SUM(H25:H27 30 31 1B Merchandise Purchase Budget 32 1udgeted Cost nf ionds sold 31 Add: Desired erin ivenlur 15 units יוג.ilable 36 Less: Benin Ivenlur FE3 -G24 Ju 14 SlISH D33 SBS15 SUM:4 C33 B15 -SUM(C35:C36) SUM(231 SUM33:14 UMI134 --C31 SUM(D35:D36) SUM E35:E36) SUM(F35:F36) -F31 SUM4 D30 -SUM(G35:G36) -D3 Budgeted Purchase 38 39 1C Schedule of cash disbursment 40 41 Inventory Purchases: 42 March Purchase 43 April Purchase 44 May Purchase 45 June Purchase 46 Total -D32 -E32 -F32 -G32 Payable -SUMID42:F42) SUM D43:F43) SUMID44:F44) SUMID45:F45) -F37 B17 SUM G42:G45) -C37 B17 D$37 B16 D37 B17 -E37 B17 -F37 B16 E37 B16 t for Purchases of Material SUM(D42:D45) SUM(E42:E45)SUM(F42:F45

47 Other Expense 48 Salaries and wages 49 Shippinn 50 Advertising 51 Other Expense 52 53 54 Total Cash P 55 Total Cash Disbursment 56 SUM (D48:F48) SUM D49:F49) SUM(D50:F50) SUMID51:F51) C12 -D48 E48 C13 -D4 SBS11 E4SB11 F4'SB$11 E50 ent for other ex -SUMID48:D53 SUM(E48:E53 SUM(F48:FS3 D54+D46 -SUM G48:G53 G54+G46 -E54+E46 -F54+F46 58 5. Summary Cash Budget 59 60 Cash Beginning Balance 61 Add: Cash Collection 62 Total cash Available 63 Less: Cash Disbursment 64 Purchase of Inventory 65 Selling and Admin Expense 66 Equipment 67 Dividends -D40 -G11 -D28 -SUM D60:D61) SUM(E60:E61) SUM(F60:F61 E40 -D76 -F28 -F40 -E76 F28 -G40 -SUM(D61:F61) -SUM(G60:G61 F46 -E54 -C19 SUM(D64:F64) SUMID65:F65) SUM D66:F66) -SUM(D67:F67) -F46 F54 -C18 SUM D64:D68) SUM E64:E68) SUM(F64:F68)SUM G64:G68) 69 Total Cash Disbursment 70 Excess/(Deficiency) 71 Financing 72 Borrowing 73 Repayments D62-D69 -E62-E69 -F62-F69 -G62-G69 SUM(D72:F72) -SUM(D73:F73) 8100 300

73 Repayments 74 Interest 5 Total Flnancln 76 Change in Cash Balance SUM D73:F73) -sijM(D74:F74) -SUM(G72:G74 -G70+G75 -8100 ((072*4%*7/12)+(F77*4%" 1/12)) -SUM D72:D74)SUM(E72:E74) SUM F72:F74 -D70+D75 -E70+E75 -F70+F75 /8 9 Income Statement 81 Sales Revenue 82 Less: Cost of Goods Sold 83 Gross Profit 81 Less: Selling and Admin Expense 85 -A48 86A49 87 л50 88 A51 -G33 SUM(D81:D82) -G48 -G49 -G50 G51 -C14 -F74 89 Depreciation 90 Interest Expense 91 92 Total Selling and Admin Ex 93 Net Income SUMIC85:C91 SUM(D83:D92)

94 95 Part 8: Retained Earning 96 97 Retained Earning, Beginning Balance 98 Add: Net Income 99 Less: Dividend 100 Retained Earning, Ending Balance 101 102 Part 9: Budgeted Balance sheet 103 04 Cash 105 Accounts receivable 106 Inventory 107 Buildings and equipment (net of accumulated depreciation) 108 Total assets 109 110 Accounts payable 111 Common Stock 112 Retained earnings 113 Total liabilities and stockholders' equit 114 -G8 D93 --G67 SUM(C97:C99) -G76 H28 G34 G9+G66-C89 -SUM(B104:B107 H45 -C100 -SUM(B110:B112