60.1K

Verified Solution

Link Copied!

Please complete in Excel and show formulas. Thanks!

TAB 1/DATA 1. Insert all the data given below into TAB1B. 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 in total. month and in total. month and in total. 2. A cash hudget. 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 65,000 85,000 115,000 80,000 75,000 b. All payments on credit sales are collected in Sales are 40% for cash and 60% on credit. 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 45% of sales. (In other words, cost of goods sold is 55% of sales) d. The company's monthly selling and administrative expenses are as followS: Variable: Shipping Other expenses Fixed: Wages and salaries 3% of sales 6% of sales $12,000

Answer & Explanation Solved by verified expert

See Answer