Financial Reporting Tips and Tricks (Completed)

Financial reporting can be fraught with repetitive tasks and often the risk of misstated numbers. In this webcast, 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. You’ll see how to use Data Validation to provide a user interface for specifying a reporting period. The report itself uses functions such as OFFSET and SUMIF to extract data from accounting reports and to present the data in any format you need. You’ll see how to make the report be self-updating after you save over an existing accounting export file, and also how to build in check figures and alarms to notify you if a report doesn’t tie back to its source.

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.

Who Should Attend
Practitioners who would benefit by creating and working with monthly financial statements in a more efficient and effective manner.

Topics Covered

• Understanding the use of the MONTH function to return the month portion of a date or month name
• Building and exporting a 12-month Profit & Loss report from QuickBooks Online and QuickBooks Desktop as a model for what to look for when exporting from other applications
• Adding a refinement to the SUMIFS function to allow the report to consolidate numbers or display departmental values
• Seeing how to use the Trusted Document feature in Excel 2010 and later to suppress the Data Connection security prompt
• Discovering how to incorporate Check Figures and Alarms into your work
• Using the Group command to interactively hide/unhide columns (and/or rows) within Excel worksheets
• Using the SUMIFS function to sum values for a single account or a range of account numbers
• Creating an in-cell list by way of Excel's Data Validation feature
• Creating self-updating financial spreadsheets by using PowerQuery/Get & Transform in Excel 2010 and later to pull data via automated queries that also overcome common issues in exported reports
• Building consolidated financial reports by using PowerQuery/Get & Transform in Excel to combine reports for two or more entities into a single data source

Learning Objectives

• Describe how to create accounting reports for any month of the year on a single worksheet
• Recognize and apply Conditional Formatting to draw attention to reports that don't balance to the source data
• Describe how to export data from an accounting package to create a "set-and-forget" link to accounting data in Excel

Level
Basic

Instructional Method
Group: Internet-based

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

Program Prerequisites
None

Advance Preparation
None