In this section my goal is to provide a good insight regarding what I can do using Excel in a data analyst role at a large organization. I do hope that for the potential hiring manager this section carries sufficient amount of information to form a general idea about my skills level in utilizing Excel tackling with complex data problems.
EXCEL PROJECTS DELIVERED:
BASF - BIT aplication (Excel component). This tool was designed using input from SAP staged and stored in Access database files and text files and fed into the Excel Power Pivots and Power BI model built on DAX functions for sales team to track their performance.
Wells Fargo/Trust Services - System testing scheduling tool for the testing team to track weekly UAT and SIT testing activities.
MHFA (Minnesota Housing Finance Agency) - Developed custom tool in Excel working with Access to gather incoming data sets from the field and feed housing financing database to track the program status.
Prime Therapeutics - Maintained and improved the existing tool tracking activities for quality control and management for the online prescription delivery. It was interacting with the monthly quality reporting system via VBA based structure.
UHG/Optum (Care Solutions) - Developed a reporting dashboard for the senior leadership team. This tool was interacting with Access database files via ODBC and Excel query functions via pivots and VBA routines to update the dashboard.
GMAC (currently dba Ally Financial) - Developed pivot based reporting system which was producing 300 pages long performance reports tracking the activites in asset based investements in the mortgage investment area.
NCR (National Car Rental) - Created a tool in support of Access based system feeding the OROS ABC (activity based accounting) software to monitor and analyze the financial data and tying it directly to individual car rental activities using unit based metrics.
Excel Skills Rating:
My Opinion on Standardized Excel Skills Tests:
Standardized tests are designed to measure one's ability to use the
built-in Excel functions that are provided through the menu options
on the ribbon. On the other hand, there is not a single test that
can even remotely measure one's ability to be fully productive in
Excel by creating VBA procedures, user defined functions, complex
and lengthy formulas and creative solutions.
If you need the candidate to bring more comprehensive solutions to complex data issues in Excel try to gauge his or her capabilities by providing a real life problem as a project and ask to resolve this in a
real life time frame whether it is couple hours, a day or a week
instead of putting that person through a standardized, timed test
module where test taking abilities are mostly tested instead of true
skills and capabilities.
I was tested on my Excel skills numerous times on standardized
Excel skills test. On the average my scores hover around the
neighborhood of 90% at mid to advance level tests. I’d place
myself at 9 out of 10 for my Excel skill and that is very satisfactory
for the most part. I understand that by using this test they gauge candidates and narrow down their options as anyone can claim
expertise in Excel and even if they sincerely think they know a lot
about it without even realizing what they are not even aware of.
Most of the client needs fall into the category that can be handled
without leveraging the true power of Excel. In this case it makes
sense that they use standardized tests. On the other hand, it
certainly does not measure the more advanced skills, abilities
and creativity of the power user or developer in order to create highly
complex spreadsheets nor it shows how the user tackles a
certain problem for data manipulation as there are literally
numerous ways to resolve a problem. For more complex tasks
such as creating a pricing decision tool in already complex
setting like a trade room, or complex rate sheet development or a
scorecard development working with large backend databasess
typical Excel skills simply do not cut.
There are two dimensions to Excel, one is that most people are
familiar with and that is Excel’s user interface and spreadsheet
along with some functions and formulas utilizing the formula line,
pivot tables and MS Query and for a lot of job descriptions this
might constitute advance skills and this indeed would be
classified as such for the average user. Even Advance Excel
classes that they cover in technical colleges do not go any further
The other dimension of Excel is wide open and if one does a little
bit internet research it is possible to see the possibilities and
capabilities are literally endless there. This dimension is in the
space of Visual Basic for Applications programming and using
Excel only as a platform by creating UDF modules and routines.
There is simply no standardized Excel test to measure the kinds
of capabilities that I am referring to here. The best way to gauge
would be to put the person on a task or send him or her a complex
problem with sample data set and the requirements and see how
he or she works towards a resolution.
Sample Excel Problems that I worked with utilizing VBA: