• +1-617-874-1011 (US)
• +44-117-230-1145 (UK)

# Excel Assignment 2 Activity-Based Costing

## Managerial Accounting

You will use Microsoft Excel to solve the attached Activity-Based Costing problem

1. Enter all the data given on the back of this sheet (direct material and labor costs, estimated overhead costs, estimated total activities and activities for jobs 81 and 82). Add a column for job ____*___ using the specific activity levels for this job (found on the chart below.) Use \$3,000 for direct materials and \$2,600 for direct labor. These should be in neatly organized, well-labeled tables.

1. Using Excel formulas, compute the activity-based overhead rate for each of the five different cost pools. Again, put these in a neat table.
1. Using Excel formulas, calculate the costs of jobs 81, job 82 and your additional job (see number above.)
1. Suppose that as a result of an efficiency study, materials movements on job 82 could be reduced by half. How much would the total cost of job 82 change? Use Excel formulas and neatly show your calculations. (Don’t change your original numbers for job 82. Instead add a column for Job 82 revised.)

Your spreadsheet should be divided into two sections. The first section should contain all of the given information and only the given information. In the second section, you should use formulas and cell references. No numbers should be entered here!

Make sure your spreadsheet is formatted to fit on one page and that your name is in the body of the spreadsheet. Submit the assignment through the BlackBoard drop box by 5:00 pm on the due date.

 Job # Machine A Hours Machine B hours Setups Material Movements DL hours 85 25 1 5 227 99 86 24 2 4 137 98 87 23 3 3 128 97 88 22 4 2 119 96 89 21 5 1 146 95 90 20 6 2 155 94 91 19 7 3 236 93 92 18 8 4 248 92 93 17 9 5 257 91 94 16 10 4 266 90 95 15 11 3 275 89 96 14 12 2 284 88 97 13 13 1 315 87

Respect Manufacturing Company

```Depreciation on machine A 	 	    \$150,000
Depreciation on machine B 	 	     600,000
Machine setup cost 	 	 	     175,000
Materials handling cost  	 	     150,000
(use direct labor hours for the cost driver)
```

Estimated Activities for 2018:

```Machine hours – machine A 	 	     700
Machine hours – machine B 	  	     700
Number of setups 	 	 	     100
Number of material movements 	  5,000
Number of direct labor hours
```

Jobs started during January 2018:

```     	 	 	 	 	 	Job 81  	Job 82   	Job XX
Direct material  	 	 	 	\$3,000  	\$3,000  	\$3,000
Direct labor 	 	 	 	 	\$1,800  	\$3,000  	\$2,600
Machine hours – machine A 	 	 	20  	        12
Machine hours – machine B 	 	 	0                8
Machine setups  	 	 	        1  	         2
Material movements                              90  	         300
Direct labor hours 	 	 	 	250  	         100
```