Excel Insights: Automating Financial Statements
11:30 AM ET | 08:30 AM PT | 10:30 AM CT Duration 90 Minutes
Webinar Includes : All the training handouts , Certificate by the Speaker ,Q/A and 90 Min Live Webinar
"Hear From David H. Ringstrom, CPA he is president and founder of Accounting Advisors, Inc."
Exporting reports for spreadsheet analysis
In this presentation, Excel expert David Ringstrom, CPA, demonstrates how you can automate and improve the integrity of financial statements that you must maintain in Microsoft Excel. Accounting software programs often don't allow specialized calculations or formatting. David will show you how to create financial statements for any month of the year on a single worksheet. You'll see how to use Microsoft Query to eliminate manual copying and pasting of data from your accounting software. Worksheet functions like OFFSET, SUMIF, and SUMIFS extract the data you need when you need it, while Data Validation enable you to provide the end user with a simple drop down list for choosing a financial period.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2019. He draws your attention to any differences in Excel 2016, 2013, or 2010 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.
• Modifying queries that return data from other sources into an Excel worksheet.
• Creating an in-cell list by way of Excel’s Data Validation feature.
• Understanding the nuances of using Microsoft Query to extract data from other Excel spreadsheets.
• Adding a refinement to the SUMIFS function to allow the report to consolidate numbers or display departmental values.
• Returning data to an Excel spreadsheet from Microsoft Query.
• Using the SUMIFS function to sum values for a single account or a range of account numbers.
• Understanding the use of the MONTH function to return the month portion of a date or month name.
• Creating a workbook with just two worksheets that will present data for any month of the year.
• Seeing how to use the Trusted Document feature in Excel 2010 and later to suppress the Data Connection security prompt.
• Building and exporting a twelve-month Profit & Loss report from QuickBooks Online and QuickBooks Desktop as a model for what to look for when exporting from other applications.
• Using the LEFT and VALUE functions to separate account numbers from account names when necessary.
• Using the Query Wizard within Microsoft Query to choose the relevant columns from your accounting report export.
• Apply a variety of Excel functions and features—all focused on helping you use Excel far more effectively.
• Define the best format for exporting reports for spreadsheet analysis.
• Recall how to use Microsoft Query to automate retrieving data from other spreadsheets.
Who will Benefit
• Income Tax Preparers
• Enrolled Agents
• Financial Consultants
• IT Professionals
• Human Resource Personnel
• Excel Users
• Government Personnel
Industries who can attend
• Accounting and Finance
• Excel Users
• Human Resources