Excel Accountant: Automating Financial Statements

Learning Objectives

• To recognize what object type Microsoft Query considers worksheet tabs within Excel workbooks to be.
• To recall how to determine if you’re using the Office 365 version of Excel.
• To demonstrate knowledge of how and when to use the Name Manager command.
• To explore Excel Query and modify an Excel Query
• To identify how to consolidate multiple entities

Course Description

Traditionally spreadsheets or Excel sheets have been the dominant way of managing many business processes. This covers everything – finance to employee schedule and beyond. Despite the contemporary times of AI, BIG data, and cloud computing. However, the seemingly long and endless process can be made short and easy with Excel automation.

Excel expert David Ringstrom, CPA, will teach you how to create dynamic financial reports—for any month of the year—on a single worksheet. Many Excel users build a worksheet for each month of the year, which can be inconvenient and tiresome to revise. Rather than using such a cumbersome method, David demonstrates how to apply the VLOOKUP, OFFSET, and SUM functions to quickly create accounting reports that allow you to toggle to any reporting period with just a couple of mouse clicks. In addition, David shares effective ways to export data from your accounting package so you can create a “set-and-forget” link to your accounting data in Excel.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

Office 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 2019, Excel 2016, and so on.

This course covers the following Major Topics: 

• QuickBooks Desktop 12 Month P&L
• Creating the Report Workbook
• Introducing Microsoft Query
• Looking Within an Excel Workbook
• Choosing Columns
• Returning Data to Microsoft Excel
• Modifying an Excel Query
• Helper Formulas
• Creating an In-Cell List of Report Periods
• MONTH function
• Data Worksheet: Current Month Column
• Data Worksheet: YTD Column
• Report Buildout
• Consolidating/Departmental Report
• Integrating Check Figures
• Check Figure Alarm
• Group/Ungroup Columns
• Data Connection Security Prompt
• Swapping Out Source Documents
• Consolidate Multiple Entities

You may explore other Excel CPE Courses.

Overview

• Do You Have Office 365?

  1 mins
• Source Data Exported from QuickBooks

  3 mins
• QuickBooks Desktop 12 Month P&L

  6 mins
• Creating the Report Workbook

  17 mins
• Modifying an Excel Query

  35 mins
• MONTH function

  57 mins
• Report Buildout

  70 mins
• Integrating Check Figures

  82 mins
• Check Figure Alarm

  85 mins
• Swapping Out Source Documents

  99 mins
• Consolidate Multiple Entities

  100 mins