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. Your expertise in Excel has been requested to assist management with various data analysis tasks. As you work through each activity, additional information will be provided as needed. This case study will allow you to exercise creativity and polish your Excel skills. Specific step-by-step instructions will not be provided to simulate a more realistic workplace assignment. Some instructions may appear vague or not comprehensive, but they are purposefully open-ended to allow you to do original work and showcase individual creativity in problem solving. What to Do: The Manager of Human Resources has downloaded a text file from the companys accounting system. She wants you to create a table. To begin this activity, locate and copy the data file named EmployeeDeptAndSalaryInfo-DataFile.txt. a. Import the text file into a new Excel workbook and then adjust column widths as necessary. Note: This file is in comma-delimited format. b. Use Save As to save the workbook in Excel Workbook and name it EmployeeInfoUpdated.xlsx. c. Rename the worksheet EmployeeDeptAndSalaryInfo. d. 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 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. e. In a separate area adjacent to the statistics created in Step d, create labels similar to the ones shown below. Projected Salary Costs for 2018: Wage Increase 2.5% European Distribution North American Distribution Overseas Distribution 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. Projected Salary Costs for 2018: Wage Increase European Distribution 2.5% North American Distribution Overseas Distribution f. 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 2018 for that department at $620,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. g. To further prepare for upcoming wage increase negotiations, the Human Resources Manager has asked you to create three versions of projected wage costs for 2018. 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. h. Format the worksheet as necessary to produce a professional appearance. Set print options so that the worksheet fits on one page in landscape orientation in an attractive layout. Exclude any cells used for criteria ranges in the print area. Set up a custom header that identifies the printout as Projected Salary Costs for 2018. In a custom footer, add your name and the current date.