EXCEL APPLICATION EXERCISE 13 75 Computing Budgeted Factory Overhead Goal

EXCEL APPLICATION EXERCISE
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.)
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?
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 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 10: Direct-Labor Hours
Row 11: Machine Hours
Skip a row.
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