Automating Excel-Based Financial Statements (Currently Unavailable)

Author: David H. Ringstrom

CPE Credit:  2 hours for CPAs

In this on-demand webcast, 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 will focus primarily on Excel 2007 and 2010, but will answer questions specific to earlier versions. Alternate instructions for Excel 2003 and earlier are included in the handout where applicable.

Publication Date: January 2014

Designed For
Practitioners who may benefit from working more effectively in Excel.

Topics Covered

  • Create a dynamic, monthly financial statement from three worksheets: one for your data, one for a cross-reference that allows you summarize multiple accounts into one report line, and a dynamic report tab. This eliminates the need to build out one report worksheet for each month of the year.
  • Use look-up formulas to quickly return results based on account numbers or account descriptions
  • See how to link your spreadsheet to data exported from your accounting package
  • Use Excel's Data Validation feature to create an in-cell drop-down list for choosing a report period
  • Add built-in check figure alarms that only appear when needed
  • Discover Excel's Group and Outline feature that allows you to expand or collapse groups of rows or columns
  • Use the MID, MONTH, and VALUE functions to transform accounting data into the desired format

Learning Objectives

  • Recognize the variety of Excel functions--all focused helping you use Excel far more effectively
  • Distinguish between use functions of VLOOKUP, OFFSET and SUMIF.

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Specialized Knowledge and Applications (2 hours)

Program Prerequisites
A basic understanding of Excel.

Advance Preparation
None

">
 Chat — Books Support