Excel Essentials: Automating Financial Statements


Overview
In this presentation, Excel expert David H. Ringstrom, CPA shows your team 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 your team 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 teach from Excel 2010, but his detailed handouts and example workbook include alternate instructions for Excel 2003, 2007, and 2013 where applicable.

Objectives
Learn a variety of Excel functions–all focused helping you use Excel far more effectively.
 
Emphasis

  • 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
Prerequisite
A basic understanding of Excel.

Preparation
No advance preparation required
 
Level of Knowledge
intermediate
 
CPE Credit
2 Hours
 
Delivery Method  
Group Internet Based
 
NASBA Field of Study
Computer Science

Author
David H. Ringstrom