D E F G H K G#: Copy Formulas A B C 1 Chapter 3: Applying Excel Name: 2 3 Data 4 Allocation base Machine-hours Estimated manufacturing overhead cost $300,000 6 Estimated total amount of the allocation base 75,000 machine-hours 7 Actual manufacturing overhead cost $290,000 8 Actual total amount of the allocation base 68,000 machine-hours 9 10 Enter a formula into each of the green cells marked with a ? below. Once complete use the "Copy Formula" button. 11 12 Computation of the predetermined overhead rate 13 Estimated manufacturing overhead cost ? 14 Estimated total amount of the allocation base ? machine-hours 15 Predetermined overhead rate ? per machine-hour 16 17 Computation of underapplied or overapplied manufacturing overhead 18 Actual manufacturing overhead cost ? 19 Manufacturing overhead cost applied to Work in Process during the year: 20 Predetermined overhead rate 7 per machine-hour 21 Actual total amount of the allocation base 7 machine-hours 22 Manufacturing overhead applied 23 Underapplied (overapplied) manufacturing overhead ? 24 25 26 27 28 29 30 ? 31 Formulas R 1 CH3 Req 2 Ch3 Rey 3 Ch3 Req 4 Ch3 + EP - + 137% A B C L D G H Req. 1: Enter 60,000 machine hours in the estimated total amount of the allocation base cell in the data section at the left. If your formulas are correct the predetermined overhead rate should now be $5.00 per machine-hour. If not, go back to the formulas page, make corrections, press the copy formulas button, and recheck your answers until they are correct. When correct, answer the questions below. Machine-hours $300,000 z machine hours $290,000 68.000 machine-hours How much is the underapplied or overapplied manufacturing overhead? ? ? machine-hours ? per machine hour Did it change? Why ar why not? 1 Chapter 3: Applying Excel 2 3 Data 4 Allocation base Estimated manufacturing overhead cost 6 Estimated total amount of the allocation base 7 Actual manufacturing overhead cost 8 Actual total amount of the allocation base 9 10 11 12 Computation of the predetermined overhead rate 13 Estimated manufacturing overhead cost 14 Estimated total amount of the allocation base 15 Predetermined overhead rate 16 17 Computation of underapplied or overapplied manufacturing overhead 18 Actual manufacturing overhead cost 19 Manufacturing overhead cost applied to Work in Process during the year 20 Predetermined overhead rate 21 Actual total amount of the allocation base 22 Manufacturing overhead applied 23 Underapplied (overapplied) manufacturing overhead 24 25 26 27 28 29 30 31 ? ? per machine hour ? machine-hours ? 7 32 33 34 Formulas Req 1 Ch3 Req 2 Ch3 Red 3 Ch3 Reg 4 Ch3 + + 128% A B D E G H Req. 2 Enter the data from requirement 2 on page 133 of the textbook in the green data cells on the left of this sheet. Machine-hours 1 Chapter 3: Applying Excel 2 3 Data 4 Allocation base Estimated manufacturing overhead cost 6 Estimated total amount of the allocation base 7 Actual manufacturing overhead cost 8 Actual total amount of the allocation base How much is the underapplied or overapplied manufacturing overhead? 7 machine-hours ? ? machine-hours 9 ? ? machine-hours 2 per machine-hour 7 10 11 12 Computation of the predetermined overhead rate 13 Estimated manufacturing overhead cost 14 Estimated total amount of the allocation base 15 Predetermined overhead rate 16 17 Computation of underapplied or overapplied manufacturing overhead 18 Actual manufacturing overhead cost 19 Manufacturing overhead cost applied to Work in Process during the year 20 Predetermined overhead rate 21 Actual total amount of the allocation base 22 Manufacturing overhead applied 23 Underapplied (overapplied) manufacturing overhead 24 25 26 27 28 29 30 31 7 per machine-hou ? machine-hours ? 7 32 33 34 Formulas Reg 1 Ch3 Reg 2 Ch3 Reg 3 Ch3 Reg 4 Ch3 + # Cm + 126% A B C F D E G H J K Req. 3: Enter 40,000 machine hours in the estimated total amount of the allocation base cell In the data section at the left and keep the rest of the data the same as requirement 2. What happens to the underapplied or overapplied manufacturing overhead? Machine-hours $100,000 ? Imachine hours $90,000 40,000 machine hours Why is it different from from the underapplied or overapplied manufacturning overhead In requirement 2? 1 Chapter 3: Applying Excel 2 3 Data 4 Allocation base 5 Estimated manufacturing overhead cost 6 Estimated total amount of the allocation base 7 Actual manufacturing overhead cost 8 Actual total amount of the allocation base 9 10 11 12 Computation of the predetermined overhead rate 13 Estimated manufacturing overhead cost 14 Estimated total amount of the allocation base 15 Predetermined overhead rate 16 17 Computation of underapplied or overapplied manufacturing overhead 18 Actual manufacturing overhead cost 19 Manufacturing overhead cost applied to Work In Process during the year. 20 Predetermined overhead rate 21 Actual total amount of the allocation base 22 Manufacturing overhead applied 23 Underapplied (overapplied) manufacturing overhead 24 25 26 27 28 ? 7 machine hours ? per machine-hour ? ? per machine-hour ? machine-hours ? ? 29 30 31 32 Formula Req 1 Ch3 Req 2 Ch3 Req3 Ch3 Req 4 Ch3 + + 130% A B C E G H K Req. 4: Enter $100,000 in the actual manufacturing overhead cost base cell in the data section at the left. What is the underapplied or overapplied manufacturing overhead now? Machine-hours $100,000 50,000 machine-hours 40.000 machine-hours Why is the amount of the underapplied or overapplied manufacturing overhead different from requirement 27 1 Chapter 3: Applying Excel 2 3 Data 4 Allocation base 5 Estimated manufacturing overhead cost 6 Estimated total amount of the allocation base Actual manufacturing overhead cost 8 Actual total amount of the allocation base 9 10 11 12 Computation of the predetermined overhead rate 13 Estimated manufacturing overhead cost 14 Estimated total amount of the allocation base 15 Predetermined overhead rate 16 17 Computation of underapplied or overapplied manufacturing overhead 18 Actual manufacturing overhead cost 19 Manufacturing overhead cost applied to Work In Process during the year. 20 Predetermined overhead rate 21 Actual total amount of the allocation base 22 Manufacturing overhead applied 23 Underapplied (overapplied) manufacturing overhead 24 25 26 27 28 ? 7 machine-hours ? per machine-hour ? ? per machino-hour ? machine-hours ? ? 29 30 31 32 Formulas Req 1 Ch3 Reg 2 Ch3 Req 3 Cha Req 4 Ch3 # G + 130%