Question

Home Insert Page Layout Formulas Data Review View Cut Copy Format Painter a Text Calibri Jaxson Motor Co. operates an automobile sales dealership. It is that time of year again where annual raises are assessed and the company must review their salary expense as a part of their annual budget. In this assignment, you wi use Excel's Vlookup feature to make this process easier. You are given two reports: (1) 2 Jaxson Motor Co Employee Listing which shows the Employee 3 Number, Employee Department, and Employee Name; and (2) YTD Salary Expense 5 Report which shows the Employee Number, 6 Employee Name and their respective salary 7 for the prior year. Use the Excel spreadsheet included here which provides both your instructions and both reports: Week 3 Assignment 2.xls Paste Font B5 VLOOKUP(A5,'Dept Listing'lA6:B11, 2") SAnve ReBetvlookup Expense Report t Number Finance Sales Accounting 595,000 550,000 330,000 600 Required: 1. Once you have reviewed your reports, prepare to perform the Vlookup to combine the information you need onto one report. You will be adding a column to the YTD Salary Expense Report so you can list thee Employee Department for each employee respectively on that report. Pay attention to any formatting required 2. Perform a Vlookup using the reports provided 3. Once the Vlookup in complete, sort the data by Department (A to Z) then by YTD Salary (Largest to Smallest) 4. In the appropriate area, answer the following questions once you have completed the previous steps a. Who are the highest paid employees in each department? b. How does the Vlookup function make it easier to compile information between reports, such as the information we were looking for in this assignment? S. Upload your assignment by clicking on "Week 4 Assignment" above

Answer 1

Ans : YTD SALARY SHEET AFTER VLOOKUP

YTD SALARY EXPENSE REPORT
Jason Motor's Co.
Employee Number	Employee Name	YTD Salary	Employee Department
113	Larson,Shelly	$48,000	Accounting
121	Kyle,lexi	$45,000	Accounting
130	Neon, Amber	$42,000	Accounting
127	Brown, Christine	$41,000	Accounting
198	Axel,Larisa	$33,000	Accounting
220	Rodriguez,Erica	$30,000	Accounting
152	Eronson,Barry	$65,000	Human Resource
145	Neol,Larry	$55,000	Human Resource
205	Oliver,Karel	$50,000	Human Resource
110	Williams,Jack	$190,000	Lower Level Management
166	Temple,Sara	$185,000	Lower Level Management
107	Kelly,Lois	$175,000	Lower Level Management
182	Hernandez,Manny	$145,000	Lower Level Management
197	Johnson,Carl	$116,000	Sales
221	Cooper,Aaron	$105,000	Sales
105	Cally, Eric	$101,000	Sales
106	Smith, Larry	$98,000	Sales
188	Peterson,Emily	$95,000	Sales
190	Larson,Christoper	$88,000	Sales
176	Brickell,Lisa	$76,000	Sales
101	Moore,Anderson	$235,000	Upper Level Management
135	Maxwell,Laura	$220,000	Upper Level Management

HIGHEST PAID EMPLOYEE IN EACH DEPARTMENT
Employee Number	Employee Name	YTD Salary	Employee Department
113	Larson,Shelly	$48,000	Accounting
152	Eronson,Barry	$65,000	Human Resource
110	Williams,Jack	$190,000	Lower Level Management
197	Johnson,Carl	$116,000	Sales
101	Moore,Anderson	$235,000	Upper Level Management

2) Vlookup helps us in reducing our work by apply a formula to link a two sheets containing information about each other. In Vlookup we select a "Search key" which is present in both the sheet and fetch the data we required data on the basis of such key in one sheet. If we do not use the Vlookup then we have to put the data manually which will take time.

3) Manager will use the above information in fixing the budgeted cost of each department in the next budget .