PLEASE SHOW ALL THE EXCEL FORMULA WORK !!!!!!

Payroll Assignment (Please follow the steps below and answer all questions with excel formulas). a. Create a spreadsheet illustrating each employee's incomel discount data considering the average of hours worked weekly in the past four months (column HOURS). To do this, you will first enter the data as shown in the figure below. Then, compute the values of each column/lines, creating the respective formulas following the instructions indicated below: PAYROLL 3 YOUR NAME & COSC3325 SECTION 4 5 ANALYSIS DATA COLLECTED SEP TO DEC 2020 6 7 EMPLOYEE DIVISION STATUS CONTRAC. CONTRAC 8 CATEGORY EMPLOYEE TYPE COMPENS TYPES HOURS 9 Category 1 2 PT 1 A 51 10 Category 2 ) PT 2 8 43 11 Category 3 3 FT 2 40 12 Category 4 2 FT c 47 REG GROSS ADJ GROSS OVER TIVE 2.95% FICA PAY NET Total Average 14 15 16 Division Restructure Budget Analysis 18 19 20 PAY - it is the amount paid hourly to the employee, it is calculated using a logic function, the value depends on the specific contract compensation assigned to employee earnings: type 1 pays $15.00 per hour; type 2 pays $19.59 per hour and type 3 pays $24.50 (With the budget freeze there are no provisions to raise these values). REG GROSS - it is the employee's weekly income, the value is calculated using a logic function, multiplying the maximum of week hours (today is 40/week) by the value of each employee's payment by contract type. ADJ GROSS - it is the employee's monthly income, the calculation is based on the weekly-based income considering that a commercial work-month has 4 weeks. OVERTIME - it is the monthly additional income due to extra hours; it is calculated using a combination of logic functions, considering the assessment of employee's status type (FT or PT) and the calculation of the number of hours worked beyond the weekly maximum (40 hours): where a multiplication factor of 1.5 will be applied to the original paid amount of hour for PT employees; FT employees have a multiplication factor of 3. All employees will be assessed but only those who made more hours than the weekly maximum (40) will receive the extra income and the value will be presented in this column. Calculate the number of weekly work hours that exceed the maximum > 40), then multiply this value by the respective multiplication factor (PT=1.5; FT=3), also considering that a commercial work-month has 4 weeks. Use this guideline to calculate the monthly additional income considering that OVERTIME is based on the employee's contract compensation, that is the amount of hour shown in column PAY. FICA - calculate the monthly discount value regarding the FICA contribution, based on the employee's monthly income showing in the ADJ GROSS column, exploiting the absolute addressing or absolute reference. The reference for the discount (percentage value) is expressed in the spreadsheet on top of the column label, it can change anytime. .NET - it is the final employee's monthly income paid, the employee salary, based on the values of monthly/additional incomes and discounts. Total and Average, compute the value using the respective functions regarding all designated columns. b. Create a new column named EMPLOYEE CONTRACT COST, and make the calculation of how much costs the contract for each employee's category, it is calculated using a logic function, the value depends on the employee's final salary showing in the NET column and the contract compensation to support employee's earnings: type A is related to 6 months contract; type B is related to 9 months contract and type C is related to 12 months contract. This new column should be covered by Total and Average lines too; C. Create a new column named DIVISION CONTRACTS COST TOTAL, and calculate the value expressed on Employee Contract Cost and the number of division employees in each category. This new column should be covered by Total and Average lines too; d. Apply conditional format in the cells from column OVERTIME using the formula: highlight in Light Green Fill with Dark Green Text only the cells that hold values greater than $500 (if the employee does not earn OVERTIME income, the cell must stay plain, not highlighted). e. Create a three-dimensional column chart illustrating the values earned regarding the respective Employee Category and the columns NET, EMPLOYEE CONTRACT COST, and DIVISION CONTRACT COST TOTAL. Add a title chart to the respective Data Table and move the chart to a new sheet. f. A committee has been formed to discuss the company restructuring plan, reviewing the budget cut prospect to 2021 and the effects of the layoffs on divisions productivity, reviewing the budget cut plan prospect of 2.7% cut on divisions annual budget for 2021, and the estimation for 2022. Considering the change in the company's perspective revenues due to the governmental fiscal help plan, the cuts expected were lower than the estimated (verified 0.7% instead of 1.7% in the $ 550k annual budget for 2021). Insert a text box entitled Division's Restructuring Budget Analysis and you will present the assessment of employee's contracts costs, providing your division payroll analysis estimates for 2022 considering the impacts of these proposals: 1) implement no layoffs in 2021 (keeping current 10 employees); 2) implement a 20% staff layoff, and 3) implement a 40% staff layoff as recommended the original cut plan for 2021. You should briefly describe the argumentation to support or not each proposal, based on the analysis of the data, highlighting potential pros/cons in a short time of each scenario and the implications for each proposal on retention of employees, division's productivity, company's competitive, and future profit/investments expense. Correlate the spreadsheet data and the chart to illustrate your findings and suggestions. Payroll Assignment (Please follow the steps below and answer all questions with excel formulas). a. Create a spreadsheet illustrating each employee's incomel discount data considering the average of hours worked weekly in the past four months (column HOURS). To do this, you will first enter the data as shown in the figure below. Then, compute the values of each column/lines, creating the respective formulas following the instructions indicated below: PAYROLL 3 YOUR NAME & COSC3325 SECTION 4 5 ANALYSIS DATA COLLECTED SEP TO DEC 2020 6 7 EMPLOYEE DIVISION STATUS CONTRAC. CONTRAC 8 CATEGORY EMPLOYEE TYPE COMPENS TYPES HOURS 9 Category 1 2 PT 1 A 51 10 Category 2 ) PT 2 8 43 11 Category 3 3 FT 2 40 12 Category 4 2 FT c 47 REG GROSS ADJ GROSS OVER TIVE 2.95% FICA PAY NET Total Average 14 15 16 Division Restructure Budget Analysis 18 19 20 PAY - it is the amount paid hourly to the employee, it is calculated using a logic function, the value depends on the specific contract compensation assigned to employee earnings: type 1 pays $15.00 per hour; type 2 pays $19.59 per hour and type 3 pays $24.50 (With the budget freeze there are no provisions to raise these values). REG GROSS - it is the employee's weekly income, the value is calculated using a logic function, multiplying the maximum of week hours (today is 40/week) by the value of each employee's payment by contract type. ADJ GROSS - it is the employee's monthly income, the calculation is based on the weekly-based income considering that a commercial work-month has 4 weeks. OVERTIME - it is the monthly additional income due to extra hours; it is calculated using a combination of logic functions, considering the assessment of employee's status type (FT or PT) and the calculation of the number of hours worked beyond the weekly maximum (40 hours): where a multiplication factor of 1.5 will be applied to the original paid amount of hour for PT employees; FT employees have a multiplication factor of 3. All employees will be assessed but only those who made more hours than the weekly maximum (40) will receive the extra income and the value will be presented in this column. Calculate the number of weekly work hours that exceed the maximum > 40), then multiply this value by the respective multiplication factor (PT=1.5; FT=3), also considering that a commercial work-month has 4 weeks. Use this guideline to calculate the monthly additional income considering that OVERTIME is based on the employee's contract compensation, that is the amount of hour shown in column PAY. FICA - calculate the monthly discount value regarding the FICA contribution, based on the employee's monthly income showing in the ADJ GROSS column, exploiting the absolute addressing or absolute reference. The reference for the discount (percentage value) is expressed in the spreadsheet on top of the column label, it can change anytime. .NET - it is the final employee's monthly income paid, the employee salary, based on the values of monthly/additional incomes and discounts. Total and Average, compute the value using the respective functions regarding all designated columns. b. Create a new column named EMPLOYEE CONTRACT COST, and make the calculation of how much costs the contract for each employee's category, it is calculated using a logic function, the value depends on the employee's final salary showing in the NET column and the contract compensation to support employee's earnings: type A is related to 6 months contract; type B is related to 9 months contract and type C is related to 12 months contract. This new column should be covered by Total and Average lines too; C. Create a new column named DIVISION CONTRACTS COST TOTAL, and calculate the value expressed on Employee Contract Cost and the number of division employees in each category. This new column should be covered by Total and Average lines too; d. Apply conditional format in the cells from column OVERTIME using the formula: highlight in Light Green Fill with Dark Green Text only the cells that hold values greater than $500 (if the employee does not earn OVERTIME income, the cell must stay plain, not highlighted). e. Create a three-dimensional column chart illustrating the values earned regarding the respective Employee Category and the columns NET, EMPLOYEE CONTRACT COST, and DIVISION CONTRACT COST TOTAL. Add a title chart to the respective Data Table and move the chart to a new sheet. f. A committee has been formed to discuss the company restructuring plan, reviewing the budget cut prospect to 2021 and the effects of the layoffs on divisions productivity, reviewing the budget cut plan prospect of 2.7% cut on divisions annual budget for 2021, and the estimation for 2022. Considering the change in the company's perspective revenues due to the governmental fiscal help plan, the cuts expected were lower than the estimated (verified 0.7% instead of 1.7% in the $ 550k annual budget for 2021). Insert a text box entitled Division's Restructuring Budget Analysis and you will present the assessment of employee's contracts costs, providing your division payroll analysis estimates for 2022 considering the impacts of these proposals: 1) implement no layoffs in 2021 (keeping current 10 employees); 2) implement a 20% staff layoff, and 3) implement a 40% staff layoff as recommended the original cut plan for 2021. You should briefly describe the argumentation to support or not each proposal, based on the analysis of the data, highlighting potential pros/cons in a short time of each scenario and the implications for each proposal on retention of employees, division's productivity, company's competitive, and future profit/investments expense. Correlate the spreadsheet data and the chart to illustrate your findings and suggestions