In this valuable course, you’ll learn from Excel expert David Ringstrom, CPA, how to build effective, easy-to-use financial statements. As an alternative to building cumbersome accounting worksheets for each month of the year, David demonstrates how to use Excel functions, including VLOOKUP, OFFSET, and SUM, to quickly create accounting reports that allow you to switch to any reporting period with only two mouse clicks. In addition, he shows you how to export data from your accounting package, improve the integrity of your spreadsheets, incorporate Check Figures and Alarms into your work, and more.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He’ll draw to your attention any differences in Excel 2013, 2010, or 2007 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.

Areas Covered:-

• Modifying queries that return data from other sources into an Excel worksheet.
• Exploring the nuances of data exported from accounting programs, such as extraneous worksheets, blank columns, and extraneous rows.
• Using Conditional Formatting to draw attention to reports that don’t balance to the source data.
• Returning data to an Excel spreadsheet from Microsoft Query.
• Using the Query Wizard within Microsoft Query to choose the relevant columns from your accounting report export.
• Creating an in-cell list by way of Excel’s Data Validation feature.
• Adding a refinement to the SUMIFS function to allow the report to consolidate numbers or display departmental values.
• 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.
• Updating the financial report to use data for a new calendar or fiscal year.
• Extracting data dynamically for a given month or year to date by way of the OFFSET function.
• Using the SUMIFS function to sum values for a single account or a range of account numbers.
• Seeing how to use the Trusted Document feature in Excel 2010 and later to suppress the Data Connection security prompt.

Learning Objectives:-

• Identify how to apply a variety of Excel functions—all focused on helping you use Excel far more effectively.
• Recall the purpose of the MONTH function in Excel.
• Define the purpose of the OFFSET function in Excel.

Why Should You Attend:-

Practitioners who may benefit from creating monthly financial statements in Excel more effectively.