Cost Management: P10-47 Spring Manufacturing Company makes two components

Cost Management
P10-47 Comprehensive Profit Plan  
Spring Manufacturing Company makes two components identified as C12 and D57. Selected budgetary data for 2016 follow:
Finished Components
C12 D57
Requirements for each finished component:
  RM 1 10 pounds 8 pounds
  RM 2   0 4 pounds
  RM 3   2 pounds 1 pound
  Direct labor   2 hours 3 hours
Product information:
  Sales price 150 220
  Sales (units) 12,000 9,000
  Estimated beginning inventory (units) 400 150
  Desired ending inventory (units) 300 200

Direct Materials Information
Cost per pound 2 3 1
Estimated beginning inventory in pounds 3,000 1,500 1,000
Desired ending inventory in pounds 4,000 1,000 1,500

The firm expects the average wage rate to be $25 per hour in 2016. Spring Manufacturing uses direct labor hours to apply overhead. Each year the firm determines the overhead application rate for the year based on the budgeted output for the year. The firm maintains negligible work-in-process inventory and expects the cost per unit for both beginning and ending finished products inventories to be identical.
Factory Overhead Information
Indirect materials-variable 10,000
Miscellaneous supplies and tools-variable 5,000
Indirect labor-variable 40,000
Supervision-fixed 120,000
Payroll taxes and fringe benefits-variable 250,000
Maintenance costs-fixed 20,000
Maintenance costs-variable 10,080
Depreciation-fixed 71,330
Heat, light, and power-fixed 43,420
Heat, light, and power-variable 11,000
Total 580,830

Selling and Administrative Expense Information
Advertising 60,000
Sales salaries 200,000
Travel and entertainment 60,000
Depreciation-warehouse 5,000
Office salaries 60,000
Executive salaries 250,000
Supplies 4,000
Depreciation-office 6,000
Total 645,000

The effective income tax rate for the company is 40%.

Prepare an Excel spreadsheet that contains the following schedules or statements for 2016:
1. Sales budget
2. Production budget
3. Direct materials purchases budget (units and dollars)
4. Direct labor budget
5. Factory overhead budget
6. Cost of goods sold and ending finished goods inventory budgets
7.Selling and administrative expense budget
8. Budgeted income statement