Excel for Auditors: Audit Spreadsheets Using Excel 97 through Excel 2007

by Bill Jelen, Dwayne K. Dowell

Overview

Providing the tools and techniques necessary for finding errors and fraud in audits, this guide for auditors looking to better validate their Microsoft Excel spreadsheets provides techniques for performing a risk assessment and gathering spreadsheet and other data from company systems. Performing audit data analysis using data and analytical management functions and pinpointing the common errors in spreadsheets with focused Excel tests is discussed, as are the best practices for error and fraud prevention when developing spreadsheets. This reference is fully updated to reflect Excel 12.

Product Details

ISBN-13:	9781932802160
Publisher:	Holy Macro! Books
Publication date:	10/01/2006
Series:	Excel for Professionals series
Pages:	212
Sales rank:	551,288
Product dimensions:	8.00(w) x 10.00(h) x 0.45(d)

About the Author

Dwayne K. Dowell has over 20 years of experience in accounting for public and private industries, and is the founder of Dwayne K. Dowell, PSC, a CPA firm located in Louisville, Kentucky. He is a Certified Public Accountant, Certified Fraud Examiner, and Certified Internal Auditor, as well as a Certified QuickBooks Pro Advisor and Certified Intact Consultant. Bill Jelen is the principal of MrExcel.com and the author of Mr. Excel on Excel and Guerilla Data Analysis Using Microsoft Excel. He lives in Uniontown, Ohio.

Read an Excerpt

Excel for Auditors

---

By Bill Jelen, Dwayne K. Dowell, Linda DeLonais

Holy Macro! Books

Copyright © 2007 Tickling Keys
All rights reserved.
ISBN: 978-1-932802-63-4

---

CHAPTER 1

Copying a Worksheet

READ THIS!

You may think you know how to copy a worksheet – but I'll bet that you don't. I guarantee this one will save you anywhere from five to 30 minutes, depending on the size of your worksheet (and how compulsive you are about your settings).

Here's the Situation

A worksheet contains more than data and formulas. There is page setup data; there are column widths, etc. When you copy cells from one worksheet to a new worksheet, you will copy the cell contents, but not the meta data (configuration information) about the sheet. There are several variations on the Move or Copy Sheet command.

Here's What to Do

1. If you need to make a copy of a worksheet and place it in the same workbook, simply hold down the Ctrl key while you drag the sheet to a new location.

2. The new worksheet is given a strange name such as Jan(2), Jan(3), etc. Double-click the sheet tab to rename the sheet.

3. To access the full range of options for the Move or Copy command, right-click the worksheet tab and choose "Move or Copy".

In the default settings for the Move or Copy dialog, Excel will move the worksheet to a new location in the current workbook. This is somewhat unnecessary, since you can easily move a worksheet by simply dragging the tab to a new location.

Tip:

Click and drag a worksheet tab to move the worksheet to a new area in the same workbook.

4. Choose the box for "Create a copy" to have Excel make an exact copy of the worksheet.

5. Finally, use the dropdown at the top.

If you want to make a copy of a worksheet in a new workbook or copy it to another open workbook, you can choose this in the "To book" dropdown.

Excel Details

Using this command offers many advantages over copying and pasting cells. Excel will copy:

* Column widths and row heights

* Cell formatting

* Print Setup information such as headers and footers

* View Manager and Scenario Manager settings

CHAPTER 2

Showing Numbers in Thousands

Here's the Situation

If you are analyzing sales for a $100 Million company, it does not make sense to show sales to the penny or dollar.

How can you display results in thousands or millions?

Here's What to Do

There are custom number formats to display results in thousands, but they are not found on the Formatting toolbar.

1. Select the range of cells containing the numeric data. Press Ctrl+1 (that is, Ctrl plus the number 1) to display the Format Cells dialog.

Tip:

When you want to set up a custom format, it is best to choose a standard numeric format to get close. Use either the numeric or currency category and change the decimal places to zero.

2. Choose Custom from the bottom of the Category list. Notice that you are starting with a custom number format of "$#,##0". This is the format that Excel built for you to display currency with zero decimal places.

Tip:

If you place a comma at the end of this custom number format, Excel will keep the original number, but divide it by one thousand when it displays it in the cell. In the Sample box below, you will see that $869,454 is now displayed as $869.

3. Add a comma at the end of the custom number format.

Gotcha

To make it clear that the numbers are in thousands, you could note this in the title of your report. Or, you can add an abbreviation to the custom number format. If your company uses "K" as the abbreviation for one thousand, then a custom number format of $#,##0, K is valid. However, to use "M" for the thousands abbreviation, you will have to put the M in quotes: $#,##0,"M"

Excel Details

Every comma at the end of the custom number format will cause the displayed number to be divided by another thousand. Thus, to display numbers in millions, put two commas at the end of the format. To display numbers rounded to the nearest hundred thousand, it is valid to combine a decimal place followed by two commas. The following table shows the effect of various custom formats on the same number.

CHAPTER 3

Quickly Seeing Sum or Average

Here's the Situation

Your CFO calls you on the phone and starts asking you questions. He asks questions like "How much did Shell Canada buy last year?", "What was the largest order from Nortel?", and "What was the average order from Kroger?".

Here's What to Do

You can answer these questions quickly without creating any formulas in Excel. In fact, if you can click the mouse quietly, your CFO might actually believe that you do know this stuff off the top of your head!

1. Select a single cell in the Customer column and click the AZ button in the Standard toolbar to sort the data by Customer.

Caution!

Be sure to select just a single cell. Selecting more than one will sort just the selected cells, which can really mess up your data. If this happens, press the Undo button or Ctrl+Z before you do anything else!

2. Scroll until you find the section of records for Shell Canada. Highlight the cells that contain revenue for those records.

Note:

Some installations of Excel have the Status Bar turned off. You might have to use View – Status Bar to turn the Status Bar back on.

3. Now – look in the Status Bar at the bottom of the screen. The Status Bar generally says "Ready" on the left side. In the right half of the status bar, you will see the words "Sum=71651". This represents the sum of the selected cells. So – the answer to the boss' first question is that Shell Canada purchased $71,651 from the company last year.

4. Scroll up and highlight the revenue for Nortel.

In this case, there are 28 cells in the selection. The status bar reveals that Nortel purchased $406K last year, but your boss wants to know the largest order from Nortel.

5. Right click on the Sum field and choose Max.

The status bar reveals that the largest order from Nortel was $25,350.

6. Next the boss wants to know the average order from Kroger. Highlight the cells for Kroger revenue. Right-click the status bar figure and choose Average.

Gotcha

If one of the cells in your selection contains an error such as #VALUE! or #NA, the QuickSum feature will turn off.

Better in Excel 2007

The Status Bar in Excel 2007 can show several values at once.

To customize the Status Bar in Excel 2007, right-click it to display the configuration menu. You can control which items are, or are not, visible.

Excel Details

The operation of the QuickSum functions for Count and CountNums is different than the functions in Excel. Usually, the COUNT function counts only numeric cells. In the QuickSum, Count counts all non-blank cells. This is equivalent to using =COUNTA() in the spreadsheet.

CHAPTER 4

Adding Subtotals

Here's the Situation

You have sales data for three dozen of your best customers. You would like to see totals by customer. You might be tempted to insert blank rows between each customer and use the AutoSum button to add totals. This might work for a few customers, but it would take too long for a large number of customers.

Here's What to Do

Instead, use automatic subtotals.

1. Select a single cell in the customer column. Press AZ in the Standard toolbar to ensure that your data is sorted by Customer. In Excel 97-2003, from the Data menu, select Subtotals. In Excel 2007, select Subtotal from the Outline group of the Data ribbon.

Note:

The Subtotals command is excellent, but it has some quirky defaults. Excel always assumes that you want to subtotal by the left-most column in your dataset. It also assumes that you want to apply the subtotals to the right-most column in your dataset. If that column contains text, then the Subtotals dialog will change the Function selection from Sum to Count.

In the current case, you want to subtotal each Customer.

Tips:

If you have more than three columns to subtotal, you need to scroll up to select other fields.

If you need each customer to print on a separate page, select the "Page break between groups" option.

2. Change the top dropdown from Region to Customer.

In this case, the function of Sum is correct.

In the Subtotal list, keep Profit checked and check COGS and Revenue.

3. When you click OK, Excel automatically inserts a new row between each customer. The Subtotals command will insert a special function called Subtotal. The Subtotal function will sum rows in the range, but it is smart enough to ignore other subtotals in the range.

Notice that Excel has added three small buttons (1, 2, and 3) to the left of the row numbers. If you press the 2 button, Excel will show you only the subtotal rows.

Tips:

Pressing button 2 is a great way to print a summary report.

Pressing button 1 shows you only the grand totals.

Pressing button 3 shows all of the rows.

Since the report shows only the subtotals, it would be cool if you could copy just those subtotals to a new workbook. However, if you select the cells, copy, and then paste, you will see that all of the hidden detail rows were copied as well. Instead, use this method.

1. Select the cells from the Grand Total to the header row.

2. In Excel 97-2003: From the menu, select Edit [right arrow] Go To. On the Go To dialog, choose the Special button.

In Excel 2007: Use Home [right arrow] Editing [right arrow] Find & Select [right arrow] Go To Special.

3. On the Go To Special dialog, choose Visible Cells Only and press OK.

The result will be that Excel selects just the visible rows. There are thin white bands in the data to indicate that the hidden rows are not selected.

You can now copy and paste to a new worksheet to get just the subtotal rows.

Tip:

The awesome shortcut for Edit – Go To – Special – Visible Cells only is Alt+; (in other words, hold down Alt while pressing a semi-colon).

Excel Details

Removing Subtotals

To remove subtotals, choose Data [right arrow] Subtotals again and select the Remove All button.

Adding a Second Level of Subtotals

1. You can add a second level of subtotals. Sort the data by Product within Region.

2. Add subtotals to the Region field; then, select Data [right arrow] Subtotals again.

3. This time, select Product and uncheck the option for "Replace Current Subtotals".

The result will be a report with four Group & Outline buttons. Pressing button 3 will provide a summary by Region and Product.

CHAPTER 5

Quickly Filling a Series

Here's the Situation

If you have to fill a range with date data, there is often a way to do this quickly using the Fill Handle. When you select a cell or a range of cells, the fill handle is the square dot in the lower right corner of the selection.

Here's What to Do

1. Enter today's date in cell A1. Move the cell pointer back to A1.

Tip:

Use Ctrl+; to enter today's date.

2. Grab the fill handle and drag down the column. As you drag, a tooltip will show you the date of the last cell in the range.

In the image below, the series would extend through December 12.

3. Release the mouse button.

Excel extends the series, adding dates.

This trick works with many types of data.

4. Enter Jan in B1. Drag the fill handle and Excel fills in the months.

5. Enter Wednesday in C1 and drag the fill handle.

6. Enter 1st Period in D1 and drag.

7. Enter 1 in E1 and drag.

Wait! What's going on? Excel was able to extend all of the series for dates, months, days, periods, quarters, etc., but Excel can't figure out that this series should be 1, 2, 3?

8. To extend this series, you will have to enter a "1" in E1 and a "2" in E2. Select both of those cells before you drag the fill handle.

This process even allows you to fill a range with odd cells.

9. Enter a "1" in the first cell and a "3" in the second cell. Now drag the fill handle.

Tip:

To extend a numeric series without entering two cells, enter a 1 in a cell. Ctrl+drag the fill handle to automatically extend the series to 1, 2, 3, etc. Similarly, if you Ctrl+drag a date, the date value will stay the same.

If you set up a custom list to control sort order (as described on page 72), you can type the first value and drag to extend that list.

1. My favorite trick is filling only weekdays. Enter a date in H1.

2. Use Ctrl+1 to format cells and select a format that shows the day of the week. With the cell pointer in H1, right-click the fill handle and drag.

3. The tooltip will make you believe that this is a normal fill, but when you release the right mouse button, a menu pops up. Select "Fill Weekdays" from the menu.

Excel fills the range with weekdays, skipping Saturdays and Sundays.

CHAPTER 6

Using a Fixed Value in Your Formula

Doesn't it just figure that Excel's most amazing built-in function can also cause quite a bit of frustration and error? I'm talking Relative and Absolute References. In English, this would be Excel's secret shorthand code to make sure that a copied formula always points to a particular cell or range.

Here's the Situation

The worksheet below shows a variety of actual sales figures for several models.

1. To calculate forecasted sales, enter the formula =B4*1.05 in cell C4 and copy it to all of the other cells.

You probably take it for granted, but it is slightly amazing that Excel converts the reference to "B4" in cell C4 to be "C5" when this formula is copied to cell D5.

2. Now, instead of entering 1.05 in the formula, enter 1.05 in cell D1 (D1 is formatted to display as a percentage) and have the formula in cell C4 point to =D1*1.05. This formula calculates the same result.

3. However, when you copy this formula to the other cells, something goes wrong. All of the forecasts are zero!.

When you copy the formula from C4 to D5, the reference pointing to B4 accurately changes to point to cell C5. This is what you want to happen. However, the reference that pointed to D1 also changed. It is now pointing to E2. Since cell E2 does not contain a growth rate, the result is zero.

How can you write a formula that will always multiply by D1?

Here's What to Do

References such as D1 and B4 are called Relative References. As you copy a formula containing relative references, the cell being referenced in the formula changes.

Sometimes, you want a cell reference to always point to a certain cell. In this case, you always want to use the growth rate in cell D1. To do this, change the reference style to an absolute reference by placing a dollar sign before the column letter and row number.

In this case, you need to change the formula in C4 to =B4*$D$1.

Now, as you copy the formula, the reference to B4 is allowed to change but every cell continues to point to the growth rate in D1. You can enter new growth rates in D1 to play what-if games.

Note that there are times where you want to freeze the row number but allow the column letter to move. In this case, a reference such as =C$1 will achieve that result. This is called a Mixed Reference.

Other times, you will want a formula to always refer to a value in column A, but it is OK to have the row number change. In this case, a reference such as =$A4 will work. This is also a mixed reference.

Excel Details

To simplify the entry of dollar signs in references, you can use the F4 key. When you are typing a formula, hit the F4 key immediately after typing a cell reference. The first time that you hit F4, the reference changes from =D1 to =$D$1. Hit F4 again to toggle to =D$1. Hit F4 again to toggle to =$D1. Hit F4 once more to toggle back to a relative reference of =D1.

CHAPTER 7

Replacing a Thousand Formulas with One

Here's the Situation

You have data that has 5000 records with quantity and unit price. You would like to calculate the sum of quantity times the unit price for all rows.

Here's What to Do

Normally, you would enter =C2*D2 in E2, copy this formula down, and add a SUM formula.

However, there is a secret kind of formula in Excel called an Array Formula or CSE Formula.

1. In cell D5001, enter =SUM(C2:C5000*D2:D5000). At first glance, this looks like an invalid formula. However, you are going to turn it into an array formula.

2. Press Ctrl+Shift+Enter. Excel adds curly braces around the formula and actually does the 5000 multiplications before showing you the result.

Array formulas can occasionally come in very handy. I find it hard to remember the keystroke combination of Ctrl+Shift+Enter, so I call these CSE formulas to help me remember that I need to hold down Ctrl+Shift+Enter.

(Continues...)

---

Excerpted from Excel for Auditors by Bill Jelen, Dwayne K. Dowell, Linda DeLonais. Copyright © 2007 Tickling Keys. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---

Table of Contents

Contents

Title Page,
Copyright Page,
About the Authors,
Copying a Worksheet,
Showing Numbers in Thousands,
Quickly Seeing Sum or Average,
Adding Subtotals,
Quickly Filling a Series,
Using a Fixed Value in Your Formula,
Replacing a Thousand Formulas with One,
Highlighting Outliers,
Turning Your Data on Its Side with Transpose,
Joining Text,
Looking up Data,
Sorting Your Data,
Dealing with Dates,
Analyzing Data with Pivot Tables,
Analyzing Results by Date,
Creating a Random Sample from a Dataset,
Finding and Analyzing Records Using AutoFilter,
Formula Auditing,
Matching Two Lists,
Finding Duplicates or Unique Values,
Finding Missing Dates in Data,
Automating Excel with VBA,
Conclusion,
HOLY MACRO! BOOKS QUICK ORDER FORM,