Scenario: You are on a temporary work assignment at Worldwide Enterprises, located in New York. Worldwide Enterprises distributes independent movies to cinemas around the world. With your expertise in Excel, you have been asked to assist management with various data analysis tasks. As you work through each part of the project, note that this Case Study is intended to allow you to polish your Excel skills in a realistic workplace scenario and that not every step will be provided for you. Instructions have been purposefully left open-ended to allow each student to do original work and showcase creativity in problem solving. Part 1 The manager of Human Resources has downloaded a text file from the companys accounting system. She wants you to import the file into Excel. To begin this activity, locate and copy the text data file named EL2-FCS-EmployeeDeptAndSalaryInfo. a. Import the text file into a new Excel workbook into Sheet1 in cell A1. Adjust column widths as necessary. Apply comma formatting with zero digits after the decimal point. Note: This file is in comma-delimited format. b. Delete the query. c. Save the Excel file with the name EmployeeInfo-Macros. d. Rename the worksheet EmployeeDeptAndSalaryInfo. e. The human resources manager is concerned about upcoming salary increases. She wants to do some analysis before negotiations begin with each department manager. Using the EmployeeDeptAndSalaryInfo worksheet, add statistics and appropriate labels to the bottom of the worksheet as follows: i) The sum of the salaries for staff in all departments. ii) Calculate the total salaries for each department. Hint: Use the SUMIF function to calculate the total for each department. f. In a separate area adjacent to the statistics created in Step e, create labels like the ones shown below. Projected Salary Costs for 2021: Wage Increase 2.5% European Distribution North American Distribution Overseas Distribution Grand Total

Enter formulas to calculate the projected salary costs for each department if a 2.5% wage increase is granted and then calculate the total cost of all salaries at the increased wage. Use a named range for the wage increase. g. The Human Resources manager is concerned that the wages for the Overseas Distribution department are too high. She wants to know what the wage increase percent would be if she capped the total wages for 2021 for that department at $765,000. Use Goal Seek to find the answer. Do not click OK to change the percent since this salary ceiling affects only one department. Instead, type a note to the manager inside a cell below the projected salary costs in which you state the result that Goal Seek calculated. At this time, the manager is only looking at various wage options; no firm decisions have been made. h. To further prepare for upcoming wage increase negotiations, the Human Resources manager has asked you to create three versions of projected wage costs for 2021. She wants the three versions stored in scenarios to keep the worksheet uncluttered and avoid confusion. The president has directed the maximum wage increase is 2.5%; however, the companys goal is to settle somewhat lower than this at 1.5%. The manager wants a third scenario calculated that settles the wage increase halfway between the other two rates. Name the three scenarios Maximum Increase, Goal Increase, and Compromise Increase, respectively. Save the revised worksheet with the Goal Increase scenario displayed. Create a scenario summary report. i. Format the EmployeeDeptAndSalaryInfo worksheet as necessary to produce a professional appearance. Set options so that the worksheet fits on one page in landscape orientation in an attractive layout. Set up a custom header that identifies the printout as Projected Salary Costs for 2021. In a custom footer, add your name, the current date, and any other identifying information you routinely use in the course. Save the revised workbook.