1 Chapter 2: Applying Excel Name: G+: 2 $390,000 3 Data 4 Markup on job cost 75% 5 6 Department 7 Milling Assembly 8 Machine-hours 60,000 3.000 9 Direct labor-hours 8,000 80,000 10 Total fixed manufacturing overhead cost $500.000 11 Variable manufacturing overhead per machine-hour $2.00 12 Variable manufacturing overhead per direct labor-hour $3.75 13 14 Cost summary for Job 407 Department 15 Milling Assembly 16 Machine-hours 90 4 17 Direct labor-hours 5 20 18 Direct materials Saop $370 19 Direct labor cost $70 $280 20 21 Enter a formula into each of the green calls marked with a 7 below. Once complete use the "Copy Formula" button 22 23 Step 1: Calculate the estimated total manufacturing overhead cost for each department 24 Milling Assembly 25 Total fixed manufacturing overhead cost ? 7 26 Variable manufacturing overhead per machine-hour or direct labor-hour ? ? 27 Total machine-hours or direct labor-hours ? 28 Total variable manufacturing overhead ? ? 29 Total manufacturing overhead ? 30 Copy Formulas ? ? Milling Assembly ? 7. ? 31 Step 2: Calculate the predetermined overhead rate in each department 32 33 Total manufacturing overhead 34 Total machine hours or direct labor-hours 35 Predetermined overhead rato per machine-hour or direct labor-hour 36 37 Step 3: Calculate the amount of overhead applied from both departments to Job 407 38 39 Predetermined overhead rate per machine-hour or direct labor-hour 40 Machine hours or direct labor-hours for the job 41 Manufacturing overhead applied 42 43 Step 4: Calculate the total job cost for Job 407 Milling Assembly ? 7 ? Milling Asserbly Total 2 2 7 ? 7 ? 45 Direct materials Direct labor cost 47 Manufacturing overhead applied 48 Total cost of Job 407 49 50 Step 5: Calculate the selling price for Job 407 51 Total cost of Job 407 52 Markup 53 Selling price of Job 407 54 55 56 57 58 59 Req. 1: Enter $300,000 for the green Total fixed manufacturing overhead cost cell in the Milling Department data section at the left. If your formulas are correct the total cost of Job 407 should now be $2,350. 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. 75% How much is the Selling Price of Job 407? Department Milling Assembly 60,000 3.000 8,000 80,000 2 $500,000 $2.00 S3.75 Did the Selling Price of Job 407 change? Why or why not 1 Chapter 2: Applying Excel 2 3 Data 4 Markup on job cost 5 6 7 8 Machine-hours 9 Direct labor-hours 10 Total fixed manufacturing overhead cost 11 Variable manufacturing overhead per machine-hour 12 Variable manufacturing overhead per direct labor-hour 13 14 Cost summary for Job 407 15 16 Machine-hours 17 Direct labor-hours 18 Direct materials 19 Direct labor cost 20 21 22 23 24 25 Total fixed manufacturing overhead cost 26 Variable manufacturing overhead per machine-hour or direct labor-hour 27 Total machine-hours or direct labor-hours 28 Total variable manufacturing overhead 29 Total manufacturing overhead 30 31 Step 2: Calculate the predetermined overhead rate in each department 32 33 Total manufacturing overhead 34 Total machine-hours or direct labor-hours 35 Predetermined overhead rate per machine-hour or direct labor-hour Department Milling Assembly 90 4 5 20 $800 $370 $70 $280 Milling Assembly ? 2 ? ? ? ? 7 ? ? 7 ? Milling Assembly ? ? ? ? 7 2 Milling Assembly ? ? ? ? ? ? Milling Assembly Total ? ? ? ? ? ? ? ? ? ? 37 Step 3: Calculate the amount of overhead applied from both departments to Job 407 38 39 Predetermined overhead rate per machine-hour or direct labor-hour 40 Machine-hours or direct labor-hours for the job 41 Manufacturing overhead applied 42 43 Step 4: Calculate the total job cost for Job 407 44 45 Direct materials 46 Direct labor cost 47 Manufacturing overhead applied 48 Total cost of Job 407 49 50 Step 5: Calculate the selling price for Job 407 51 Total cost of Job 407 52 Markup 53 Selling price of Job 407 54 55 56 57 58 59 60 61 ? ? ? A B C D 1 Chapter 2: Applying Excel E H Req. 2: Enter the data from requirment 2 on page 85of the text in the green cells for Job 408 at the left. 75% What is the new Selling Price for Job 40B? Department Milling Assembly 60,000 3,000 8,000 80,000 $390,000 $500,000 $2.00 $3.75 Department Milling Assembly ? ? ? 2 2 2 7 3 Data 4 Markup on job cost 5 6 7 8 Machine-hours 9 Direct labor-hours 10 Total fixed manufacturing overhead cost 11 Variable manufacturing overhead per machine-hour 12 Variable manufacturing overtread per direct labor-hour 13 14 Cost summary for Job 408 15 16 Machine-hours 17 Direct labor-hours 18 Direct materials 19 Direct labor cost 20 21 22 23 24 25 Total fixed manufacturing overhead cost 26 Variable manufacturing overhead per machine-hour or direct labor-hour 27 Total machine-hours or direct labor-hours 28 Total variable manufacturing overhead 29 Total manufacturing overtiead 30 31 Step 2: Calculate the predetermined overhead rate in each department 32 33 Total manufacturing overhead 34 Tolal machine-hours or direct labor-hours 35 Predetermined overhead rato por machine-hour or direct labor-hour 35 Milling Assembly 7 ? ? 7 ? ? ? ? 7 ? 7 Milling Assembly 2 7 ? 2 7 ? B D E F G H K L Milling Assembly ? ? ? ? ? ? A 36 37 Step 3: Calculate the amount of overhead applied from both departments to Job 408 38 39 Predetermined overhead rate per machine-hour or direct labor-hour 40 Machine-hours or direct labor-hours for the job 41 Manufacturing overhead applied 42 43 Step 4: Calculate the total job cost for Job 408 44 45 Direct materials 46 Direct labor cost 47 Manufacturing overhead applied 48 Total cost of Job 408 Milling Assembly Total ? ? ? ? ? ? ? ? ? ? ? 49 50 Step 5: Calculate the selling price for Job 408 51 Total cost of Job 408 52 Markup 53 Selling price of Job 408 54 55 ? ? 56 A D E B C 1 Chapter 2: Applying Excel 2 3 Data 4 Markup on job cost 75% 5 6 Department 7 Miling Assembly 8 Machine-hours 60.000 3.000 9 Direct labor hours 8.000 80,000 10 Total fixed manufacturing overhead cost 5390,000 $500,000 11 Variable manufacturing overhead per machine-hour $200 12 Variable manufacturing overhead per direct labor-hour 53.75 13 24 Cost summary for Job 407 Department 15 Miling Assembly 26 Machine-hours 90 4 17 Direct labor-hours 5 20 28 Direct materials $300 $370 19 Direct labor cost 570 5280 20 21 Enter a formula into each of the cells marked with a ? below 22 23 Step 1: Calculate the estimated total manufacturing overhead cost for each department 24 Milling Assembly 25 Total fixed manufacturing overhead cost 2 2 26 Variable manufacturing overhead per machine-hour or direct labor-hour ? ? 27 Total machine-hours or direct labor hours ? ? 28 Total variable manufacturing overhead ? 7 29 Total manufacturing overhead ? ? 30 31 Step 2: Calculate the predetermined overhead rate in each department 32 Miling Assembly 33 Total manufacturing overhead ? 7 34 Total machine hours or direct labor hours 2 2 35 Predetermined overhead rate per machine-hour or direct labor-hour ? ? 36 37 Step 3: Calculate the amount of overhead applied from both departments to Job 407 38 Milling Assembly 39 Predetermined overhead rate per machine-hour or direct labor-hour ? 40 Machine-hours or direct labor hours for the job ? ? 41 Manufacturing overhead applied 2 ? 42 43 Step 4: Calculate the total job cost for Job 407 dd Miling Assembly 45 Direct materials ? 2 ? 46 Direct labor cost ? 2 2 47 Manufacturing overhead applied 2 ? ? 48 Total cost of Job 407 2 49 50 Step 5: Calculate the selling price for Job 407 51 Total cost of Job 407 7 52 Markup 2 53 Seling price of Job 407 ? 54 WE Chapter 2 Form Filled in Chapter 2 Form Total You should proceed to the requirements below only after completing your worksheet. Required: 1. Check your worksheet by changing the total lixed manufacturing overhead cost for the Milling Department in the Dala area to $300,000, keeping all of the other data the same as in the original example. If your worksheet is operating properly, the total cost of Job 407 should now be $2,350. If you do get this answer, find the errors in your worksheet and correct them. How much is the selling price of Job 407? Did it change? Why or why not? 2. Change the total fixed manufacturing overhead cost for the Milling Department in the Data area back to $390,000, keeping all of the other data the same as in the original example. Determine the selling price for a new job, Job 408, with the following characteristics. You need not bother changing the job number from 407 to 408 in the worksheet. Department Cost summary for Job 408 Milling Assembly Machine-hours 40 10 Direct labor-hours 2 6 Direct materials $700 $360 Direct labor cost $50 $150 3. What happens to the selling price for Job 408 if the total number of machine-hours in the Assembly Department Page 86 increases from 3,000 machine-hours to 6,000 machine-hours? Does it increase, decrease, or stay the same as in part 2 above? Why? 4. Restore the total number of machine-hours in the Assembly Department to 3,000 machine-hours, What happens to the selling price for Job 408 if the total number of direct labor-hours in the Assembly Department decreases from 80,000 direct labor hours to 50,000 direct labor-hours? Does it increase, decrease, or stay the same as in part 2 above? Why? A B D F . E H J L Req. 3: Change the total number of Assembly Department machine hours in green cell at the left to 6,000 hours, 75% Did the new Selling Price for Job 408 change compared to req. 2? Dopartment Milling Assembly 60,000 2 8,000 80.000 $390,000 $500,000 $2.00 $3.75 Why or why not? 1 Chapter 2: Applying Excel 2 3 Data 4 Markup on job cost 5 6 7 & Machine-hours 9 Direct labor-hours 10 Total fixed manufacturing overhead cost 11 Variable manufacturing overhead per machine hour 12 Variable manufacturing overhead per direct labor-hour 13 14 Cost summary for Job 408 15 16 Machine-hours 17 Direct labor-hours 18 Direct materials 19 Direct labor cost 20 21 22 23 24 25 Total fixed manufacturing overhead cost 26 Variable manufacturing overhead per machine-hour or direct labor-hour 27 Total machine-hours or direct labor-hours 28 Total variable manufacturing overticad 29 Total manufacturing overhead 30 Step 2: Calculate the predetermined overhead rate in each department 32 33 Total manufacturing overhead 34 Total machine-hours or direct labor-hours 35 Predetermined overhead rate per machine-hour or direct labor-hour 36 Department Milling Assembly 90 4 5 20 S800 $370 S70 $280 Milling Assembly ? ? 7 ? ? ? ? ? ? ? Milling Assembly ? ? ? ? ? ? Formulas Reg 1 Ch2 Req 2 Ch2 Req 3 Ch2 Red 4 Ch2 + A A B C D E F G H J L M ? ? Milling Assembly ? ? ? ? ? ? 35 Predetermined overhead rate per machine-hour or direct labor-hour 36 37 Step 3: Calculate the amount of overhead applied from both departments to Job 408 38 39 Predetermined overhead rate per machine-hour or direct labor-hour 40 Machine-hours or direct labor-hours for the job 41 Manufacturing overhead applied 42 43 Step 4: Calculate the total job cost for Job 408 44 45 Direct materials 46 Direct labor cost 47 Manufacturing overhead applied 48 Total cost of Job 408 Milling Assembly Total ? ? ? ? ? ? ? ? ? ? 49 50 Step 5: Calculate the selling price for Job 408 51 Total cost of Job 408 52 Markup 53 Selling price of Job 408 54 55 ? ? ? 56 57 58 D E F K L F B Department Milling Asserbly 60.000 3,000 8,000 2 $390,000 $500,000 $2.00 $3.75 Did the new Selling Price for Job 408 change compared to req. 2? Why or why not? Department Milling Assembly 90 4 5 20 $800 $370 $70 $280 6 7 8 Machine-hours 9 Direct labor-hours 10 Total fixed manufacturing overhead cost 11 Variable manufacturing overhead per machine-hour 12 Variable manufacturing overhead per direct labor-hour 13 14 Cost summary for Job 408 15 16 Machine-hours 17 Direct labor-hours 18 Direct materials 19 Direct labor cost 20 21 22 23 24 25 Total fixed manufacturing overhead cost 26 Variable manufacturing overhead per machine-hour or direct labor-hour 27 Total machine-hours or direct labor-hours 28 Total variable manufacturing overhead 29 Total manufacturing overhoad 30 31 Step 2: Calculate the predetermined overhead rate in each department 32 33 Total manufacturing overhead 34 Total machine-hours or direct labor-hours 35 Predetermined overhead rate per machine-hour or direct labor-hour 36 Milling Assembly 7 ? ? 7 7 ? 2 ? ? 2 ? Milling Assembly 7 ? ? ? ? A B C F H J K L 37 Step 3: Calculate the amount of overhead applied from both departments to Job 408 Milling Assembly ? ? ? 7. ? Milling Assembly Total ? 7 2 7 . 39 Predetermined overhead rate per machine-hour or direct labor-hour 40 Machine-hours or direct labor-hours for the job 41 Manufacturing overhead applied 42 43 Step 4: Calculate the total job cost for Job 408 -44 45 Direct materials 46 Direct labor cost 47 Manufacturing overhead applied 48 Total cost of Job 408 49 50 Step 5: Calculate the selling price for Job 408 51 Total cost of Job 408 52 Markup 53 Selling price of Job 408 54 55 56 57 58 59 60 61 62 63 64 65 66