Get ahead before the holidays. Individual webinars are $149 throughout October. *Pricing automatically applied in cart.

Excel Agility: Financial Statement Automation Techniques

Access this expert-led webinar instantly, available anytime on-demand.

Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00 $149.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

In this insightful session, author and 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. This provides a powerful alternative to building out a separate worksheet for each reporting period and provides easier maintenance and better data integrity. You'll see how to use Power Query to create a self-updating connection to a twelve-month financial statement that you export from your accounting software. You'll also see how to use Data Validation to create a drop-down list for specifying a report period, and how to use the SUM, OFFSET, and SUMIFS functions to summarize data into detailed or roll-up report formats.


 

David is theauthor of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’sHidden Treasures”, and has written or co-authored six other books. Hedemonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019, or 2016 during the presentation and in his detailed handouts. The handouts include anExcel workbook with most of the examples he uses during his demonstrations.


Excel for Microsoft 365 is a subscription-based product that receives periodic featureupdates. Conversely, perpetually licensed versions have year numbers in theirnames and do not receive any feature updates.


Who should attend:


Professionals seeking to create financial reporting spreadsheets that are easier to use and have improved data integrity.



Topics typically covered:


• Suppressing the External Data securitywarning on a workbook-by-workbook basis.


• Creating a workbook with just twoworksheets that will present data for any month of the year.


• Understanding the use of the MONTHfunction to return the month portion of a date or month name.


• Using the OFFSET function todynamically reference data across different accounting periods.


• Automating the extraction of data for agiven month or year to date by way of the OFFSET function.


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


• Building and exporting a 12-monthProfit & Loss report from QuickBooks Online and QuickBooks Desktop as amodel for what to look for when exporting from other applications.


• Filtering unwanted data out of PowerQuery results.


• Improving the integrity of spreadsheetsby using SUMIF to look up values in a more flexible fashion than VLOOKUP.


• Using the SUMIFS function to sum valuesbased on multiple criteria.


• Using the SUMIFS function to sum valuesfor a single account or a range of account numbers.


• Creating self-updating financialspreadsheets by using Power Query pull data via automated queries that alsoovercome common issues in exported reports.


Learning objectives:

• Name what the SUMIFS function returns if a match cannot be found.

• Identify one of the uses of Data Validation.

• State which column the OFFSET function would return data from.

Level: Basic
Format: Live Webcast
Instructional Method: Group Internet Based
NASBA Field of Study: Computer Software & App (2 hours)
Program Prerequisites: None
Advance Preparation: No
  1. Introduction
  2. Topics at a Glance 00:01:50
  3. Presenting with Microsoft 365 for Windows  00:04:33
  4. Section 1: Formulas and Functions 00:06:09
  5. SUM/OFFSET Functions 00:07:38
  6. Using the SUMIF Function 00:14:02
  7. SUMIFS with Two Range Criteria 00:22:39
  8. Section 2: Report Setup 00:29:19
  9. QuickBooks Online 12 Month P&L 00:30:06
  10. Creating the Report Workbook 00:39:43
  11. Building the Report Worksheet 00:41:59
  12. Section 3: Power Query Integration 00:46:00
  13. Using Power Query to Link to P&L (1/6) 00:47:04
  14. Using Power Query to Link to P&L (2/6) 00:48:33
  15. Using Power Query to Link to P&L (3/6) 00:50:04
  16. Using Power Query to Link to P&L (4/6) 00:56:15
  17. Using Power Query to Link to P&L (5/6) 00:58:46
  18. Using Power Query to Link to P&L (6/6) 01:02:05
  19. Set Power Query Auto-Refresh 01:08:19
  20. Data Connection Security Prompt 01:13:24
  21. Section 4: Data Validation 01:14:47
  22. Data Validation List of Report Periods 01:15:09
  23. OFFSET Function for Current Month 01:18:34
  24. Section 6: Formula Implementation 01:22:15
  25. OFFSET Function for Current Month 01:22:41
  26. OFFSET/SUM for YTD Totals 01:27:33
  27. SUMIFS Function for P&L Report 01:30:11
  28. SUMIFS Function for P&L Report 01:35:24
  29. What We Covered 01:38:17
  30. Now It’s Your Turn—But I’m Here If You Need Me! 01:39:37
  31. Presentation Closing 01:39:55
  • David H. Ringstrom, CPA

    David H. Ringstrom, CPA

    David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in E [...]

    View Full Profile

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or [email protected].

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.

  • Cell 00:07:44, 01:09:27, 01:18:51, 01:30:44
  • Column 00:08:27, 00:09:17, 00:15:03, 00:31:38, 00:42:06, 00:48:36, 01:02:29, 01:23:12
  • Data Validation 00:03:26, 01:15:00
  • Dialog Box 00:39:30, 00:47:15, 00:58:39, 01:02:38, 01:07:41, 01:15:22
  • Dynamic Range 0:08:18
  • Filter 00:58:23
  • Financial Statement 00:00:07
  • Formula 00:02:08, 00:03:41, 00:15:16, 00:23:57, 01:19:23, 01:2
  • Function 00:02:13, 00:05:02
  • LOOKUP 00:15:51
  • Microsoft 365 00:04:34, 00:05:26
  • Name Box 01:19:29
  • OFFSET 00:08:17, 00:16:23, 01:22:16, 01:27:42
  • Power Query 00:02:36, 00:05:56, 00:45:06, 00:48:49, 00:59:19, 01:07:58, 01:15:32
  • Profit and Loss Statement (P&L) 00:29:31, 00:31:17, 00:40:39, 01:15:12
  • QuickBooks Online 00:01:07, 00:29:26, 00:30:06, 00:45:43
  • Range Names 01:21:42
  • Refresh 01:09:06
  • Row 00:08:55, 00:14:59, 00:50:40, 01:23:17
  • Spreadsheet 00:01:29, 01:08:28
  • SUM 00:08:18, 00:16:23, 00:23:02, 01:22:22, 01:27:39
  • SUMIF 00:09:39, 00:14:03, 01:22:29
  • SUMIFS Function 00:09:48, 00:22:46, 01:22:48
  • VLOOKUP 00:16:41
  • Workbook 00:24:54, 00:29:51, 00:40:31, 00:47:13
  • Worksheets 00:25:00, 00:30:01, 00:47:30, 01:03:47
  • XLOOKUP 00:16:43

Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

Data Validation : An Excel feature that allows users to assign data entry rules to one or more cells within an Excel worksheet.

Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.

Dynamic Range: Dynamic ranges are also known as expanding ranges - they automatically expand and contract to accommodate new or deleted data.

Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.

Financial Statement: Financial statements (or financial reports) are formal records of the financial activities and position of a business, person, or other entity. ... A balance sheet or statement of financial position, reports on a company's assets, liabilities, and owners equity at a given point in time.

Formula: A formula is an expression which calculates the value of a cell.

Function: Functions are predefined formulas and are already available in Excel.

LOOKUP: The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel.

Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.

Name Box: The Name Box is the box to the left of the formula bar that displays the cell that is currently selected in the spreadsheet. If a name is defined for a cell that is selected, the Name Box displays the name of the cell. You can use the Name Box to define a name for a selected cell as well.

OFFSET: A worksheet function that allows users to create dynamically sized ranges for use within other worksheet functions, such as SUM or SUMIF.

Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.

Profit and Loss Statement (P&L): A financial statement that is used to summarize a company’s performance and financial position by reviewing revenues, costs and expenses during a specific period of time, such as quarterly or annually.

QuickBooks Online: QuickBooks Online is our cloud-based subscription service that gives you the freedom to work from any device — anytime, anywhere.

Range Names: A user-defined identifier for a cell or block of cells. Range names simplify formula writing and auditing, as the words SalesTax are readily identifiable within a formula as opposed to $B$1. Range names cannot contain spaces; must begin with a letter; be no longer than 255 characters in length; and can be comprised of only letters, numbers, underscores, periods, and slashes.

Refresh: The Refresh command appears on the Options tab of Excel 2007 and 2010 as well as the Analyze tab of Excel 2013. Pivot tables store a snapshot of the underlying source data, so they don’t immediately reflect changes to said data. You must periodically refresh any pivot table to ensure it reflects any changes to the source data.

Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.

SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet.

SUMIF: A look-up function in Excel that allows you to add up numbers based upon a criterion that you specify. Unlike VLOOKUP, the SUMIF function can add up two or more values and returns zero (instead of #N/A) if no match is found.

SUMIFS Function : A look-up function in Excel that allows you to add up numbers based upon up to 127 criteria that you specify. Unlike VLOOKUP, the SUMIFS function can add up two or more values and returns zero (instead of #N/A) if no match is found.

Spreadsheet: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.

VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.

Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.

Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

XLOOKUP: The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. Where a valid match is not found, return the [if_not_found] text you supply.

Customer Satisfaction Guarantee
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

Frequently Asked Questions

Automating financial statements in Excel involves connecting data sources to output reports so that statements update with minimal manual effort. The most common approach is linking cells across worksheets—pulling trial balance data from one tab into an income statement or balance sheet template on another. Using named ranges and structured table references makes these links more readable and resilient to row insertions or deletions. Power Query automates the extraction and transformation of raw data exported from accounting software, refreshing source data with a single click. Macros (VBA) can handle repetitive tasks like formatting, copying values, or generating period-end reports automatically. Combining Power Query for data preparation with well-structured formulas in the report template creates a nearly fully automated workflow. Finance professionals who implement these techniques reduce month-end close time significantly and eliminate manual data re-entry errors—a major advantage available through Aurora Training Advantage's Excel Agility training series.
Several Excel functions are indispensable for financial statement automation. SUMIF and SUMIFS aggregate account totals based on criteria like account code or department, replacing manual subtotal entry. VLOOKUP or the more flexible XLOOKUP retrieves account descriptions or balances from a reference table by account number. INDEX/MATCH offers a powerful two-way lookup for complex mapping scenarios. IFERROR wraps formulas to prevent #N/A or #REF! errors from disrupting reports when source data is incomplete. INDIRECT allows dynamic sheet references—useful for multi-period reports that reference different monthly tabs by name. For investment analysis embedded in financial models, NPV and IRR calculate returns directly within the workbook. Mastering this function toolkit is the foundation for accountants and finance professionals migrating from manual spreadsheets to automated reporting. Aurora Training Advantage's Excel Agility series provides structured, practical instruction on applying these functions in real-world financial reporting contexts.
Power Query transforms the often tedious process of preparing financial data for reporting. Rather than manually copying and cleaning data exported from accounting systems each month, Power Query connects directly to the source file and applies a saved set of transformations—removing unwanted columns, renaming headers, filtering accounts, and reshaping data—automatically each time you click Refresh. This eliminates repetitive cleanup work and ensures consistent results regardless of who runs the process. Power Query can combine data from multiple sources, such as multiple export files or different fiscal periods, into a unified dataset feeding your financial statement templates. It also handles common issues in accounting exports such as extra header rows, merged cells, or inconsistent date formats. For finance teams producing monthly or quarterly reports, Power Query is the single highest-leverage tool for reducing manual effort while increasing accuracy, consistency, and auditability of financial statement preparation.
Yes, Excel macros written in VBA can automate virtually any repetitive formatting or distribution task in financial reporting. A macro can apply consistent number formats, bold header rows, adjust column widths, and add borders across multiple sheets in seconds—tasks that would take considerable manual time. For period-end reporting, a macro can copy a completed report tab to a new workbook, save it with a date-stamped filename, and prepare it for email distribution. Macros can loop through a list of departments or cost centers and generate individualized report tabs or files from a single source dataset. For teams distributing reports to multiple stakeholders, macros eliminate the risk of accidentally including source data or internal calculation sheets in the output file. While Power Query handles data transformation, macros excel at output formatting and file management—together, they create a comprehensive automation solution for recurring financial reporting workflows in Excel.
A rolling 12-month financial model always shows the most recent 12 periods without manually shifting data or rewriting formulas. The key is using dynamic column headers based on TODAY() or a reference date cell—formulas like EOMONTH(reference_date,-11) through EOMONTH(reference_date,0) generate correct month labels automatically. OFFSET or INDEX with MATCH on the date headers retrieves the correct period's data from your source table as months roll forward. Power Query can source and structure the underlying actuals data so a single Refresh pulls in the latest month's figures. Named ranges or Excel Tables anchored to your data source ensure formulas need no adjustment as new columns are added. For FP&A professionals presenting to leadership, a well-built rolling model eliminates end-of-month scramble to update reports, making financial storytelling more consistent and dependable. Aurora Training Advantage's Excel Agility series covers these automation techniques with practical, step-by-step instruction.