By Alicia Penn and Joe Wanzala
This post is part of an ongoing series of videos on how Microsoft Excel can help CJA practitioners (including attorneys, paralegals, investigators, and mitigation specialists) in their CJA cases.
Previous Post: Formatting Tables
Today’s Post: Pivot Tables for Financial Fraud Analysis
Introduction:
You might get a Microsoft Excel spreadsheet in your discovery. Things like bank records, call detail records, cell tower data, or tax documents are often produced in this format, as Excel (.XLSX) or .CSV files. This is good! The benefit to getting data in an Excel file, as opposed to a PDF, is that Excel will let you easily sort and filter data. For example, if you get telephone call logs in Excel format, you can easily sort the calls by date and time. You can filter that information to select specific telephone numbers and review just those calls quickly. This would be difficult to do with a PDF and would take much longer. [https://nlsblog.org/category/software-guides/excel/ ].
This blog post takes this one step further. The pivot tables function in Microsoft Excel lets you rotate and combine data otherwise confined to static columns. This allows you to manipulate existing data in order to see relationships that might not immediately be obvious in the spreadsheet. For example, if you have a spreadsheet of bank records in fraud case, you can use pivot tables to discover how much money is sent by each victim and how much money is transferred to a certain bank, by date. You can also use pivot tables to support more intricate conclusions—for example, that the government had no evidence about a particular defendant’s involvement in a fraud scheme before his arrest and subsequent search. This was a definitive issue in a real case and using pivot tables led to a successful Fourth Amendment challenge. Below we will first set out our fact pattern based on a real timeshare resale fraud scheme. Then we will show you how pivot tables can be used to understand data and answer questions.
Fact Pattern: Timeshare Resale Fraud Scheme
Our client is Ike Kando. He is indicted in a Belizian time share fraud conspiracy, along with over ten (10) codefendants. He is not the first name on the indictment but is further down—his role in the case appears to be that of a money mule. He receives checks made payable to him in the mail, deposits the checks into his own bank account, keeps a cut for himself, and sends the rest via Western Union transfer to a bank account in Belize.
Through viewing discovery, we piece together that the general scheme is this—one of the codefendants finds timeshare owners. He contacts them and offers them a deal too good to be true—that a buyer in Belize wants to buy their timeshare at a price high above market value. But! In order for the timeshare owner to take advantage of this amazing opportunity, they first have to send the Belize buyer a percentage of the purchase price to satisfy local Belize rules. The timeshare owner is told to send the money to one of several money mules used by the fraudster. The money mules deposit the checks into their own bank accounts and then wire the money to a bank controlled by the fraudsters, who really are in Belize. (Figure 1).
Relevant Timeline:
In August 2002 a fraudster contacts Victim 5 and tells them to send a check to Luke Brownson, one of the money mules. After he sends the check, Victim 5 becomes suspicious and contacts the FBI. DOJ investigates and subpoenas Brownson’s bank records.
In October 2002, Ike Kando is pulled over for a traffic violation. The local police detain him and search his car. They find suspicious bank records and take Ike Kando to the local station, where he is questioned. The next day, Ike Kando is also interviewed by an FBI agent. Law enforcement gets a search warrant for Ike Kando’s house and search it. There they find more documents that implicate him in the timeshare resale fraud scheme.
Because of the bank records and documents in Ike Kando’s car and house the FBI issue more subpoenas for the banking information of other money mules, Brownson and Ted Romanowski. Ike Kando and the other money mules are indicted.
Spreadsheet Introduction:
One piece of discovery we receive from the government is a spreadsheet created by the main case agent. It contains the bank records of the money mules in our case. Each column contains data related to different aspects of each transaction. For example, Row No. 14 tells us that on 8/22/2002, Victim 1, based in in Alabama, sent a check for $12,000 to Ike Kando in Sioux Falls, North Dakota, and Row No. 17 shows that on 8/24/2002, Ike Kando sent a wire transfer for that same amount to the bank account in Belize controlled by the fraudsters:
This spreadsheet, as is, does allow us to filter and analyze some information. We could, for example, find out which transactions are associated with certain money mules by filtering in Column E, the money mule Column and selecting only individual mules, such as Ike Kando. We could also add another filter in Colum C, the victim’s column, and further filter transactions that way. The limitation of only filtering on this spreadsheet is that the data stays tied to the columns and rows they are currently in.
Pivot Table Introduction:
Pivot tables allow you to move data out of its position in the original worksheet and create new tables using existing information. Using a pivot table on a different worksheet, you can select data relevant to your query and arrange it so you can see relationships that might not be immediately obvious on the original worksheet. You can also use other tools, such as slicers, timelines, charts, and dashboards to further filter, manipulate, and view data. The benefits to pivot tables include maintaining an original worksheet, creating and preserving different worksheets that correspond to different questions, and creating visual tools that can be used by other team members (who don’t need to understand the underlying tables themselves). Dashboards and pivot graphs based on filtered, rearranged information can also be used as demonstratives.
Video – Pivot Table Introduction
Questions of interest that cannot easily be answered by the spreadsheet, as is:
The answers to these questions could help us argue our client had a minor role, that their loss amount should be smaller, and that the evidence being used against them was the product of an unlawful search and thus should be suppressed. They are answers not easily answered by the spreadsheet as is, but with the use of pivot tables become clear and simple:
- How much money was sent by each victim to the money mules, by date?
- How much money was transferred by each money mule to the Bank of Belize, by date?
- What did the government know about our client’s involvement in the fraud scheme prior to his arrest?
- How much of the information obtained by the government about the fraud scheme was derivative of the subpoenas issued to our client’s bank after the arrest?
How to Answer Question 1: How much money was sent by each victim to the money mules, by date?
If you tried to answer this question only using the filter and sort functions of the existing spreadsheet, you could only calculate a single answer at a time. For example, you could filter by a specific victim, and then show their transactions with each of the money mules, and then sort by date. This might look like this, for money sent by Victim 1 to each of the money mules:
You could then run this kind of filter for each individual victim and save the results—essentially running six (6) different filters.
Or– you could answer this question more efficiently using a pivot table.
To create a pivot table, use the “insert” tab on the main menu, and select the entire table as the source of information. Use the default settings and create a pivot table in a new worksheet. The blank pivot table will look like this:
It is a new sheet in your Excel workbook. The columns from the spreadsheet are now free-floating pivot table fields. These can be rearranged from rows to columns and vice versa by moving the fields between the filters, rows, columns, and values areas.
We are interested in the money mules, so we can select that to go into the column area:
Because we want to see the relationship between the money mules and the victims, we put the victims data into the rows area:
The next piece of data we are interested in is the sum of the deposits related to the victims and money mules, so we drag the deposits column to the values area:
We also want to include date information, so let’s add that to the rows area:
Now, we can answer the question without further filters– “How much money was sent by each victim to the money mules, by date?” You can see, in this existing pivot table, that Victim 1 sent Ike Kando $12,000 in August, Luke Brownson $7,700 and Ted Romanowski $12,500 in September, etc. etc.
You can manipulate this data even further, making it simpler to read, using a type of filter called a “slicer.” A slicer lets you run one or more filters on the existing pivot table. For example, we can create a slicer to filter what we see on the worksheet by victim, money mule, or another field selected for the table.
To add a slicer, we go to the “insert” tab and select “slicer.” When the slicer dialog box pops up we select the victim and mule categories. This will create a mini-window for each category—victim in one and mule in the other:
By default it will show us all the data. We can toggle on and off the option to select individual or multiple fields. Here we de-select victims 1,2, 4, 6 and Blank in the victim slicer, and de-select Romanowski in the money mule slicer:
Now we can see only what we selected—the transactions between Romanowski and Victims 3 and 5, by date. By using the toggle and filter capabilities of the victim and money mule slicer, you can see how you can answer any questions about these relationships as broadly or narrowly as possible.
Pivot tables also allow us to use a timeline slicer to manipulate data by date. Go to the insert tab and select timeline. Select the date option and see how you can filter data by month:
Video – Money Sent by Victims
How to Answer Question 2: How much money was transferred by each money mule to the Bank of Belize, by date?
To answer this question, make a new pivot table worksheet using the data from the primary sheet. Change the information that goes into the pivot fields according to the question—because we are predominately concerned with the money mule data and with money moving to the Bank of Belize, by date, we can put the money mule data and wire transfer location in the rows section and the date data in the columns section. The withdrawals go into the values section.
To isolate the data of wire transfers to Belize, make a slicer for wire transfer location and then selected only Belize, eliminating from view the data with a blank in the wire transfer location field.
We can now easily see, from this very basic pivot table, the answer to our questions. Ike Kando transferred $12,000 to the Bank of Belize on August 24, $14,400 on August 27, etc. etc.
To make the pivot table more comprehensive, add more fields into the report. To track how much money was transferred by each money mule to the bank of Belize and when, and which victim was the source of that money, put the money mule data and months data in the row section, and the victim data in the column section. Make slicers for the money mules and victims and a timeline to filter by date. Insert a pivot chart, leading to a worksheet that looks like this:
A benefit to creating a more comprehensive worksheet with slicers, charts, and pivot tables is that you can then use the pivot tables, with their pre-filtered data, to make dashboards. Dashboards allow you to combine data already isolated in different worksheets, which means you can view pre-filtered data to answer different questions simultaneously.
Video – Transfers by Money Mule
How to Answer Question 3: What did the government know about our client’s involvement in the fraud scheme prior to his arrest?
How to Answer Question 4: How much of the information obtained by the government about the fraud scheme was derivative of the subpoenas issued to our client’s bank after the arrest?
To answer these questions, we will create a dashboard.
Dashboards
To create a dashboard, start with a blank worksheet within the same Excel file. Copy over each chart, the slicers, and timelines from the two worksheets we just created. Since the slicers and timelines are just filters, only one set of the slicers and timelines needs to be copied. It does not matter which of the two worksheets the slicers are copied from. The graphical information in the Dashboard depends on the data that has already been processed using the pivot tables within each worksheet. However, because we have only copied over one set of slicers and filters, we need to make sure that the single set of slicers and timelines are linked to the data in each of the two worksheets. If we don’t link the slicers to both worksheets, only one data set will respond.
To connect the slicers to the other worksheet so that we get responsiveness from both charts, we right click on the slicer and go to “report connections.” We can see here that the data from the other worksheet is referenced but not connected. Check off the other worksheet, so that the slicer is accessing that worksheet as well. When we return to the dashboard all the slicers or filters should be responsive across all the data on the dashboard. Do this for all the slicers and timelines on the new worksheet.
Video – Creating a Dashboard
Once the dashboard is created utilizing elements from the various worksheets, we can run broader queries across all our data. Furthermore, the dashboard can be used by other members of the case team as a reporting tool. Even if they are not familiar with building or manipulating the underlying Pivot Tables, other team members can use dashboards to easily analyze and gain insights into the data. Dashboards can also be useful during team or client meetings, and dashboard screenshots can be used as possible exhibits or demonstratives for motions, trials, or hearings.
Timeline Filters
To answer questions three and four, we will use the two timeline filters. The one entitled “Date,” tracks the dates certain transactions occurred. The one below it entitled ” Date Government Obtained Info,” tracks the dates when the government received information based on subpoenas they issued.
Using the “date” timeline, we can filter by selecting one month, or more than one month by using our shift key. As we do that the visual representation of the data in charts will revise accordingly.
We know from our case timeline that our client was arrested in October of 2002, and we can see by filtering our dashboard for Ike Kando, that he had received money from several victims in August and September and during that same time period he had also wired money to Belize.
However, the question is what did the government know and when did it obtain that information? For insight we can use our “Date Government Obtained Info” timeline. When we filter for the month of July, we see that the government had obtained no information about any aspect of the fraud scheme in July. When we filter for August we still get nothing on our dashboard. We know that the government received a tip from one of the victims in August, however, the dashboard is still blank for that month because they received no banking information based that tip off until September. When we filter for the month of September, we see that is when they received Luke Brownson’s bank records with information about his involvement in the scheme. However, they still had no information about our client, Ike Kando. It is only when we get to October that we see that the government has obtained information about Ike Kando’s involvement in the scheme.
We can therefore assert that the government did not know about our client’s involvement in the fraud scheme before the traffic stop in October and that all information the government obtained about our client’s involvement in the scheme was derivative of the documents found during the illegal traffic stop. We had a client involved in a similar fact pattern as the case we used in this hypothetical. In that case, because we could show that the case against our client was all derivative from the illegal traffic stop’s poisonous tree fruits, we ended up with a favorable negotiated agreement for our client.
Video – Using the Dashboard to Answer Questions 3 & 4
Conclusion
If you have made it through this entire blog post and the videos and run into trouble replicating our results or with your own pivot tables and dashboards, please contact us! Excel is a powerful tool but it is also easy to make a misstep that can cause your worksheets to not work as you intend. We have tried with this post and videos to give starting instructions paired with an actual case scenario, but we did not include every single step and decision here. We are happy to look at your work with you and troubleshoot, and hope that you find pivot tables as helpful as we do.