Question

how do I do this spreadsheet in a sense of equations.

Download the Applying Excel form and enter formulas in all cells that contain question marks.

The Chapter 2 Form worksheet is to be used to create your own worksheet version of the example in the text.

Enter formulas in the cells that contain question marks. For example, in cell B25 enter the formula "=B10".

After entering formulas in all of the cells that contained question marks, verify that the amounts match the example in the text.

Check your worksheet by changing the total fixed manufacturing overhead cost for the Milling Department in the Data area to $300,000, keeping all 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 not get this answer, find the errors in your worksheet and correct them.

You should proceed to the requirements below only after completing your worksheet.

Save your completed Applying Excel form to your computer and then upload it here by clicking “Browse.” Next, click “Save.” You will use this worksheet to answer the questions in Part 2.

Chapter 2: Applying Excel Data Markup on job cost 75% Machine-hours Direct labor-hours Total fixed manufacturing overhead cost Variable manufacturing overhead per machine-hour Variable manufacturing overhead per direct labor-hour Department Milling Assembly 60,000 3,000 8.000 80,000 $390,000 $500,000 $2.00 $3.75 Cost summary for Job 407 Machine-hours Direct labor-hours Direct materials Direct labor cost Department Milling Assembly 90 20 $800 $370 $70 S280 Enter a formula into each of the cells marked with a ? below Assembly Step 1: Calculate the estimated total manufacturing overhead cost for each department Milling Total fixed manufacturing overhead cost Variable manufacturing overhead per machine-hour or direct labor-hour Total machine hours or direct labor-hours Total variable manufacturing overhead Total manufacturing overhead Step 2: Calculate the predetermined overhead rate in each department Milling Assembly Total manufacturing overhead Total machine-hours or direct labor hours Predetermined overhead rate per machine-hour or direct labor-hour Assembly Step 3: Calculate the amount of overhead applied from both departments to Job 407 Milling Predetermined overhead rate per machine-hour or direct labor-hour Machine-hours or direct labor-hours for the job Manufacturing overhead applied Step 4: Calculate the total job cost for Job 407 Total Milling Assembly Assembly 21? Direct materials Direct labor cost Manufacturing overhead applied Total cost of Job 407 ? Step 5: Calculate the selling price for Job 407 Total cost of Job 407 Markup Selling price of Job 407

Answer 1

original solution -

fe data АЗ D 3 data 4 markup 75% department milling assembly 8 machine hours 3,000 60,000 9 direct labor hours 10 total fixed manufacturing overhead cost 8,000 80,000 $ 390,000 $ 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 20 800 $ 18 direct materials 370 19 direct labor cost 70 280 20 21 22 23 milling $ 390,000 $ 500,000 24 assembly 25 total fixed manufacturing overhead cost 2.00 $ 26 variable manufacturing overhead per machine hour or direct labor hour 27 total machine hours or direct labor hours 28 total variable manufacturing overhead 3.75 60,000 80,000 120000 300000 $ 510,000 $ 800,000 29 total manufacturing overhead 30 31 32 milling assembly $ 800,000 $ 510,000 33 total manufacturing overhead 34 total machine hours or direct labor hours 60,000 80,000 35 predetermined overhead rate per machine hour or direct labor hour 8.5 10 36 37 38 milling assembly 39 predetermined overhead rate per machine hour or direct labor hour 8.5 10 40 machine hours or direct labor hours for the job 90 20 41 manufacturing overhead applied 765 200 42 43 milling 800 $ 44 total assembly 45 direct materials 370 | $ 280 $ 1,170 46 direct labor cost 70 $ 350 47 manufacturing overhead applied 200 $ 765 965 48 total cost of job 407 2,485 49 50 51 total cost of job 407 52 markup 2,485 $ 1,863.75 $ 4,348.75 53 selling price of job 407 54 55

formula view of original solution -

fe data АЗ A 2 3 data 4 markup 0.75 department milling assembly 8 machine hours 3000 60000 80000 9 direct labor hours 10 total fixed manufacturing overhead cost 8000 390000 500000 11 variable manufacturing overhead per machine hour 12 variable manufacturing overhead per direct labor hour 3.75 13 14 cost summary for job 407 department milling assembly 15 16 machine hours 90 4 20 370 280 17 direct labor hours 18 direct materials 800 19 direct labor cost 70 milling assembly =810 25 total fixed manufacturing overhead cost =C10 26 variable manufacturing overhead per machine hour or direct labor hour 27 total machine hours or direct labor hours =B11 =C12 =C9 =B26*B27 =C26*C27 =B8 28 total variable manufacturing overhead 29 total manufacturing overhead =B25+B28 =C25+C28 milling assembly =C29 33 total manufacturing overhead =B29 34 total machine hours or direct labor hours =B8 =C9 35 predetermined overhead rate per machine hour or direct labor hour =B33/B34 =C33/C34 milling assembly 39 predetermined overhead rate per machine hour or direct labor hour =B35 =C35 =C17 =B39*B40 =C39*C40 =B16 40 machine hours or direct labor hours for the job 41 manufacturing overhead applied milling assembly =B18 total 45 direct materials =C18 =B45+C45 =B46+C46 =B47+C47 46 direct labor cost =B19 =C19 47 manufacturing overhead applied =C41 =B41 48 total cost of job 407 =SUM(D45:D47) 51 total cost of job 407 =D48 52 markup =D51*B4 53 selling price of job 407 =D51+D52

solution after changing to $300,000

fe data АЗ 3 data 4 markup 75% department milling 60,000 assembly 8 machine hours 3,000 9 direct labor hours 10 total fixed manufacturing overhead cost 80,000 8,000 $300,000 $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 17 direct labor hours 800 | $ 18 direct materials 370 19 direct labor cost 70 280 20 21 22 23 24 milling $300,000 2.00 $ assembly 25 total fixed manufacturing overhead cost $500,000 26 variable manufacturing overhead per machine hour or direct labor hour 27 total machine hours or direct labor hours 28 total variable manufacturing overhead 3.75 60,000 80,000 120000 300000 $420,000 $800,000 29 total manufacturing overhead 30 31 32 milling $420,000 assembly 33 total manufacturing overhead $800,000 34 total machine hours or direct labor hours 80,000 60,000 35 predetermined overhead rate per machine hour or direct labor hour 36 37 38 milling assembly 39 predetermined overhead rate per machine hour or direct labor hour 10 06 630 40 machine hours or direct labor hours for the job 41 manufacturing overhead applied 200 42 43 44 milling assembly total 45 direct materials 370 $ 800 $ 70 $ 1,170 46 direct labor cost 280 350 47 manufacturing overhead applied 630 200 830 48 total cost of job 407 2,350 49 50 51 total cost of job 407 52 markup 2,350 $1.762.50 53 selling price of job 407 $4,112.50 54 55

formula view after changing to $300,000 -

fe data АЗ 3 data 4 markup l0.75 department milling assembly 8 machine hours 60000 3000 9 direct labor hours 10 total fixed manufacturing overhead cost 8000 80000 300000 500000 11 variable manufacturing overhead per machine hour 2 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 20 17 direct labor hours 370 280 18 direct materials 800 19 direct labor cost 70 20 21 22 23 milling assembly 24 25 total fixed manufacturing overhead cost =C10 =B10 =C12 =B11 26 variable manufacturing overhead per machine hour or direct labor hour 27 total machine hours or direct labor hours 28 total variable manufacturing overhead =B8 =C9 =B26*B27 =C26*C27 =B25+B28 =C25+C28 29 total manufacturing overhead 30 31 milling assembly =C29 32 =B29 33 total manufacturing overhead 34 total machine hours or direct labor hours =B8 =C9 =B33/B34 =C33/C34 35 predetermined overhead rate per machine hour or direct labor hour 36 37 milling assembly 38 39 predetermined overhead rate per machine hour or direct labor hour =C35 =B35 40 machine hours or direct labor hours for the job =B16 =C17 41 manufacturing overhead applied =B39*B40 =C39*C40 42 43 milling assembly =B18 =B19 44 total 45 direct materials =B45+C45 =C18 =C19 46 direct labor cost =B46+C46 47 manufacturing overhead applied =B41 =B47+C47 =C41 =SUM(D45:D47) 48 total cost of job 407 49 50 51 total cost of job 407 52 markup =D48 =D51*B4 53 selling price of job 407 =D51+D52 54 55