1. The information and requirements for the Chapter 2 Applying Excel problem can be found on page 78 of the textbook 3. Enable macros on the spreadsheet if a waming pops up. Click the "Options" button, then in the pop up box choose "Enable the content", then click "OK" Security Warning Macros have been disabled. Options... Microsoft Office Security Options Security Alert - Macro Macro Macros have been disabled. Macros might contain viruses or other security hazards. Do . not enable this content unless you trust the source of this file. Warning: It is not possible to determine that this content came from a trustworthy source. You should leave this content disabled unless the content provides critical functionality and you trust its source. More information File Path: X:\Spring 2020 \ACTG 131 Excel Labs (Ch 09 131 Lab w Macro.xiom Help grotect me from unknown content (recommended) Enable this content OK Cancel Open the Trust Center 4. Go to the next page, "Formulas", and comlete the green cells with a "?" using Excel formulas only and not by entering numbers/amounts. If Excel formulas are not used the reports, the answers for the later requirements will be incorrect when you enter new data amounts that are given to compare different scenarios as the wrong amount would be used in the calculations. 5. Once all of the formulas are entered, use the "Copy Formulas" button to copy the formulas to the other sheets of the workbook. 6. Select the "Req 1 Ch2" sheet in the workbook and enter the data change in the green cell as specified in Requirement 1. 7. If your formulas are correct the total cost of Job 407 should be $2,350. This number is in bold red text on the worksheet. 8. If you don't get this answer, find the formula errors in the green cells back on the Formulas sheet and make your corrections. Once corrected update the formulas on the other sheets by clicking on the "Copy Formulas" button again. 9. When you have the correct answer, type your answers for the Requirement 1 questions in the green boxes on the right of the worksheet. 10. Go to sheet "Req 2 Ch2" in the workbook and change the data at the top of the sheet to the data given in requirement 2 in the textbook, then answer the questions for requirement 2 in the green answer boxes on the right of the worksheet. 11. Go to sheet "Req 3 Ch2" in the workbook and make the given change to the machine hours for the Assembly department in the green box then answers the questions for requirement 3. 12. Go to sheet "Req 4 Ch2" in the workbook and make the given change to the direct labor hours for the Assembly department in the green box then answers the questions for requirement 4. A B C. D E F G H H 1 J Copy Formulas 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 $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 5 20 18 Direct materials $800 $370 19 Direct labor cost $70 $280 20 21 Enter a formula into each of the green cells marked with a ? 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 ? ? 26 Variable manufacturing overhead per machine-hour or direct labor-hour - ? ? 27 Total machine-hours or direct labor-hours ? ? 28 Total variable manufacturing overhead ? 2 29 Total manufacturing overhead ? ? 30 31 Step 2: Calculate the predetermined overhead rate in each department 32 Milling Assembly 33 Total manufacturing overhead ? ? 34 Total machine-hours or direct labor-hours ? ? 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 7 ? 41 Manufacturing overhead applied ? ? 42 13 Step 4: Calculate the total job cost for Job 407 44 Milling Assembly 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 Total c M E F I J Req. 2: Enter the data from requirment 2 on page 78 of the textbook in the green cells for Job 408 at the left. 75% The Job number has alread been upated so ignore the note in the textbook regarding changing the job number in this Excel file What is the new Selling Price for Job 4089 Department Milling Assembly 60,000 3,000 8,000 80,000 $390,000 $500,000 $2.00 $3.75 Department Milling Assembly 7. 2 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 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 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 36 37 Step 3: Calculate the amount of overhead applied from both departments to Job 408 ? ? 2 Milling ? ? ? 7 ? ? Assembly ? ? 7 ? 2 Milling 2 2 2 Assembly ? ? ? Milling 39 Predetermined overhead rate per machine-hour or direct labor-hour 40 Machine-hours or direct labor-hours for the job 11 Manufacturing overhead applied 42 43 Step 4: Calculate the total job cost for Job 408 ? ? ? Assembly ? ? ? Milling ? Assembly ? ? ? Total ? ? ? ? ? 45 Direct materials 46 Direct labor cost 17 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 ? 2 ? B D E 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 reg. 27 Department Milling Assembly 60,000 8.000 80,000 $390,000 $500,000 $2.00 $3.75 Why or why not? ? Department Milling Assembly 40 10 2 6 $700 $360 $50 $150 Milling 1 Chapter 2: Applying Excel 2 3 Data 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 overhead 29 Total manufacturing overhead 30 31 Step 2: Calculate the predetermined overhead rate in each department 32 33 Total manufacturing overhead 31 Total machine hours or direct labor-hours 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 12 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 19 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 ? ? ? ? ? Assembly ? ? ? 2 ? Milling ? ? ? Assembly ? ? ? Milling Milling ? ? ? Assembly ? ? ? Milling Total ? ? ? Assembly ? ? ? ? ? ? ? ? ? ? A B M G H I 1 Reg. 4: Change the total number of Assembly Department direct labor hours in green cell at the left to 50,000 hours. 75% Did the new Selling Price for Job 408 change compared to reg. 29 Department Milling Assembly 60,000 3.000 8,000 $390,000 $500,000 $2.00 $3.75 Why or why not? Department Milling Assembly 40 10 2 6 $700 $360 $50 $150 1 Chapter 2: Applying Excel 2 3 Data 4 Markup on job cost 5 6 7 7 B 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 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 overead 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 Milling Assembl ? ? ? ? ? ? Assembly ? ? 7 ? ? Milling Assembly ? ? ? ? ? ? Milling Assembly ? ? Assembl ? ? ? 7 Milling Total ? ? ? 2 7 Assembly 2 ? 2 ? 7 ? ? 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