Exp22_Excel_Ch07_HOE_Salaries | Excel Chapter 7 Hands-On Exercise | Exp22 Excel Ch07 HOE Salaries

Exp22_Excel_Ch07_HOE_Salaries | Excel Chapter 7 Hands-On Exercise | Exp22 Excel Ch07 HOE Salaries

#Exp22_Excel_Ch07_HOE_Salaries#Excel_Ch07_HOE_Salaries#Ch07_HOE_Salaries#HOE_Salaries#Salaries#Exp22_Excel_Ch07_HOE#Exp22_Excel_Ch07#Exp22_Excel#Step_by_Step_Exp22_Excel_Ch07_HOE_Salaries

Contact us:

WhatsApp : +92 3209624776
Email : myitlab800@gmail.com

WhatsApp Direct Link for Chat
https://wa.link/xz3r2a

Exp22_Excel_Ch07_HOE_Salaries

Project Description:
You are an assistant accountant in the Human Resources (HR) Department for Home Protection, Inc., a company that sells smart home security systems to residential customers. Home Protection, Inc., with locations in Atlanta, Boston, Chicago, and Cleveland, has a manager at each location who oversees several account representatives. You have an Excel workbook that contains names, locations, titles, hire dates, and salaries for the 16 account representatives and 4 managers. To prepare for your upcoming salary analyses, you downloaded salary data from the corporate database into the workbook. The HR director wants you to perform several tasks based on locations and job titles. You will use date functions to identify the year each employee was hired and how many years they have worked for the company. In addition, you will use logical functions to calculate annual bonus amounts. Next, you will insert math and statistical functions and a map to help analyze the data. Finally, you will review the financial aspects of automobiles purchased for each manager.
1 Start Excel. Download and open the file named Exp22_Excel_Ch07_HOE_Salary.xlsx. Grader has automatically added your last name to the beginning of the filename.
2 Your first task is to extract the year hired from the hire date for each employee.

In cell F7 of the 1-Date Logic worksheet, insert the YEAR function to identify the year from the date stored in cell E7. Use the fill handle to copy the function from cell F7 to the range F8:23 using the Fill Without Formatting option.
3 Your next task is to calculate how long each manager and representative has worked for the company.

In cell G7, insert the YEARFRAC function using the Hire Date and Last Day of the Year. Use relative and mixed references correctly. Use the fill handle to copy the function in cell G7 to the range G8:G23 using the Fill Without Formatting option.
4 You want to identify which day of the week each employee was hired. To do this, you will insert a WEEKDAY function to return an integer representing the weekday.

In cell H7, insert the WEEKDAY function using the Hire Date. Copy the function from cell H7 and paste to the range H8:H23 using the No Borders paste option.
5 You will use a custom number format to display the result as a weekday instead of an integer.

Select the range H7:H23, apply the custom number format dddd, and apply left horizontal alignment.
6 Column B contains the city location for each account rep and manager. However, you also want to display the regions. Atlanta is in the South, Boston is in the Northeast, and Chicago and Cleveland are both in the Midwest. You will use the SWITCH function to identify which region each city is in.

In cell C7, insert the SWITCH function to identify the city in cell B7. Switch Atlanta for South (cell C2), switch Boston for Northeast (cell C4), switch Chicago for Midwest (cell C3), switch Cleveland for Midwest (cell C3). Insert city names as text strings, and use mixed references to the cells containing the regions. Copy the function from cell C7 and paste to the range C8:C23 using the paste Formulas option.
7 Your next task is to calculate the annual bonus amount for each employee. The company uses a tiered bonus system that awards a specific percentage of salary based on hire date.

In cell J7, insert the IFS function to test if the Hire Date is before 1/1/2013. If so, the bonus is 8% of the salary. If the Hire Date is before 1/1/2018, the bonus is 4% of the salary. If the Hire Date is before 1/1/2023, the bonus is 2% of the salary. If none of the conditions are met, the bonus is 0. Use mixed references to the dates in the range J2:J4 and bonus percentages in the range K2:K4. Copy the function from cell J7 and paste it in the range J8:J23 using the paste Formulas option.
8 The HR director recommends that the company pay managers at least $80,000.

In cell K7, insert an AND function that evaluates if the the Title is Manager and if the Salary is less than $98,000. Insert the title as a text string and use a mixed reference to the $98,000 in cell G3. Nest the AND function as the logical test within an IF function. If the results of the AND function are true, display the text Due for raise. If the results are false, display the text N/A. Use the fill handle to copy the function to the range K8:K23 using the Fill Without Formatting option.

Видео Exp22_Excel_Ch07_HOE_Salaries | Excel Chapter 7 Hands-On Exercise | Exp22 Excel Ch07 HOE Salaries канала Myitlab Solution Services