View the step-by-step solution to:

# 12-59 Allocating Costs Using Direct and Step-Down Methods Goal: Create an Excel spreadsheet to allocate costs using the direct method and the...

See attached file.
12-59 Allocating Costs Using Direct and Step-Down Methods
Goal: Create an Excel spreadsheet to allocate costs using the direct method and the stepdown
Scenario: Antonio Cleaning has asked you to help them determine the best method for
allocating costs from their service departments to their producing departments.
12-B2.
12-B2 Allocation of Service Department Costs
Antonio Cleaning provides cleaning services for a variety of clients. The company has two producing
departments, Residential and Commercial, and two service departments, Personnel and
Administrative. The company has decided to allocate all service department costs to the producing
departments-Personnel on the basis of number of employees and Administrative on the basis of
direct department costs. The budget for 20X7 shows
1. What are the total costs for the Residential department using the direct method?
What are the total costs for the Commercial department using the direct method?
2. What are the total costs for the Residential department using the step-down method?
3. What are the total costs for the Commercial department using the step-down method?
4. Which method would you recommend that Antonio Cleaning use to allocate their
service departments' costs to their producing departments? Why?
Step-by-Step:
1. Open a new Excel spreadsheet.
2. In column A, create a bold-faced heading that contains the following:
Row 1: Chapter 12 Decision Guideline
Row 2: Dallas Cleaning
Row 3: Cost Allocations from Service Departments to Producing Departments
Row 4: Today's Date
3. Merge and center the four heading rows across columns A through H.
4. In row 7, create the following bold-faced, center-justified column headings:
Column B: Personnel
Column D: Residential
Column E: Commercial
Column F: Total Res/Comm
Column H: Grand Total
5. Change the format of the column headings in row 7 to permit the titles to be displayed
on multiple lines within a single cell.
Alignment tab: Wrap Text: Checked
6. In column A, create the following row headings:
Row 8: Direct Department Costs
Row 9: Number of Employees
Skip 2 rows
Note: Adjust the width of column A to 27.14.
7. In column A, create the following bold-faced, underlined row heading:
Row 12: Direct Method:
8. In column A, create the following row headings:
Row 13: Direct Department Costs
Row 14: Personnel Allocation
Row 16: Total Costs
Skip 2 rows
9. In column A, create the following bold-faced, underlined row heading:
Row 19: Step-down Method:
10. In column A, create the following row headings:
Row 20: Direct Department Costs
Row 21: Step 1-Personnel Allocation
Row 23: Total Costs
11. Use data from Fundamental Assignment 12-B2 to enter the amounts in columns B
through E for rows 8, 9, 13, and 20.
12. Use the appropriate calculations to do the totals in row 8 for columns F and H.
Use the appropriate calculations to do the totals in row 9 for columns F and G.
13. Use the appropriate formulas to allocate the costs from the service departments to the
producing departments using each of the methods.
14. Use the appropriate calculations to do the totals in columns B through E and in column H,
rows 16 and 23.
15. Format amounts in columns B through H, rows 8, 13, 16, 20, and 23 as
Number tab: Category: Accounting
Decimal: 0
Symbol: \$
16. Format the amount in columns B through E, rows 14, 15, 21, and 22 as
Number tab: Category: Accounting
Decimal: 0
Symbol: None
17. Change the format of the total costs amounts in columns B through E, rows 16 and 23,
to display a top border, using the default line style.
Border tab: Icon: Top Border
18. Change the format of the amounts in row 9, columns B through G to center justified.

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents