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.)

2 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.. 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.

3 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.

4 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.

5 These topics focus on the most important aspect of Excel the data tools which can be specifically used to analyze data and detect anomalies.. 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.

6 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. 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.

7 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.. 128 14.

8 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.

9 141 18. Ratio Reporting Attendees can download sample workbooks and templates by visiting , and clicking on the Security and Excel tabs 19. 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.

10 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. 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.