Question: EXCEL APPLICATION EXERCISE 13 75 Computing Budgeted Factory Overhead Goal
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.
Answer to relevant QuestionsConsider the following information pertaining to a year’s operations of Hartford Manufacturing:Units sold ................ 1,600Units produced ............. 2,300Direct labor .............. $4,400Direct materials used ... “There should be a strong relationship between the factory overhead incurred and the cost-allocation base chosen for its application.” Why?Tammy’s Cosmetics uses a normal costing system and has the following balances at the end of its first year’s operations.WIP inventory ....... $231,000Finished-goods inventory ... 198,000Cost of goods sold ..... ...Refer to the chapter discussion of Enriquez Machine Parts Company beginning on page 534. Suppose Enriquez decided to use only one overhead cost pool for both departments with direct labor cost as the single cost-allocation ...The expected activity of the paper-making plant of Conroy Paper Company was 58,400 machine hours per month. Practical capacity was 73,000 machine hours per month. The standard machine hours allowed for the actual output ...
Post your question