Automating Excel-Based Financial Statements Part 1

In this insightful session, author and Excel expert David Ringstrom, CPA, shows you step-by-step how to create dynamic accounting reports for any month of the year on a single worksheet. This provides a powerful alternative to building out a separate worksheet for each reporting period and provides easier maintenance and better data integrity. You'll see how to use Power Query to create a self-updating connection to a twelve-month financial statement that you export from your accounting software. You'll also see how to use Data Validation to create a drop-down list for specifying a report period, and how to use the SUM, OFFSET and SUMIFS functions to summarize data into detailed or roll-up report formats.

David H. Ringstrom, CPA, has more than 30 years of experience as a spreadsheet and accounting software consultant and speaker. He has presented over 2,500 live webinars and is the author or co-author of ten books, including “Microsoft 365 Excel All-in-One for Dummies”, “Microsoft 365 Excel for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and “QuickBooks Online for Dummies”.

In his webinars, David encourages questions throughout the presentation and demonstrates every technique twice: first on a PowerPoint slide with numbered steps and then live in Excel for Microsoft 365 for Windows. He highlights any differences in Excel 2024, 2021, or 2019 during the session and in his detailed handouts. Attendees also receive an Excel workbook containing most of the examples he uses, making it easy to follow along and apply the techniques later. David additionally supports Excel for Mac users by answering their follow-up questions via email.

Who Should Attend
Professionals seeking to create financial reporting spreadsheets that are easier to use and have improved data integrity.

Topics Covered

• Creating self-updating financial spreadsheets by using Power Query pull data via automated queries that also overcome common issues in exported reports
• Filtering unwanted data out of Power Query results
• Most features and functions work in Excel for Mac as well but expect differences
• Automating the extraction of data for a given month or year to date by way of the OFFSET function
• Using SUMIFS to total values based on multiple conditions
• Suppressing the External Data security warning on a workbook-by-workbook basis
• Creating self-updating financial spreadsheets by using Power Query to pull data via automated queries that also overcome common issues in exported reports
• Exploring the nuances of data exported from accounting programs, such as extraneous worksheets, blank columns, and extraneous rows
• Using the OFFSET function to dynamically reference data across different accounting periods
• Creating a workbook with just two worksheets that will present data for any month of the year
• Creating a 12-month P&L report from QuickBooks Desktop and then exporting to the comma-separated value (CSV) format to streamline analysis
• Adjusting Power Query refresh settings to control when and how data updates

Learning Objectives

• Name what the SUMIFS function returns if a match cannot be found
• Identify one of the uses of Data Validation
• State which column the OFFSET function would return data from

Level
Intermediate

Instructional Method
Group: Internet-based

NASBA Field of Study
Accounting (2 hours)

Program Prerequisites
Prior working knowledge of Microsoft Excel is recommended.

Advance Preparation
None