Strategic Modelling for Human Resource Management - Case Study Example

 Strategic Modeling Of Human Resource Content CHAPTER NAME PAGE Number I. Introduction 3 II. Dependency Diagram 4 a. The Dependency Diagram: An Overview 5 III. How to use HRM Simplified 2008: A guide IV. The Main areas of Work in the HRM Simplified 6 V. The Macro 12 VI. The charts 13 VII. VBA Programming 15 Introduction One of the largest tasks for any company or organization no matter how big or how small is to stabilize the recruitment situation. By ‘stabilizing the recruitment’ we mean to occupy, retain, promote and keep the labor motivated until departure of the workforce. The workforce plays a major role in any organization, as a matter of fact most of the organizations now a days have realized the crucial importance of Human resources as a major part of the ‘company’s growth’. For those of the companies or organizations that still doubt the importance of human resources in an organization. Here we have a distinguished software created on MS Excel 2007, the latest of its kind. We like to call this software “HRM Simplified 2008, v 1.2”. This software is bound to clear every company’s doubt that does not realize the importance of human resources. Not only will the ‘HRM Simplified 2008’ help in clearing the doubts of non believers but for the companies that consider human resources as their greatest assets will have a lot to gain from this software and the ‘HRM Simplified 2008’ will not only save the customer tons of money spent on expensive software but this will also help the clients in terms of saving the most valuable entity on the planet… time. Dependency Diagram Here in the dependency diagram we would analyze the most important determinants of our prime objective of this report that is to generate the expected profits using the available and pre - provided variable factors. This diagram, as the name suggests, will also be able to tell us which variable or constant is dependent on which other variable or constant. To do this we will have to divide all the available variables and constants into 3 sub – divisions which are (This sub – division is just to make the understandability easier): 1. Inputs 2. Intermediates. 3. Outputs. Fig I.1: The Dependency Diagram The Dependency diagram: An Overview The dependency diagram clearly states all of our constants and variables that are to be used in the current project and will definitely help in creating a Microsoft excel sheet which will allow any company to upgrade their own Human Resource System. Not only has the dependency diagram helped us to clearly identify the factors that are about to affect our calculations but the dependency diagram has also helped us to scatter these factors and variables into three major variations which are the inputs, intermediates and outputs. The dependency diagram has provided us with other valuable information also, such as the need of a difference between “Old staff” or the old seniors, old juniors and old partners, and the “new staff” or the new junior and the new senior. The use of the term ‘Old’ has arisen due to the clause that the new employees might not be able to contribute for contracts. How to Use the HRM Simplified 2008 v 1.2 The use of the excel software is very easy. All the cells with grey background and orange numbers are the cells which will accept the inputs and based on these inputs the macro will calculate the profit. Fig V.1: How to The pink cells with red text are the cells assigned for output functions and are formulated. Even if the formulas on these cells are accidently deleted; these can be recovered every time the user uses the macros. The use of the software is very simple, all the user needs to do is to enter the new number of staff and the HRM simplified will calculate the profits for the next 10 years. The design and Main areas of work: HRM Simplified 2008 The chief operations page or the home page of the HRM Simplified 2008 looks like the following on an MS Excel Sheet: Fig III.1 I have created a software that can not only give out the results by just the help of the usual constants but the all new HRM Simplified 2008 lets the user of the software edit the following other functions also: 1. Editing the salary of the Junior Consultants. 2. Editing the salary of the Senior Consultants. 3. Editing the salary of the Partners. 4. Editing the winning contracts ratios of Senior Consultants and Partners. 5. Editing the promotion rates (in percent age) for both the Junior Consultants as well as senior consultants. 6. The HRM Simplified 2008 also lets the user edit the Average revenue per contract which is now set at 500,000 pounds by default. The amazing new HRM Simplified 2008’s main functionality also includes the inputs of various calculative variables such as: 1. The number of new contracts. 2. The number of new Junior Consultants. 3. The number of new Senior Consultants. With the help of the above mentioned inputs and other vital constants and variables provided in the report will help calculate the estimated profits of the organization by if & what if analysis techniques, which will basically include changing of the variables in the main calculative sheet. How does it all work? The working of the HRM Simplified 2008 is quite simple and just about anybody can use it, even if the user has very little knowledge about MS Windows, office or computers as a matter of fact (of course the user must know how to start the system and open the Software  ). Basically the HRM Simplified 2008 has two sheets in which all the operations are taken care of. The names of the 2 sheets are: 1. Framework: The Framework Sheet is the chief calculative sheet which includes all the variables, constants and formulas. Even the slightest change in the sheet could lead to disaster of the whole application and ruin the calculations; this is due to the fact that hundreds of cells have been interlinked in the sheet to produce optimum and accurate figures and results. This is the reason why this sheet (Framework) will be hidden and assigned as read only so that everyone may not have the access to modify the sheet. If the buyer of the application doesn’t want the sheet to be hidden, there is a great security feature in MS Excel 2007 which will enable the user to assign a read - only password in the sheet which will enable the password bearer to modify according to his own needs. But as far as I am concerned, I am damn sure of the fact that the sheet won’t require any extra editing because I have already provided the option to edit even the most secure and edit-proof constants that are to be used as the bases of calculations in the project. The design of the framework sheet is as below… Fig: III.2 This is just a basic design that has been created in excel (although only half of it), since all of it cannot be copied I will just provide you with the data for about 4 years (current year + 3 years) This Year Year 1 Year 2 Year 3 Staff Brought Forward Current Junior 8 9 10 12 Current Senior 5 5 6 8 Current Partner 3 4 5 6 Staff - 20% Junior 2 2 2 2 Senior 1 1 1 2 Staff Undertaking One Contract Junior 3 3 3 3 Senior 0.6 0.6 0.6 0.6 Partner 0.5 0.5 0.5 0.5 Staff Bidding Contract per year/per person Senior 0.3 0.3 0.3 0.3 Partner 0.5 0.5 0.5 0.5 Bidding contract for next year Senior 1.5 1.3 1.8 2.4 Partner 1.5 1.6 1.9 2.3 Total 3.0 2.9 3.7 4.7 Contracts Forecasting 3.0 2.9 3.7 4.7 Contracts Junior can handle 2.7 3.0 3.3 4.0 Contracts Senior can handle 8.3 8.3 10.6 13.1 contracts Partner can handle 6.0 8.0 9.5 11.4 Contract capacity 2 3 3 4 contracts pending 1.0 -0.1 0.4 0.7 Revenue per contract £500,000 £500,000 £500,000 £500,000 Staff needed for contract capacity Junior 6.0 9.0 9.9 12.0 Senior 1.2 1.8 2.0 2.4 Partner 1.0 1.5 1.7 2.0 Staff left Junior 2.0 0.0 0.0 0.0 Senior 3.8 3.2 4.4 5.4 Partner 2.0 2.5 3.1 3.7 Recruitment New Junior 0 1 2 3 New Senior 0 1 2 3 Turnover Rate 20% 20% 20% 20% Promotion Rate 15% 15% 15% 15% Staff Left( including promotion) Junior 6 6 6 8 Senior 5 3 4 5 Total Partner 3 3 4 5 Total Junior 6 7 8 11 Total Senior 5 4 6 8 Salary per person Junior £30,000 £30,000 £30,000 £30,000 Senior £60,000 £60,000 £60,000 £60,000 Partner £100,000 £100,000 £100,000 £100,000 Total Salary Cost £840,000 £780,500 £1,000,700 £1,265,765 Total Revenue £1,000,000 £1,500,000 £1,650,000 £2,000,000 Total Profit £160,000 £719,500 £649,300 £734,235 Fig III.3 These are all the variables and constants that are used to develop the application on MS Excel 2007, once the sheet is activated it is very easy to figure out how the calculations had been made, the calculations were quite simple and were made solely based on the figures provided by the company. Although the user should know that their was one minor calculation that was done based on the my assumptions that is the number of employees to be added each year (except promotion and dismissal, only the no. of employees recruited), it is + 1 employee each year, and is mentioned under the recruitment section of the sheet clearly marked brownish red. 2. MAIN: The main sheet is the outlook of these calculations and the outputs are based on the change in the main sheet framework, the main sheet looks something like this: Fig III.4 The pink cells with red colored text within are the output cells. The grey cells with orange ext from within are the input cells. The chart in the sheet is based on the change of figures in the output fields of profit. All the profits will be copied from the ‘Framework’ sheet into the output boxes once the required figures have been entered and the macro has been pressed, which will allow the profits field to be filled and the chart to be displayed correctly according to that. Macro We have used a single macro function for all of the functions of the sheet to make it easier. This single macro will calculate all the profits and other outputs as well as it will display and modify the chart in the sheet. The macro, formulas and data will be error proof and free of corrections. That is, the macro function cannot be edited since it is in a read-only sheet. The most major advantage of this macro is that even though the formulas in the profit calculation field are wiped out, by using this macro the formulas are placed in the file again. The macro in the excel sheet looks like this: Fig. IV.1 The Chart Fig. V.1 The chart in the excel sheet is a simple line chart which has been modified to create a 3-D view and colors that will just make the customers look directly its way because of the fact that the chart has got contrast and clashing colors. Not only are the looks of the chart special but other factors such as the display of value at the point clicked are also impressive, this is totally amazing the graph will display the value at the point of year that it is clicked on. Secondly, the graph only works when the macro is used instead of direct formula entry. Fig. V.2 The group work chart is totally lame and not at all enthusiastic; this should have been improved by the group’s appointed design professionals. This could have been made much better is a line chart would have been used, secondly this chart does not have the capabilities to show that exact values unlike my chart. The VBA Programming used in the chief Macro The primary programming routine used in the chief macro is simple and it runs as follows: Sub Macro4() ' ' Macro4 Macro ' This is the chief macro ' ' Keyboard Shortcut: Ctrl+Shift+J ' ActiveCell.FormulaR1C1 = "=Framework!R[50]C[-5]" Range("G16").Select ActiveCell.FormulaR1C1 = "=Framework!R[49]C[-4]" Range("G17").Select ActiveCell.FormulaR1C1 = "=Framework!R[48]C[-3]" Range("G18").Select ActiveCell.FormulaR1C1 = "=Framework!R[47]C[-2]" Range("G19").Select ActiveCell.FormulaR1C1 = "=Framework!R[46]C[-1]" Range("G20").Select ActiveCell.FormulaR1C1 = "=Framework!R[45]C" Range("G21").Select ActiveCell.FormulaR1C1 = "=Framework!R[44]C[1]" Range("G22").Select ActiveCell.FormulaR1C1 = "=Framework!R[43]C[2]" Range("G23").Select ActiveCell.FormulaR1C1 = "=Framework!R[42]C[3]" Range("G24").Select ActiveCell.FormulaR1C1 = "=Framework!R[41]C[5]" Range("G24").Select ActiveCell.FormulaR1C1 = "=Framework!R[41]C[4]" Range("G25").Select ActiveCell.FormulaR1C1 = "=Framework!R[40]C[5]" Range("G26").Select End Sub Read More