Excel Assignment 2 Activity-Based Costing
Managerial Accounting
You will use Microsoft Excel to solve the attached Activity-Based Costing problem
- 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.
- Using Excel formulas, compute the activity-based overhead rate for each of the five different cost pools. Again, put these in a neat table.
- Using Excel formulas, calculate the costs of jobs 81, job 82 and your additional job (see number above.)
- 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
