TAB 1/DATA 1. Insertall te data given below into TAB18. This is the only TAB whi

Question

TAB 1/DATA 1. Insertall te data given below into TAB18. 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. Include the following detailed budgets: 1.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. Aschedule of cash disbursements for selling and administrative expenses, by A cash hudget. Show the budget by month and in total. in total. month and in total. month and in total. 2. 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 65,000 85,000 115,000 80,000 75,000 Sales are 40% for cash and 60% 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 Thecompany's gross margin percentage is 45% of sales (in other words, cost of goods sold is 55% of sales) The company's monthly selling and administrative expenses are as follows: Variable: Shipping Other expenses Fixed: Wages and salaries b. d. 3% of sales 6% of sales $12,000

Explanation / Answer

Sales Budget April May June Total Budegeted Sales 85000 115000 80000 280000 Schedule of expected cash collections: April May June Total Cash Sales 34000 46000 32000 112000 Credit Sales:    March sales 39000 39000    April Sales 51000 51000    May sales 69000 69000 Total Collections 73000 97000 101000 271000 Receivables 48000 Merchandise purchase budget April May June Total Cost of goods sold (55%) of sales 46750 63250 44000 154000 Add: Ending inventory 25300 17600 16500 16500           (40% of next month's cost) Total cost of goods neded 72050 80850 60500 170500 Less: Beginning inventory 18700 25300 17600 18700 Budgeted purchases 53350 55550 42900 151800 Schedule of cash disbursements for purchases April May June Total Budgeted purchases 53350 55550 42900 151800     March purchases 20075 20075     April Purchases 26675 26675 53350     May Purchases 27775 27775 55550     June Purchases 21450 21450 Cash payment for purchases 46750 54450 49225 150425 Payables 21450 Schedule of cash payments S&A expenses April May June Total Variable expenses     Shipping expenses (3% of sales) 2550 3450 2400 8400     Other expenses (6% of sales) 5100 6900 4800 16800 Total Variable expenses 7650 10350 7200 25200 Fixed Expenses    Wages and salaries 12000 12000 12000 36000     Advertising 7000 7000 7000 21000 Total fixed expenses 19000 19000 19000 57000 Total cash selling and administrative expenses 26650 29350 26200 82200 Cash Budget for the qurter ending June30, 2018 April May June Total Beginning cash balance 8000 5100 15300 8000 Add: Collections 73000 97000 101000 271000 Total cash available 81000 102100 116300 279000 Cash disbursements:      For purchases 46750 54450 49225 150425      For S& A expenses 26650 29350 26200 82200      For equipment purchases 14500 3000 17500      For dividends 4000 4000 Total cash disbursements 87900 86800 79425 254125 Ending Cash Balance -6900 15300 36875 24875 Minimum Cash balance 5000 5000 5000 5000 Excess / (Shortage) -11900 10300 31875 19875 Financing: Borrowing / (Repayments) 12000 0 -12000 0 Interest -360 -360 Total Financing 12000 0 -12360 -360 Ending Cash Balance 5100 15300 24515 24515 BLUEBIRD COMPANY Income Statement for the quarter ending June 30, 2018 Sales 280000 Cost of goods sold 154000 Gross profit 126000 Selling and administration expenses     Shipping expenses 8400     Other expenses   16800    Wages and salaries 36000     Advertising 21000    Depreciation 5000 Total selling and edministrative expenses 87200 Net operating income 38800 Interest expense 360 Net income 38440 BLUEBIRD COMPANY Balance Sheet as at June30, 2018 Cash 24515 Accounts Receivable 48000 Inventory 16500 Buildings and equipment, net    Beginning balance 214100    Purchases 17500    Depreciation -5000 226600 Total Assets 315615 Liabilities and Stockholders' equity Accounts Payable 21450 Common Stock 183575 Retained Earnings    Beginning balance 76150    Net income 38440    Dividends -4000    Ending balance 110590 Total Liabilities and Stockholders equity 315615

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.