Phase 1: Internal Alignment Policy
Set Policies and Write the Detailed Report
The purpose of the Detailed Report is to demonstrate that you set your policies strategically. That is, that there is a clear line of thought from the WHO-WHAT-HOW of your client to your choice of values, and that specific values were the basis for defining your policies. The report will be typed and cover all logic and policies as they relate to corporate values. The detailed report will begin on a new page (after the Executive Summary) and contain the following elements, labeled and defined in the order listed below.
Mission, Analysis, and Values (MAV): This section is a duplicate of the first assignment, unless you have improved it based on comments and better understanding of the case. The mission, WHO-WHAT-HOW analysis, and values are simply restated here so the connection with P1 policies is clear. That link must be clear, because you will refer only to these values to justify the policies you set. Once this link is clear, you will not need to continue referring back to the mission or analysis in your report. You can be strategic by referring to individual values only.
Your second tab, at the bottom of your Excel case will be titled "P1-Job families." Click it.
Policies: This section of your report will be entirely different for each phase report. For each phase you create policies as the basis for future compensation decisions by the corporation. A policy is simply a rule for making a decision. As each policy is made, explain in your written report how it fulfills or partially fulfills some selected value. Use the specific name you gave the value in the analysis section, and underline the value each time you mention it. (If you find it necessary to refer back to your corporate mission or analysis to justify a decision, then your values need further development.)
In Phase 1 you will write three policies to include in the Detailed Report and therefore to defend from selected values:
1. The families you name and define are your first policy. ACME has many jobs, but 24 of them are listed in "Case P1 JDs." Group these jobs into two families based on one of your defined values. To do this, write a definition for Family A that centers around some task, situation, exposure or other distinguishing characteristic of its jobs in such a way that its jobs have a unique opportunity to fulfill one of your chosen values. A good definition will equip you, or someone else, to go through the list of all jobs and sort them either into or out of that family. The definition is a rule for deciding which jobs fit in the family and which do not, therefore the definition is a policy. The definition is not simply supported by a certain value; it is based upon the value; which creates the strategic link to your MAV.
The other family simply includes all other jobs. Include the Family A definition in your report, tell in your report which value it is based upon, and list the jobs you include in each family. If this definition is clear, the reader should be able to understand why the value chosen to define Family A is not as important for Family B jobs. This does not mean that the value base of Family A does not occur at all in Family B, nor does it mean that the base value is the only value affecting jobs in Family A. It simply means that the majority of that value's importance will occur in Family A jobs. If you sort a job into one family or the other, and the decision why may not be clear to the reader; explain why you placed it there. Your explanation will relate to the base value.
Explain the logical connection between job family definitions and the value. This explanation is what demonstrates that your job family policy is strategic. That process not only makes your policies more defensible in court (Sooner or later someone will sue.), but it also makes policies that work for competitive advantage, uniting all of your policies under your corporate values system.
Because of their important link to strategy, every time you mention a value in the detailed report, underline the word or phrase for that value, just as you did in the Values section of the report, and be sure the word or phrase is identical to the one in the values discussion. This will make the strategic link easier for readers to follow.
Apply your policy by assigning each job to one family or the other. If you are not familiar with a given job, use the job descriptions in "Case P1 JDs" to determine assignments. If any job(s) do not fit obviously within one family or the other, based on your family definitions, make clear how your choice satisfies some established policy, value, or other logic. [To assign jobs to one family or the other, simply click the square gray button to the right or left of the job name on the Excel sheet. If you change your mind, just click the opposite button. All jobs must be assigned to one family or the other, and no family can have more than 15 jobs. If your definitions place too many jobs in one family or the other, go back and "tweak" your definitions a little more.]
2. The job factors you select are your second policy. Identify a group of compensable factors that address your values. You will find a list of factors by category on the Excel sheet labeled "P1--Factors and anchors." Click on the bottom tab by that name, and you will be taken to this sheet. This sheet contains a table of suggested factors in pale gray letters. This is not an exhaustive list of possible factors or anchors, but it is provided to speed your decision process for this case only. To select a factor, type the name of a value that suggests that factor should be included into the colored cell to the left of the factor name. If a factor should be included for some other reason than a specific value, then simply enter "X" in the same left cell. You may choose as few as one factor per category, but do not go beyond four factors per category. You must support at least half of your selected factors with your values. The rest can be marked with an "X."
In your report, list each factor and explain why it was chosen. If it supports a value, explain that link. Underline the mention of that value.
Once a factor is chosen, a series of six possible degrees of inclusion will appear to the right, again in pale gray. To anchor a degree as one you will use, enter a number in the colored cell underneath. The pale yellow cells are for Family A, and the pale orange cells are for Family B. You may use a different combination of factors for each family, as long as you do not exceed four factors total per category. Once you enter a number, the cell color will darken. Anchor as many degrees as you think you will need for the variety of jobs in each family.
For example, if Education is a chosen factor, and you enter numbers into the yellow cells under High School/GED and Associates or tech degree, then you have determined that all jobs in Family A will require either a high school degree or an associate degree. Later in the case you will indicate for each Family A job which degree level it requires, and you will do so by entering one of the two numbers you chose for either high school or associate.
Continuing with the example above, if the two numbers selected for high school and associate degree were 1 and 2 respectively, then you have indicated that an associate degree is worth twice as much as a high school degree for your organization (2 is twice as large as 1). If you wanted the associates degree to be worth 50% more, then choose 2 and 3 to represent your degrees of inclusion (3 is 50% larger than 2).
All factors in the same family must have the same number of degrees. For example, if you choose three degrees for education in Family A, you must also choose three degrees for all other factors in Family A. Number the anchors beginning with zero or one, the lowest number being the lowest degree of inclusion, and increasing in worth to the right. In addition to all factors in a family having the same number of degrees, all factors in a family must have the same anchor numbers. For example, if Family A has anchors numbered 1, 2, 3 under education, Family A must have factors numbered as 1, 2, 3 under all other factors selected. Even though the numbers are the same, the anchors need not be all from the same column. For example, Family A education may include "Less than hi" as 1, "High school" as 2, and "Associate" as 3; while Years of experience may include "No" as 1, "Suggests systems-level" as 2, and "Designs new products" as 3. Family B can be entirely different from Family A, as long as all degrees and anchors are consistent within Family B. For example, Family A degrees may be 1, 2, and 3; while Family B degrees could be 0, 1, 2, 3, and 4. But all of Family B must have the same degrees, just as all of Family A must have the same degrees. (The reason that they must be the same within a family is because if one factor has a higher maximum number than another, you have inadvertently given that factor more importance. Wait until you "weight" factors on a later sheet to make such distinctions.)
Scroll down the sheet, picking at least one factor within each of the four categories, Skill, Effort, Responsibility, and Working conditions. Once you have determined all factors and degrees, click the print icon at the top of the window. You will want a hard copy of this Excel sheet as a reference when you enter degrees into the "Job evaluations" sheet.
3. The factor weights you assign are your third policy. The compensable factors you chose are listed in the table on the "Factors and weights" sheet. Determine which compensable categories are more important than others, and indicate how much more by entering percentages into the dark green spaces beside each category. Notice there is a separate column of cells for each job family, so the percentages may be distributed differently across families, but each column (family) must add to 100%. When categories add up to 100% for a given family, the white space beside each category will indicate in red how much of the category remains to be assigned to specific factors in that category. Enter percentages beside the compensable factors to add up to the category percent. When this is correctly done, the red percent cell is again blank. You determined in your MAV the order of importance of your values. That order should be reflected in weights you give their factors across both families. It is also expected that the value that drove your definition of Family A will be heavier in Family A than Family B, though Family B can certainly have that value weighted to some percent. In the Detailed Report, simple state in a short paragraph what factors and weights gave your Family A strength in the value around which it is defined. Your report only needs to contain explanations for the highest one or two percentages assigned to each family.
Apply your policy to the Phase 1 software on the "P1--Job evaluations" sheet.
There you will find 24 small tables, one for each job. Each is already labeled for the family in which you placed it, and each has the factors and percentages you chose as weights. Work your way through each job, typing in the degree of inclusion (0, 1, etc.) for that particular job. Use the job descriptions in "Case P1 JDs" to decide what levels of skills or competencies the job requires. Use the pages you printed from "P1-Factors and anchors" to determine what number to enter for that degree. For example, if you chose a 1 to anchor education at "Less than a high school education," and a particular job needs that level of education, you would enter a 1 in the corresponding cell. The table will automatically weight that anchor by the percent you entered into the previous sheet. At the last job you will find a gray button labeled, "Press to sort jobs by value." Pressing this button will take you to the "Job structure" sheet and reorganize your jobs into a job structure. Take a look at the order of jobs in each family. Do they make logical sense? Be sure jobs of higher value are indeed higher on the list. For example, the Assembler I should not be valued higher than the Assembly Supervisor, and the secretary should not be valued higher than the executive secretary. If these or others seem to be in the wrong order, go back to "Job evaluations" and make adjustments to your points. Then again press the sort button.
Index of values: Just as you might look in the back of a text book and find key words in an index, telling you what page to find its discussion, you need an index of your values to help a reader find where the values are discussed in relation to your policies. The policies section of your detailed report lists policies in the order that they were developed and notes there which values were used to set the policy. This section lists all values again, in the order they were listed in the Values section, but this time do not repeat their definition or connection with mission or analysis. This time, after each value, mention every policy developed based on that value. This is not new data, but the policy information is in a new order. In affect it is an index for finding values in the above text. For example, if the person reads here that you supported your value of personal growth of employees by weighting the education factor heavily, then the reader should be able to look back in your report and find where you set weights for factors and find your initial comment that education was weighted heavily to support your value of personal growth for employees (Value # whatever). Some policies you made to support one value may be counterproductive toward another. This is sometimes unavoidable. Note any such cases, and explain why your policies favored the more important value.
Financial Impact: Look ahead in the Excel program to P2-Budget. On the Income Statement there is a record of the past two years, the current year, and a projection for next year. If nothing is done, the projection for net earnings per share (NET EAR Pr SH) is a slight drop. All other indicators in the income statement are on the upswing, so give your best guess for why that is happening. (I don't expect you to adequately analyze a financial statement for this course. Just think about it and take a stab.)
Executive Summary: Now type an executive summary to place at the beginning of your report. It will be your cover page, so make it the first page of the document and include no other cover page. You produce it last, because it summarizes what you have done throughout the policy writing process. (It contains no information not already included in your Detailed Report.) You then place it at the beginning of the report, because it is the first thing the CEO will want to look at. It will be no more than one typed, double-spaced page for Phase 1 (P1). The Executive Summary will contain four parts: a) The heading [course number, ACME Phase 1, Your Name], b) list of values in order of importance (name them, no definition here), c) list the policies you made (don't defend or explain here), and d) a one or two sentence comment on use of values. Indicate which value or values were utilized most in setting phase policies and which least or not at all. (This is not necessarily a reflection on the importance of the value, just its usefulness in this phase.) A summary contains nothing that is not detailed in the rest of the report. Values are taken from the Values section; the policies are all decisions made in the Policies section; and the Use of values information is from the Index of values section. In the policies section of the Ex Sum, you need not give all details. For P1 give the family definitions (Policy 1), name the factors chosen (Policy 2), and mention which two or three factors were weighted the most (Policy 3). The executive summary may be the only thing an executive reads, until a grievance or law suite. Then details are demanded, and they better be complete! After the summary, the Detailed Report begins a new page. Facts for the Index and the Summary are provided on the Case sheets.
As an executive concerned that our values are being emphasized, I would see in the Ex Sum what values were considered, I would see how they derived from our mission and client needs in the MAV, and how policies are based on them in the detailed report. Then if I wanted to see how any particular value were used, I would look in the Index of Values to find where and what decision, if any, was made with that value in mind. The Excel just demonstrates that what you said in the detailed report can be carried out.
Organize the Final Copy of Your P1 Report
Organize your report as follows. (To be sure you include all parts as required, and no extraneous parts, you may choose to copy this outline into your word processor and simply fill it in with your information.):
1. Executive Summary (with no other cover or title pages, write this so it fits on one page)
a. Name and other identification
b. Values
c. Policies (For Phase 1: list family names, list factors chosen within each of the four factor categories, and indicate largest weights)
d. Use of values
2. Detailed Report
a. Mission, Analysis & Values (MAV)
b. Policies
i. Job Families
ii. Job Factors
iii. Factor Weights
c. Index of values
d. Financial impact
Consider how well all parts flow together with logic and format. The whole phase product must work not only for implementing the policies, but also as a reference in court. Your instructor is more concerned with consistent, coherent logic than with what any particular policies are. Include no copies of Excel tables or pages in the written report, unless they make your explanations easier. You must turn in a copy of your Excel work with the report. Your instructor will specify what format is required for submitted work. It may be hard copy, e-mail attachment, etc.
Phase 2: External competitiveness policies
Set Policies and Write the Detailed Report
As with P1, P2 will have a detailed report. It will have all the same major elements, and it will be subject to all quality and presentation standards required for P1.
MAV: Include your MAV at this point, just as you did in P1. You may revise any part of this, based on better understanding, or simply cut and paste the same MAV you submitted in P1. No other parts from your P1 written report will be included in your P2 submission.
Policies: In P2 there are six policies to decide, some of which you must defend from your values. You will make policies separately for each job family:
1. Pay Strategy for Family A. Select a pay policy strategy (lead, lag or match) for Job Family A, and indicate by pressing the button on Case spreadsheet "P2-Design A." Defend the decision in your report with reference to specific values.
2. Companies Surveyed for Family A. Determine what type of organizations will be surveyed, and relate your choice to some criteria (size, technology, job type, product market, etc.). Note that six industries are listed down the left, and seven company sizes are listed across, totaling 54 possible companies to survey. To apply your strategy, place an "x" in ten separate yellow cells. Next you will need to indicate which averaging technique you wish to use (mean, weighted mean, median, or mode) by clicking on the appropriate button.
3. Benchmark Jobs for Family A. Select benchmark jobs by entering job numbers in the orange cells. To do this, look to the right side of the spreadsheet. There you will find a table listing the 24 job numbers. The names are only visible if you determined in P1 that the jobs were to be in this family. To the right of the job number is the point value you derived in P1 and the number of positions queried from the ten companies you selected. Further right are various tallies from the survey. When selecting benchmark jobs in the real world, you consider many factors. Here your information is very limited, so just base your choice solely on maximum spread job values. Pick one at the high end and one at the low, and two in the middle.
The graph at the bottom of the Excel sheet shows the dollar range of pay on the left edge and the job value gradations along the bottom. Within this field are yellow triangles, each representing one of the jobs in this job family. As you select jobs to be benchmark jobs, the corresponding triangles will turn solid orange.
[Note: You may spot on the graph job triangles that you would like to use as benchmarks. (Those closest to forming a line will give you the best line.) By placing your cursor over a triangle, the coordinates will appear (job value first, pay survey second). Use this information to find the corresponding job number to enter into the benchmark list.]
Once you have entered all four benchmark jobs, the graph below will plot the corresponding pay line. Note and discuss the "fit" of market prices with internal evaluations based on the value of "r2." If r-square is "unacceptable," it is because points lie too far apart to form a dependable line. You will need to switch out one or more benchmark jobs in order to get an acceptable r-square. If that doesn't work, you may need to revisit some decisions from P1 to change job importance to more closely fit the market. Any r2 >.7 is acceptable, but it may affect the percentage by which you lead or lag. Note: A fit too close to 1.0 suggests that the organization is not distinct in its evaluation of jobs.
[Note: The amounts paid for jobs may seem too high or too low for the real world. Do not let this distract you from the principles being applied. Pay your employees in proportion to the market as given.]
4. Adjusted Pay Line for Family A. You will make up to three pay line adjustments in the purple cells of the spreadsheet. If you chose to Match competitors' pay, you will have two purple cells. Otherwise enter a number for the strength of your strategy (lead or lag). Numbers will need to be larger, if benchmark points tend to fall far from the pay line. (This is one reason a good value for r is required.) In the next purple cell enter a positive number to tilt the line for a hierarchical slope, or a negative number to tilt for an egalitarian slope. Explain your slope and strategy choices in your report as they relate to your values. In the last purple cell adjust for time lag by a percent reasonable for half a year.
5. Pay Grades for Family A. On P2-Structure A you will find the payline you constructed on the previous sheet, but this time it has triangles on it representing all jobs in Family A. Enter the number of pay grades you choose for this family, based on your strategy and any natural groupings of the jobs. To make this decision, note first the number of grades allowed for your slope strategy. Hierarchical slopes need many grades (promotions), while egalitarian slopes require relatively few. Then look to the Graph at the right. Are there any natural clusters of jobs? Chose a number of grades that allows you to begin grades between major clusters of jobs. Once you have entered a number, the table below your entry will show that number of grades along with the beginning and ending values for this family. You determine the intermediary grade breaks by entering the beginning of all other grades in the blue cells. Make those entries based on where breaks would seem appropriate in your graph. The ending value for the previous grade will automatically be entered when you enter the beginning of the next. When all grade values are complete, the graph to the right will show "stair steps" for the levels. Look at the table below the graph. It now shows into which grade each job in this family falls. Are the grade breaks in the best places? Consider if there are any jobs in the family that should be separated by a grade break in order to create promotion (and therefore pay differences) between the jobs. There should be a promotion between secretary and executive secretary and between Assembler I and Assembly Supervisor, for example. You can move the lines between grades by changing the beginning values of the grades. Explain what you have done in your report, but you need not support this step with reference to your values, because you were simply carrying out the policy already decided in the step above.
[Note: It is not wise to create a pay structure table that contains specific job value information. Employees who see it may wish to argue over the relative value of jobs placed in the same grade, or over how close their job is to the next pay level. Even so, you may need to know the value of some jobs in order to correctly place a grade beginning between them. For this reason the values are hidden in the pay structure sheet. To reveal the values, place your curser just to the left of the top left corner of the pay table, beside where it says, "Job Titles." Click and drag down to the bottom of the table along that edge, and job values will appear beside each job in white letters on a blue background.]
6. Pay Ranges for Family A. To the right of the entries for beginning and ending of pay grades is an indication of the middle of the range. This dollar figure was determined by how you divided the grades along the payline. You can now create a range for each grade by entering the spread you want each grade to have. The spread is the percent above and below the midpoint that you want each pay range to include. Just to get things started, you might enter 20 in each spread. Once entered, the cells to the right reflect the minimum and maximum pay a person might receive for jobs in that grade. The ranges will also be reflected in the graph and table. Together these illustrate your pay structure. Adjust the spreads to fit your strategy-Hierarchical ranges overlap slightly; Egalitarian ranges overlap at least to the midpoint of all contiguous ranges; and neutral can be either. Any place where consecutive ranges do not overlap are an indication that employees cannot be promoted from the lower to the higher grade. This is rarely necessary, and always requires a strategy explanation. In any case, explain all decisions, but you need not necessarily tie them to specific values.
Repeat the process for Family B.
Index of values: End the detailed report with a review of each value and which policies, if any, support each value, just as was assigned in P1, but this time indexing only the policies developed in P2.
Projected Financial Impact: Your budget sheet has not yet changed, because your policies have not yet affected employee pay. Type a short paragraph noting how you might expect it to change once the above improvements have been implemented.
Executive Summary: Type an executive summary based on policies developed in P2. The format requirements are the same as for P1, but with information pertaining to policies and use of values for P2 only. Name your values again and note if any have changed from your P1 submission. Which values are used most and least? Again, place this summary at the beginning of your report. Because it covers six policies, it may be as long as two pages, but again the Detailed Report will begin on a new page.
Organize the Final Copy of Your P2 Report
Organize your report as follows:
1. Executive Summary
2. Detailed Report
a. MAV
b. Policies for Family A
i. Pay Strategy
ii. Companies Surveyed
iii. Benchmark Jobs
iv. Adjusted Pay Line
v. Pay Grades
vi. Pay Ranges
c. Policies for Family B
e. Index of values
f. Projected Financial Impact
3. Attach your Excel file in the same delivery
Phase 3-Employee contribution and administrative policy
Two categories of compensation policy are addressed in Phase 3: Employee contribution policy draws on values to establish what individuals should be paid, and how they can progress through the promotion and reward system. Administrative policy draws on values to determine how budgets and payroll will come together, and how policy will be communicated to employees. In Phase 3 you discover how well existing pay fits into the scheme you designed in P1 & P2.
As with P2, P3 will have a detailed report. It will have all the same major elements, and it will be subject to all quality and presentation standards required for P1 P2. Again, it will begin with your values section.
These can all remain identical to P2, or you may change any part of them to improve your report. You do not have to go back and revise any policies in the P1 or P2 written report to match Excel changes. (This is where it's nice not to be in the real world.)
There are six steps to P3, involving four policies to decide, three of which must be defended with your values. The table on Sheet P3-Merit Grid is where you will develop a forced distribution of pay for your employees. With a grid you are able to force the distribution across quartiles as well as performance, and you can determine the cost of pay adjustments before knowing individual appraisals.
1. Policy 1: Quartile Distribution. Do you want quartiles to be skewed toward the top, bottom, or a neutral bell? you must decide what percent of employees you wish to see distributed in each quartile, and enter the percentages in the salmon-colored cells to the left. This will determine how employees will be distributed across all ranges for both families. Your decisions will be reflected in the bar graph to the right. If you expect high turnover or rapid growth, then you can expect the bottom of a range to have more employees than the top (skewed toward the bottom, Quartiles 1&2 have a higher percent than Q 3&4, compa-ratio below 1.0). If you are stable and into quality control, then you will have low turnover and employees will tend to cluster toward the top of the range (skewed toward the top, Quartiles 3&4 have a higher percent than Q 1&2, compa-ratio above 1.0). If neither of these conditions is a factor for your ACME, then place dispersion around the midpoint (symmetrical dispersion, Q 2&3 are the same and higher than Q 1&2, compa-ratio at 1.0). Which, if either, would best represent your stated values? If you choose to have a skewed bell curve, you must have a practical reason, if not a values-driven reason. If you choose a curve other than a bell, you must have a values-driven reason.
2. Policy 2: Forced Distribution by Performance. Do you want them skewed toward more people receiving rewards, less people receiving rewards, or a neutral bell? Your decisions will be reflected in the graph below. Skewing the performance rankings toward "Far Exceed" would indicate a highly stable workforce. Many employees will receive "high" rewards, but that means stringent limits on how high "high" is. Differences in rewards from high to low will not be much. Skewing toward "Not Met" would reflect stiff internal competition and more expected turnover. Which, if either, would best represent your stated values? If you choose to have a skewed bell curve, you must have a practical reason, if not a values-driven reason. If you choose a curve other than a bell, you must have a values-driven reason.
The white cells in the Merit Grid now show the percent of employees who will fall into each of the 20 subdivisions of quartile and merit distribution. The relation of these cells is represented in the 3-dimentional graph in the lower right.
3. Merit Grid. The boss has indicated to the right of the merit grid what percent he is willing to commit to pay increases and the percent of error he will allow. This is not a policy but a carrying out of your Policies 1 and 2 in line with budget constraints. You can achieve this before knowing individual appraisals by entering percentages in the yellow cells just over each white cell in the Merit Grid. For example, if you have 4.00% in a white cell, and the constraint is 4% overall, entering 5.00% in the corresponding yellow cell will use up .04X.05=.20% of the 4.00%, leaving 3.80% yet to be distributed. Enter and change percents until you come within the allowable error amount. Remember that the advantage of quartile differences is to allow lower quartile employees a higher pay increase than upper quartile employees can be afforded, and the purpose of performance-tracking is to motivate higher performers with more reward than low performers. No tie to values is necessary for this distribution. It just needs to work!
4. Adjustments to Pay. Again, on Sheet "P3-Adjusted base" you are simply implementing your policies. You will see a list of 24 employee pays for various positions. These represent individual employees, not jobs, so a job title may be listed more than once. Some jobs with which you are familiar may not be listed at all. The top table shows the job title of each position and the family and grade you assigned to that job in P2. Two columns over the table indicates into which quartile this particular person's pay falls. If the pay falls outside of the range you designed, the corresponding cell will indicate OVER or UNDER. Some employees must have their pay adjusted (in the yellow cells) so that their pays fall within their designated ranges. Do this by entering positive or negative adjustments into the yellow cells. Tip: The two graphs to the right of the table replicate the two pay graphs for Family A and B, but this time they include the positions listed in your table. As you adjust pay for each position, the corresponding points will move into range on the graph. Also, by comparing the job's family and grade in the top table to the corresponding minimum and maximum pay in the smaller tables below, you can get a better idea of how much adjustment you will have to make in each pay.
This will take several operations to get the pay adjusted the way you want it. Each adjustment will be a finer tuning than the one before. First adjust pay so that all pay falls within a quartile in the "Final quartile" column.
In the table, the next column indicates the rating assigned by the employee's immediate supervisor, and after that the merit percent from your merit grid for that employee and then the dollar conversion followed by the final pay. The final quartile is listed at the end of the table. This is the one that must ultimately have no over or under quartile pay. Adjust the yellow cells again, even if it means that some pay is under in the initial adjusted quartile. No, you are not through yet.
Below the table are two graphs. The top one is a duplicate of the one you designed for quartile distribution in the previous spreadsheet. The one below it is the current final distribution across quartiles. Try to make them similar by adjusting the yellow cells again. To do this, note which bar in the lower graph is too long and which is too short. Then go back up to the column that indicates into which quartile each pay finally falls. Adjust the yellow cell for one of the pays in a too-long quartile so that it moves into a too-short quartile. You will probably not be able to get them perfect, but you can get them to where the correct bars are long and short in relation to each other and within 10% of identical.
Now comes the last adjustment, I promise! Look at the compa ratios to the left of the bar graphs. They correspond to the Family and grade table to the left. To the right of each grade are spaces for the minimum and maximum compa ratio possible for that grade, based on your design in P2. For example, if in Family B, Grade 2 you set the spread at 20%; then the maximum compa ratio is 1.2 (1+20%) and minimum is 0.8 (1-20%). If no positions are listed above for that grade, the spaces are blank. With this many employees, all grades would have jobs, but in this exercise we are only concerned about the 24 jobs above. Final compa ratios for each grade and for whole families (bold type) appear next. If a grade compa ratio is outside the maximum limits, the numbers will appear green-circled (below range) or red-circled (above the range). If your family or total compa ratio are too far off from your desired ratio (determined on your Merit Grid sheet and displayed over your desired bar graph) they will appear green-circled or red-circled. If they are colored, look within the family to see which grade is off the most. Then go find a job within that family and grade that is off the most, and adjust it. The Family graphs to the right may be useful to tell how much you can lower or raise pay for a given position without changing the quartile (which may change your Final graph too much). Individual and grade compa ratios do not have to be as close as family ratios, so long as pay falls within the range.
The last column in the top table shows job values. By placing your curser over individual points on the A and B graphs, the coordinates will appear, and you can use the values to identify which employees need pay adjustments. There is no need to describe all this adjustment in your final report, and you need not write your policy to reflect all these adjustments. You just need to experience how the adjustments affect HR numbers. Your work will show in good graphs and tables.
It probably occurred to you as you were adjusting the tables, graphs and numbers that in the real world you would be drastically changing some people's pay. You did it here simply to get numbers that look good. Now you need to write a policy for actually doing it, considering how it will affect employees.
5. Policy 3. Adjustment and communication policy. Now that you know by how much, how should pay be adjusted over time, what options should be offered to employees, and how will you inform them? The numbers in Spreadsheet "P3-Adjusted base" appear as if they happen all at once. The president has in fact ordered that all adjustments occur within 2 years at the most. Write a policy for how you will do this, and how you will inform and assist your employees with adjusting to the required change. If you must cut major portions from an employee's pay, how will you minimize loss of employees and loss of morale? Is there an ethical issue to address? If you must make large pay increases to bring some pay into line, how will you assure that the organization is not just giving more money to an employee who is doing work too poor for someone else to hire at good pay? Relate this decision and communication process to your stated values. This time you are more likely to have to make decisions that pit one value against another, or flat out contradict a value. If that happens, note which value or values have been sacrificed and why-because of the CEO's requirements, values trade-offs, etc.
6. Appendix: Notice to Employees. In 250 words or less type a general announcement to employees explaining how pay will be adjusted over the next two years and any services HR will offer in the process.
Index of values: As with Phase 1 & II, end the detailed report with a review of each value and which policies, if any, supported each value.
Projected Financial Impact: The Budget sheet in Phase 3 has been altered from Phase 2 to show changes from implementing your policies. Type a short paragraph noting how it is different. Which figures are different? What has made them different?
Executive Summary: Type an executive summary with the same format as for Phase 1 and 2 to place at the beginning of your report, but the content is for Phase 3.
Organize the Final Copy of Your Report
Organize your report as follows:
1. Executive Summary
2. Detailed Report
a. Mission and Analysis
b. Values
c. Policies
i. Forced Distribution
ii. Merit Grid
iii. Adjustments to Pay
iv. Communication Policy
d. Index of values
e. Projected Financial Impact
3. Attach your Excel file in the same delivery