• +1-617-874-1011 (US)
  • +44-117-230-1145 (UK)
Live Chat
Follow Us:

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.

* You will receive an e-mail with your specific job assignment.

  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

Estimated Overhead Costs for 2018:

Depreciation on machine A 	 	    $150,000 	
Depreciation on machine B 	 	     600,000 	
Machine setup cost 	 	 	     175,000 	
Materials handling cost  	 	     150,000 	
Other overhead costs 	 	 	     180,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