Excel 2013 for Intermediate Users - ASA Research

1 Excel 2013 for Intermediate Users A S A R e s e a r c h J. Carlton Collins, CPA ASA Research - Atlanta, Georgia Excel 2013 for Intermediate Users Page 2 Copyright May 2013 Table of Contents 1. Random Numbers Attendees will learn how to generate random numbers to be used for statistical sampling purposes.. 5 2. Consolidating Data Attendees will learn how to consolidate data (such as budgets, financial reports, departmental reports, inventory lists, salesperson reports, location reports, etc.) using four different techniques as follows: Formulas, spearing formulas, consolidation tools, pivot tools.. 9 3. Benford s Law Benford s Law predicts the occurrence of digits in large sets of data and these predictions might help red-flag potential irregularities.

2 23 4. Using Regression to Create & Using Budgets to Detect and Prevent Fraud An accurate budget should be the CPAs first line of defense for detecting and preventing fraud. Attendees will learn how to quickly create a budget using regression analysis applied to historical data. You will also learn how to scrutinize each line item using a variety of techniques including Pearson, R-Square, Skew and Kurtosis functions to determine if a suitable basis for regression analysis exists, and if not, alternative methods will be used to budget that particular line item. That budget will then be seasonalized and rounded. From there, a balance sheet budget and cash flow forecast will be prepared based on the seasonalized budget, and the importance of using a seasonalized budget to detect and prevent fraud will be discussed.

3 29 5. Profit Margin Monitoring Profit margins that miss their target speak volumes. Attendees will learn how to budget for profit margins by asking two simple questions and working backwards using prior year data to target specific profit margins. Once established, these profit margins can also be used as benchmarks to help detect fraud or errors.. 49 6. Data Analysis Tools Attendees will practice using data analysis tools to slice and dice data, filter data, group data, subtotal data, and pivot data. These topics focus on the most important aspect of Excel the data tools which can be specifically used to analyze data and detect anomalies.

4 54 7. Queries Attendees will learn how to pull data directly from an accounting system, from within Excel , for quick and easy data analysis.. 80 8. Sparklines - Sparklines are new tools in Excel that can be used to visually analyze large volumes of data, and attendees will learn how to utilize this tool to save time and provide better data visualizations.. 86 9. Conditional Formatting Attendees will practice with Excel s conditional formatting tools which allows the user to create rules for highlighting data with different colors to help visually analyze the data.. 86 Excel 2013 for Intermediate Users Page 3 Copyright May 2013 10.

5 Excel Functions Attendees will also learn about a variety of tips and tricks such as Aggregate function which can be a useful tool in analyzing data, and will receive a list of the top 171 functions the course instructor thinks apply to CPAs.. 90 11. Macros Attendees will learn how to create macros, and learn the critical difference between absolute and relative macros.. 113 12. Sales Order and Macro Case Study - Time permitting, attendees will see a demonstration of how macros can be used to help build a user -friendly template, such as a Sales Order Form .. 117 13. Gantt Charts - Excel does not provide Gantt chart functionality, therefore attendees will learn how to apply clever tricks to achieve the desired effect.

6 128 14. Combination Charts Attendees will learn how to create a combination chart one chart atop another, for comparison purposes.. 130 15. Organization Charts Attendees will learn how to create an organizational chart using Excel s SmartArt capabilities.. 131 16. What s New in Microsoft Office 2013 ? Attendees will learn about Excel 2013 s newest features and functions, and see many of them in action.. 132 17. What s New in Microsoft Office 2010 For those attendees jumping from Excel 2007 to 2013 , brief coverage of the new features and functions added to Excel 2010 are also included. 141 18. Ratio Reporting Attendees can download sample workbooks and templates by visiting , and clicking on the Security and Excel tabs 19.

7 Instructor Biography .. 164 Excel 2013 for Intermediate Users Page 4 Copyright May 2013 Excel 2013 for Intermediate Users CPE Course Information Learning Objectives To increase the productivity of accountants and CPAs using Excel s best and newest commands, features and functions Course Level Intermediate Pre-Requisites Familiarity with Microsoft Excel Advanced Preparation None Presentation Method Live lecture using full color projection systems and live Internet access with follow up course materials Recommended CPE Credit 8 hours Handouts Templates, checklists, web examples, manual Instructors J. Carlton Collins, CPA AdvisorCPE is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors.

8 State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be addressed to the national Registry of CPE Sponsors, 150 Fourth Avenue, Nashville, TN, 37219-2417. Telephone: 615-880-4200. Copyright May 2013 , ASA Research and Accounting Software Advisor, LLC 4480 Missendell Lane, Norcross, Georgia 30092 All rights reserved. No part of this publication may be reproduced or transmitted in any form without the express written consent of ASA Research , subsidiaries of Accounting Software Advisor, LLC. Request may be e-mailed to or further information can be obtained by calling or by accessing the ASAR esearch home page at: All trade names and trademarks used in these materials are the property of their respective manufacturers and/or owners.

9 The use of trade names and trademarks used in these materials are not intended to convey endorsement of any other affiliations with these materials. Any abbreviations used herein are solely for the reader s convenience and are not intended to compromise any trademarks. Some of the features discussed within this manual apply only to certain versions of Excel , and from time to time, Microsoft might remove some functionality. Microsoft Excel is known to contain numerous software bugs which may prevent the successful use of some features in some cases. Accounting Software Advisor makes no representations or warranty with respect to the contents of these materials and disclaims any implied warranties of merchantability of fitness for any particular use.

10 The contents of these materials are subject to change without notice. Contact Information for J. Carlton Collins Excel 2013 for Intermediate Users Page 5 Copyright May 2013 Random Numbers Excel provides two tools for generating random numbers as follows: 1. RAND 2. RANDBETWEEN (You must first activate the Analysis ToolPak) RAND - The RAND function in Microsoft Excel allows you to generate random numbers in Excel . Specifically, type RAND() in a given cell to produce a random number between 0 and 1, as shown. Comments 1. RAND is a volatile function, which means it will be recalculated any time the enter key is pressed, so the random number constantly changes.