A B C D E F H J M N Name: G# 1 Chapter 3: Applying Excel 2 3 Data 4 Allocationibus 5 Estimated manufacturing overhead cost 6 Estimated total amount of the allocation base 7 Actual manufacturing overhead cost B Actual total amount of the allocation base Machine-hours $300,000 75,000 machine-hours $290,00D 68,00D machine-hours Copy Formulas 10 Enfer a formula into each of the green cells marked with a 7 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 7 machine-hours 15 Predatornined 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 Predeterminad overhead rate ? per machine-hour 21 Actual total amount of the allocation baso ? machine-hours 22 Manufacturing overhead applied ? 23 Underapplied (overapplied) manufacturing overhead 7 24 25 26 27 2B 29 30 31 32 33 34 35 36 37 3B Formulas Ruq 1 Ch3 Roq 2 Ch3 Reg 3 Ch3 Rey 4 Ch3 + 5 C + 115% A L M P 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 formula 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-haurs $200,000 ? machine-hours 3290,000 68,00n machine-hours How much is the underapplied or overapplied manufacturing owerhead? 7 ? machne hours ? per machine hour Did it change? Why or why not? ? ? permachine-hour 7 machine-hour 1 Chapter 3: Applying Excel 2 3 DATA 4 Allocaron base 5 Estimated manufacturing overhead cost 6 Estimated total amount of the alocation base 7 Actual manufacturing overite cel 8 Actual total amount of the alacation base 9 10 11 12 Computation of the predetermined overhead rata 13 Estimated menutetuning overhead cost 14 Estimated total amount of the alocation base 15 Predetemired Overhead rate 16 17 Computation of underapplied or averapplied manufacturing overhead 18 Actual manufacturng averead cost 19 Manufacturing overtoac cost applied to Work in Prozees during the year. 20 Predetermined overhemale 21 Adual total amount ar a albestion taas 22 Manufacturing overhead appled 23 Underapplied overapplied) manufacturing overtoad 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 7 ? Formulas Req 1 Ch3 Req 2 Ch3 Red 3 Ch3 Req 4 Ch3 + ET + 100% A C L M N G K 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-haurs How much is the underapplied ar overapplied manufacturing overhead? ? machine tour 7 machina-hours ? machine hours ? per machine hour 7 ? per machine tour 7 machine-hours 1 Chapter 3: Applying Excel 2 3 DATA 4 Allocaten base 5 Estimated manufacturing overhead cost 6 Estimated total amount of the alocation base 7 Actual manufacturing overite cel 8 Actual total amount of the alacation base 9 10 11 12 Computation of the predetermined overhead rata 13 Estimated menutecturing overhead cost 14 Estimated total amount of the alocation base 15 Predetemired Overhead rate 16 17 Computation of underapplied or averapplied manufacturing overhead 18 Actual manufacturng averead cost 19 Manufacturing overtoac cost applied to Work in Prozees during the year. 20 Predetermined overhemale 21 Amul total amount of the Albation base 22 Manufacturing overhead appled 23 Underapplied overapplied) manufacturing overtoad 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 7 Formulas Req 1 Ch3 Req 2 Ch3 Red 3 Ch3 Req 4 Ch3 + ET + 100% A C L M N G 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 ? machine tour SPO 000 40 000 machine hours Why is it different from from the underapplied or everapplied manufacturing overhead in requirement 27 ? machine hours ? per machine tour 7 1 Chapter 3: Applying Excel 2 3 DATA 4 Allocaron base 5 Estimated manufacturing overhead cost 6 Estimated total amount of the alocation base 7 Actual manufacturing overite cel 8 Actual total amount of the alacation base 9 10 11 12 Computation of the predetermined overhead rata 13 Estimated menutetuning overhead cost 14 Estimated total amount of the alocation base 15 Predetemired Overhead rate 16 17 Computation of underapplied or averapplied manufacturing overhead 18 Actual manufacturng averead cost 19 Manufacturing overtoac cost applied to Work in Prozees during the year. 20 Predetermined overhemale 21 Adual total amount ar a albestion taas 22 Manufacturing overhead appled 23 Underapplied overapplied) manufacturing overtoad 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 ? per machine tour 7 machine hours 7 Formulas Req 1 Ch3 Req 2 Ch3 Req3 Ch3 Req 4 Ch3 + ET + 100% A M P D H 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 manufacturning overhead different from requirement 27 7 ? machne hours ? permachine heur ? ? permachine-hour 7 machine-hour 1 Chapter 3: Applying Excel 2 3 DATA 4 Allocaten base 5 Estimated manufacturing overhead cost 6 Estimated total amount of the alocation base 7 Actual manufacturing overite cel 8 Actual total amount of the alacation base 9 10 11 12 Computation of the predetermined overhead rata 13 Estimated menuturing overhead cost 14 Estimated total amount of the alocation base 15 Predetemired Overhead rate 16 17 Computation of underapplied or averapplied manufacturing overhead 18 Actual manufacturng averead cost 19 Manufacturing overtoac cost applied to Work in Prozees during the year. 20 Predetermined overhemale 21 Adual total amount ar a albestion taas 22 Manufacturing overhead appled 23 Underapplied overapplied) manufacturing overtoad 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 7 ? Formulas Req 1 Ch3 Req 2 Ch3 Req 3 Ch3 Req 4 Ch3 + ET + 100%