Reference: https://docs.google.com/file/d/11JmoEiAuk2JV1-zg3JX6xo9_nSktOoI7/edit?usp=docslist_api&filetype=msexcel

Note: The Years of service can simply use the Year function and no need to add any text like "years".This way you can and should, filter by greater than(>). Include absolute cell references where necessary too. Follow all instructions from above and below. Send the file link when finished with the Microsoft Excel task. If all the steps are done correctly, you will be given a helpful rating.

1)Download the fileSMS210 Project 2 Starter.xlsx.

2)Rename the workbook to Project 2.xlsx.2

3)Start Excel and open Project 2.xlsx.

Part 1

In your new role as HR Assistant you have been asked to create various reports from the Employee Data provided by the IT Department.

4)In the Master List worksheet, do the following:

a)Freeze the top row and 2 left columns.2

b)Adjust column widths so that all data is visible.1

c)The Ottawa office has recently closed and a new office has opened in Montreal. Use the Find and Replace command to change each occurrence ofOttawawithMontreal.1

5)Enter todays date in cell O1.2

6)In cell M2 enter a formula to calculate the Years of Service based on Hire Date as of today for each employee. Fill the formula down to ensure all employees Years of Service is up to date.5

7)Make a copy of the Master List worksheet. Rename the copied sheet Filter Fun. Place it immediately to the right of the Master List worksheet.1

8)In the Filter Fun sheet:

a)Sort the data first by theLast Namefield and then by theHire Datefield (both in ascending order).2

b)Use the Filter feature to display only those Employees with more than 10 years of Service.2

9)Make another copy of the Master List worksheet. Rename the copied sheet Subtotal Fun. Place it immediately to the right of the Filter Fun worksheet.1

10)In the Subtotal Fun sheet:

a)Sort the data first byJob Status(in ascending order).1

b)Insert Subtotals into the list to make a report that sums the Total Annual Salary for each change in Job Status and include a grand total.

Set Subtotals to display only Annual Salary Totals and Grand Total.

Adjust column widths where necessary.4

11)Switch back to the Master List sheet.

a)Make a PivotTable on a new sheet, using the following guides:

b)The Location should be used in the Column area2

c)The Job Status should be used in Row area2

d)The Annual Salary should be in the Value area.

- Sum the data

- Give it a Custom Name of Total Salary

- Format it to show dollar signs and zero decimals 4

e)Resize the columns so the data fits well.1

f)Rename the Pivot Table's sheet "Salary by Location".1

Part 2

The manager of the sales force, Bill Spokane, has asked you to help him build an Excel workbook that he could use to maintain data about the sales people.

He has already recorded the sales for the six salespeople, but he has asked you to put in formulas that would calculate a commission at 5% on monthly sales, total sales, and total commissions for each salesperson.

He would also like to have a summary worksheet that shows the sales and commissions of the sales force.Management has decided to give a bonus to its sales force based on their performance for the year. Bill has asked you to calculate the bonus amount based on the table provided in the Compensation worksheet.

Note the six sheets which contain sales data for six salespeople -Smith,Mitchell,Overton,Scott,Walton, andThompson.

12)Familiarize yourself with theCompensationworksheet.

13)Group the worksheets for the six salespeople. Enter formulas in the Smith worksheet that will calculate the monthly commission (using the amount in cell B9 of the Compensation worksheet), the total sales, and total commission. (Note: since you have the worksheets in a group, you should only have to enter the formulas once, in one worksheet).3

14)Programs sometimes lose treasured older features when they get updated.

AutoFormat, which has been around for several generations of Excel is "hidden" by default in Excel 2013, so you have to add it to your Quick Access Toolbar in order to use it.

Click theCustomize Quick Access Toolbarbutton in the Quick Access Toolbar, then selectMore Commands. In the dialog box, change the value of theChoose Commands Fromoption from Popular Commands toAll Commands. Scroll down the now-longer list of available commands and selectAutoFormat. Click theAdd > >button, then theOKbutton.

15)With the worksheets still grouped, apply the Accounting 2 Autoformat to cells A3:C17. Resize the columns if necessary. Ungroup Worksheets.4

16)Add a new worksheet before the Smith worksheet.Name this worksheetSummary.1

17)Enter the title, Sales Summary 2013 in cell A1.Merge and Center cells A1:D1. Type the labelsSalesperson,Total Sales,Total Commission, andBonusinto cells A3 through D3, respectively.2

18)Using simple "3-D" formulas, place each salesperson's name in the rows of column A, their Total Sales in column B, and Total Commission in column C.Apply the Accounting 2 Autoformat to cells A3:D9.4

19)Enter a VLookup formula in the Bonus column (D) for each salesperson that will look up their Sales in the Bonus Table and retrieve the correct Bonus Amount. Make sure that your formula can becopieddown the column.5

20) Add a row of Total calculations to the bottom of columns B through D; adjust formatting so all data is visible.