• Analytics

Summary of Tools

  • Here we have a brief description of each of the TeamMate Analytics tools – just click on the tool name for a description.

    You can jump to the relevant section by clicking below:

    TeamMate Analytics Toolbar

     TeamMate-Analytics-Toolbar

      

    The TeamMate Analytics toolbar includes various tools that help with day to day Excel productivity, and specifically help with cleansing and manipulating source data into the format you need.
     

    Format as – Reformats a number into one of 3 types:

    • Number – a custom number format can be set in Options
    • Date – a custom date format can be set in Options
    • Currency – a custom currency format can be set in Options
     

    Manipulate Text - A variety of tools to manipulate text fields:

    • Convert to Proper Case
    • Covert to UPPER CASE
    • Convert to lower case
    • Remove leading spaces
    • Remove trailing spaces
    • Remove leading and trailing spaces
    • Remove all spaces
    • Remove multiple spaces
    • Convert textnumbers to numbers
    • Convert numbers to text
     
    • Remove numeric characters
    • Remove alphabetic characters
    • Remove non-numeric characters
    • Remove non-alphabetic characters
    • Remove unprintable characters
    • Remove leading characters
    • Remove trailing characters
    • Remove all after X characters
    • Advanced character removal
     

    Insert Borders – Formats the selected cells with 3 standard accounting borders

    • Top border
    • Top and bottom border
    • Top and double bottom border
     

    Tick/Cross – Inserts a “tick” / “check” or “cross” symbol into a cell using the included TeamMate Analytics ticks font.

    Wrap Text - Toggles wrap text on/off for the current selection.

    Merge, Wrap and Autofit – Merges the selected cells, wraps the text and autofits the cells to the appropriate height. When the built in excel function “Merge and Centre” is used, it is unable to autofit the row height to ensure that all the data is visible, TeamMate Analytics solves this.

    Debit/Credit Columns - Splits an amount column into two separate debit/credit columns, or combines separate debit and credit columns into a single column

    Fill Cells – Fills selected cells with fixed values, incremental values or random values. You can either prefix, append or replace existing values. You can also “fill from above”, allowing you to repeat a value (e.g. supplier number or date) where it only appears once.

    Fix Dates – Have you ever had a system report where Excel doesn’t recognise the dates? This tool will automatically work out what format the source data is in, and covert it to dates that Excel (and TeamMate Analytics) can recognise and work with.

    Global Find and Replace – Searches for a value or text across the current workbook, all open workbooks or all workbooks in a folder. You can also choose to replace the found term if desired.

    Add Date Column – Creates a formatted date column, for example to identify the “week number” and “financial year”, which can then be used for summarisation.

    Header and Footer – Inserts a standard header and footer into the worksheet, using information stored in Engagement Details.

    Comment to Header – Many of the TeamMate Analytics tools store details about the options that were selected, source data, etc. as a comment in cell A1 as an audit trail. This button converts this comment into a document header, also incorporating stored information from Engagement Details.

    Reverse Polarity – Converts positive numbers to negative numbers and vice versa.

    Swap Delimiters – Converts numbers in the format “12.345,67” to “12,345.67” (a common problem with European data reports) so Excel can recognize them as numbers.

    Move Minus Sign to Front – Moves the minus sign in numbers that are displayed as “9,652.23-” from the back to the front, so Excel can recognise them as numbers (e.g. “-9,652.23”).

    Convert to Values – Converts any formulas in the current selection to their values.

    Extract Visible Cells – Extracts all the visible cells (i.e. not cells that are hidden or filtered out) on the sheet to a new sheet.

    Sheet Index – Creates a click-able index sheet, either ordered alphabetically or in the original sheet order.

    Sheet Checker – Checks your source data for potential issues like merged cells, duplicate headers, total rows, blank rows/columns, etc. As well as identifying issues, Sheet Checker can automatically fix most of them at the click of a button.


    Sampling

     sampling

      

    Monetary Unit Sampling – Estimates, extracts and evaluates a statistically selected sample from a population.

    Stratification – Splits data into bands containing ranges of values. You can also define your own stratification bands and also select a random sample from each band.

    Random Sampling – Extracts a number of randomly sampled items from your population.

    Systematic Sampling – Extracts every ‘nth’ item from the population.



    Data Analysis Tools

    TeamMate-Analytics-Ribbon  

     

    Join Sheets – Joins two sheets together, based on a common matching column – this may simple, but it literally has hundreds of uses and one of the most regularly used tools in TeamMate Analytics!

    Append Sheets – Joins multiple sheets that all have the same column structure together. For example, where you have separate reports for each sales person, each warehouse, etc. that you wish to combine into one report.

    Aging – Re-ages a listing based on a variety of aging options, including custom aging bands that you can define.

    Quick summary – Quickly and simply summarises your data, based on the fields and criteria you specify.

    Advanced summary – Summarises your data in a variety of ways and performs statistical analysis on the results.

    Ledger split – Splits a report into several worksheets based on the values in a selected column. For example, it can split an inventory listing into a separate tab for each warehouse.

    Column statistics – Produces a detailed report on the actual data in each of your columns, such as maximum, minimum, mean, standard deviation, skewness, positive values, zero values, etc.

    Highlight Changes – Highlights any differences between 2 versions of a worksheet or workbook and can add a comment to the changed cell to show the previous value.

    Benford’s Testing – Performs Benford’s Digit Analysis testing to identify unusual patterns in data – includes 1st, 2nd, 3rd and first 2 digit tests.

    Sequential Invoices – Identifies any instances where a supplier has issued multiple invoices with sequential references.

    Exception Report – Extracts any records that meet the criteria you specify (up to 3 criteria).

    Specific Comments – Searches a worksheet or range for specific comments, such as “suspense”, “rainy day”, “unknown”, etc. It can use values from a stored list in options.

    Top and Bottom – Extracts the top and bottom X records from a list.

    Gap detection – Identifies for gaps in a sequence of numbers or partly numeric references, such as missing invoices or journals.

    Duplicate extraction – Extracts duplicate records, based on up to 3 fields you specify.

    Same, Same, Different – Extracts records where 2 fields are the same and a 3rd is different for example, same amount, same date, different vendor.

    Outliers – Extracts records where the amount significantly deviates from the mean.

    Out of Mask – Extracts records where a field does not match a pre-defined mask, such as bank account numbers/sort codes, National Insurance/social security numbers, etc.

    Number Analysis - Extracts any records which have a rounded amount, to 10, 100, 1,000, 10,000, 100,000 or 1,000,000, any records with values ending in ’999′ or zero values.

    Analytical Review – Compares two columns of numbers and calculates the absolute and percentage differences (e.g. current period and prior period).

    Number Checker – Compares two columns of numbers and looks for any possible missing digits or transposition errors between them.

    Options – Allows you to specify global options for TeamMate Analytics



    Other Tools / Features

    Multi Drill Down – Allows you to perform a “Show Detail” on multiple cells in a pivot table at the same time, either exporting the results to separate sheets, or to a single sheet.

    Rename Show Detail Reports – When you “Show Detail” in a Pivot Table, Excel renames the sheet numerically, e.g. ‘Sheet4′. TeamMate Analytics can automatically rename the new sheet with information about the cell you drilled down on.

    Double Click to Sort – Allows you to sort your data by double clicking in row 1 of the column you wish to sort by – this can be turned off in the options

    Quick Stats – Provides statistics about the selected cells on the right click menu. Selecting a statistic stores the value to the clipboard, so you can easily paste it to another cell or even another document/program. For example you can copy the total of the selected cells and paste that into Word.

    Quick Filter and Quick Extract – Allows you to quickly filter out or extract items matching selected cells.

    Tick Font – A special font consisting of a wide variety of ticks that can be used to mark up your data. This font is available in any Windows program, so you can use it in Word or PowerPoint too.



    Section modules

     sectionmods

      

    TeamMate Analytics includes 7 “Section Modules” each specifically targeted to an area of the accounts.

    Prepare Sheets – This prepares a worksheet, ready for use with the Section Modules.

    Non Current Asset Section Module

    • Asset additions
    • Asset category summary
    • Recalculate straight line depreciation
     
    • Recalculate reducing balance depreciation
    • Zero or negative net book value
    • Accumulated depreciation > net book value
     

    Inventory Listing Section Module

    • Aging
    • Recalculate inventory balance
    • Calculate inventory turnover (by value and quantity)
    • Zero or negative unit cost
    • Zero or negative quantity
     
    • Large inventory amounts (by value and quantity)
    • Inventory received around a specific date
    • Inventory location summary
    • Last sales price lower than cost
    • Total sales value lower than cost of goods sold
     

    Trade Receivables Ledger Section Module

    • Account over given amount
    • Accounts with credit balance
     
    • Accounts over credit limit
    • Accounts with a rounded balance
     

    Trade Receivables Listing Section Module

    • Aging
    • Duplicates
    • Invoices over a given amount
    • Credit transactions
    • Invoices posted on specific days
     
    • Invoices posted on specific dates
    • Transactions around a specific date
    • Transactions posted between 2 times
    • Transaction summary by user
    • Rounded amounts
     

    Trade Payables Ledger Section Module

    • Account over given amount
    • Accounts with debit balance
     
    • Accounts over credit limit
    • Accounts with a rounded balance
     

    Trade Payables Listing Section Module

    • Aging
    • Duplicates
    • Invoices over a given amount
    • Debit transactions
    • Invoices without purchase orders
    • Invoices posted on specific days
     
    • Invoices posted on specific dates
    • Transactions around a specific date
    • Transactions posted between 2 times
    • Transaction summary by user
    • Rounded amounts
     

    General Ledger/Journal Listing Section Module

    • Duplicate journals
    • Missing journals
    • Non balancing journals
    • Journals over given amount (by journal line or total journal debits)
    • Transactions posted on day of week
    • Transactions posted on specific dates
    • Transactions posted between 2 times
     
    • Rounded amounts
    • Multiple users, same journal
    • Summary by user
    • Summary by period
    • Summary by source
    • Summary by period and account
    • Summary by source and account
    • Summary by user and account
    • Summary by period and source
     

    Expenses / PCard Transaction Listing Section Module

    • Aging (by transaction and posted date)
    • Transactions with rounded amounts
    • Debit transactions
    • Split transactions
    • Multiple cards per cardholder
    • Specific comments
    • Days (by transaction and posted date)
    • Specific dates (by transaction and posted date)
    • Between times (by transaction and posted time)
    • Extract sample for testing (by account or month)Summary by cardholder
     
    • Summary by cost centre
    • Summary by merchant
    • Summary by card status
    • Summary by currency
    • No transaction limits
    • Zero transaction limits
    • Transaction limits exceeded
    • No monthly limits
    • Zero monthly limits
    • Monthly limits exceeded