ACCT 222 Problems Excel Template 7, Fall 2014

Name: Section: When totals and subtotals are correct the cell will change to green. Exercise 7-2 a. C. Howland Manufacturing Co. Production Budget For the Month Ended March 31, 20-- Units to be sold Ending inventory required Total Beginning inventory Units to be manufactured during March b. C. Howland Manufacturing Co. Direct Materials Budget For the Month Ended March 31, 20-- Material A Material B Materials needed for production of 18,000 units gal. lbs. Ending inventory required Total gal. lbs. Beginning inventory Units to be purchased gal. lbs. Unit cost Materials purchases budget Name: Section: When totals and subtotals are correct the cell will change to green. Exercise 7-3 a. J. Reutlinger Manufacturing Co. Production Budget For the Month Ended October 31, 20-- Units Sales Plus desired ending inventory, October 31 Total Less estimated beginning inventory, October 1 Total production b. J. Reutlinger Manufacturing Co. Direct Labor Budget For the Month Ended October 31, 20-- Cutting Assembly Total Hours required for production: Cutting hours Assembly hours Hourly rate Total direct labor cost Name: Section: When totals and subtotals are correct the cell will change to green. Exercise 7-4 Highlands Manufacturing Company Cost of Goods Sold Budget For the Year Ended December 31, 2013 Finished goods inventory, Jan. 1 Work in process inventory, Jan. 1 Direct materials inventory, Jan. 1 Direct materials purchases Direct materials available Cost of direct materials used Direct labor Factory overhead Total manufacturing costs Total work in process during year Cost of goods manufactured Cost of goods available for sale Cost of goods sold Name: Section: When totals and subtotals are correct the cell will change to green. Exercise 7-5 Kenwood Manufacturing Company Cost of Goods Sold Budget For the Year Ended December 31, 2013 Finished goods inventory, Jan. 1 Work in process inventory, Jan. 1 Direct materials inventory, Jan. 1 Direct materials purchases Direct materials available Cost of direct materials used Direct labor Factory overhead Total manufacturing costs Total work in process during year Cost of goods manufactured Cost of goods available for sale Cost of goods sold Name: Section: When totals and subtotals are correct the cell will change to green. Exercise 7-6 Grecian Company Budgeted Income Statement For the Year Ended December 31, 2013 Sales Income tax Name: Section: When totals and subtotals are correct the cell will change to green. Exercise 7-8 Flexible Budget 14,000 Units 15,000 Units 16,000 Units Sales Less variable costs: Direct materials Direct labor Variable factory overhead Variable selling and administrative expense Total variable costs Contribution margin Less fixed costs: Fixed factory overhead Fixed selling and administrative expense Total fixed costs Operating income Name: Section: When contribution margin and operating income amounts are correct the cell will change to green. Exercise 7-9 Riviera Manufacturing, Inc. Performance Report For the Month Ending May 31, 20-- Variance Sales Less variable costs: Direct materials Direct labor Variable factory overhead Variable selling and administrative expense Contribution margin Less fixed costs: Fixed factory overhead Fixed selling and administrative expense Operating income Budget (5,000 units) Actual (5,000 units) Name: Section: When totals and subtotals are correct the cell will change to green. Problem 7-2 1. Royal Tire Company Sales Budget For the Year Ended December 31, 2013 Product Total Sales Passenger car tires Truck tires Total 2. Royal Tire Company Production Budget For the Year Ended December 31, 2013 Units Truck Tires Sales (from sales budget) Plus Total Less Total production 3. Royal Tire Company Direct Materials Budget For the Year Ended December 31, 2013 Direct Materials Total Quantities required for production: Passenger car tires (total prod. x lbs. per unit) Truck tires (total production x lbs. per unit) Plus Total Less Total quantity to be purchased Unit price Total direct materials purchases Unit Sales Volume Unit Selling Price Passenger Car Tires Rubber (lbs.) Steel Belts (lbs.) Name: Section: When totals and subtotals are correct the cell will change to green. Problem 7-2 4. Royal Tire Company Direct Labor Budget For the Year Ended December 31, 2013 Department Total Molding Finishing Hours required for production: Passenger car tires (total production x hr. per unit) Truck tires (total production x hr. per unit) Total Hourly rate Total direct labor cost 5. Royal Tire Company Factory Overhead Budget For the Year Ended December 31, 2013 Indirect materials Indirect labor Depreciation of building and equipment Power and light Total factory overhead cost 6. Royal Tire Company Cost of Goods Sold Budget For the Year Ended December 31, 2013 Finished goods inventory, Jan. 1 Direct materials inventory, Jan. 1 * Direct materials purchases Total direct materials available Less direct materials inventory, Dec. 31 ** Cost of direct materials used Direct labor Factory overhead Cost of goods manufactured Cost of goods available for sale Less finished goods inventory, Dec. 31 Cost of goods sold *Direct materials inv., Jan. 1: Pounds Cost Total Rubber Steel belts Total **Direct materials inv., Dec. 31: Name: Section: When totals and subtotals are correct the cell will change to green. Problem 7-2 Rubber Steel belts Total Name: Section: When totals and subtotals are correct the cell will change to green. Problem 7-3 1. Royal Tire Company Selling and Administrative Expenses Budget For the Year Ended December 31, 2013 Selling expenses: Advertising expense Sales salaries expense Travel expense Total selling expenses Administrative expenses: Office salaries expense Officers' salaries expense Office rent expense Office supplies expense Telephone and fax expense Total administrative expenses Total selling and administrative expenses 2. Royal Tire Company Budgeted Income Statement For the Year Ended December 31, 2013 Sales Cost of goods sold Gross profit Selling and administrative expenses Income from operations Income tax Net Income Name: Section: When contribution margin and operating income amounts are correct the cell will change to green. Problem 7-6 Flexible Budget Unit $ 29,000 Units 31,000 Units Sales Direct materials: Lumber Paint Direct labor: Cutting Assembly Paint Variable factory overhead Variable selling and administrative expense Total variable expense Contribution margin Less fixed costs: Fixed factory overhead Fixed selling and administrative expens Operating income change to green. Name: Section: When contribution margin and operating income amounts are correct the cell will change to green. Problem 7-7 1. Scottsdale Styles, Inc. Performance Report May, 2013 Variance Sales Direct materials: Lumber Paint Direct labor: Cutting Assembly Paint Variable factory overhead Variable selling and administrative expense Total variable expense Contribution margin Less fixed costs: Fixed factory overhead Fixed selling and administrative expens Operating income 2. 3. Budgeted (31,000 units) Actual (31,000 units) Name: Section: When totals and subtotals are correct the cell will change to green. Problem 7-9 1. Factory Overhead Cost Budget Percent of normal capacity 80% 90% 110% Number of units Number of standard direct labor hours Budgeted factory overhead: Fixed cost: Depreciation on building and machinery Taxes on building and machinery Insurance on building and machinery Superintendent's salary Supervisors' salary Maintenance wages Total fixed cost Variable cost: Repairs Maintenance supplies Other supplies Payroll taxes Small tools Total variable cost Total factory overhead cost Name: Section: When totals and subtotals are correct the cell will change to green. Problem 7-9 2. Factory Overhead Cost Budget Percent of normal capacity 80% 90% 110% Number of units Budgeted factory overhead: Fixed cost: Taxes on building and machinery Insurance on building and machinery Superintendent's salary Total fixed cost Semi-fixed cost: Depreciation of building and machinery Supervisors' salaries Maintenance wages Repairs Total semifixed cost Variable cost: Other supplies Payroll taxes Small tools Maintenance supplies Total variable cost Total factory overhead cost Name: Section: When totals and subtotals are correct the cell will change to green. Problem 7-10 1. Factory Overhead Cost Budget Percent of normal capacity 80% 90% 110% Number of units Number of standard direct labor hours Budgeted factory overhead: Fixed cost: Depreciation on building and machinery Taxes on building and machinery Insurance on building and machinery Superintendent's salary Supervisors' salary Maintenance wages Total fixed cost Variable cost: Repairs Maintenance supplies Other supplies Payroll taxes Small tools Total variable cost Total factory overhead cost Name: Section: When totals and subtotals are correct the cell will change to green. Problem 7-10 2. Factory Overhead Cost Budget Percent of normal capacity 80% 90% 110% Number of units Budgeted factory overhead: Fixed cost: Taxes on building and machinery Insurance on building and machinery Superintendent's salary Total fixed cost Semi-fixed cost: Depreciation of building and machinery Supervisors' salaries Maintenance wages Repairs Total semifixed cost Variable cost: Other supplies Payroll taxes Small tools Maintenance supplies Total variable cost Total factory overhead cost