Excel™ Training, Techniques and Tools:
|
- Excel™ Statistical Tools for Sampling and Analysis
- Create and Run Macros and Custom Menus – Use Visual Basic
- Harness Pivot Tables and Advanced Filtering Techniques for Clear, Rapid Analysis
- Use VLOOKUP and Automatic Data Validation Techniques
- Design Professional Level Charts – A Picture is Worth a Thousand Words
|
Accounting and Auditing Review, Update, Templates and Training:
|
- EPS Calculations with a Complex Capital Structure – FASB 128
- Automated Cash Flow Statements and Cash Flow Metrics – FASB 95
- Capital vs. Operating Lease – Lease vs. Purchase Analysis – FASB 13
- Analytical Review and Metrics Directly From the G/L – SAS 56
- Fraud Analysis Including Benford’s Law and Automatically Finding “Plugs”
- Statistical Sampling – Understand It Like a Specialist - Once and For All
|
Technology Training and Tools:
|
- XBRL Basics: Tags, Taxonomies and Using Edgar
- Hyperlinks, Drill-Down Capability, Data/Reporting Portals, Interactive Internet
|
An Intensive, Practical Two-Day Hands-On Workshop
Designed for Fast-Track Financial and Accounting Professionals
|
|
|
|
Using Excel™ for Auditing and Analytical Analysis
|
- Review of SAS 56 – Analytical Review
- Financial metrics using Excel™
- Introduction to Excel™ Pivot Tables
- Introduction to Excel™ Pivot Charts
- Performance measures for the corporate accounting department
- Using Subtotal and Function_num
- Using Pivot Table Wizard
- Using Custom or Advanced Filtering
- Using Subtotal
- Rapidly designing a custom Auto Filter, top X%, etc.
- Statistical Analysis using Excel™ – Inventory/Sales Case Studies
- Using the Analysis Tool Pack add-in
- Harnessing Descriptive Statistics
- Stratifying and aging inventory automatically
- Using an array formula to analyze abnormal transactions
- Sumif() and Countif() to age and stratify inventory
- Concatenate for dynamic text and worksheet headers
- Database and list management functions for rapid analysis
- Using the Conditional Sum Wizard
- Using Benford’s Law with Excel™
- Leading digit phenomenon
- Automatically extrapolating leading digit
- Using Excel™’s Regression Analysis – Case studies
- Finding unauthorized T&E expenses
- Finding Obsolete Inventory
- Fixed and variable costs
- Advanced Pivot Table Reports
- From G/L to Pivot Table Reports
- Drill-down capability
- Posting/downloading from the Web
- Advanced filtering using complex criteria (Boolean algebra)
- Advanced vs. custom filtering
- Filtering for text, values or formulas
- Case study: inventory
- Finding duplicate payments – (1) Pivot Table, (2) Unique Records, (3) Tagging (4) Conditional Formatting
- Using IF Statements to (1) flag errors and (2) for conditional computations
- Using Conditional Formatting
- Using the VLOOKUP
- Comparing Databases
- Matching employee addresses to vendor addresses, more!
- Calculating royalties
|
|
Statistical Sampling Using Excel™’s Statistical Analysis Tools
|
- Sampling: Every Nth vs. Random
- Using Excel™’s Statistical Analysis Tools
- T-test (equal and unequal variances) vs. Single Factor Anova
- Two Factor Anova
- Correlation coefficient vs. covariance
- Descriptive Statistics analysis tool; exponential smoothing
- F-test two-sample for variance comparisons
- Fourier Analysis vs. linear regression and LINEST
- Automatic rank and percentile
- Using the t-test vs. the z-test
- Case Study: Inventory Examination using Excel™’s Statistical Analysis Tools
- Determining sample size based on precision and confidence levels
- Sampling: Attribute and dollar value
- Combining Word Mail Merge Wizard with Excel™ (stratified receivable confirmations, invoices, etc.)
- Review of SAS 56 – Analytical Review
|
|
Using Excel™ to Automate Fin. & Atg. Dept. Processes and Workpapers
|
- How to Use Excel™ templates to rapidly comply with FASB pronouncements
- FASB 128 – EPS on a template
- Summary for FASB 128
- Case Study: Basic and Diluted EPS with a complex capital structure (warrants, options, convertible debt, etc.)
- FASB 95 – Cash Flow Analysis
- Summary for FASB 95: Cash Flow - from Operations (CFO); from Investing (CFI) and from Financing (CFF)
- Using the IF function to automate Cash Flow Statements
- Cash Flow Metrics automatically calculated and explained
- FASB 13 – Capitalized or Operating Lease – on a template
- Summary for FASB 13
- Capital or Operating Lease – automatic analysis!
- Lease vs. Purchase decision – template for analysis
- Automatic data validation techniques
- Using the source field and allow field
- Restrictions, range of values
- Input and error messages; circling invalid data
- Formula Auditing, trace precedents and dependents
- Error checking and trace errors
- Show formulas
- How to search and find “plug” numbers
|
|
Creating and Using Excel™ Macros – Case Study
|
- Creating Custom Menus
- Automatically recording and running a new macro
- Using Visual Basic and the Visual Basic Editor
- Assigning Macros to a custom menu
- Creating and using an auditing tick marks Toolbar
- Clip art box and insert picture
- Inserting appropriate tick marks into Excel™ work papers
- Using Hyperlinks to organize accounting work papers
- Worksheet protection techniques
|
|
A&A Financial Reporting Features Using Excel™
|
- Exporting data from your firm’s G/L to Excel™
- Digital reporting with drill-down capacity using hyperlinks
- Budgeting templates with drill-down capacity using hyperlinks
- Intranet and Internet distribution – Case Study
- Pivot tables for financial reporting on a web browser
- Reporting portals – data banks – several years’ data to analyze
- Creating dynamic, interactive work sheets
- Security and internal controls
- Using Chart Wizard
- Editing, adding notes and customizing your charts
- Building a dual-scale chart (Sales, COS, etc.)
- Using the Pivot Charts Wizard for dynamic analysis
- Automatically grouping and aggregating data
- Using the chart interface
|
|
XML, XBRL, SEC, Excel™ and the Future of Financial Reporting
|
- Understanding tags and mark up languages: HTML and XML
- Financial industry tags and taxonomies: XBRL
- SEC filings – using EDGAR online
- Using Excel™ 2003 for XML import/export of data
|
CPE Credits...
|
|
This seminar is recommended for 8 hours of Accounting and 8 hours of Auditing credit for a total of 16 CPE hours.
The National Center for Continuing Education 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 North, Suite 700, Nashville, TN, 37219-2417. Visit the NASBA web site at www.nasba.org.
|
|
|
|
This seminar is ideal for accountants, auditors and financial professionals with both large and small organizations who would like a crystal-clear explanation on how to use Excel™’s advanced features to effectively manage A&A applications. Included in this group are:
- CFO’s, Treasurers, Controllers and accounting staff
- Divisional CFOs, Controllers and Budget Directors/Analysts
- CPA firms and staff
- Auditors: Internal and Independent
- Financial VPs., Directors, Managers, Analysts and staff
|
Seminar Prerequisites
|
|
Course Level: Intermediate
Prerequisites: Basic knowledge of Excel™, Accounting and Auditing
|
|
|
|
|
|
|
|
If you have a group of 14 or more employees who want to improve their skills and productivity by effectively utilizing Excel™ to solve everyday A&A problems or need other financial training, then NCCE’s customized training is the cost effective solution for your organization. Bring Accounting & Auditing Using Excel™ in-house and ensure that your staff has the technical skills, knowledge and training to assure top productivity. NCCE’s in-house seminar clients include: Hewlett-Packard; Oracle; Harley-Davidson; Stanley Tools; General Mills and Deloitte & Touche.
For more information or to schedule a custom in-house program, e-mail NCCE at contact@nccetraining.com or telephone 800-635-9615.
Back to top
|
© Copyright 2006 Performance Seminars, Inc.
|
