Ascending Excel: Advanced Financial Reporting
Duration: 90 Minutes
Faculty: David Ringstrom Course ID: 1046 Level: Intermediate
In this presentation Excel expert David H. Ringstrom, CPA will walk you through creating a dashboard to facilitate analyzing a general ledger report. David will begin with showing how to create a self-updating dashboard by using Microsoft Query to automatically pull data from an Excel workbook that you can overwrite each month. He’ll then show you how to use pivot tables to create different summary views of your data. You’ll be able to control these pivot tables simultaneously with the Slicers feature in Excel 2013 and later. Other topics covered include pivot charts, self-updating chart titles, and creating waterfall charts in Excel to illustrate a profit and loss or income statement. The presentation closes with a discussion of sparklines and conditional formatting, which are two other tools commonly used in Excel dashboards.
- Recall the Excel menu where the Microsoft Query command resides
- Identify the Excel feature that causes Sparklines to automatically display additional data points added to the source data
- Identify the command that enables you to connect two or more pivot tables to a Slicer
Areas Covered in the Session:
- Adding interactivity to pivot tables by using the Slicer feature for filtering in Excel 2010 and later
- Assembling a dashboard from multiple pivot tables
- Avoiding the dreaded PivotTable Field Name Already Exists error prompt
- Creating self-updating chart titles that change automatically as you filter or slice data related to the chart
- Displaying two or more pivot tables close together on a single worksheet without triggering a conflict
- Duplicating Excel worksheets in two different ways
- Eliminating the risk of workbook links by using Microsoft Query to get data from one workbook into another
- Filtering two or more pivot tables simultaneously by way of the Slicer feature in Excel 2010 and later
- Illustrating financial statements with the Waterfall chart in Excel 2016 and later.
- Jump-starting data visualization with the Quick Analysis feature.
- Managing information overload by creating a Top 10 pivot table.
- Minimizing visual clutter by hiding worksheet grid lines.
- Presenting the largest or smallest values in chart form by way of a Top 10 pivot chart.
- Preventing pivot tables from automatically resizing columns when you refresh or filter the data.
- Understanding the conflict that a linked picture overlaying a slicer can pose.
- Understanding the conflicts that can arise when you position two or more pivot tables too close in proximity to each other.
- Using Excel’s PivotTable feature to condense large amounts of information into manageable chunks.
- Using linked pictures as a navigation tool to return to the source pivot table.
Who Will Benefit:
- CEO’s / CFO’s / CTO’s
- Tax Personnel
- Enrolled Agents
- Financial Consultants
- Banking Professionals