Manpower Planning using Ms. Excel Power Query

In this project, i tried to using a tools in Microsoft Excel, Power Query to help me process my data. I got some data from kaggle by Muhammad Imran Zaman “Employees Evaluation for Promotion” (https://www.kaggle.com/datasets/muhammadimran112233/employees-evaluation-for-promotion). Then i also add other data that’s not available from there such as employees name, gender, and active month including start month and end month in 2023. From the data that i have, i created manpower planning that is often used in HR field for some purposes, such as recruitment and selection programs, reduce the labour cost so there won’t be overstaffing, manpower planning also can help the company or the organization in the stability of a concern, and many more.

For helping HR team, i created this project so the manager, or maybe the Head of Director (HOD), or someone who have access to manage the manpower, can easily informed us the need of their department, or their region, in a year. This data can be shared through local server or maybe sharepoint to help them fulfill they’re data at the same time.

For access this project, you can visit this link:

https://drive.google.com/drive/folders/13Jdqp_-WL4m9FX9FAbme1w8q7e0x-mrr?usp=drive_link

First, the raw that I called Manpower Planning_Before, contains some information such as employee ID, employee name, department, region, etc, as shown in the picture.

In column L (Start) and column M (End), to inform us about the employee’s active month in year 2023. For example employee number 1, Bernice Matthams start from 1 and end until 12, it means Bernice is an active employee from January, the first month in the year, until December. For other case, employee number 3, Amie Mathews start from 1 and end until 6, it means Amie is only active as an employee from January until June 2023. Column N until Y contain formulas to describe the employee’s active month per month, 1 is January until 12 is December. With the help of the first row (cell N1-Y1), for example the formula that I used for column N is ‘=IF(AND([@Start]>=N$1,[@End]<=N$1),1,0)’, and for column M-Y just change cell N$1 to M$1-Y$1. From that formula we’ll get 1 point if the condition is true, if the condition is false so we’ll get 0 point. Again for example, Bernice active from 1–12, so from January-December Bernice has 1 point in each month, and for Amie, because she’s only active until June, so only column 1–6 has 1 point, in column 7–12 or it means July-December is 0 because she’s not an employee anymore. And for column Z (Validity), if the employee is active for a year or maybe only for several months, they’re considered valid as 1 headcount or 1 employee. This column also contains a formula ‘=IF(AND([@Start]>=1,[@End]<=12),1,0)’.

Then we want separate this data into several files based on their region then based on department. So their manager/HOD/branch manager can review only their part. Here are the steps to separate them:

1. Create some folders, for this project i created 2 folders, Region and Department.
2. This data will be reviewed by 2 reviewer, so for the first reviewr, i separated this data based on region. In folder Region, i created some excel files again and named it based on their region, for example Region 1–5, Region 6–10, Region 11–15, etc.
3. Open the region file that has been created, for example, i used file Region 26–30. Then we have to open the Power Query Editor, you can access this tools from tab Data >> Get Data >> Launch Power Query Editor or you can use shortcut Alt+A+PN+L.

4. Once the Power Query Editor opened, open the data source by clicking the menu New Source >> Excel Workbook then choose the file that contains the data that will be separated. Then a navigator windows will be shown in the display, choose the data that will be proceed, make sure the data is a table.

5. The display when the data is ready is shown in the picture below, because i only want to show some data based on some region, in column Region click the dropdown box, then choose the item that will be proceed, for example, i choose the region 26–30, and other region will not be show in the data. Then sorting the data ascending or descending if needed.

6. After the data is clean from other region that not related, if you choose to not showing some column, you can click in the column that will be removed, then choose menu Remove Columns >> Remove Columns (if you want to remove ONLY the column that you choose) or Remove Other Columns (if you want to remove other columns EXCEPT the column that you choose) >> choose the columns that will be removed.

7. If the data is clean and already separated, click menu Close & Load in the top left corner of the screen. Then the data will be proceed in the new sheet. The data is shown in the picture below.

8. After the data is separated based on their region, I added some column such as column January (1) until December (12) and column validity like in the first data that we used. I also added summary about how many employees that will be active in every month, for this I’m using a formula ‘=SUBTOTAL(9,mpp[1])’, mpp is the name of the table that I used and from that formula I got summary of active employees in January. For summary of employees active in February until December, I just change the mpp[1] to mpp[2–12]. The reason why I added this column again instead of taking the data directly from Manpower Planning_Before is because there is a possibility that the first reviewer want to change the month active of some employees, so the data will be different from the first data that we have and the data is automatically updated, if we taking directly the data from the first source, column 1–12 (Jan-Dec) will not be updated. Also, if the first reviewer want to add some headcount/manpower, I also added some blank rows so they can fill it if they needed. And I add some conditional formatting, for column Start if someone isn’t being an active employee since January (1) so the cell will turning into red, and for column End when someone is being unactive employee before December (12) the cell will turning into red too. For Column 1-Validity when someone doesn’t get 1 point (0 point), the cell also will turning into red. I can automatically activating the cell into red when the condition is true by using Conditional Formatting. For column Start i’m using condition if ‘[@Start]<>1’ (Point in cell Start DOESN’T EQUAL to 1), for column End i’m using condition if ‘[@End]<12’ (Point in cell End LESS THAN 12), and for columns 1-Validity i’m using condition if ‘[@1-Validity]=0’ (Point in cell 1-Validity EQUAL to 1). The final table that will be distributed is shown in a picture below.

9. Repeat step 3–8 for other region until all the data is ready for distribute to the first reviewer. Then we have some files that has been separated.

10. For the second reviewer, i must separated again the file based on their department. But before I separated it, I must combine all the data that has been reviewed by the first manager. The step is more and less same with step 3–8, but we change the source from Manpower Planning_Before to Folder Region that contain all the files based on their region. In the folder Department i created some files, for example Sales & Marketing, Technology, HR, etc.

11. Open the department file that has been created, for example, i used file Sales & Marketing. Then we have to open the Power Query Editor, you can access this tools from tab Data >> Get Data >> Launch Power Query Editor or you can use shortcut Alt+A+PN+L.

12. Once the Power Query Editor opened, open the data source by clicking the menu New Source >> Folder then choose the folder that contains the data region. Then windows will be opened and showing all the data/table in the folder that we choose, than click Transform Data.

13. The display after we transform all the data in folder Region is shown in the picture below.

14. As in picture above, we can see column Content and a box with two arrows, click on that box, and new windows will appear as seen in the picture below. Click on the name of the table that we’ll proceed, then click OK.

15. Then repeat step 5–8, but because we need the data is separated by their department, I filtered the data in column Department. Then the final table based on their department is ready for distribute to the second reviewer. Then we have some files that has been separated.

16. After all the data has been separated and has been reviewed by all the reviewer, then I combine again all the data become one file. The steps is like when I combine the data based on their region became department, so we just combine all the data that we have after the second reviewer, in Power Query Editor. And the final table that has been updated will become our Manpower Planning_After. The final data is shown in the picture below.