Jet Essentials Basic Training Series Part 1 of 4

Analyzing Data with Excel Tables

Welcome to your Basic Training Series.  I put this series together to provide my Jet Essentials clients access to the information you need to get started with reporting!  Make sure to try out the practice exercises  after you've watched all the videos!  If you need help, I'm available at jae@onyxreporting.com.
Don't miss the follow up Basic Training series, sign up for our bi-monthly newsletter.
 

Do you use Tables?

For many of my clients, data analysis starts the same way, regardless of whether they're in Accounting, Sales, AP, AR, or Inventory. First you find the data in your ERP (NAV, GP etc.) then you copy the data into Excel.  From there, analysts will usually use custom formulas to create new measures across columns, or maybe add VLOOKUPs to add custom fields to the table.

Did you know Excel 2007 and later has a feature that allows you to build formatted "Tables" (Excel > Insert > Table) out of the data you copy into Excel?  Older versions of Excel called it a "List".  Advantages of using Excel Tables include the ability to add column filters, provide grand totals and add color formatting.  If you're getting really fancy you can look at adding Slicers and Pivot Charts to create robust Dashboards.
 

Seeing Detail versus Summarizing Data

If you think of copying data straight out of a table in your ERP as a "Data Dump" or detail report, then using Excel's Pivot Table to aggregate by a specific field (ex. total sales grouped by Country) would be a "Summary Report."

Pivot tables may sound intimidating, but they are easy to use. Select your Excel Table, then insert a Pivot Table (Excel > Insert > Tables > Pivot Table).  Now, Just drag and drop field names into a row or column.  Then drop a Numeric field into the Values area to aggregate.  It takes a little practice, but this opens up a wealth of new and dynamic analysis options.

Note: usually the default is to SUM value fields, but you can also COUNT, MIN, MAX and AVG.
Examples of Pivot Tables:

• See sales by city, by customer, and by item, over specific periods of time
• See purchases by vendor and item over time
• See donations by organization, by city by donor over time.

 

Use Jet Essentials' NL("Table") to Automate Reporting in Excel

Analysis against one ERP table
Example: Sales Transactions. You may want to quickly analyze the sales from your Customer table to learn more about sales during a period. You may want to know who the sales reps are, who their customers are, what country the customer is in, and what the sales were for the period. All data is extracted from just one table in your ERP.

• Check out this video:  Analyzing Data with Table Queries (6:31)

Analysis against multiple ERP tables
Often times, you'll want to extract data from a main table and then add supplementary columns from other tables.  In Excel it's common to use VLOOKUPs to add the supplementary data, but the Table Builder in Jet Essentials can automate that step too!

Example: Analyzing Purchase Transactions. The transaction table usually provides the vendor number but not the vendor name or any additional information about that vendor. In this scenario you would need to access the vendor master table for that information. You may also want to add in columns to make calculations based on data like vendor state or Payment Terms. 

The Jet Essentials Table Builder Wizard makes it easy to build a table by pulling data from other tables.  You can learn everything you need to know about Table Builder in 20 minutes by watching these tutorials!

• Introduction to Table Builder (6 min)
• Multi-Table Reports with Table Builder Part I , Part II
• Understanding "Include Duplicates" Filter (2 min)

 

Your Next Steps

These videos are designed to get you started and give you just a TASTE of what's possible.  If you want to know more about improving performance, working with FlowFields and FlowField filters, or just need a refresher on how to make the most of Pivot Tables, I'm just an email away!  In fact, if you go to our website, www.onyxreporting.com, you might be able to chat with me right now!
 

I look forward to hearing from you!

Don't miss the follow up Basic Training series, sign up for our bi-monthly newsletter.