Applying fundamental cal skills and Tools in Problem Solving Chapter 1 High Top . The high top shoes require additional leather. The group estimates it will take about 30% more time to assemble the leather and sole compared with the original option . Since these shoes require more leather, the costs to deliver boxes will increase by $.02 . All other matteturing costs remain the same as there for the original option Now, you need to finish the worksheet to determine the manufacturing costs of the textured leather and high top options Level 2 per shoe the oped ced Complete the following: 1. Open the workbook named Comparison.xlex located in the Chapter 1 folder, and then save the file as Labor Comparison.xlsx 2. Complete column C and D for the textured leather and high top options based on the data provided for the original option and the information provided by the manufacturing group. 3. Calculate the total labor cost for each option 4. Format all values in the worksheet with two decimal places. Use dollar siyens in the first row and total row only. 5. In row 11 below the option totals, calculate the number of tasks in cach option and the number of possible tasks based on the list in column A. Format these values in bold and italic. In cell Ell, insert the label Tasks and format in bold and italic. 6. In cell E3, calculate the maximum cost of the box delivery task for the three options. Use the label Max. Cost as the column heading. Calculate the maximum cost for cach of the other manufacturing tasks and for the total labor 7. In cell F3, calculate the minimum cost of the box delivery task for the three options Use the label Min. Cost as the column heading, Calculate the minimum cost for cach of the other manufacturing tasks and for the total labor. 8. Several rows below the total, in column B, calculate the average labor cost of all man ufacturing tasks for all three options combined. Blank cells should not be included in the average. Enter the label Average to the left of the calculation, 9. The data for the toc support brace, toe support pad, and back support cushion was omitted in error from the worksheet. The manufacturing group advises to include $1.02 in labor costs to assemble these three items for the textured leather option and to include 5.85 in labor costs to assemble these three items for the high top option. Insert a row above the "Assembly of Leather, Sole" task, and include the necessary values and the label Leather Support Assembly. (Recall that the original option does not include these items.) Check all your formulas to ensure they are updated correctly after inserting the new values. 10. Save and close the Labor Comparison.xlsx workbook $3 contains the costs associated with the manufacturing tasks for one shoe t the material analysis for the three options is Sts. Figure 1.17 shows the labor comparison worksheet that original option. 17: Labor comparison worksheet E A B C D 1 TZEdge Labor Cost Three Option Comparison Original Textured 2 Manufacturing Tasks Per Shoe: Option Leather High Top 3 Delivery of Boxes to Production Line $ 0.20 4 Cutting of Leather 1.03 5 Attachment of Appliques 2.03 Sewing of Logos to Leather 1.94 7 Assembly of Leather, Sole 1.56 8 Assembly of Arch Support 0.59 9 Total Labor 10 11 facturing group has determined how the different design options wi hours and skills required to assemble + 1. Open the workbook named Jobs.xlsx located in the Chapter 1 folder, and then save the file as Job Analysis.xlsx 2. In cells F2:44, calculate the associated annual adjusted salary. This adjusted salary is the annual salary divided by the cost of living multiplier minus the expected annual car payments. (Note that car payments are given in dollars per month ($/month). 74 1 Chapter Exercises Applying Fundamental Excel Skills 3. In cell F6, write a formula to calculate the average adjusted salary of the three positions. 4. In cell F7, write a formula to determine the value of the lowest adjusted salary. This formula should automatically update if any of the data inputs are later changed. 5. In cell F8, write a formula to determine the value of the highest adjusted salary. This formula should automatically update if any of the data inputs are later changed. 6. In cell G9, write a formula to determine the number of positions that include a bonus. 7. In cells H2:H4, calculate the value of the adjusted salary package for each position over a two-year period, including bonuses. The bonus does not need to be adjusted for location (multiplied by location factor) because the employee plans to use the bonus toward a vacation. Assume that the employee will receive a 2.5% raise after the first year of employment in the new position. 8. Display dollar values in columns E to H without cents, and include a dollar sign only in the first row of columns with dollars. Format the cost of living multipliers with two decimal places displayed, and align these values on the decimal point. 9. Another position for which the employee is qualified has just become available. This position, a senior consultant position, is located in Washington D.C., has an annual salary of $68,000, plus a $1,500 hiring bonus. Because the position is located in downtown Washington, assume that the employee will not be purchasing a car and that the cost of living multiplier is 1.4. Insert the data for this new position just below the New York position. Complete the calculations for the adjusted salary and the total two-year financial package. Verify that all of the other values you've calculated update correctly. Adjust the formatting of the new data, as needed, to match the formatting of the existing data. 10. Highlight in yellow the row of the position with the highest two-year financial package, and bold the text in this row. 11. Save and close the Job Analysis.xlsx workbook. Level 2 - Analine Decional Sales Information for CKG Auto Creolen automatique Insertion Dessin Formules Mise en page Accueil Donnes Affi Rvision Arial 10 A A = abe IS Coller G I s v 3 G H Total 2y Financial Bonus Package 5000 375 A1 XVfx Position A B C D E F Cost of Annual Living Adjusted 1 Position Location Salary Multiplier Monthly Car Payment Salary 2 Product Manager New York 84250 2.1 0 3 Networking Specialist Houston 52100 0,94 389 4 Systems Analyst Patsburgh 48750 0,92 5 6 Average: 7 Low Offer High Offer 9 w positions w/bonus 10 11 12 13 14 15 16 17 18 19 20 21 23 24 25 26 27 28 20 30 31 32 33 14