Automating Excel-based Financial Statements (Video) (Course Id 2771)

• Who is this course for?
  This course is for CPAs, accountants, and other professionals who need Continuing Professional Education (CPE) and have prior experience with Microsoft Excel.

• What is this course about or what problem does this course solve?
  The course teaches how to automate Excel-based financial reports for any month of the year using a single worksheet, improving efficiency, data integrity, and ease of maintenance.

• How can the knowledge from this course be used?
  Participants can use the techniques learned to streamline financial reporting by connecting Excel to exported data, using dynamic formulas, and adding drop-downs for easier period selection.

• Why is this course important to a CPA or Accountant?
  It equips CPAs and accountants with practical Excel automation skills that reduce manual work, enhance report accuracy, and make financial statement preparation more efficient.

• When is this course relevant or timely?
  The course is especially relevant when recurring monthly or quarterly financial reports are required, offering a more scalable and maintainable approach to financial data analysis.

• How is a course like this consumed or used?
  This video-based course is delivered via QAS Self Study and includes demonstrations, downloadable Excel workbooks, and a final exam to be completed online for CPE credit.

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 is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Video - NASBA QAS - NASBA Registry - 2771

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

Chapter 1 - Automating Excel-based Financial Statements

Chapter 1 Review Questions

Key Terms