Transcript 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
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)
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
About James
• 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
@jronaldlee
Why Excel for IEX WFM?
• 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
Ideas that can be repurposed for other data
management platforms for non-ODBC environments
In One Hour…
We Won’t
We WILL
• Master pivot tables
or pivot charts
• Turn you into an
Excel Guru
• Learn Visual Basic
for Applications
(VBA)
• Learn about data v.
reports
• Discuss Excel best
practices
• Show an in depth
example of
changing a report
into data
Learn more on-line when you see this icon:
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.
Our Goal
100.00%
98.00%
96.00%
94.00%
92.00%
90.00%
88.00%
86.00%
84.00%
82.00%
80.00%
Blue
Red
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
Dynamic Named Range
• Named ranges make for easy data synopsis Size in columns
=OFFSET('Extracted
Data'!$A$1,0,0,COUNTA('Extracted Data'!$A:$A),27)
Start Here
Move left or right
this many rows
Move up or down
this many columns
Size in rows
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
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
Time as an Integer
• 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”
 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
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
Applied Techniques
SCHEDULE ADHERENCE
The intermediary
100.00%
98.00%
96.00%
94.00%
92.00%
90.00%
88.00%
86.00%
84.00%
82.00%
80.00%
Blue
Red
Pulling the 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!
Remember to save your report
Select, Copy
Paste, Click.
Resulting Table
The Loop
The Adherence Report follows
an outline format:
• Report Name
 Agent
 Date
 Date
 Summary
 Agent
 Date
 Date
 Summary
 Agent
 Date
 Date…
One level Deeper
• Agent
 Date
 Aux Scheduled
 Unstaffed Scheduled
 Aux Actual
 Unstaffed Actual
 Aux In
 Unstaffed In
 Aux Out
 Unstaffed Out
 Logon Scheduled
 Total Scheduled
 Logon Actual
 Total Actual
 Logon In
 Total In
 Logon Out
 Total Out
These 18 data points give repeat in detectable cycles and can
be used to determine discrete data and complete our table
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
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.
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?
From Table to Chart
100.00%
98.00%
96.00%
94.00%
92.00%
90.00%
Blue
88.00%
Red
86.00%
84.00%
82.00%
80.00%
2010-49
2010-50
2010-51
2010-52
2010-53
2011-02
2011-03
2011-04
2011-05
2011-06
2011-07
2011-08
2011-09
2011-10
2011-11
2011-12
2011-13
2011-14
2011-15
2011-16
Sum of LogonAdh Team
Year Week
Blue
Red
Grand Total
2010-49
93.53% 87.15%
90.62%
2010-50
91.39% 90.00%
90.74%
2010-51
93.58% 87.17%
90.33%
2010-52
91.57% 91.36%
91.46%
2010-53
92.43% 87.92%
90.09%
2011-02
89.59% 88.33%
88.95%
2011-03
92.99% 91.07%
91.98%
2011-04
91.54% 92.23%
91.90%
2011-05
89.61% 93.01%
91.36%
2011-06
93.56% 91.15%
92.39%
2011-07
95.56% 93.68%
94.62%
2011-08
93.96% 92.50%
93.26%
2011-09
92.59% 92.72%
92.66%
2011-10
93.72% 92.19%
92.98%
2011-11
95.56% 93.68%
94.62%
2011-12
93.96% 92.50%
93.26%
2011-13
92.59% 92.72%
92.66%
2011-14
92.11% 93.35%
92.70%
2011-15
94.25% 92.50%
93.37%
2011-16
95.73% 96.49%
96.13%
Grand Total
92.96% 91.60%
92.29%
Is This Useful?
100.00%
21 Hopkins, Zak
95.00%
26 Bottari, Edna
30 Perry, Matthew
90.00%
85.00%
31 Durant, Sean
35 Honan, Robbie
37 Woodworth, Amanda
80.00%
38 Druhan, Kathy
42 Calcagno, Michael
75.00%
43 Keefe, Mark
44 Fleming, Jason
70.00%
47 Lee, Michael
48 Moore, John
65.00%
60.00%
55.00%
51 Taylor, Tifany
52 Franklin, Mary Lou
54 Smith, Justin
56 Charleston, Kristen
57 Delman, Brittney
50.00%
59 Crandall, Morgan
60 Hinds, Stevie
Thank you for attending!
QUESTIONS?
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