You have the responsibility for making a report to help the company administrators to review the division's payroll costs and provide input to guide actions to preserve the employee’s retention, considering the layoffs were implemented in 2020 due to a budget cut for 2021 (the estimated cut of 1.7% in the annual division budget, with potential to rise to 2.7% on the 6 first months of 2021). It is your job to provide data to the Budget Committee to review the company’s strategic plan, to preserve the company’s efficiency, the division’s budget, and to avoid new layoffs. You are the head of the Data Research division, created two years ago to provide company departments (Marketing, Sales, and Operations) with collection/analysis/update/report of real-time market information from the company’s online strategies. You will present a prospect report for the division employee’s payroll for 2022, describing alternative scenarios for the company to retain the current 10 employees or justify the reduction of only 20% of current staff instead of 40% requested by the Budget Committee. The company was granted additional revenue due to the recent governmental fiscal incentives package, which open room to review the budget cut effectively verified in January 2021 was 0.7% instead of 1.7% prospected in for the annual division budget of $ 550k. Then, you developed the division employee’s contract cost analysis, to argue in favor of reduced layoff percentages or advocate that layoff are no longer need in your division, inclusive justify the implementation of a new budget policy that allows managers to save at least 5% of the division’s original budget as Emergency Reserve Fund. In your report, you must provide the analysis describing all possible budget scenarios: 1) no layoffs; 2) 20% layoffs and 3) 40% layoffs (considering the division's $ 550k annual budget). In all scenarios, you will try to present justifications considering the possible savings by assessing employee’s contracts, pointing directions for the company restructuring budget plan. You have been asked to prepare a spreadsheet to put together the payroll data estimation and summarize the impacts of these prospects. The data presented below is an average of the worked hours per week of each division employee for the past four months (September to December 2020 after the layoff). The analysis takes into consideration the costs of the employee’s contract terms: i) the employee’s professional expertise: Category 1 (trainees), Category 2 (Junior), Category 3 (Middle), Category 4 (Senior); ii) the employee’s status (full-time/part-time); iii) the contract type (duration of the contract in months); iv) the contract compensation term ($ hourly); and v) the total/average amounts spent by the division with payroll (total/average incoming paid). It is your job to provide additional data regarding the division payroll costs (cost by each employee’s category contract versus all cost spent with the division’s employees). Put your name in the indicated cell. Proceed with the implementation of all items from a to g:

a. Create a spreadsheet illustrating each employee’s income/ 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:

• 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 frizz 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.

g. Apply professional design in the spreadsheet: labels, formats, colors, and backgrounds to better present your data and the chart.

Answer rating: 100% (QA)

I can assist you in providing the necessary formulas and calculations for the requested data analysis Ill provide the stepbystep instructions and form... View the full answer