Spring Manufacturing Company makes two components identified as C12 and D57. Selected budgetary data for 2010 follow:

The firm expects the average wage rate to be $25 per hour in 2010. 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 percent.

 

Required

Prepare an Excel spreadsheet that contains the following schedules or statements for 2010:

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 general administrative expense budget

8. Budgeted income statement

Finished Components D57 C12 Requirements for each finished component: RM1 8 pounds 4 pounds 1 pound 3 hours 10 pounds RM2 RM3 Direct labor Product information: Sales price Sales units Estimated beginning inventory (units) Desired ending inventory (units) 2 pounds 2 hours S150 $220 12,000 9,000 400 150 300 200