Using Weekday() function in Fraud Audits

I just looked at the last date I blogged and realized that I am woefully behind!
I have been very busy.  I just finished writing a step-by-step EBook on Pivot Tables.
This time I wrote the EBook both for CPE credit and then created a separate EBook for people looking to purchase the EBook but who don't need CPE credit for it. Go to http://cpaselfstudy.com/category_14/Excel.htm if you are looking to purchase the CPE course (It is a 5 Hour course). If you just want to purchase the Ebook, you can find the information on that over on the top right-side of the blog under EBooks and Videos for Purchase.

In addition to that, I just finished reviewing an Excel course entitled "Excel Time Value of Money Functions for CPAs " which was written by Joe Helstrom and which can also be found at http://www.cpaselfstudy.com/.

Finally, I just finished my first 1 hour video course on Excel Tools and Tips. I am very excited about that and hope to add a few more soon.

Plus, of course it is summer and you know how that goes!

So, I am taking the lazy approach here and picking an excerpt from a course Joe and I wrote recently entitled " Fraud Audit Techniques Using Excel" that I thought you might find useful.  If you are interested in this course go to
http://cpaselfstudy.com/item_188/Fraud-Audit-Techniques-Using-Excel.htm


Using the Weekday() Function in a Fraud Audit


You can use date functions to identify potential fraud such as checking for activity on weekends.
One quick test is to see if journal entries are being processed on a weekend.

The Weekday function returns the numeric value of the day. So, for example, if you test September 1, 2010, =Weekday(A1) will return a value of 4 since 9/1/2010 was Wednesday the 4th . You could then use conditional formatting to review all the values of 6 and 7 to see if there is anything unusual about the date or you could just sort it.

• Select cell E2 and type =Weekday(A2,1)

• Press Enter

• Copy it down the column

Now that we have the weekday values, we could sort by number or we could use conditional formatting to identify Saturdays and Sundays (6 and 7)

• Select your data E2.E23

• Click on the Conditional Formatting icon on the Home Tab

• Select Highlight Cell Rules

• Select Greater Than….

• Enter 5 in the dialog box and click OK

Your data should look like the screenshot below:

You can easily see what transactions were occurring on Saturday and Sunday (6=Saturday and 7=Sunday)

Organize, Summarize and Analyze Data with Pivot Tables

As many of you have noticed, I have been blogging about Pivot Tables for awhile and the good news is that I have finally finished writing my new updated Pivot Table EBook. It covers everthing - from creating a pivot table, to drilling down for data, to creating multiple reports from a single pivot table. It concludes by showing you how to update your data automatically.  The EBook uses exercise files so that you can walk through and follow along.
If you want to buy the 5 Hour CPE Course on pivot tables, click here and go to cpaselfstudy.com 
If you just want to purchase the EBook and files (with NO EXAM or CPE Credit) click here.

I have now added a column on the right with EBooks to Purchase in case you are interested. I will be adding more to it shortly.

Enjoy the weekend.

Creating Multiple Pivot Tables All At Once

If you are using Pivot Tables, it is extremely easy to create multiple pivot tables from your original pivot table by using the Report Filter.
The Report Filter which displays at the very top of the pivot table  is the best feature within the pivot table as it controls all the information seen in the pivot table. It also allows you to easily generate multiple pivot reports.



In the example below, Ship Via is showing as the Report Filter. Currently, I am seeing the total orders for all the ships but I can easily generate a separate pivot table for each of the individual carriers.

1. Click on a cell in the pivot table

2. Go the Options contextual ribbon





3. Select the little down arrow next to Options  (in the first group on the ribbon),

4. Select Show Report Filter Pages…..

4. Select the Report Filter that you want to view in detail. We only have one report filter here so you would select
5. Click OK




It may seem as though nothing happened however if you look down at the sheet names in your file, you will see that Excel has created worksheets for all the individual carriers. In my example, I now have a worksheets named UPS, Parcel Post, FedEx etc and when I select the sheet named FedEx I have a pivot table just showing the orders FedEx. If I click on the UPS sheet, then the pivot table displays just the order information for UPS.

Here is another quick example: If the report filter allowed you to select between 4 Salespeople, Smith, Jones, Brown and White and the Report Filter instead of displaying All was displaying Smith, you would see 3 sheets at the bottom that Excel created – Jones, Brown and White. (Jones would not display as a new separate sheet as that is already displayed on the “original worksheet”.

This can be a big timesaver.

Video: Pivot Tables Make Everything Just Right

Someone posted a comment and sent me a link to a video on Pivot Tables.
For a class, some students at Ball State put together this musical video which is now on Youtube of course.
A for effort and creativity.
Who says Excel is dull?
Pivot Tables Rock!
Click the link if you are interested in watching it.
http://www.youtube.com/watch?v=GwbSJFtRmDw&feature=player_embedded

Automatically Updating Your Pivot Table

Maybe it is a coincidence or maybe because it is because I am updating my Pivot Table EBook for Excel 2010 for CPASelfstudy.com  but I have been seeing  a lot of questions and comments regarding updating pivot tables automatically as new data is added. So, of course I thought I would talk about it here.

The good news is that in Excel 2007 and Excel 2010, it is very easy to set your data up so that Excel will automatically pull it into your existing pivot tables. The reason for this is that Excel 2007 and Excel 2010 allow you to easily format your data as a table. Once Excel considers your data a table, it automatically treats all the data as part of the table even as data is added or removed from the parameters of the table. You still need to click the Refresh button when the data changes but you don't have to change the source data location each time anymore.

Here is how to change your data into a table

• Click in the data
• Click Insert tab
• Click Table
• Verify the data range  and make sure the checkmark is in "My table has headers"
• Click OK

That's it, Excel now considers your data to be a table.  If you open your Pivot Table and re-select  your data source, the pivot table will now update as the range changes.  Now, if data is added it will automatically be incorporated into the parameters of the original source data.


By the way, another plus of turning your data into a table is that Excel automatically displays alternating row shading and AutoFilter drop down boxes for you.