Fluent Essays

Assignment 2- Lab Practice Description — Data Analysis using MS Excel Payroll 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 ofa 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 divisions $ 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); ii) 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:            1                                                                                                         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 Divisions 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. Additional instructions and helpful hints 1. The main purpose of this assignment is not only to familiarize you with the business aspects of the spreadsheet but also to give you a chance to learn how to use it efficiently in a real context. The problem requires you to match basic training with some advanced software use and data analysis to support effective communication to your leadership. You need to evaluate the values paid as salary and make efficient justifications to support the different proposals for changes on the layoff policy to the budget committee. If you are new to Excel, be sure you consult the assignment rubric to identify the essential technical elements that will be assessed and to complete at least one of the tutorials (see below or the Blackboard or the WileyPLUS) before beginning this assignment. 2. The value of being an expert in spreadsheets, is the agility to manage and to update data automatically, the concept of formula/functions, cell’s references/addressing, formats, and design are essential to provide efficient data visualization. The ability to apply correctly these concepts is the key to master spreadsheets. This assignment is not only about providing the expected values in the columns/lines but make sure to develop the required abstraction to use correctly the available tools to provide the best formula to make the calculations as required. Is that will assure the data is going to be easily manageable, updating and formatting automatically in the most efficient way to be used to support data analysis. 3. A common mistake a few students make, even with my comments elsewhere about it is: when the problem description says calculate the values of columns/lines ... by multiplying the monthly hours”....    Assignment 2- Lab Practice Description — Data Analysis using MS Excel Payroll 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 ofa 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 divisions $ 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); ii) 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:            1                                                                                                         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 Divisions 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. Additional instructions and helpful hints 1. The main purpose of this assignment is not only to familiarize you with the business aspects of the spreadsheet but also to give you a chance to learn how to use it efficiently in a real context. The problem requires you to match basic training with some advanced software use and data analysis to support effective communication to your leadership. You need to evaluate the values paid as salary and make efficient justifications to support the different proposals for changes on the layoff policy to the budget committee. If you are new to Excel, be sure you consult the assignment rubric to identify the essential technical elements that will be assessed and to complete at least one of the tutorials (see below or the Blackboard or the WileyPLUS) before beginning this assignment. 2. The value of being an expert in spreadsheets, is the agility to manage and to update data automatically, the concept of formula/functions, cell’s references/addressing, formats, and design are essential to provide efficient data visualization. The ability to apply correctly these concepts is the key to master spreadsheets. This assignment is not only about providing the expected values in the columns/lines but make sure to develop the required abstraction to use correctly the available tools to provide the best formula to make the calculations as required. Is that will assure the data is going to be easily manageable, updating and formatting automatically in the most efficient way to be used to support data analysis. 3. A common mistake a few students make, even with my comments elsewhere about it is: when the problem description says calculate the values of columns/lines ... by multiplying the monthly hours”....“calculate the value using” or “apply the format...” you are not supposed to do these calculations or apply the formats by hand - these are to be done by the spreadsheet program automatically based on the calculations and procedures you will implement/setting! You must figure out how to put in the right formulas/functions/formats/settings, in other words, you are instructing the spreadsheet -through the formulas/functions/formats/settings - to do these calculations. 4. Excel Tutorials: You may use any of the Office 2003/2007/2010/2013/2016 etc. versions of Excel software. The following Microsoft website is a good resource for learning about Excel. http://office.microsoft.com/en-us/training/FX100565001033.aspx“calculate the value using” or “apply the format...” you are not supposed to do these calculations or apply the formats by hand - these are to be done by the spreadsheet program automatically based on the calculations and procedures you will implement/setting! You must figure out how to put in the right formulas/functions/formats/settings, in other words, you are instructing the spreadsheet -through the formulas/functions/formats/settings - to do these calculations. 4. Excel Tutorials: You may use any of the Office 2003/2007/2010/2013/2016 etc. versions of Excel software. The following Microsoft website is a good resource for learning about Excel. http://office.microsoft.com/en-us/training/FX100565001033.aspx