Activity-Based Costing:

Excel needed.

Bumblebee Mobiles manufactures a line of cell phones. The management has identified the following overhead costs and related cost drivers for the coming year.

Overhead Item	Expected Cost	Cost Driver	Quantity
Setup Costs	$24,000	Number of setups	60
Ordering Costs	9,000	Number of Orders	90
Machine costs	36,000	Machine Hours	36,000
Power	5,000	Kilowatt hours	100,000

The following were incurred in manufacturing two of their cell phones, Bubble and Burst, during the first quarter.

	Bubble	Burst
Direct materials	$1,300	$1,525
Direct Labor	$900	$900
Units Completed	800	500
Number of setups	6	10
Number of Orders	60	120
Machine Hours	1,500	2,000
Kilowatt hours	6,000	5,000

Enter the Formulas in the appropriate cells on the excel worksheet and solve the worksheet. For example, FORMULA 1 = B8/E8.

Please show me what formulas you use.

Check figure: Cost per Unit Bubble (cell B43) should be $8.75

B D E F 1 2 3 4 ABC Activity-Based Costing Amount 60 900 36,000 100,000 36,000 5 Data Section 6 7 Overhead Item Expected Cost Cost Driver 8 Setup costs $24,000 Number of setups 9 Ordering costs 9,000 Number of orders 10 Machine costs Machine hours 11 Power 5,000 Kilowatt hours 12 13 14 Bubble Burst 15 Direct materials $1,300 $1,525 16 Direct labor $900 $900 17 Units completed 800 500 18 Number of setups 6 10 19 Number of orders 60 120 20 Machine hours 1,500 2,000 21 Kilowatt hours 6,000 5,000 22 23 Answer Section 24 25 Pool Rates 26 Setup FORMULA1 per setup 27 Ordering FORMULA2 per order 28 Machine Costs FORMULA3 per machine hour 29 Power FORMULA4 per kilowatt hour 30 31 Product Costs 32 33 Bubble Burst 34 Direct materials $1,300 $1,525 35 Direct labor 900 900 36 Overhead 37 Setups FORMULA5 FORMULA6 38 Ordering FORMULAZ FORMULAS 39 Machine costs FORMULAS FORMULA10 40 Power FORMULA11 FORMULA12 41 Total cost $2,200 $2,425 42 43 Unit cost FORMULA13 FORMULA14 44 45 46 47 48 49 50