Forensic Auditing with Excel

The role of a forensic auditor may be to identify suspicious transactions which may be fraudulent. Excel can be a useful tool to review a smaller dataset.

This Excel Zoom article shows users how to perform 5 simple forensic tests, including:

  • Identify Duplicate Transactions
  • Identify Rounded Transactions
  • Find above average payments
  • Identify a gap in transactions
  • Compare highest to 2nd highest transaction

To see the article, go to: https://excelzoom.com/five-ways-performing-forensic-audit-using-ms-excel/

Excel Tip – Text Functions #1

There are times text needs to be converted to lower case, proper case, or upper case for consistency sake. Excel provides functions to easily convert text in these cases.

However, Excel does not convert text to sentence case — the first letter only is capitalized. Using a combination of functions will easily convert text to sentence case.

The formula to convert the text in A1 is:

=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))

For examples, see the following:

Examples of text functions.
Examples of Text functions

Next week will continue with more useful text functions.

Slicers — Making Pivot Tables Easier

You have perfected your data analysis with a pivot table and you are about to share with other users. Slicers make Pivot Tables easier for other users to filter data in the table.

Slicers are a pop-up dialog box which allow the user to click on button to quickly filter the data.

To insert:

  1. Click in your Pivot Table.
  2. From the Pivot Table Tools > Analyze toolbar, Click Insert Slicer.
  3. Select the fields to provide a filter.
Insert slicer

4. To filter select the value on the slicer dialog box.

Slicers with Pivot Table

To select multiple values, click on the Multi-Select icon then click on the desired values. To clear the filter, select the Clear Filter icon with the red x to display all values.

Vlookup — True or False?

Vlookup is one of the functions I use the most. It can locate data within a table and return a value. One of the most confusing things about the function is whether or not to include the True or False at the end.

When using the Excel Function wizard, it argument in the function is called Range_lookup.

Function Wizard for Vlookup.
Vlookup in Function Wizard

Simply stated, use a TRUE or 1 when you want an approximate match. Use a FALSE or 0 when you need an exact match.

Use TRUE when the data in the lookup table does not contain all of the possible data points. For example, in the figure below, if you want to look up the tax rate you would use an approximate match since all incomes are not in the table. If the net income were 100,001, it would return a rate of 39%.

Tax Table

It finds the closest value that is not larger than the lookup value.

In order to use an approximate match, your data must be sorted as well. Otherwise, use a FALSE or exact match.

For more examples, see this YouTube video:

Video explanation

Excel Tip: Sort by Color

In a data area, several cells are selected by highlighting in a different color.  The example show cells that were manually selected, rather than using conditional formatting.  However, if cells are shaded or have a different font color due to conditional formatting, this will work as well.

Excel worksheet with some cells  shaded.

These can easily be sorted to the top of the list.  Place your cursor in one of the cells that has the color to sort to the top.  Right click.

From the pop-up menu, select sort > Selected cell color on top.  This will sort based on the color.

New to Analytics? — Start here

Think you are new to analytics? You may have a start. Most of us in business, and especially accounting are quite adept in Excel. This makes the tool a perfect place to start. You cannot use the largest data sets, but with new Excel tools such as PowerPivot, that is constantly increasing.

Analyzing data in Excel helps you learn the process of determining which questions can be answered from the data and using an analytics technique to summarize. If you can do a Pivot Table in Excel, you are on the road to learning more about analytics.

For more information, see this article from Computerworld: https://www.computerworld.com/article/3315737/use-microsoft-excel-to-learn-about-data-analytics.html

Excel Tip: Easily enter Absolute references

When entering formulas, use the F4 key to toggle between absolute and relative references.  When typing the above example, after typing “A1”, press F4.  Keep pressing F4 until the combination of absolute/relative references appears. 

Press F4 to make cell value Absolute

For a refresher on the differences between Relative and Absolute references, go to:

Excel Tip – Flash Fill

Here’s your Monday Excel tip to help you become an Excel whiz!

Excel 2013 has a great new Flash Fill feature gives you the ability to take a part of the data entered into one column of a worksheet table and enter just that data in a new table column using only a few keystrokes.  Unlike a formula, the new columns are static, so if the original information is updated, the data won’t change.  If you need to have the new columns update, then Text Formulas are the better choice. 

In the following example, flash fill was used to complete all of the information after the 1st row was complete:

To use the Flash-fill:

  1. Type in the pattern of the information you wish to extract.  It must be in a column next to the original data.
  2. Press Enter
  3. From the ribbon, select Cells > Fill > Flash Fill or press CTRL + E

In the following examples, names of individuals were imported into a single column. The Flash Fill can be used to separate the names into 3 columns, but generate an email address, based on the pattern in the first cell of the column.

Enter the data in the first cell. Press Enter, then CTRL + e
The first name is complete.

Auto-fill is used to fill in the rest of the data, including:

This feature can be used in a lot of different situations. This YouTube has 20 more ideas of how you can use Flash Fill.