Assignment2 Lab PracticeDescriptionDataAnalysisusing MSExcel

MatchingTrainingtoSoftwareUse

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 employees retention, considering the layoffs

were implemented in 2020 and 2021 (following the agreement to proceed with the layoffs in stages until

reach 40% at the end of 2021) due to a budget cut for 2021 (the estimated cut of 5% in the annual division

budget, with potential to rise to 7% on the middle of 2021). It is your job to provide data to the Budget

Committee to review the companys strategic plan, to preserve the companys efficiency, planning

to recompose the divisions budget, and avoid new layoffs.

You are the head of the Data Research division, created in 2018 to provide company departments (Marketing,

Sales, and Operations) with collection/analysis/update/report of real-time market information from the

companys online strategies. You will present a prospect report for the division employees payroll for 2022,

describing alternative scenarios for the company to retain the current number of employees or justify the

reduction of only 10% of current staff (indicating which category would bring less productive impact to the

division. In February 2021 the company was granted additional revenue due to the recentfederal government

fiscal incentives package, which open room to review the budget cut of 5% applied on the annual division

budget of $ 550k and the 20% layoff effectively verified from August 2020 to January 2021. Considering this

scenario, you will develop the division employees contract cost analysis, to argue in favor of reduced layoff

percentages or even advocate that layoffs are no longer need in your division, inclusive you will justify the

implementation of a new budget policy that allows division managers to get back at least 2% of the divisions

cut budget as Emergency Fund. In your report you must provide the analysis describing the possible budget

scenarios:

1) no layoffs;

2) 10% layoffs (considering the actual division's budget of $ 522.5k after the January budget cut) and

indicating which category would bring less productive impact to division productivity

3) Justify the recomposition of the divisions cut budget as Emergency Fund.

In all scenarios, you will try to present justifications considering the possible savings considering the updates

on revenues and by assessingemployees 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 scenarios. The data presented below is an average of the worked hours

per week of each division employee for the past six months (March to August 2021 after the layoffs

effective in August 2020 and January 2021). The analysis takes into consideration the costs of the employees

contract terms:

i. the employees professional expertise: Category 1 (trainees), Category 2 (Junior), Category 3

(Middle), Category 4 (Senior);

ii. the employees 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 employees

category contract versus all costs spent with all the divisions employees). Put your name in the

indicated cell. Proceed with the implementation of all items from a to h:

a. Create a spreadsheet illustrating each employees income/discount data considering the average

of hours worked weekly calculated based on the period of six 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/line, creating the respective formulas following the instructions indicated below:

1

PAY it is the amount paid hourly to an employee, it is calculated using a logic

function, the value depends on the specific contract compensation assigned to

employee earnings: type 1 pays $13.00 per hour; type 2 pays $17.59 per hour and

type 3 pays $21.50 (With the budget frizz there are no provisions to raise these

values)

REG GROSS it is the employees 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 employees payment by contract type.

ADJ GROSS it is the employees 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 employees

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 employees contract compensation, that is the number of hours shown

in column PAY.

FICA calculate the monthly discount value on employee salary regarding the

mandatory FICA contribution, based on the employees 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 employees monthly income, it is the value on their paycheck, the

employee salary, based on the values of monthly/additional incomes and discounts.

Total and Average, compute the value using the respective excel functions it would

show in all designated columns.

b. Create a new column named EMPLOYEE CONTRACT COST, and make the calculation of how

much costs the contract for each employees category, it is calculated using a logic function, the

value depends on the employees monthly final salary showing in the NET column and the

contract compensation to support employees earnings: type A is related to 6 months contract;

2

-----------------

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 bar 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, 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

implementation of the budget committee recommendations for the cuts (executed in 2020 and 2021),

the effects of the layoffs on divisions productivity, reviewing the budget cut plan prospect of 7% cut

on divisions annual budget for the end of 2021, and the estimation for 2022. Considering the change

in the companys perspective revenues due to the federal government fiscal help plan, the cuts

expected should be equalized to respond to what was indeed verified 3.2% instead of 5% applied in

the $ 550k annual budget in 2020 due to what was estimated for 2021. Insert a text box entitled

Division's Restructuring Budget Analysis and you will present the assessment of employees

contracts costs, providing your division payroll analysis to guide the estimates for 2022 considering

the impacts of these proposals (remember your analysis is based on the average of hours worked for

each employee category verified during six months1

): 1) implement NO layoffs in 2021 (keeping the

current number of employees) or 2) implement only a 10% staff layoff against the recommended the

original cut plan for 2021 and 3) justify the recomposition of the divisions budget in 2% as Emergency

Fund. You should briefly describe the argumentation to support 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, divisions productivity, companys competitiveness, and

future profit/investments expense, especially the defense of the divisions budget recomposition in

2% Emergency Fund. Correlate the spreadsheet data and each chart bar to illustrate your findings

and suggestions narrative.

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

present your data and the chart (it should be colorful but still in a professional way).

h. Save the file and named your file as Assignment2 -FullName 1 point penalty for not follow the

required file name format.

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.

1 It is recommend that you test different values on the column HOURS to have a better perspective of how the

numbers will behave in the better and worse case, considering the extra need to be paid as additional

incoming.

3

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 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 should instruct

the spreadsheet - through the formulas/functions/formats/settings - to perform these actions.