1 / 29

Excel for IEX WFM Professionals

Excel for IEX WFM Professionals. James Lee Customer Experience Manager Electric Insurance Company www.ElectricInsurance.com. Electric Insurance Company. National Insurance Provider for GE Employees and Other Discerning Customers GE consolidated affiliate company established in 1966

duard
Download Presentation

Excel for IEX WFM Professionals

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel for IEX WFM Professionals James Lee Customer Experience Manager Electric Insurance Company www.ElectricInsurance.com

  2. Electric Insurance Company National Insurance Provider for GE Employees and Other Discerning Customers • GE consolidated affiliate company established in 1966 • GE’s primary commercial insurer in US, Canada & EU • Offering personal auto, home, condo, renters & umbrella • Specialty coverage for flood and recreational vehicles • Insuring 250+ GE officers and over 60,000 GE employees • 50,000 non-GE customers came from referrals • Customer and third party recognition: • Named Customer Service Department of the year by American Business Awards • 99% of customers who filed a claim renewed • 82% Net Promoter Score (NPS)

  3. Compliance in an Ever-Changing Environment Electric Insurance Company History • Located in Beverly, Massachusetts • 450 employees (~190 licensed) • One million contacts annually • Mature customer-facing organization • Focused on delivering exceptional experiences and insurance solutions • IEX WFM introduced in 1999, upgraded to 4.5 Q1 2011 • NICE applications introduced 2009 • Recording & Storage • Interaction Analytics • Quality Management • Workforce Management • Feedback

  4. About James @jronaldlee Customer Experience Manager for Electric Insurance Company® Previous IEX WFM customer at SkyMall and AAA Focus is not WFM Focus is on data that allows us to make better business decisions on behalf of our customers

  5. Why Excel for IEX WFM? Ideas that can be repurposed for other data management platforms for non-ODBC environments • Retrieve data in non-IEX WFM environments • Present information using Excel’s data management tools • Pivot tables • Charts and graphs • Summarize as you want to • Blend IEX WFM data with other information

  6. In One Hour… Learn more on-line when you see this icon:

  7. Report = Output Report output is an end product. It does not readily combine with other reports. Information is presented as text instead of data. Reports and charts are the end product of data analysis.

  8. Our Goal

  9. Classic Table Formatting • Start in A1 • Row one is for headings • Bold text for row one • Never skip rows • Avoid formulas in very large tables • Formatting is for reports, not tables • Consider other tools when • More than 75 columns of data • More than 200,000 rows of data

  10. Dynamic Named Range Size in columns Start Here Move left or right this many rows Move up or down this many columns Size in rows Named ranges make for easy data synopsis=OFFSET('Extracted Data'!$A$1,0,0,COUNTA('Extracted Data'!$A:$A),27)

  11. General Best Practices One worksheet for all related raw data Don’t mix raw data with results, reports, or graphs No blank cells or rows Avoid merged cells Avoid formatting cells as text Never store numbers as text Use true dates and times Naming conventions Classic table formatting Discrete v. Continuous data

  12. True Dates and Times • Know the difference • Accumulation of time • Time of day • Use formatting to display times and dates as desired • Convert Excel’s default time to understand the accumulation of time • To Excel, times and dates are integers

  13. Time as an Integer • Excel converts the time of day into a portion of a number. 12:00 noon is half way through the day, so it is 0.5 (cell G2) • Excel will also convert this into a date… the 0th of January, 1900 • When you enter “12:00” into Excel, it is converted • Before the colon becomes hours • After the colon becomes minutes • The data displays as you typed it, but the formula bar displays Excel’s interpretation of the data as “12:00:00 PM”

  14. Time Implications • Numbers as time and dates combined • 0 = 0:00:00 on January 0, 1900 • 1.5 = 12:00:00 PM January 1, 1900 • 40,664.60417 = 2:30:00 PM on May 1, 2011 • Adding time in Excel results in calendar dates progressing • e.g., 12:30 + 12:30 = 1:00 (1:00 AM the next day) • You can control output to show 25 hours by formatting:Format > Number > Custom > [h]:mm:ss • OR convert time to minutes by multiplying by 1,440 (number of minutes in a day

  15. Applied Techniques Schedule Adherence

  16. The intermediary ?

  17. Pulling the Report Remember to save your report Report By: MU Recommend: Relative dates, -15 to -1 Include: Details by day, agent ID: Agent Data: Exclude days where data was not received Variance: Do not include Sort Options: Be consistent!

  18. Select, Copy

  19. Paste, Click.

  20. Resulting Table

  21. The Loop The Adherence Report follows an outline format: • Report Name • Agent • Date • Date • Summary • Agent • Date • Date • Summary • Agent • Date • Date…

  22. One level Deeper • Unstaffed Scheduled • Unstaffed Actual • Unstaffed In • Unstaffed Out • Total Scheduled • Total Actual • Total In • Total Out These 18 data points give repeat in detectable cycles and can be used to determine discrete data and complete our table • Agent • Date • Aux Scheduled • Aux Actual • Aux In • Aux Out • Logon Scheduled • Logon Actual • Logon In • Logon Out

  23. Visual Basic When you press the button, this code loops through up to 64,000 rows and extracts the 18 data points we want, analyzes and calculates the discrete data, and adds it to the data table. Updated spreadsheet available online

  24. Pivot the Data • Pivot Tables and Pivot Charts are important enough that Microsoft made them the first item on the “Insert” ribbon. • The built in summary provided by Excel gives you powerful and flexible data summary and presentation tools.

  25. Calculated Fields • Pivot tables can display data that does not exist in your table with calculated fields. • Pivot Table Tools > Options > Fields, Items, & Sets > Calculated Field • By calculating adherence in the pivot table, we avoid what bad practice?

  26. From Table to Chart

  27. Is This Useful?

  28. Thank you for attending! Questions?

  29. Resources • Links to resources and a copy of the spreadsheet used today can be found at http://jronaldlee.com/iex-excel/ • Click the contact link to drop me an email • Recommended Web forums: • Ozgrid: http://www.ozgrid.com/forum/ • Mr. Excel: http://www.mrexcel.com/ • Author John Walkenbach: • Excel Bible • Power Programming with VBA

More Related