can i get an explanation on how to use the formulas for the adj gross

Matching Training to Software Use Payroll You have the responsibility for making a report to help the company administrators to implement new employee's career plan, providing data to the investment/budget committee. So, you are managing the employee's payroll data from the new division of Data Research, created two years ago to provide the company departments (Marketing Sales and Operations) with collection/analyze/update/report of real time market information, by making the calculation of their pay checks shown below. As the division's head, you want to demonstrate alternatives to justify the maintenance of 2.5% salary raise police to the administrators debate regarding the company's employees career plan, justifying the inclusion of Data Research division employees in the salary raise police. You have been asked to prepare a spreadsheet to put together the payroll data and summarizing the impacts of the actual salaries to the career plan in order to demonstrate the amount spent with the division (that had a raise in 3.7% in the budged). analyzing how the degradation of the salaries might impact in the retention of the division's employees and demonstrating to the board reasons to support/justify the improvement in the employee's compensations by include them the percent of the salary raise. The values shown are the total hours of each worker in the month and the value of the hour work for each one in the department and the total/average monthly values spent which department's workers, put your name in the indicated cell: a. Create a spreadsheet illustrating each employee's incomings/discounts data in the month. To do this, you will first enter the data shown in the figure below. Then compute the values of each columns/lines, for the monthly values as indicated below: F H - 1 PAYROL 2 B D YOUR NAME & CLASS SECTION REG GROSS OVER TIME ADI GROSS 3.65% FICA PAY NET CONT TYPE 1 3 1 2 Total Average HOURS 42 5 35 50 32 4 EMPLOYEE 5 Phil Collins 6 Nicole Kidman 7 Marty Stuart & Sunny Sweeney 9 10 11 12 13 14 15 16 17 Salary Raise Request Analysis PAY - it is the value paid hourly to the employee, it calculates using a logic function, it depends of the contract type to support employee promotions: type 1 pays $17.22 per hour; type 2 pays $15.39 per hour and type 3 pays $12.50; REG GROSS - the employee weekly incoming base, the value is calculated using a logic function, multiplying the maximum of week hours (today is 40) by the value of each employee's pay. OVER TIME - the value is calculated using a logic function, where 1.5 multiplication factor will be applied to the original paid hour. All employees will be assessed but only those who made more hours than the weekly maximum (40) should indicate the 1 17 "2. . . . extra value will be paid in this column. Calculate the number of worked hours that exceed the maximum (>40), then multiply this value by the factor (1.5). Use this amount to calculate the additional value should be paid to the employee regarding his/her contract hours showing in column PAY. ADJ GROSS - the employee incoming, which the calculation is based on the incoming column REG GROSS, considering a work month has 4 weeks FICA- calculation of the discount value to be apply in the employee's incoming regarding the FICA percent contribution, the discount percent should be used is expressed in the spreadsheet on top of the column label (in past month is 3.65%) NET - the final employee's incoming earned, based in the values of ADJ GROSS, OVER TIME and FICA Total and Average, compute the respective formula regarding each columns REG GROSS, OVER TIME, ADJ GROSS, FICA and NET; b. Create a new column named NET2.5, and make the calculation of employee's salary with a raise of 2.5% in their actual NET value, this new column should be cover by Total and Average too; c. Apply conditional format in cells in the column OVER TIME using a formula: highlight in yellow only the cells that holds values (if the employee does not have OVER TIME earns, the cell must stay plain, not highlighted); d. Create a three-dimensional bar graph illustrating the values regarding the respective Total and Average for the columns NET and NET2.5 in a new sheet. e. A committee has been formed to discuss the employee's career plan and the alternatives to alignment to maintenance the police of 2.5% salary raise with the budget decisions of the company's investment, that raise the division budget in 3.7%. Inset a text box titled Salary Raise Request Analysis you should describe brief arguments to support the salary raise request, based in the analysis of the payroll data, how it might impact in the division/company budget but also the company competitive and future profit, using the spreadsheet data and the graphic to illustrate your findings. Present a summary of your conclusions with arguments to explain the potential degradation of the employee's compensations and highlight the justification to support the salary raise request; f. Use professional design in labels, formatting, colors and backgrounds to present your spreadsheet data. es Additional instructions and helpful hints Matching Training to Software Use Payroll You have the responsibility for making a report to help the company administrators to implement new employee's career plan, providing data to the investment/budget committee. So, you are managing the employee's payroll data from the new division of Data Research, created two years ago to provide the company departments (Marketing Sales and Operations) with collection/analyze/update/report of real time market information, by making the calculation of their pay checks shown below. As the division's head, you want to demonstrate alternatives to justify the maintenance of 2.5% salary raise police to the administrators debate regarding the company's employees career plan, justifying the inclusion of Data Research division employees in the salary raise police. You have been asked to prepare a spreadsheet to put together the payroll data and summarizing the impacts of the actual salaries to the career plan in order to demonstrate the amount spent with the division (that had a raise in 3.7% in the budged). analyzing how the degradation of the salaries might impact in the retention of the division's employees and demonstrating to the board reasons to support/justify the improvement in the employee's compensations by include them the percent of the salary raise. The values shown are the total hours of each worker in the month and the value of the hour work for each one in the department and the total/average monthly values spent which department's workers, put your name in the indicated cell: a. Create a spreadsheet illustrating each employee's incomings/discounts data in the month. To do this, you will first enter the data shown in the figure below. Then compute the values of each columns/lines, for the monthly values as indicated below: F H - 1 PAYROL 2 B D YOUR NAME & CLASS SECTION REG GROSS OVER TIME ADI GROSS 3.65% FICA PAY NET CONT TYPE 1 3 1 2 Total Average HOURS 42 5 35 50 32 4 EMPLOYEE 5 Phil Collins 6 Nicole Kidman 7 Marty Stuart & Sunny Sweeney 9 10 11 12 13 14 15 16 17 Salary Raise Request Analysis PAY - it is the value paid hourly to the employee, it calculates using a logic function, it depends of the contract type to support employee promotions: type 1 pays $17.22 per hour; type 2 pays $15.39 per hour and type 3 pays $12.50; REG GROSS - the employee weekly incoming base, the value is calculated using a logic function, multiplying the maximum of week hours (today is 40) by the value of each employee's pay. OVER TIME - the value is calculated using a logic function, where 1.5 multiplication factor will be applied to the original paid hour. All employees will be assessed but only those who made more hours than the weekly maximum (40) should indicate the 1 17 "2. . . . extra value will be paid in this column. Calculate the number of worked hours that exceed the maximum (>40), then multiply this value by the factor (1.5). Use this amount to calculate the additional value should be paid to the employee regarding his/her contract hours showing in column PAY. ADJ GROSS - the employee incoming, which the calculation is based on the incoming column REG GROSS, considering a work month has 4 weeks FICA- calculation of the discount value to be apply in the employee's incoming regarding the FICA percent contribution, the discount percent should be used is expressed in the spreadsheet on top of the column label (in past month is 3.65%) NET - the final employee's incoming earned, based in the values of ADJ GROSS, OVER TIME and FICA Total and Average, compute the respective formula regarding each columns REG GROSS, OVER TIME, ADJ GROSS, FICA and NET; b. Create a new column named NET2.5, and make the calculation of employee's salary with a raise of 2.5% in their actual NET value, this new column should be cover by Total and Average too; c. Apply conditional format in cells in the column OVER TIME using a formula: highlight in yellow only the cells that holds values (if the employee does not have OVER TIME earns, the cell must stay plain, not highlighted); d. Create a three-dimensional bar graph illustrating the values regarding the respective Total and Average for the columns NET and NET2.5 in a new sheet. e. A committee has been formed to discuss the employee's career plan and the alternatives to alignment to maintenance the police of 2.5% salary raise with the budget decisions of the company's investment, that raise the division budget in 3.7%. Inset a text box titled Salary Raise Request Analysis you should describe brief arguments to support the salary raise request, based in the analysis of the payroll data, how it might impact in the division/company budget but also the company competitive and future profit, using the spreadsheet data and the graphic to illustrate your findings. Present a summary of your conclusions with arguments to explain the potential degradation of the employee's compensations and highlight the justification to support the salary raise request; f. Use professional design in labels, formatting, colors and backgrounds to present your spreadsheet data. es Additional instructions and helpful hints