Excel Essentials: Automating Financial Statements & Designing Error-Proof Spreadsheets


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 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.

In the second session, David H. Ringstrom will present numerous techniques that he relies on to improve the integrity of spreadsheets that he builds for himself and others. This session will empower with simple ways to avoid introducing errors into your own workbooks. 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.

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
  • Learn how range names minimize errors, save time in Excel, serve as navigation aids, and can store information in a hidden location
  • Learn design techniques that minimize errors.
  • Minimize errors by arranging worksheets or workbooks side by side while you build formulas.
  • Learn how to incorporate check figures and alarms into your work.
  • Master look-up functions, which can lower the risk of referring to an incorrect cell
  • Learn ways to restrict how others use your spreadsheet, including using the Scroll Area and Very Hidden properties to protect sensitive worksheets
  • Toggle a single worksheet between a detailed view and a summary view by using the Custom Views feature—instead of creating separate summary and detail worksheets.
  • Discover techniques that automatically provide fall-back positions in the event that you need to see an earlier version of your spreadsheet.
Prerequisite
A basic understanding of Excel.

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

Author
David H. Ringstrom