Automating Excel-Based Financial Statements (Completed)

Date: Thursday, August 24, 2017
Instructor: David H. Ringstrom
Begin Time:  12:00pm Pacific Time
1:00pm Mountain Time
2:00pm Central Time
3:00pm Eastern Time
CPE Credit:  2 hours for CPAs

In this session, Excel expert David Ringstrom, CPA, demonstrates how to create dynamic accounting reports for any month of the year on a single worksheet. Often, Excel users set out to build a worksheet for each month of the year, which can be cumbersome to revise. As an alternative, David explains how to use Excel functions, including VLOOKUP, OFFSET, and SUMIF, to quickly create accounting reports that toggle to any reporting period with a couple of simple mouse clicks. He also outlines effective ways to export data from your accounting package so you can create a “set-and-forget” link to your accounting data in Excel.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He’ll draw to your attention any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

Who Should Attend
Practitioners who may benefit from learning how to create monthly financial statements in Excel more effectively.

Topics Covered

  • Discovering how to incorporate Check Figures and Alarms into your work
  • Learning why, in many cases, you should export reports intended for spreadsheet analysis to a .CSV file instead of an Excel workbook
  • Creating an in-cell list by way of Excel's Data Validation feature
  • Learning a simple design technique that greatly improves the integrity of Excel's SUM function
  • Using Conditional Formatting to draw attention to reports that don't balance to the source data
  • Using Excel's OFFSET function to dynamically reference data from one or more accounting periods
  • Improving the integrity of spreadsheets with Excel's VLOOKUP function

Learning Objectives

  • Define how to create accounting reports for any month of the year on a single worksheet
  • Apply a variety of Excel functions focused on helping you use Excel far more effectively
  • State how to export data from an accounting package to create a "set-and-forget" link to accounting data in Excel

Level
Intermediate

Instructional Method
Group: Internet-based

NASBA Field of Study
Computer Science (2 hours)

Program Prerequisites
Experience Working with Excel Spreadsheets Recommended.

Advance Preparation
None

">
 Chat — Books Support