ACC 561 EXCEL Application Exercise 12-59, Allocating Costs Using Direct and Step-Down Methods
ACC 561 EXCEL Application Exercise 12-59, Allocating Costs
Using Direct and Step-Down Methods, on p. 584
Introduction to Management Accounting: Horngren, C. T.,
Sundem, G. L., Stratton, W. O., Burgstahler, D., & Schatzberg, J. (2008).
Introduction to Management Accounting (14th ed.). Upper Saddle River, New
Jersey: Pearson-Prentice Hall.
Axia College of University of Phoenix (UoP)
Complete the following Problem Sets from the text. Show your
work.
EXCEL Application Exercise 12-59, Allocating Costs Using
Direct and Step-Down Methods, on p. 584
12-59 Allocating Costs Using Direct and Step-Down Methods
Goal: Create an Excel spreadsheet to allocate costs using
the direct method and the step-down method. Use the results to answer questions
about your findings.
Scenario: Antonio Cleaning has asked you to help them
determine the best method for allocating costs from their service departments
to their producing departments. Additional background information for your
spreadsheet appears in Fundamental Assignment Material 12-B2. Exhibit 12-4 on
page 532 illustrates the types of calculations that are used for allocating
costs using the direct method and the step-down method.
When you have completed your spreadsheet, answer the
following questions:
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 C: Administrative
Column D: Residential
Column E: Commercial
Column F: Total Res/Comm
Column G: Total Admin/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
Note: Adjust column widths so that headings use only two
lines.
Adjust row height to ensure that row is same height as
adjusted headings.
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 15: Administrative 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 22: Step 2—Administrative 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.
19. Save your work to disk, and print a copy for your files.
