Automating Excel-Based Financial Statements Part 2

In this presentation author and Excel expert David Ringstrom, CPA shows you how to transform an automated financial statement for a single entity into a consolidated financial statement that empowers you to create both consolidated and individual financial statements for as many entities as you wish. Techniques covered include Power Query, Data Validation, and the SUMIFS worksheet function.

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

• Using Conditional Formatting to draw attention to reports that don’t balance to the source data
• Most features and functions work in Excel for Mac as well but expect differences
• Removing unnecessary columns from Power Query results
• Creating a dynamic waterfall chart title that automatically reflects the report period being shown
• Consolidating financial statement exports from two or more entities by appending queries within Power Query
• Moving a waterfall chart to a new worksheet
• Using SUMIF to total values for specific matching criteria
• Adding a refinement to the SUMIFS function to allow the report to consolidate numbers or display departmental values
• Discovering how to incorporate Check Figures and Alarms into your work
• Switching the data source of a Power Query connection to another Excel workbook, such as an export from a different QuickBooks company or a workbook in a new location
• Suppressing the External Data security warning on a workbook-by-workbook basis

Learning Objectives

• Recall the character that you place between a list that you type in the Source field of the Data Validation dialog box
• State which ribbon tab the Flash Fill command appears on in Excel
• Identify the Excel for Microsoft 365 function that enables you to separate text into two or more cells

Level
Intermediate

Instructional Method
Group: Internet-based

NASBA Field of Study
Accounting (2 hours)

Program Prerequisites
Automating Excel-Based Financial Statements Part 1 is recommended but not required

Advance Preparation
None