Automating Financial Statements

Astute Excel: Automating Financial Statements

Presented by David H. Ringstrom, CPA

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.

Topics Covered:

• Creating an in-cell list by way of Excel’s Data Validation feature.

• Improving the integrity of spreadsheets with Excel’s VLOOKUP function.

• Discovering how to incorporate Check Figures and Alarms into your work.

• Overcoming VLOOKUP’s quirks by using SUMIF to look up numeric values.

• Using Excel’s VLOOKUP function to look up an item description based on an input provided by the user.

• Using Conditional Formatting to draw attention to reports that don’t balance to the source data.

• Learning a simple design technique that greatly improves the integrity of Excel’s SUM function.

• Learning why, in many cases, you should export reports intended for spreadsheet analysis to a .CSV file instead of an Excel workbook.

• Seeing how to use the Trusted Document feature in Excel 2010 and later to suppress the Data Connection security prompt.

Learning Objectives:

• State what object type Microsoft Query considers worksheet tabs within Excel workbooks to be.

• Recall how to determine if you’re using the Microsoft 365 version of Excel.

• Demonstrate knowledge of how and when to use the Name Manager command.