50.1K

Verified Solution

Link Copied!

You are the manager of a group of 15 employees. You have to determine each employee's annual raise. Here are the guidelines you have been given.

The total amount of raises must not exceed 3.5% of the total of your department's current salaries. This is your budget.

An employee's raise depends upon their performance rating and how their salary compares to the maximum salary of the classification.

The classification and salary ranges are:

Classification	Lower Limit	Upper Limit
1	$0	$50,000
2	$50,001	$120,000
3	$120,001	$150,000

You calculate the employee's salary range percentage with the following formula:

% of range max = (current salary - lower limit of class) divided by (upper limit - lower limit)

The raise guideline is given in the following table (A = superior, B = average, C = below average):

% of range maximum is greater than or equal to	Rating	% of range maximum is less than	Raise %
0%	A	25%	9%
25%	A	50%	8%
50%	A	75%	6%
75%	A	100%	3%
0%	B	25%	5%
25%	B	50%	4%
50%	B	75%	3%
75%	B	100%	2%
0%	C	25%	2%
25%	C	50%	1%
50%	C	75%	0%
75%	C	100%	0%

For example, if Ms. Fields is in classification 2, has a current salary of $80,000, has a performance rating of B, then:

% of range max = (80,000 - 50,001) divided by (120,000 - 50,001)

% of range max = 43%

Raise = 4% of $80,000 or $3200

You must calculate a raise for each employee and make a PowerPoint presentation to your boss explaining your raise decisions. To do this:

1. The employee data is given below:

Wagner,1,38000,A Miles,1,28000,C Baldwin,1,35000,B Wright,1,45000,A Hawk,1,49000,B Determan,2,55000,B Mitchell,1,48000,A Knight,3,125000,A Smith,2,58000,C Stegman,1,45000,A Hawbaker,1,31000,B McMichael,1,47000,A Little,2,105000,B Jordan,1,35000,A Laudon,2,108000,B

2. Copy this text file to your machine.

3. Open your copy of the text file with Excel and, using Excel's text import wizard, read the file into a spreadsheet. (Data >Get External Data> Import Text File)

Commas delimit the text file. The fields, in order, are: employee name, employee classification, employee current salary and the employee performance rating. For example, here is one employee record: Smith,1,35000,A

Name the spreadsheet (.xls file) yourname where you replace yourname with your last name, for example witt.xls

Add the columns you will need to make your decision.

* % of range max (see formula above),

Guideline raise % (based on above table),

* Guideline raise in $ (guideline raise % times current salary),

Actual raise in $ (to meet the budget constraint of 3.5% you may have to lower someone's guideline amount),

* Actual raise % (actual raise $ divided by current salary),

* Guideline raise in $ minus actual raise in $

You must use Excel computations and formulas in creating the columns bulleted with a bullet point and an *.

Total the following columns: current salary, guideline raise $, actual raise $ and guideline minus actual raise $.

Compute on the spreadsheet the budget amount (3.5%), the average salary, the average actual raise in $ and the average actual raise % as a % of total salaries.

You must use Excel computations and formulas in creating these totals and averages.

Include column headings. Label the computed fields.

Make sure all currency fields on the spreadsheet are displayed as dollars and cents. Make sure all percentage fields are displayed as percentages with one number after the decimal point.

Develop a PowerPoint presentation for your boss. Name the presentation (.ppt file) yourname where you replace yourname with your last name, for example witt.ppt

a. Make the presentation business-like. Review the suggestions in the Effective PowerPoint handout. Provide a business-like background

b. The first slide should be a title slide. Include the name of your department, the title of your analysis and your name. Include a clipart logo for your company. You can use clipart from the library that comes with PowerPoint.

c. The second slide should contain a departmental summary. Show total departmental salaries, total budgeted raise $, total guideline raise $, total actual raise $ and average actual raise %.

d. In another slide or two, give your reasons (a couple of phrases) for changing an employee's raise from the guideline. Comment on each employee or group of employees you changed. Be specific. Include a comment for each employee or group of employees. Explain your decisions.

e. Create another worksheet in the same .xls file and copy the following columns from the main worksheet: Name, Classification, Salary, Rating, % of range maximum, Actual $ Raise and Guideline - Actual Raise $. Sort this worksheet first by classification in descending order and second by ascending employee performance rating. Include this entire worksheet as the last slide. Make sure it is readable on the PowerPoint slide.

can the answer be as excel file in order to understand , screenshot no helpfule at all

Answer & Explanation Solved by verified expert

See Answer