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

The firm expects the average wage rate to be $25 per hour in 2022. Spring Manufacturing uses direct labor hours to apply overhead. Each year the firm determines the overhead application rate for the year based on budgeted direct labor hours for the year. The firm maintains negligible Work-in- Process Inventory and expects the cost per unit for both beginning and ending inventories of finished products 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 2022:

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, broken down into two components: Selling Expenses and Administrative Expenses

8. Budgeted income statement, the last item of which is labeled After-Tax Operating Income.

Transcribed Image Text:

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