Excel Extension Tutorial: Creating a Transition Matrix in Human Resource Management

Learn how to create a transition matrix in Excel for HR management. Follow step-by-step instructions to analyze job title transitions from 2017 to 2018 with sample data. Explore pivot tables to visualize employee movements within the organization.

• Excel
• Transition Matrix
• HR Management
• Pivot Tables
• Data Analysis

maxim Follow

Uploaded on Aug 03, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. Chapter 6 Excel Extension: Now You Try! Creating a Transition Matrix Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

2. Background In this Excel Extension tutorial, you will learn how create a transition matrix with raw values and a transition matrix with proportions. For the purposes of this exercise, imagine that there are four job titles of interest: Project Manager, Project Consultant, Technical Consultant, and Technical Assistant. In addition, we are interested in those who were not in the organization in 2017 but who held one of these jobs in 2018, as well as those who held one of these jobs in 2017 but who was no longer in the organization as of 2018. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

3. Step 1 Open the Excel workbook titled Chapter 6 - Excel Extension.xlsx . Click on the sheet called Tutorial. You will use the data contained in this sheet to learn how to create a transition matrix. Note that the sheet includes three fields/columns: EmployeeID (unique employee identifier) 2017 (job title in 2017) 2018 (job title in 2018) Note that there are 79 employees in this sample. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

4. Step 2 Select the cells in the three fields. Do not, however, select cells below the last row of data. In other words, select the cells in columns A, B, and C and rows 1 80. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

5. Step 3 Click on the tab called Insert. Click on the Tables button. 2 Select PivotTable. 3 Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

6. Step 4 A window called Create PivotTable appears. The Table/Range: field is already filled based on the previously selected cells. Click on the bubble next to New Worksheet. Click OK. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

7. Step 5 A blank PivotTable appears in a new sheet. Note that the field names EmployeeID, 2017, and 2018 appear under Choose fields to add to report:, which is part of the PivotTable Fields window. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

8. Step 6 Drag the EmployeeID field name to the Values box (from the list of field names that appears under Choose fields to add to report:). Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

9. Step 7 Drag the 2017 field name to the Rows box (from the list of field names that appears under Choose fields to add to report:). Drag the 2018 field name to the Columns box (from the list of field names that appears under Choose fields to add to report:). Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

10. Step 8 Click the X in the upper right of the PivotTable Fields window to close the box. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

11. Step 9 The resulting PivotTable is a transition matrix that contains the raw number of individuals who worked in a given job in 2018 based on which job they worked in 2017. Specifically, the Row Labels correspond to 2017, and the Column Labels correspond to 2018. Next, we will edit the transition matrix to make it easier to interpret. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

12. Step 10 To make the PivotTable more intuitive to use, we will order the job name rows such that the jobs are ranked in hierarchical order, with Not in Organization at the bottom. To do this, right click (or hold CTRL and click if you have a Mac) on a cell that appears under Row Labels, select Move, and select Move [ row label name ] to [desired location] (e.g., Move Not in Organization to End). Repeat until the rows are in the following order: Project Manager, Project Consultant, Technical Consultant, Technical Assistant, and Not in Organization. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

13. Step 11 The Row Labels should be ordered as depicted in the image. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

14. Step 12 Repeat the process from Step 10, except in this case right click the cell corresponding to each column name, select Move, and select Move [ column label name ] to [desired location] (e.g., Move Not in Organization to End). Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

15. Step 13 The Column Labels should be ordered as depicted in the image. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

16. Step 14 The transition matrix in the form of a PivotTable is now in a more interpretable format. Again, this transition matrix contains the raw numbers representing how employees transitioned through the company from 2017 to 2018. For example, as shown in cell B6, 2 individuals who were Project Consultants in 2017 transitioned to Project Managers by 2018. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

17. Step 15 To create a transition matrix containing proportions, right clickon any cell containing a value in the PivotTable, and select Value Field Settings . Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

18. Step 16 A window called Value Field Settings opens. Click on the Show Values As tab. Click on the dropdown menu. Select % of Row Total from the dropdown menu after. Click the Number Format button. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

19. Step 17 A window called Format Cells opens. Select Custom in the Category: dropdown menu. In the field below Type:, enter the following format: .00. Click OK. 1 Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

20. Step 18 Click OK in the Value Field Settings window. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

21. Step 19 Now the transition matrix contains proportions as opposed to raw counts. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

22. Step 20 Select the cells that contain the row and column names, as well as the proportions. Click on the Copy button in the Home tab, or press CTRL+C (or Command+C if you are using a Mac). Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

23. Step 21 Paste the copied cells from the previous step below the PivotTable by clicking the Paste button in the Home tab, or by pressing CTRL+V (or Command+V if you are using a Mac). Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

24. Step 22 Although not necessary, consider deleting the .00 values in the new transition matrix to make it easier to read. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

25. Step 23 Click on the header above the first column (A) to highlight the entire column. Right click on the column header and select Insert to insert a blank column to the left of the first column. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

26. Step 24 In the empty row directly above the recently pasted transition matrix, enter the year 2017 in the first cell above the Project Managerlabel, which corresponds to 2018 job titles. In the cell directly to the left of the column containing the Project Manager label, enter the year 2017, as this column references 2017 job titles. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

27. Step 25 Select the cells directly above the 2018 job titles, and click the Merge button. Select the cells directly to the left of the 2017 job titles, and click the Merge button. The transition matrix with proportions is now formatted and complete. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

28. Questions You just learned how to create a transition matrix in Excel. Now, it s time to apply your new skills to a different dataset, which appears in the sheet called Practice in the Excel workbook; the job titles contained within the Practice sheet are (in descending order in terms of job level): Floor Manager, Sales Associate, Cashier, and Product Runner. Once you have created a transition matrix with raw values and a transition matrix with proportions, respond to the following questions. 1. Which job(s) showed the most turnover (left the organization) from 2017 to 2018? 2. What other trends did you notice? 3. In terms of workforce planning, what will you recommend to the organization based on your interpretations of the transition matrices? Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.

29. Chapter 6 Excel Extension: Now You Try! Creating a Transition Matrix Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.