On Demand Webinar
Webinar Details $219
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Software, Taxation and Accounting
- Credit: ATATX 1.5, CPE 2.0
-
In this comprehensive course, a follow-up to QuickBooks/Excel Data Analysis Part 1, expert David Ringstrom, CPA, demonstrates even more ways you can analyze data from QuickBooks Desktop and QuickBooks Online. Among other topics, David covers how to flatten multiple-column reports back into a list format for further analysis, how users of Excel 2016 and later can visually present a summary profit and loss report in chart form by way of Waterfall charts, how users of Excel 2013 and later can implement the Bing Maps add-in to transform summary data into interactive maps, and how users of earlier versions of Excel can clean up their QuickBooks reports.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to 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.
Who Would Be Interested in This Course:
Practitioners who wish to expand their ability to analyze data from QuickBooks Desktop and QuickBooks Online.
Your Benefits of Attending:
- Mapping sales by city in Excel 2013 and later with Bing Maps.
- Understanding how to manage the Protected View prompt in Excel 2010 and later.
- Using Slicers in Excel 2013 and later to filter data within reports exported to Excel from QuickBooks.
- Streamlining the process of building Waterfall charts from QuickBooks Online Profit & Loss reports by way of the SUMIF function.
- Exporting customer contact lists from QuickBooks Online and QuickBooks Desktop.
- Flattening the Profit & Loss by Class report to unlock the data for analysis by way of pivot tables, filtering, and other tools in Excel.
- Filtering transactions from QuickBooks reports by date in any version of Excel.
- Utilizing the Timeline feature in Excel 2013 and later to filter pivot tables based on date ranges.
- Choosing the most analysis-ready report format from QuickBooks Desktop and QuickBooks Online.
Learning Objectives:
- Identify QuickBooks features and techniques that can be used to streamline reports for faster analysis in Excel.
- Recall how to apply Trace Dependents and Trace Precedents while auditing formulas.
- Recall the keystroke that enables you to select two or more non-adjacent items from a Slicer.
Level:
Intermediate
Format:
Self-Study
Instructional Method:
On-Demand Webcast
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Practitioners who have completed QuickBooks/Excel Data Analysis Part 1 or have comparable experience with QuickBooks and Excel.
Advance Preparation:
None
Table of Contents
Introduction
Exel Versions 00:00:46
Export Contact Lists 00:03:25
Protected View Settings 00:12:48
Mapping Customer Addresses (Excel 2013+) 00:16:19
Mapping Sales by City (Excel 2013+) 00:23:22
Mapping Sales by City (Excel 2013+) (cont.) 00:25:52
Mapping Sales by City (Excel 2013+) (cont.) 00:27:55
Flattening Quickbooks Reports 00:32:42
Flattening Quickbooks Reports (cont) 00:36:25
Flattening Quickbooks Reports (cont) 00:39:33
Flattening Quickbooks Reports (cont) 00:44:45
Flattening Quickbooks Reports (cont) 00:47:21
Flattening Quickbooks Reports (cont) 00:49:28
Flattening Quickbooks Reports (cont) 00:51:26
Slicing Quickbooks Data (Excel 2013+) 00:52:18
Slicing Quickbooks Data (Excel 2013+) (cont.) 00:55:41
Exporting Transaction Details Report 00:59:16
Timeline Feature (Excel 2013+) 01:02:17
Filtering by Date Range 01:08:07
Creating a Waterfall Chart (Excel 2016+) 01:10:44
Creating a Waterfall Chart (Excel 2016+) (cont.) 01:14:11
Creating a Waterfall Chart (Excel 2016+) (cont.) 01:18:00
Quickbooks Online Waterfall Prep (Excel 2016+) 01:19:51
Quickbooks Online Waterfall Prep (Excel 2016+) (cont.) 01:22:03
Separating Chart of Accounts 01:25:46
Separating Chart of Accounts (cont.) 01:28:36
Separating Chart of Accounts (cont.) 01:30:11
Separating Chart of Accounts (cont.) 01:33:21
Reconstituting General Leger 01:33:36
Reconstituting General Leger (cont.) 01:39:54
Reconstituting General Leger (cont.) 01:43:44
Conclusion 01:47:15
Index
- Bing Maps 00:16:47, 00:28:11
- Concatenation 00:05:41
- Filter 00:26:25
- General Ledger Report 01:33:49
- Pipe symbol 00:40:31
- Pivot Table 00:26:47, 00:40:19
- Protected View 00:12:54
- QuickBooks Desktop 00:04:19, o1:00:38
- QuickBooks Online 00:05:08, 01:01:21
- Reports 00:04:20, 00:23:24
- Slicer Feature 00:53:01
- SUMIF 01:22:05
- Table Feature 00:48:05
- Text to Columns Wizard 01:26:52
- Timeline 01:02:53
- Total Row 00:53:52
- Waterfall Chart 01:10:53
Bing Maps: The Bing Maps app for Office helps you use location data from a given column and plot it on a Bing Map. It also provides basic data visualization using your location data.
Concatenation: A technique that allows you to join two or more pieces of text together. Although its simplest to use the ampersand (&), you can also use the CONCATENATE function in Excel.
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.
General Ledger Report: The General Ledger report will show you the beginning balance, transactions and total for each account in your Chart of Accounts in QuickBooks Online (QBO).
Pipe symbol: Alternatively referred to as a vertical bar, the pipe is a computer keyboard key "|" that is two vertical lines above one another and commonly looks like a full vertical line. This symbol is found on the same United States QWERTY keyboard key as the backslash key.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
Protected View: To help protect your computer, files from these potentially unsafe locations are opened as read only or in Protected View. By using Protected View, you can read a file and see its contents and enable editing while reducing the risks.
QuickBooks Desktop: QuickBooks Desktop is a one-time purchase accounting software to be installed on your office desktop computer.
QuickBooks Online: QuickBooks Online is our cloud-based subscription service that gives you the freedom to work from any device — anytime, anywhere.
Reports: A report is a set of instructions that the program uses to display data from your company file. All available reports can be accessed through the QuickBooks Reports menu.
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.
Slicer Feature: A visual filtering feature available with pivot tables in Excel 2010 and tables and pivot tables in Excel 2013.
Table Feature : The Table feature in Excel 2007 and later is an improvement on the List feature in Excel 2003 and earlier. The Table feature provides enhancements that make it much easier to analyze lists of data.
Text to Columns Wizard: An Excel feature which allows users to separate data from a single column within an Excel spreadsheet into two or more columns, or to remove unnecessary data from within a column.
Timeline: A visual filtering feature in Excel 2013 that enables you to filter data based on varying date ranges.
Total Row: A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.
Waterfall Chart: An Excel 2016 chart that can distinguish how amounts contribute to a bottom-line figure, which is useful in illustrating certain financial statements. A waterfall chart can show total revenue, and then correspondingly how COGS, expenses, and other amounts compare in scale to net income. Waterfall charts are not visible when a workbook is opened in an earlier version of Excel.
