An Alternative HRIS Strategy Using Excel and VBA

An Alternative HRIS Strategy, Using Excel and VBA HR Value, LLC Delivering exceptional results with the click of a mouse Roy Farrell Managing Director Roy. Farrell@4 hrv. com www. 4 hrv. com (614) 266 -5926 Svet Batoev Senior Developer HR Value, LLC 1

Discussion Outline An Alternative HRIS Strategy Technology is an often overlooked barrier to being a successful business partner. After a discussion the business case opportunities and challenges, we will introduce you to a powerful solution sitting on your computer. This could be part of an HRIS strategy that uses the best of your technology: – use your HRIS and other systems for their core functions – use the powerful functionality of Excel and VBA to give you all the good stuff We will provide an introduction to VBA. We will show specific examples of code that can automate some important functions. While we only have an hour and this is by no means a complete discussion of VBA, it will present a starting point to solutions you may not have know existed. 2

HR Value What We Do We help companies with custom solutions in two key areas: • Business Analytics and Information management • Automation of time intensive processes We take the most complex process and give clients “click of the mouse” ease, all at a cost that is a fraction of system modifications or additional software. The client can easily access data from multiple systems, HRIS, payroll or outsourced solutions, and refresh that data at any time. They can access even the most complex analysis, reports and charts. All with tools that are sitting- largely underutilized - on their desktops. We add value, and save our clients time and money This is not a sales pitch. We want to introduce you to an Excel you may not know exists, by using some very specific examples. We provide help in these areas, but everything discussed here is available on your desktop. 3

Administrative and compliance activities dominates HRIS systems and dollars Ed Lawler penned an article that points to the root cause of the problem. Dr. Lawler asks us to “give HR more respect” but recognizes that “the group with the expertise to help the organization better manage and utilize people—the human-resources department—often is too mired in administrative tasks to tackle higher-value work. ” Edward E. Lawler III, The HR Department: Give It More Respect, MIT Sloan Management Review, Posted March 10, 2008 http: //sloanreview. mit. edu/wsj/insight/hr/2008/03/10/ 4

How do you break the cycle? The good Dr. Lawler never quite explains how the HR staff “spend less time processing benefits requests and more time being the expert resource on the state of the organization's work force and its ability to perform” but his perception is spot on – HR is caught in a death spiral of compliance and administration. The question remains – WHY? Edward E. Lawler III, The HR Department: Give It More Respect, MIT Sloan Management Review, Posted March 10, 2008 http: //sloanreview. mit. edu/wsj/insight/hr/2008/03/10/ 5

HR Value Point of View • Core HR technology solutions are built to address the very important compliance and administration tasks of their profession, largely driven by Payroll, Benefits, and record keeping. It is very difficult to develop robust planning tools or administer multiple approaches with a payroll system • Even when using 'The Best’ software, you are tied to the capabilities and functionality of your HRIS system that often delivers a rigid and plain vanilla process. This is not what leadership wants. • There is no competitive advantage in doing things just like the competition. • Add-on packages claim to solve your problems, but you soon discover you simply trade one set of limitations for another, or create information silos that restrict meaningful analysis - all at a considerable expense • And there is little interest in spending more money on expensive systems or system modifications that cannot demonstrate a positive ROI. 6

Technology must be used to help give a competitive advantage from Talent C-Suite credibility means: • mastery of planning techniques • the ability to model pay and performance options quickly and clearly for a busy, non-specialist audience You need to provide the frameworks and tools to guide talent decisions made inside and outside of the HR function. You need to differentiate talent pools and talent based on the strategic context and potential return It’s simply said, but not easy in execution. Technology promises to deliver solutions, but often falls short. You simply cannot rely on a Payroll system to provide these tools. 7

Every Business process is large and complex Compensation Decision Making Process Total target compensation levels that consider market rates and internal company value. Market Pay Ongoing program management, performance evaluation, and planning. Policies and process to manage program operations according to the design. Performance & Evaluation Governance Market-based objectives, set to enable value-adding organization performance. Salary and incentive ratios that reflect the business role and competitive environment. Pay Mix Incentive for top performers and plan entry points that differentiate for achievement. Business Roles Objectives &Goals Upside & Threshold Measures & Priorities Mechanics Performance metrics and weights that clearly communicate business priorities. Technology that link pay to performance across all attainment levels.

Barriers to effective Compensation Decision Making Technology Challenges: - The administrative and compliance nature of HRIS - The skill level required to get at the data - Multiple data silos - System changes are slow and expensive - Many desired system changes are not possible Market Pay Performance & Evaluation Governance Business Roles Financial Measures Challenges: - Financial results are continually changing - Reorganizations - Lose connection between rewards budgets and financials - Transfers and proration - Data in multiple systems - Sales data in multiple systems - Commissions in multiple systems Pay Mix Upside & Threshold Governance Challenges: Measures & Objectives - Complex regulations Priorities &Goals - Changing complex regulations - Compensation data in multiple silos Performance Management Challenges: - Gender and ethnicity reviews - Data in Multiple systems - Real timing challenges - Different schedules Mechanics - Compensation Committee schedules 9

Yet technology remains the Holy Grail In your quest for administrative efficiencies you wind up with dedicated applications that don’t talk to each, and so many tasks for which there is no specialized application. The unfortunate reality is that your core business solutions are built to address specific needs. These solutions create information silos that restrict meaningful analysis without considerable manual effort. These tasks need to be accomplished, and your day becomes consumed with paperwork or repetitive spreadsheets. This leads to higher costs and lower productivity. 10

Technology is complicated by design Data Sources Legacy Enterprise Data Warehouse ETL Architecture Staging Area Extract, Operational Data Store Transform Sales Purchase Orders Inventory Flat File and Load Data Warehouse Virtual Data Massive Parallel Internal External Virtual Reporting/ Analytics Virtual Dashboard Virtual Applications Services – Web/Data Messaging Relational XML Real Time Analytics PORTAL

Technology costs are often most frightening of all. It is not just the acquisition cost: • new investments in infrastructure • annual maintenance • periodic upgrades • training • additional IT staff required with most software • the need to hire “power users” to your team to work the tool The five and ten year total costs can be staggering, and it is often difficult to demonstrate a positive return on investment. Few enterprise wide solutions cost less than a million dollars and many end up costing ten million or more. 12

Now, imagine… software solutions without limits at a fraction of the cost of alternatives Spreadsheets provide all the flexibility and you need to administer even the You can automate them. most complex plans, but they can quickly become unwieldy. Let us introduce to the Excel you never knew existed 13

It doesn’t have to be this complicated Data Sources Legacy Enterprise Data Warehouse ETL Architecture Staging Area Extract, Operational Data Store Transform Sales Purchase Orders Inventory Flat File and Load Data Warehouse Virtual Data Massive Parallel Internal External Virtual Reporting/ Analytics Virtual Dashboard Virtual Applications Services – Web/Data Messaging Relational XML Real Time Analytics PORTAL

It doesn’t have to be this complicated Data Sources Legacy Sales Purchase Orders Inventory Flat File Relational XML Internal Excel and other Microsoft Office products utilize a programming language called Visual Basic for Applications or VBA. Excel and VBA provide the capability to rapidly integrate information from different applications, without a applications, without costly infrastructure. Excel and VBA integration features come pre-built, so using Excel as an information integration platform avoids the time and cost of developing applications from Unfortunately, many scratch that perform the Excel's features are same tasks. VBA allows construction of sophisticatedofbusiness tools largelydata unknown • a pre/post information processor forstill business • automation of time intensive tasks or dismissed prematurely 15

Excel comes standard with tools that can provide sophisticated task support Excel to the rescue! Microsoft Excel fills the niche of being able to perform complex business analysis tasks or automate business tasks rapidly and cost effectively Excel is one of the most powerful, flexible packages on the market today Excel has tremendous capabilities that can help streamline your business tasks Unfortunately, many of Excel's features are still largely unknown or dismissed prematurely Information analysis, organization and processing Report and form generation Creating and combining planning tools Import and combine data from multiple databases Integrate information from different applications Charting and Modeling Budgeting and Planning “What if” scenarios Accruals And anything else you can imagine 16

Think beyond your spreadsheets! - Using Excel with VBA The bottom line is that you already have powerful software and a network available for employees to use, so the investment in software and computer infrastructure has already been made. Excel and VBA gives you an alternative: 1. The ability to create “turnkey” processes, such as • Automate sales commissions and sales reporting • Salary and Incentive administration • Automate proxy compensation tables • Eliminate paper executive award agreements, with electronic signature • Create Total Rewards statements 2. Automate time intensive tasks in a spreadsheet The possibilities are limited only by your imagination. 17

Develop powerful and sophisticated “apps” that Macros simply cannot Macros aren’t enough. Many Excel ‘Power Users’ build a Macro library to save time. However, the Macro recorder really doesn’t work well: • Macros can’t use variables – it hard codes objects (range, cell, charts, etc. ) • Cannot use non active sheets when selecting One significant drawback is that macros do not allow ‘looping’ of procedures. Looping VBA code allows you break a large file into multiple files for distribution to salespeople or managers, or allows you interrogate or combine multiple files. This simply cannot be done with macros. VBA is fairly easy to use, and you can build custom VBA objects. 18

Excel and VBA 1. Excel and other Microsoft Office products use a programming language called Visual Basic for Applications. VBA comes standard with Excel. There are some differences between versions of Excel – 2003, 2007, 2010, and these need to be understood if using different versions 2. VBA allows a business professional using Excel to: a. Create custom, distributable procedure libraries of worksheet functions that eliminate large, hard to type formulas on the worksheet. b. Automate repetitive tasks such as charts, reports or data analysis c. Create business applications with powerful graphical user interfaces d. Communicate with and control databases e. Apply the exact business logic behind any process 3. Excel and VBA gives your team Standardized Tools a. verified, standardized computer tools that are compatible b. standardized work environments, methodologies and business tools c. eliminates “reinventing the wheel” every time a new project starts d. a cost effective, flexible, rapid analysis environment 4. With VBA and Excel, the business professional has a flexible tool that can be rapidly configured to solve sophisticated business tasks. 19

Develop powerful and sophisticated business tools You can create: 1. Graphic User “click of the mouse” interface 2. Flawless Workflow 3. “Foolproof” process 4. Tools maintained by non-technical users 20

1. GUI Design – Intuitive Graphic Interfaces 1. Excel has built-in capabilities, which allow elaborate graphical user interfaces (GUI’s) to be developed. 2. The Excel controls used to develop these interfaces are easy to employ, basically just draw them and set a couple of options. 3. Because GUI’s are so easy to construct in Excel, Excel can be utilized very effectively and cheaply as a pre/post information processor for business data 4. And most important, they can be used to build a flawless and foolproof workflow. 21

2. Workflow managed by Menu functions, tailored to your requirements– no compromises “Click of the Mouse” ease Tool Creation is fully Automated Consolidate data – no “Cut & Paste” Import new data from multiple sources Reports dynamically updated Unlimited Planning and Budgeting functionality 22

3. Workflow creates a “foolproof” process Users cannot change tools: Separate User and Admin menus Editing functions disabled Core data cannot be changed Calculations cannot be altered Documents cannot be altered Database updated automatically Reports automatically updated 23

4. “Non-Technical” users can update tables, without IT or “power user” intervention Administrator can change all plan parameters without IT support or programming 24

VBA allows construction of sophisticated business tools in Excel Need to understand: The basic principles of the VBA language. The concept of a VBA project. The concept of a VBA procedure. How to manage and use VBA modules that store VBA code The operation of the VBA Editor The purpose of presenting these concepts is not to turn business professionals into full time software developers. The opposite is the case. The main reason for a business professional to use Excel and its capabilities are because they're pre-built. 25

VBA language is Object Oriented VBA is an Object oriented language, different from a procedural language. “Kick the Ball” in English translates to Ball. Kick in VBA You can then add parameters. “Kick the Soccer ball hard, high, and to the left” Becomes Ball (“Soccer”). Kick Direction: =Left, Force: =Hard, Elevation: =High VBA Component Analogous to Comments Object Noun Collection Plural Noun Usually defines which object: Worksheets(1) Method Verb Object. Method (Ball. Kick) Parameter Adverb Lists parameters after the method. Separate the parameter from its value with : = (Ball. Kick. Direction: =Left) Property Adjective You can set a property. Activecell. height = 10 or you can 26 query the value of a property x = activecell. height

A VBA project is a collection of Excel objects, references, modules and userforms • A VBA project can be created in different Microsoft Office products. • When a new workbook is created in Excel, a new VBA project is automatically created and associated with that workbook file • A workbook can contain only 1 VBA project • You add the elements to a VBA project when you need them. • Whatever Microsoft application a VBA project is constructed in (i. e. Excel, MS Word, MS Power. Point…etc), that application is used to launch the project. When a workbook file is saved, its associated project is automatically saved. When a workbook file is closed, its associated project is closed. 27

VBA Procedure Need to understand: The basic principles of the VBA language. The concept of a VBA project. The concept of a VBA procedure. How to manage and use VBA modules that store VBA code The basic operation of the VBA Editor A procedure is a named sequence of statements that are executed as a unit. VBA procedures are used to perform tasks such as communicating with databases, calculating equations…etc. A VBA procedure consists of a procedure declaration statement and ending statement with statements in between. The statements between a procedure’s declaration and ending statement perform the procedure's task. When a procedure is executed, its statements are executed in a top-down line by line fashion performing operations, like reading a book page. 28

VBA Modules Standard modules are the workhorse of a VBA project. They store and organize procedures that are used to perform tasks like controlling Excel, communicating with databases, calculating equations…etc. We will show specific examples of code to demonstrate modules. If you want to receive the excel files with these examples, please complete the feedback form and indicate so. Include your name and email address. Or send an email directly to royfarrell@4 hrv. com 29

VBA Editor VBA projects are created, edited and debugged in the VBA Editor. In order to create VBA projects, you need a basic understanding of the VBA Editor. The VBA Editor is activated from the Excel ribbon and selecting Developer then Visual Basic. You could also press Alt + F 11. You can toggle between Excel and VBA by pressing Alt + F 11 Note that the Developer tab is not automatically installed in Excel: 1. 2. 3. 4. Click the File tab Click Options Click Customize Ribbon Under Customize the Ribbon and under Main Tab, select the Developer Check box 30

VBA Editor window contains many windows that can be displayed at once. The Project window lists the currently open VBA projects and their elements. You navigate through projects in the Editor by using the Project window. The Properties window displays the properties of the currently active object. Here, Module 1 is selected and the name property of that object appears in the window. A Code window displays a module in the Editor allowing you to write and edit its code. Multiple Code windows may be displayed and arranged in the VBA Editor at once. 31

VBA Editor can be customized You can change the appearance of the VBA Editor by clicking on View and selecting the items you want displayed. This will save you time when performing different tasks. 32

VBA comes standard with a set of built-in debugging tools A designer uses these tools in a detective role, stepping through a procedure’s code line by line executing them, viewing their results and detecting mistakes along the way. Language errors result when statements are constructed incorrectly: • Improper use of parenthesis • Forgetting to type an End or Next keyword • Misspelling a keyword VBA will detect these errors before the code is executed. Run-time errors occur when a statement attempts an impossible operation. Some common run time errors include identifying objects that do not exist or dividing in an expression by 0. VBA detects these errors during code execution. Logical errors occur when an executing procedure does not do what it is supposed to do. The syntax is correct and performs operations, just not the expected ones. Errors of this nature cannot be easily detected by just executing the code. 33

VBA comes with tools to speed your work – Intelli. Sense 1. Typed 'sh' with the intention to refer to Sheet 1 object. Then clicked 'Complete Word' button. 2. This shows what Intelli. Sense gave as a result. I double-click Sheet 1 3. After Sheet 1 was in place I typed '. ' (dot) to see/select a property/method of the Sheet 1 object. I got the list of property/method immediately after typing the dot. 34

VBA Code Examples VBA allows to build powerful and sophisticated applications that rival stand alone applications costing hundreds of thousands or millions of dollars. We will show you a stand alone salary planning tool, and show you some of the VBA code that makes all of it possible. These functions could be part of a library that could be included in spreadsheets for efficiencies, or part of a turnkey solution. • Disable Cut/Copy/Paste • Add a sort button • Import file • Loop • Color rows If you want to receive the excel files with these examples, please complete the feedback form and indicate so. Be sure to include your name and email address. Or send an email directly to royfarrell@4 hrv. com 35

Salary Tool - Automated with VBA Disable Cut/Copy/Paste Import files Multiple files can be imported with a click of the mouse to refresh the data any time To prevent managers from ‘messing up’ the Data, we disable certain dangerous Functionality. They ‘click the mouse’ for a flawless workflow Looping is not available in the Macro Recorder, But is necessary for repetitive functions, such as: - Creating Multiple planning tools - Parsing Data - Combining completed tools Add sort buttons To prevent managers from ‘messing up’ the data, we disable the sort function, but give them ‘click of the mouse’ sorts Color Rows To help managers navigate rows of Data, we color coded the two types of 36 employees – Salaried and Operations

Too good to be true? Questions and Discussion HR Value, LLC Delivering exceptional results with the click of a mouse Roy Farrell Roy. Farrell@4 hrv. com www. 4 hrv. com (614) 266 -5926 37

The Quest for HR Value Pay for Performance is the Holy Grail of the Human Resources profession. Companies want to ensure that pay is aligned with the talent that produces organizational success and adds real value. Talent is a company’s most important asset, and it can also be the most expensive asset – representing up to 70% of a company’s operating expenses. We must get this right. There are many challenges and books devoted to the subject. We will focus on an often overlooked barrier – the role of technology. The reality is that you must be able to administer any plan you design. There has to be a seamless link your strategy for talent and your administrative process. 38