Excel Essentials: Automating Financial Statements
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.
Learn a variety of Excel functions–all focused helping you use Excel far more effectively.
- 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
A basic understanding of Excel.
No advance preparation required
Level of Knowledge
Group Internet Based
NASBA Field of Study
David H. Ringstrom