EXCEL APPLICATION EXERCISE
13-75 Computing Budgeted Factory Overhead
Goal: Create an Excel spreadsheet to compute budgeted factory overhead rates and apply factory overhead to production. Use the results to answer questions about your findings.
Scenario: Kevin Aeronautics Company has asked you to determine its budgeted factory overhead rates. It would also like you to apply the appropriate factory overhead amounts to actual production and determine any variances. Additional background information for your spreadsheet appears in Fundamental Assignment Material 13-A1 . (Ignore data in the Fundamental Assignment Material for product M89.)
When you have completed your spreadsheet, answer the following questions:
1. What was the budgeted factory overhead rate for department A? Department B?
2. What overhead amount was distributed to department A? Was the overhead over- or underapplied? By what amount?
3. What overhead amount was distributed to department B? Was the overhead over- or underapplied? By what amount?
1. Open a new Excel spreadsheet.
2. In column A, create a bold-faced heading that contains the following:
Row 1: Chapter 13 Decision Guideline
Row 2: Kevin Aeronautics Company
Row 3: Overhead Applications Using Budgeted Rates
Row 4: Today’s Date
3. Merge and center the four heading rows across columns A–G.
4. In row 7, create the following column headings:
Column B: 20X0 Budget
Column D: 20X0 Actual
Column F: Variances
5. Merge and center the 20X0 Budget heading across columns B–C.
6. Merge and center the 20X0 Actual heading across columns D–E.
7. Merge and center the Variances heading across columns F–G.
8. In row 8, create the following center-justified column headings:
Columns B, D, and F: Dept. A
Columns C, E, and G: Dept. B
9. In column A, create the following row headings:
Row 9: Factory Overhead
Row 10: Direct-Labor Hours
Row 11: Machine Hours
Skip a row.
Row 13: Overhead Rate
Row 14: Distributed Overhead
Row 15: Over/(Under) Applied
Note: Recommended column widths: column A = 18, columns B–G = 12.
10. Use data from Fundamental Assignment Material 13-A1 to enter the amounts for the department
A and B 20X0 budget predictions and 20X0 actual results.
11. Use the appropriate formulas to calculate the following amounts:
20X0 budgeted overhead rates for depts. A and B . Row 13, columns B and C
20X0 distributed overhead for depts. A and B . Row 14, columns D and E
20X0 over/under applied overhead . Row 15, columns D and E
Flexible budget variances for depts. A and B . Row 9, columns F and G
Activity budget variance for dept. B . Row 10, column G
Activity budget variance for dept. A . Row 11, column F
Total variances for depts. A and B . Row 15, columns F and G
12. Format amounts in rows 10 and 11 as follows:
13. Format amounts in rows 9, 14, and 15 as follows:
14. Format amounts in row 13 as follows:
15. Modify the format of the total variances in row 15, columns F and G to display a top border using the default Line Style.
Border tab: Icon: Top Border
16. Save your work, and print a copy for your files.