Advanced Excel, Data Modeling, VBA Programming, & Automation

At Azura Consulting, we provide the knowledge and experience to help companies and individuals get the most out of the software they already use every singe day.  We leverage the capabilities and combinations of advanced Excel, Power Query, Power Pivot, Power BI, and VBA programming help our clients to work smarter, not harder. As companies and markets evolve and complexity increases, it is vital that time, energy, and resources are not spent on tasks that don’t add value to the organization. Our services also help our clients avoid purchasing costly single-task software solutions, and allows them to create custom developed tools that are fit for purpose, but also flexible enough to allow for modifications as situations change.

 

 


Q: What can be done better using the tools a client already has access to?

  1. Understanding and employing best practices within Excel and it’s incorporated ‘powertools’, namely PowerQuery, PowerPivot, and VBA.
  2. Working ‘smarter’, not ‘harder’ by leveraging the existing capabilities of Excel. (i.e. Advanced Functions, Tables, Data Models, PivotTables, PowerPivot, PowerQuery, and so on…)
  3. Creating standardized templates, models, and processes. Avoid ‘recreating the wheel’ with every ad-hoc analysis and request. An enormous amount of time, cost, and frustration is easily avoided by developing more efficient ways of doing common and repetitive tasks.
  4. Designing processes in a way that leads to automation of repetitive or time consuming tasks. This is especially important if input or typographical errors can be introduced by manual inputs and manipulation.
  5. Introduce automation to reporting – auto-generate PowerPoint reports from Excel, auto-generate emails and PDF attachments from Excel, and the list goes on…

Q: What are some of the most common topics and themes experienced when working with new clients that do not have advanced technical skills?

  1. Introducing and furthering the understanding of the true capabilities of Excel.
  2. Understanding and teaching the critical difference between ‘data’ and ‘information’.
  3. Identifying what skills to acquire, and what “skills” to avoid. This is highly variable, depending largely on an individual’s role and deliverables.
  4. Picking the right tool: Learning how to determine what is the best tool or technique to use.
  5. Developing the awareness and insight to know when and how to apply the right tool in order to add value.

Q: What types of common business activities can Azura Consulting help us improve and optimize?

  1. Categorization of spend – to model and align spend reporting with a standardized taxonomy for informed and proactive operations management, supplier relationship management, and/or market approach.
  2. Development of category and/or departmental profiles – a complete view of the “How much, with who, for what, when, where, and why’s” of company spend that goes above and beyond operational accounting reports.
  3. Procurement-relevant analytics and analysis modeling – Vendor Pareto, ABC-Inventory Analysis, Krajlic Matrix, Risk & Opportunity Assessments, etc.
  4. Baselining and quantification of potential opportunities – measuring targeted and forecasted financial impact to the organization. Carefully prepared in a manner to allow for post-initiative reporting on the level of success achieved.
  5. Development of business cases for strategic allocation of resources i.e. standardized Cost/Benefit financial modeling, breakeven modeling and forecasting.
  6. Development of dynamic cost models for labour, equipment, and projects. Introducing the concept of dynamic ‘what-if’ modeling allows analysts to run robust side-by-side scenarios to produce comparisons that quantify the financial impact of decisions made.
  7. Automation of analytics, template creation, data compiling, reporting, forecasting, data modeling, document creation.  The list of tasks that can be automated is almost endless. To restate a point made at the top of this page: it is vital that time, energy, and resources are not spend on tasks that don’t add value to the organization.

All of these activities typically take place in Excel and provide the foundation for every company’s success

 


Q: How do you classify or determine the level of proficiency your clients have in Excel?

The following is a list of the abilities typically demonstrated by users, as classified by three levels of Excel proficiency:

Intermediate

  1. Intermediate-Level Functions: such as Index/Match, Aggregate(), IF().
  2. Active use of Tables.
  3. Skillful use of PivotTables, Pivot Charts, and Slicers.
  4. Basic Charting, and understanding of editing chart properties.
  5. Recording of simple macros (also knowing when to use, when not to use, recorded macros).
  6. Demonstrate a clear understanding of:
    • Boolean Logic (True/False), and how boolean logic is applied to formulas, functions, and math within Excel.
    • Absolute and Relative Formula References.
    • The structure of formula functions and their respective parameter types (required and optional parameters).
    • Tabular vs. cross-tabular layouts (a common Achilles heel of junior analysts as they work with data).
    • Macros – the pros/cons, purpose/intention, and limitations of recorded macros.

Advanced

  1. Advanced-Level Functions: such as combining and nesting functions in different combinations to produce a desired result.
  2. Modeling – financial and dynamic models.
  3. Advanced Charting.
  4. Dashboard creation.
  5. Basic VBA and automation – write simple macros from scratch, and edit recorded macros into more reusable code subroutines.
  6. Demonstrate a clear understanding of:
    • Structured formula references, and their unique behavior that differs from Absolute and Relative references.
    • R1C1 Notation.
    • Named Formulas & References.
    • When to use VBA, and when NOT to use VBA (decisive ability to pick the right tool for the job).

Professional

  1. VBA – Advanced automation of processes, reporting, and analytics.
  2. VBA – write and develop robust user defined functions, code modules, userforms, and user interfaces.
  3. Application development – Add-ins & custom functionality.
  4. Cross-platform integration and programmable interface development.
  5. Demonstrate a clear understanding of:
    • Volatile functions, and their impact on model and system performance.
    • VBA code optimization.