Automating Excel-Based Financial Statements (Currently Unavailable)

Author: David H. Ringstrom

CPE Credit:  3 hours for CPAs

In this on-demand course, Excel expert David H. Ringstrom, CPA shows you how to create dynamic accounting reports for any month of the year on a single worksheet. Many users set out to build a worksheet for each month of the year, which can be cumbersome to revise. Instead David will show you how to use functions like VLOOKUP, OFFSET and SUMIF together to quickly create accounting reports that toggle to any reporting period with just a couple of mouse clicks. The session also demonstrates effective ways to export data from your accounting package so that you can create a set-and-forget link to your accounting data in Excel.

David’s materials cover Excel 2016, 2013, 2010, and 2007. When applicable, some slides include alternate instructions for Excel 2003. In this presentation, he demonstrates techniques in Excel 2010, as he finds it’s presently the version most widely utilized by Excel users. David’s detailed handouts serve as reference tools you can fall back on after completing one of his courses. He also provides an Excel workbook that includes a majority of the examples he uses during each session.

Publication Date: October 2015

Designed For
Practitioners seeking to develop automated financial statements linked to data extracted from an accounting program.

Topics Covered

  • Create an in-cell list by way of Excel's Data Validation feature
  • Improve the integrity of spreadsheets with Excel's VLOOKUP function
  • Learn a simple design technique that greatly improves the integrity of Excel's SUM function
  • Learn why in many cases you should export reports intended for spreadsheet analysis to a CSV file instead of an Excel workbook
  • See how to use the Trusted Document feature in Excel 2010 and later to supress the Data Connection security prompt
  • Use Excel's OFFSET function to dynamically reference data from one or more accounting periods
  • Use the Text to Columns feature to eliminate unwanted text without retyping, or to separate text into separate columns

Learning Objectives

  • Recognize the best format for getting accounting data to Excel from an an accounting package
  • Identify how to use the OFFSET function to return data for a specific month within a range of 12 columns
  • Identify how to use Data Validation for restricting the user to selecting a valid month name from a list

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (3 hours)

Program Prerequisites
None

Advance Preparation
None

">
 Chat — Books Support