Analyzing & Automating Rewards Data with Excel

Spend less time formatting data and more time analyzing and evaluating it. Join Excel rock star Dianne Auld as she demonstrates several tips for routine data formatting and calculations in this two-hour virtual classroom. Learn how to create pivot tables and charts to efficiently summarize large data sets, and add interactive tools to allow for more robust analysis. Then watch Dianne create a macro to automate repetitive tasks, allowing more time to focus on utilizing data rather than creating it. After this class, you will know how to:

• Create interactive pivot tables quickly and easily
• Write and run a macro to automate repetitive tasks

Extra value! Take home session files and instructions on how to replicate the functions demonstrated.

What You Will Learn:

Section 1 — Use Pivot Tables and Charts to Analyze Data and Create Interactive Dashboards

• Model the impact of different base pay increases per level in the organization on average comparative ratios to market and on cost of increase.
  • Use pivot tables to count, sum or average data
  • Use the report filter to create one pivot table per manager or division
  • Use the pivot table to analyze salary or age or years of service distribution
  • Create pivot charts
  • Use slicers to make them interactive for very effective dashboards

Section 2 — Write Macros to Automate Frequent Tasks

• Modeling the impact of different eligibility levels, share allocation numbers and stock price on cost of stock granted, cost of stock granted as a percent of base pay and shares granted as a percentage of shares outstanding.
  • Record a macro
  • Save and open macro files
  • Run a macro by using a control key or linking it to a shape

All demonstrated using one example.