Automating Excel-Based Financial Statements Part 2

Author: David H. Ringstrom

In the second part of this two-part series author and Excel expert David H. Ringstrom, CPA shows you how to build upon the techniques in part 1 to create consolidating financial statements that can display results for an individual entity or roll up a group of entities. You'll also see how to use dynamic array worksheet functions in Microsoft 365 to create dynamic financial statements that automatically add or remove accounts by using worksheet functions such as FILTER and VSTACK. David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 and earlier) during the course as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the course. Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel 2021, Excel 2019, and so on.

Publication Date: August 2023

Designed For
Practitioners who would benefit by creating and working with monthly financial statements in a more efficient and effective manner.

Topics Covered

• Adding a refinement to the SUMIFS function to allow the report to consolidate numbers or display departmental values
• Consolidating financial statement exports from two or more entities by appending queries within Power Query
• Determining if your version of Excel is perpetually licensed or part of the Microsoft 365 platform, which periodically adds new features to Excel
• Displaying subsets of data dynamically by way of the new FILTER worksheet function
• Stack different ranges of cells vertically or horizontally with the VSTACK and HSTACK functions

Learning Objectives

• State the arguments used in Excel's VSTACK function
• Identify which command in Power Query enables you to return a data set back to an Excel workbook or data connection
• Recognize which version(s) of Excel offer the FILTER function
• Identify on which ribbon tab the Conditional Formatting feature appears on
• Identify one of the uses of Data Validation

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Experience working with Excel spreadsheets recommended.

Advance Preparation
None