Question

please solve in excel/table format where applicable. thank you.

E17-14 Venus Creations sells window treatments (shades, blinds, and awnings) to both commercial and residential customers. The following information relates to its budgeted operations for the current year. Revenues Direct materials costs Direct labor costs Overhead costs Operating income (loss) Commercial $300,000 $ 30,000 100,000 85,000 215,000 $ 85,000 Residential $480,000 $ 50,000 300,000 150,000 500,000 ($ 20,000) The controller, Peggy Kingman, is concerned about the residential product line. She cannot understand why this line is not more profitable given that the installations of window coverings are less complex for residential customers. In addition, the residen- tial client base resides in close proximity to the company office, so travel costs are not as expensive on a per client visit for residential customers. As a result, she has decided to take a closer look at the overhead costs assigned to the two product lines to determine whether a more accurate product costing model can be developed. Here are the three activity cost pools and related information she developed: Activity Cost Pools Estimated Overhead Cost Drivers Scheduling and travel $85,000 Hours of travel Setup time 90,000 Number of setups Supervision 60,000 Direct labor cost Expected Use of Cost Drivers per Product Commercial Residential Scheduling and travel 750 500 Setup time 350 250 Instructions (a) Compute the activity-based overhead rates for each of the three cost pools, and deter mine the overhead cost assigned to each product line. (b) Compute the operating income for each product line, using the activity-based over head rates. (c) What do you believe Peggy Kingman should do?

Answer 1

Overhead Rate = Overhead/Total Activity
Scheduling and travel	68
Setup time	150
Supervision	15.00%
Overhead cost assigned = Overhead Rate*Activity Consumed
	Commercial	Residential
Scheduling and travel	51,000.00	34,000.00
Setup time	52,500.00	37,500.00
Supervision	15,000.00	45,000.00
Total assigned	118,500.00	116,500.00
Calculation of Operating Income
	Commercial		Residential
Revenues		$300,000		$480,000
Direct materials costs	$30,000		$50,000
Direct labor costs	100,000		300,000
Overhead costs	118,500	248,500	116,500	466,500
Operating income (loss)		$51,500		$13,500
Should not eliminate Residential Line

с D E в 1 Overhead Rate = Overhead/Total Activity 2 Scheduling and travel |=85000/(750+500) 3 Setup time =90000/(350+250) 4 Supervision =60000/(400000) 6 Overhead cost assigned = Overhead Rate Activity Consumed 8 Scheduling and travel 9 Setup time 10 Supervision 11 Total assigned 12 Commercial =B2*750 =B3*350 =B4*100000 =SUM(B8:B10) Residential =B2*500 =B3*250 =B4*300000 =SUM(C8:C10) 13 Calculation of Operating Income 14 Commercial Residential 300000 1 480000 15 Revenues 16 Direct materials costs 17 Direct labor costs 18 Overhead costs 19 Operating income (loss) 20 Should not eliminate Residential Line 30000 100000 -B11 50000 300000 -C11 -SUM B16:B181 =SUM[D16:D18) =C15-C18 =E 15-E 18