Excel Dashboards — A How-to for Compensation Professionals
A Skill-Building Seminar with Excel Expert Dianne Auld
Do you work with a tremendous amount of compensation data, making it difficult to perform analysis and draw conclusions and insights? Do you feel you overload team members and managers with cumbersome, confusing and intimidating Excel spreadsheets? Leveraging Excel dashboards is a powerful way to consolidate vast amounts of data, findings and analysis into a condensed, executive summary view that will facilitate sound business decisions.
In this course, learn how to create an Excel dashboard that will illustrate how pay structures compare competitively to the market. Begin with an introduction to the concept of Excel dashboards, why they are useful and the basics needed to get started. Then, get hands-on experience in Excel as you follow along with the instructor to create the interactive executive dashboard. Hands-on tactical learning, relevant compensation scenarios, job aids and sample spreadsheets ensure you can use what you learn immediately at the office.
Create a streamlined Excel dashboard using a real-world scenario that illustrates how pay structures compare competitively to the market.
Transform vast amounts of data and display analysis and key findings in a condensed space where it is easy to access, interact with and understand.
Learn how to gain critical information and insights from dashboards in order to make quick and effective business decisions.
Covers what is a dashboard; why it is useful; basics about what you need to do to get started
Creating the Framework and Customizing the Quick Access Toolbar
Customize the framework; set the print area and page layout; customize the quick access toolbar to include useful tools for creating dashboards
Dashboards Functionality and Data Overview
Illustrates sample dashboard’s layout and functionality (multiple areas and regions, various pay scales, ability to age market data to specific dates such as salary survey dates); describes data included in this dashboard and shows where the source data is for populating the dashboard
Name areas and regions to use with data validation; create interactive fields with data validation; customize the data validation using the INDIRECT and SUBSTITUTE formulas
Bring in pay scales using the OFFSET and MATCH formula; hide any error references using the IFERROR formulas
Age market data for survey date and current date using the DATEDIF formula; extend the formula to find the aging factor; age company data using the aging factor; bring in market data using the AVERAGEIFS formula with named ranges
Compa Ratios and Conditional Formatting
Calculate comparative ratio pay scale midpoint to the market median; add conditional formatting icons to indicate if the pay scale is above the market, close to the market, or below the market
Create and customize compa ratio charts
Camera Tool and Protecting the Dashboard
Move data into dashboard using the camera tool; hide development sheets; set protection preferences
About Excel Dashboards
Excel dashboard functionality has many benefits. Watch this introduction to the course and learn how dashboards can boost your efficiencies and enable more sound business decisions. Be sure your volume is turned on to hear the video.
This seminar is designed for compensation/HR professionals such as compensation analysts, managers, directors or anyone who works with large amounts of data and wants to create Excel Dashboards. This course is for those with intermediate Excel skills. Microsoft Excel 2007 is used in the demonstrations. Please note that Excel functionality varies by software version. Some formulas used in this course are: IF Formulas, Data Validation and Protection Functions, ARRAY Formulas, LOOKUP and Reference Formulas.
Dianne Auld, GRP, CCP, owns a consulting practice, Auld Compensation Consulting, in Cape Town, South Africa. She consults a wide range of organizations across Africa and the Middle East in all areas of total rewards. She has developed and taught courses in Africa, the Middle East, Europe, and the United States.
Dianne is also a faculty member at WorldatWork and a reviewer for the WorldatWork Journal. Well respected and known as an expert in the compensation field and Excel, Dianne’s skills and expertise are in high demand. In fact, her Excel Dashboards session at the WorldatWork Total Rewards 2012 Conference and Exhibition was “sold out” with a waiting list. Diane has partnered with WorldatWork to develop several new educational products to assist compensation professionals with their day-to-day tasks in Excel, including “Pay Structures – Develop a Framework from Start to Finish”, “Excel Skills for Compensation Professionals”, “Excel Tips for Compensation Professionals” and “Excel Dashboards: A How-to for Compensation Professionals”. These Excel courses include sleek visual and audio demonstrations of tasks compensation professionals need to perform in their job function.
"After WorldatWork's Excel Dashboards class, I used what I learned to produce a dashboard for one of our product divisions that eliminated the need for us to use a consultant and saved us $100K. I was given an excellence award AND saved the company a ton of money! It was such a success that I've been asked to create dashboards for the other divisions."
— Marion Zion Compensation Analyst, St. Jude Medical