Cost Management: A Strategic Emphasis 5th Edition by David Stout, Edward Blocher, Gary Cokins

Exercise 61

Comprehensive Profit Plan Spring Manufacturing Company makes two components identified as C12 and D57. Selected budgetary data for 2010 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
	RM1	RM2	RM3
Cost per pound	$ 2.00	$ 2.50	$ 0.50
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 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

Step 1 of 8

1.?Sales Budget

Spring Manufacturing Company Sales Budget 2010
?	C12	D57	Total
Sales (in units)	12,000	9,000	21,000
x Selling Price Per Unit	$150	$220
Total Sales Revenue	$1,800,000	$1,980,000	$3,780,000